fbpx

How to create complex calculators in Excel and SharePoint

This is an amazing feature which is largely unknown. Just create desired calculations in Excel, publish to SharePoint and now, parameters can be changed on a browser page with automatic recalculation. ZERO programming!

Instant web calculators

The Need for web calculators

Often we need calculators for business usage. Banks and Insurance companies often have web based calculators for Loan Repayment Schedules, Insurance Premium calculations and so on.

Typically these calculators are originally created by someone using Excel. Unfortunately, Excel file cannot just be converted to a web page on the company web site. Therefore the Excel file is usually sent to some programmer. She will look at the functions used and the logic applied in Excel and re-write that using the web programming language. She will also create the textboxes on the web page where users can enter parameters like loan amount, amount to be insured, age, duration of loan, etc.

Finally, after due testing, the web page will be published by the programmer. This is a time consuming process.

The Solution

Solution is to publish the Excel file itself in such a way that it becomes a web page and allows desired parameters to be entered by the user.

This is exactly what is possible using Excel (2007 and above) and SharePoint (2007 and above). The steps are shown below.

Use the sample file. Notice that it has few formulas for calculating the monthly installment, interest and principle component.

image

The individual parameters have been given Names. This is done by clicking in the cell – Loan Amount cell for example and typing a name for it in the Cell Name area.

image

This has already been done. We have three named cells: Amount, Rate and Duration. Click in each cell to view the name. You can also see it in Formulas – Name Manager.

Right now, everything is working in Excel. But we want to publish it as a web page – without involving an IT person and without writing a single line of code.

Publishing to SharePoint

This works with all types of SharePoint (enterprise edition) or OneDrive (the free personal edition) and OneDrive for Business (which you get as a part of Office 365 subscription).

Here is how you do it.

Save the file to OneDrive. File – Share – Get Sharing Link (view only type)

image

Now paste the link in a browser and see what happens. You will see the entire sheet. However, as we have published it as a View only file, nothing can be edited.

We want people to edit only two parameters – the Loan Amount and Duration. How to get this done?

Browser View Options

For Office 2010 and 2013 choose File – Info – Browser View Options. For 2007, this appears as a button in the Save As dialog.

image

In the Browser View Options – choose the Parameters tab. You will see three parameters. Choose only Amount and Duration and click Ok.

image

Save the file and again try the browser view link.

Now something has changed. On the right side of the browser page, we see a small area where both parameters are shown with textboxes. Type a number and press Enter.

The calculation will now be refreshed based upon your input. Simple and Effective. If you leave one of the textboxes empty, it uses the previous value.

image

Using this method, any business calculator can be created quickly – by the domain expert – and published almost instantly for immediate use.

***

4 Responses

  1. the hyperlink below “Download sample file and follow along” points to a word document.

Queries | Comments | Suggestions | Wish list