What is a Pivot Table?

Pivot tables...

I'm sure its a phrase that spreads the fear of the unknown in offices all over the country, it certainly did with me before I decided to bite the bullet and familiarize myself with this alien function in Excel.

Pivot tables could be filed alongside other functions and applications that make you realize analysis that previously was too labour intensive to consider or even impossible is now available within a couple of clicks. Once you've cracked the basics of a pivot table, you'll be able to make decisions on massive data sets in minutes (or less).

What is a pivot table?

A pivot table is a tool that enables very large datasets to be manipulated and summarised very quickly and often without having to modify the original dataset. The pivot table exists alongside a data source and 'pulls' data from the source into the pivot table. It will not modify the original dataset in any way so it lends itself to trial and error learning.

Pivot tables in Excel consist of a couple of key elements, namely:

Filters

No different to filters in excel in any other instance, can be used to remove/omit data depending on certain criteria.

Columns

Columns (running up and down) configure the data in the vertical plane of the pivot table, the titles of each column is displayed in the uppermost row of the pivot table.

Rows

Same as Columns but for the rows (running across) of the pivot table.

Values

This could be considered as the 'result' of the chosen columns and rows.

Once you have created the pivot table, a column title from the original data source needs to exist in the Columns, Rows and Values boxes to display something meaningful.

To familiarise yourself with a pivot table, i'd highly recommend downloading a recent bank statement via your online banking (most provide CSV/similar downloads). One of the reasons I suggest this is because the data will usually be in tabular format which is essential for pivot tables. It also provides a 'real world' dataset that you will find familiar.

Once you've got your dataset and you've made your pivot table, experiment by dragging the column titles into the rows, columns and values boxes and you'll be summarising your bank transactions in no time.

Hopefully the information above has provided you with some guidance to help you with your analysis. If you've got any questions, or opinions feel free to get in touch!

@eddierthomas

Comments

How to combine multiple files with Power Query (with no VBA and just 10 mouse clicks!)

The need to combine information from multiple files is one that most users of Excel will have come across at some point in the use of Excel. I've personally spent far too many hours aggregating data from multiple files, that are identical in structure, so that I can analyse larger datasets and provide insights into products and processes. For anyone who has also done this and not yet discovered Power Query you'll probably be amazed how simple the process has become. I realise there might be some who will say "just use VBA, its easy once you learn how to code..." and they would be right. The method using Power Query provides a zero code solution that is an evolution of the Excel interface that many will already be familiar with. In this example, I've created a sample file and created a number of duplicates of the file which I've saved in a folder. The folder contains only these files and i'd recommend you do the same if you're looking to try out this pr...

Extracting data from Word (.docx) files into Power Query

Word and Excel don’t usually get along too well so it's no surprise that Power Query isn't directly compatible with its estranged cousin Word either. If you are presented with the need to import data from Word into Power Query you'll be please to hear it is possible however it requires a couple of manual steps to make it work. The manual steps could fairly easily be completed by a batch file which would automate the process further. Here is the Excel data pasted 'as values' in a Word file which i'll use for the first example Here is the Excel data pasted with 'keep source formatting' which i'll reference a couple of times in the article. Although the steps I've covered below aren't complex, this whole process has some unknowns around it so you may find the result in your instance varies from mine. The Word file I've used contains the contents of a range of excel cells that I deliberately pasted as values into Word to create a test file f...

How to automate the import of all files in a Google Drive folder to PowerBI, now updated, please read the first paragraphs!! (PowerQuery)

Update!!! This method no longer works although there is a new Google Sheets connector for PowerQuery that is currently in beta. If you're using PowerBI, you'll need to enable the preview features to enable it. https://docs.microsoft.com/en-us/power-query/connectors/googlesheets In the interest if demonstrating how it 'did' work, the original post is provided below. If you attempt to replicate this, you'll quickly realise the website doesn't behave as it used to. (here is the original article) Using PowerQuery to access multiple files within the same folder on a local or network drive is a game-changing feature that will almost certainly save many people hours of effort. This functionality is great if your data exists in a place that is easily accessible but what do you do if your data is somewhere less accessible like Sharepoint, OneDrive or even Google Docs? I have previously connected to data on SharePoint and found it fairly straight forward which raises the...

Red Gig

Search This Blog