This is the second part of my Excel series showing Pivot Tables as tool I use for ad hoc analysis along with my unpopular opinion, that Excel is underrated.
Deeper Analysis With Pivot Tables
Pivot tables allow you to organise and summarise your data without changing the source data itself.
Building a Pivot table takes under a minute and is an incredibly useful tool for aggregating and arranging your data without the need for time-consuming reports.
Highlight the sheet you want to use
Go to Insert Pivot Table on the ribbon
Select that you want it to be generated in a new sheet and let the wizard do the work
The Pivot Table field list appears on the right-hand side. You can now drag the fields to the different areas to summarise and organise your data.
To filter at the top of the sheet add the fields that you need.
Rows and Columns:
Drag and drop the fields you need to show as a row on the left-hand side of the grid and columns across the top.
By default the value you drop into the values area will show as a Count. By clicking and selecting ‘Value Field Settings’ you can show Sum, Average, % of column total and perform a calculation.
Big and small businesses use Excel because it’s easy to learn and allow analysts and stakeholders to speak the same language. If more sophisticated addons are required (Power BI, Power Pivot, Power Maps) these are open source and easy to use.
Excel offers functionality and plenty of bang for your buck with a usable interface and plenty of addons for scalability.