Excel Pivot Tables & Graphs
If you have to analyse data in your job then the chances are you’ll need to understand Excel Pivots and graphs. Once you get to grips with some simple core principles of use, it’s the quickest, clearest way to drill down on a detailed dataset and present your audience with some meaningful analysis that’s easy to digest in smaller subsets.
Summarising Data
You can use a PivotTable to summarize, analyse, explore, and present summary data. PivotCharts complement PivotTables by adding visualizations to the summary data in a PivotTable, and allow you to easily see comparisons, patterns, and trends. Both PivotTables and PivotCharts enable you to make informed decisions about critical data in your enterprise. You can also connect to external data sources such as SQL Server tables, SQL Server Analysis Services cubes, Azure Marketplace, Office Data Connection (.odc) files, XML files, Access databases, and text files to create PivotTables, or use existing PivotTables to create new tables.
Using Pivot Tables In Real Examples
If all of the above still sounds bit confusing, it may help to think of some real-world examples of where pivot tables will come in useful. The following scenarios explain how pivot tables extract the key information that is required:
1. Compare sales totals for different products.
Let’s say you have a worksheet that contains monthly sales data for three different products – product 1, product 2, and product 3 – and you want to know which is returning the highest sales value. You could, of course, look through the worksheet and manually add the corresponding sales figure to a running total every time each product appears until you have totals for all of them. All well and good when you have a relatively small set of data, but let’s position this with a sales worksheet that has thousands and thousands of rows. Manually sorting through them all is just not practical and would take you a lifetime! Using a pivot table, you can automatically aggregate all of the sales figures for each product and total up their sales value in less than a minute!
2. Show product sales as percentages of total sales.
Pivot tables be default show the totals of each row or column when created. But that’s not the only figure you can automatically produce.
For example, if you entered quarterly sales values for three separate products into an Excel sheet and turned this data into a pivot table, it would automatically display the three totals at the bottom of each column — having added up each product’s quarterly sales. If you wanted to find the percentage these product sales contributed of all company sales you can configure each column to give you the column’s percentage of all three column totals, instead of just the column total. If three product sales totalled £200,000 in sales, for example, and the first product made £45,000, you can edit a pivot table to instead say this product contributed 22.5% of all company sales.
To show product sales as percentages of total sales in a pivot table, simply right-click the cell carrying a sales total and select “Show Values As” > “% of Grand Total.”
3. Combine duplicate data.
In this example let’s say you have an excel report that has multiple instances of a single item. This may be the fact that you have sales data from each day of the week listed out as separate rows that you want to combine as a single weekly total. So in your spreadsheet, you have 7 separate instances of each individual product sales total. In order to get the correct weekly data, you need to combine the view totals for each of these duplicates.
That’s where the pivot table comes into play. Instead of having to manually search for and combine all the metrics from the duplicates, you can summarize your data (via pivot table) by product description, and the sales data from those duplicate days will be aggregated automatically.
Organising your data to get the desired summary profile
Now you have an idea of some simple scenarios, it’s time to look at your specific data and what you’re trying to achieve. Understanding which data to put where when building your pivot table is perhaps the biggest area to get on board with. For some time now Microsoft Excel actually creates a list of suggested pre-build pivot tables, so you don’t have to set them up from scratch. In many cases these are a great shortcut when you want a quick and simple cross section of your data. When we start to need more complex cross references or multiple variables, that’s when the custom build route becomes the best option.
Creating Pivot Tables from scratch
There are 4 simple steps to building Pivot tables:
Enter your data into a range of rows and columns.
Highlight your cells to create your pivot table.
Drag and drop or tick a field into the “Row Labels” area.
Drag and drop or tick a field into the “Values” area.
Fine-tune your calculations.
After you’ve completed Step 1, Excel will create a blank pivot table for you. Your next step is to drag and drop or tick a field — labelled according to the names of the columns in your spreadsheet – into the “Row Labels” area. This will determine what unique identifier – employee name, product code etc – the pivot table will organise your data by.
Once you’ve established what you’re going to organize your data by, your next step is to add in some values by dragging or ticking a field into the “Values” area.
The sum of a particular value will be calculated by default, but you can easily change this to something like average, maximum, or minimum depending on what you want to calculate.
In our specific course, we’ll take you through this and much more so you can confidently create your own pivot tables and graphs so that your colleagues and clients can consume a clear analysis of the information they need to run their business.