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

  • 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

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

  • Highlight the range or the whole column to be separated.
  • Make sure there is enough space to the right for the cells to split, if you have data here it will be overwritten.
  • On the Ribbon go to Data > Text to Columns and the wizard will open


The Wizard

  • Select Fixed Width if you are sure all the data is the same length or Delimited if there is a symbol or space you want to use to tell Excel where to split.
  • If you have chosen to split by a delimiter then select one or more delimiters to use like space. @ can be used to split an email address to show domain names in one column.
  • Click finish and you’re done.


Remove Duplicates

  • Select the range of cells or column
  • On the Data tab, click Remove Duplicates
  • Click OK
  • A message is displayed indicating how many duplicate values were removed and how many unique values remain, or if no duplicate values were removed.

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



Transpose data

  • Select the data you want to transpose
  • Right-click where you want the data to go
  • Copy
  • Paste Special (from the context menu)
  • Select the Transpose option
  • Done!


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