Excel Pivot Tables

Here is the second part of my Excel series showing the tools I use for ad hoc analysis and data cleansing.

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.


Set up

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.

Photo by from Pexels

By Helen Anderson

I’m passionate about technology and building data applications.