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.

 

pivot-table-excel


Set up


Formatting

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.


Filters:

To filter at the top of the sheet


Rows and Columns:

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


Values:

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

excel-text-to-columns


Remove Duplicates

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

 

excel-remove-duplicates


Transpose data

excel-transpose-text


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

Bitnami