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.
- 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
- 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
- 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.
- 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.
- Select the data you want to transpose
- Right-click where you want the data to go
- Paste Special (from the context menu)
- Select the Transpose option
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.