Excel Pivot Tables and Tools

This is the second part of my Excel series showing Pivot Tables and tools I use for ad hoc analysis and data cleansing 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


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

Rows and Columns:

Show rows on the left-hand side and columns across the top


By default will show the Count of that field but can be changed by clicking and selecting ‘Value Field Settings’ to show Sum, Average, % of column total and perform a calculation

Split a cell in two

Set up


The Wizard


Remove Duplicates

Bonus Tip: To do this in Google sheets uses =UNIQUE() to create a new list with a unique set of records.



Transpose data


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 rawpixel.com from Pexels


# #

September 8, 2018