In this article we will see how to link SharePoint 2013 data to Excel and create business intelligence visualisations in Excel. The scenario is that my business user wants to know how the profit margin varies when the rate of raw material increases over a period of time. In this case, the price that the business can levy to it's users is 60% of the raw material price with some profit margin. In this example, we have a calculated the field "PriceOfEndProduct" as =(Rate*0.06+2.15)*1.1+0.28. The data is in a SharePoint List as shown below.
Now to link this SharePoint List to Excel, you need to click on as in the following:List and "Export to Excel", as shown below.
You will get a prompt to Open or Save a owssvr.iqy file. Save this file and rename it to a proper naming convention. This is your connection file of SharePoint List data to Excel.
Start Excel then select "New File" --> "Data" --> qConnections --> "Add" --> then "Browse for More" as shown below.
Choose the .iqy file you've renamed. Click on "Properties" and change the refresh options as below.
Click on any cell and click on "Existing Connections" and choose the connection you've just created to get the SharePoint List data into your workbook.
Create a column % of Profit Margin in your Excel sheet to the column just next to the SharePoint data last column. After this step, a suitable chart can be selected to view the data.
Now every time data is added to the base SharePoint list, this chart will be updated. So a business user can use the ease of Excel to create graphic visualisations as required. The points to note is that not all columns need to be in a SharePoint base list. A business user can combine data from both sources.