Many companies are using Sharepoint (yours might be one of them). Sharepoint has been used to create applications, white-label social networking sites, and central repositories that increases interaction in a group or amongst different ones.

What if you were using to collect data and wanted to analyze and display it for others to view? After a couple of hours of searching the web and finding barely anything on how to do it, I decided to figure it out on my own. I decided to use Microsoft Excel because that is what I’m using at work, and I wasn’t sure if I would be able to open a Microsoft Access database at a different location. Tip: If you will be using a computer that doesn’t belong to you, you always want to go for the most widely-used application even if you can do more “damage” with a more robust one.

Sharepoint to Excel

These instructions work for Microsoft Excel 2007/2010. With a little tweaking, it might work with Excel 2003. While you are in Sharepoint, you will see an Action link at the top, click on it. When you do, a drop-down menu will appear with different actions that you can perform.  Click on the Export to Spreadsheet option. When the File Download pop-up window appears, click the OK button. Microsoft Excel will open up with a spreadsheet view of the data dump from Sharepoint.

The great thing is that it’s connected! All you have to do is go to Data->Refresh All, and the data will refresh with any changes done to the Sharepoint list. Another great thing that I need to share is that if you click Connections, you can have the data automatically refresh according to the time that you specify. Cool, huh?

Pivot Tables, Anyone?

After you have Sharepoint and Excel linked, you can start creating your dashboard to analyze the data. Choose the columns you want for the pivot table, and then go to Insert -> Pivot Tables. I recommend that you create the pivot tables and then from there, you can create the pivot charts. If you have ever seen IBM’s Cognos tool, this part is very similar to it. You just create the way you want your tables to look by drag-and-drop to build your report. You can select a column as a report filter, column label, row label, and/or value. Just play around with It until it looks the way you want it to. When you have it set, then create your chart.

Pivot Charts!

All you have to do is press the Pivot Chart button, and select the chart type. Voila! You might have to tweak the appearance of the chart and the axis. Make it pretty and you are ready to start analyzing. The best thing is that when the Sharepoint list refreshes, then so does the pivot tables and charts.

If you decide later that you want to use another chart type, you can. You don’t have to feel as though you are stuck using the one you initially chose.

Happy Analyzing!

Tags: excel dashboard, Microsoft Access, Microsoft Excel, Microsoft SharePoint, Pivot chart, PivotTable, Sharepoint, sharepoint export to excel, sharepoint lists