I have loads of data but making sense of it is proving a challenge.

Pivot tables.

Stop… don’t run away!!

Some people that come across Microsoft Excel’s Pivot table go all glassy eyed and run a mile in the opposite direction.

But seriously, if you have a lot of data to analyse, say a data dump from a data base, Pivot Tables are a fantastic tool.

Any well formed table of data can be knocked into shape using Pivot Tables.

  • Spot Trends
  • Find gaps in data
  • Identify top customers

If you think it sounds like Pivot Tables could help you get to grips with your mountain of data Contact us for a free initial discussion.

PowerQuery & PowerPivot.

These new(ish) Excel features take data analysis using Excel to a whole new level.

By making use of Excel’s PowerPivot Data Analysis Expressions (DAX) Language  you can import millions of rows of data from multiple data sources into a single Excel workbook and create complex reports to get the most from your raw data.

This Wikipedia article gives a techie overview of PowerPivot, you should contact us to discuss how it might be implemented to your benefit by your organisation.

As a simple example

The spreadsheet table below, is generated by connecting directly to a Wikipedia page listing the 50 US states.

The page is connected to Excel and the data is then manipulated using Excel’s PowerQuery & PowerPivot features to generate this new view.

The actual table in the spreadsheet (not the one below) will update whenever the Wikipedia* page is updated! Unbelievable!

*Thank you Wikipedia.

2 Comments on I have loads of data but making sense of it is proving a challenge.

  1. hey nice tutorial but i got one quetison and would be nice if you can give me the solution for it here is the quetison: is there any possible way to make self updateable pivot table like if i update something on main table to be visible in pivot table if so pls give answer on mail here tnx alot btw

    • Hi,

      You will have to use VBA to get a pivot table to self update. You can for example, set a timer to update the table at set intervals, update the table when the sheet is selected.

      If your source data is in the same workbook you can use VBA to update the pivot table when the data is updated.

      If your data is an external database you can’t force the pivot table to update when the date is updated.

      Hope this helps

Leave a Reply

Your email address will not be published.