Tables are powerful tools that allow analysis to be conducted quickly, and more easily. A Pivot Table is a strong tool in Excel that allows you to extract relevant data from large data sets with just a few simple clicks.
Usefulness of Pivot Tables
A Pivot Table is useful for financial analysts, project managers, auditors, sales analysts, finance controllers, human resources and many other fields where large data is processed. To insert a Pivot Table from an existing Excel table, do the following:
Click on one of the cells in the dataset
Find insert, then Pivot Table
Fill in the dialog box, and place the table in Existing or New worksheet
Drag the fields of the previous table, and drop in the new Pivot Table
Here are some of the many things you can implement with your Pivot Table:
1. Drill Down then Audit
With a Pivot Table in Excel, you can find out the data that results in a certain value. Double click the cell, and the values that make up the data open up in a new sheet.
You need to refresh whenever information in a data set gets updated. The three ways of doing this are as:
At the ribbon, go to - Pivot Table Tools - Options - Refresh
Right-click on the table, then choose Refresh
Any Pivot Table created with multiple fields comes with an automatic inclusion of a subtotal to the Group's top. You can change the subtotal to appear at the bottom if you prefer. Here is one you can implement.
1. Enter a minimum of two fields at Label Row
2. Go to Pivot Table Tools - design - subtotals
3. Make a choice in the three options shown
4. Report Layout
There are different Report layouts to choose from for a Pivot Table. These are Outline, Compact and Form. You can choose by going from Pivot Table Tools - design - Report Layouts. Each of these has advantages and disadvantages.
Compact layout Advantages:
Well optimized for readability.
Related data is kept in one color.
Copying and pasting the data to a different worksheet for analysis becomes difficult.
Outline layout Advantages:
Field headers in various columns.
It has “Repeat All Item Labels” functionality.
The Pivot Table data can be reused for analysis.
It presents a classic style for the Pivot Table.
It takes up a lot of horizontal space.
Tabular layout Advantages
Presents field headers in various columns
Has “Repeat All Items Labels”.
Shows data in the usual traditional form
Allows the data to be reused in a new location
Occupies a lot of horizontal space
Subtotal can’t appear at the group's top.
5. Change or make Count of, Sum Of
Having ‘Count Of’ instead of ‘Sum of’ happens for three reasons:
1. Presence of Blank cells in the values column in your dataset
2. You have text cells in the value column in the dataset
3. “A” values data field is grouped in the Pivot Table
To correct blank field:
1. Enter any value or Zero in the blank cell
2. On the Pivot Table, click ‘Count Of’ and drag it off the ‘Values’ location
3. Refresh Pivot Table
6. Number Formatting
Formatting is made much easier on an Excel Pivot Table by right-clicking a value, then number formatting. You can choose from data such as currency, number, or percentages, among others.
7. Formatting Error Values
Errors in pivot Table can be overwritten with text or a custom value. To implement this, right-click any value, choose Pivot Table and check the Errors Value Show box. The box gets activated this way. You are now free to enter a correct value.
8. Formatting Empty Cells
When data cells have empty cells, this can be as a result of the data source. This can be corrected on the data cell by entering a text or value in the blank space. Go to Pivot Table - Pivot Table Too - Options - Layouts and formats “Form” For empty cells show. You can now enter any text or value of your choice.
9. Retain Column Width on Refresh
It can be very frustrating when a column width changes after you’ve refreshed. Pivot Table has a way to avoid this. Right click within Pivot Table, choose Pivot Table and then uncheck the box in layout and format that says, “Autofill column width on update”
10. Display Report filter on Numerous Pages
With Excel Pivot table, it is possible to show Report filter on different sheets in the workbook. To do this, go to Pivot Table - Tools - “options” - options drop down - ‘show report’ filter pages.
A Pivot Table allows you to analyze data in different ways, making it the most essential feature in Excel. Using “Summarize Values By” tab, you can do all sorts like sum, average, count, Max, a Min, StdDev, Var and others.
12. Show or Make Unique Count
A significant feature added from Excel 2013 is the Unique Count feature. To implement this Go to Insert - Pivot Table. Check the “add this to the Data Model” box, and press OK
13. Percentage of Grand Total
There are many calculations made available in the SHOW VALUES option in the Pivot Table. ‘Percentage of Grand Total’ is one among many. This will help calculate data in number forms such as sales, and other quantifiable items.
14. Percentage of Column Total
Still under SHOW VALUES, the ‘Percentage of Column Total’ is another possibility. This will calculate values in a column.
15. Percentage of Row Total
SHOW VALUES also has ‘Percentage of Row Total’. It calculates the percentage of the rows as described.
16. Difference From
SHOW VALUES also has the ‘Difference’ calculation. It calculates the difference between values of different parameters, such as years and months.
17. Running Total In
Also referred to as the ‘Year to Date’ analysis, “Running Total In” calculates the collection of values in a given time period. It works by taking one time period after another, for a progressive addition. It can be in either percentage or number forms.
18. Group by Date
It is easy to present dates in groups with a Pivot Table. First, right-click the ‘Date’ values, whether in the column or at the rows. Choose ‘Group’. From here, you have the choice between, months, quarters and days.
19. Group by Years and Quarters
Pivot Table makes it possible to calculate sales, numbers and other variables by quarters. It is fast and easy to implement, without the fear of errors that manually extracted data can produce.
20. Sorting by Largest and Smallest
Pivot Table allows sorting of values in a variety of ways, such as choosing between small or large sizes. Right-click on a cell such as “Year”. Locate SORT, and then SORT NEWEST TO OLDEST.
21. Sort Using a Custom List
A custom list can be easily filled with personal data or lists. This could be a list that comprises team members at work, regions, countries and phone numbers. The objective of a custom list is to avoid repetition of work, and errors that occur manually.
22. Filter According to Dates
You can implement many date filters with Pivot Table. Filtering can be done by a given date category, whether by weeks, years or months. This is a useful function if you want to know what sales or transactions took place within a particular period.
23. Filter According to Values Such as Top 3 Products
A pivot table can be filtered to display top customers, best sales, and other variables. The top ten filters is a common usage among filter implementation.
24. Insert Slicers
Slicers are pictorial filters. They are a kind of interactive tool that allows you see what is filtered in the Pivot Table. Excel versions 2010 upwards have this feature.
24. Slicer Styles
There are various slicers available. The slicer styles add some color to your workbook. ‘Slicer styles’ allows you to add columns to the slicer.
25. Slicer Linking for Multi-Pivot Tables
Conventionally, slicer tables connect only to the Pivot Table it is inserted in. A feature called "Report Connection" allows connection with multiple tables, and can be implemented with Excel 2013 and 2016.
26. Different Methods to Filter a Slicer
The new feature in Excel called slicers is visual, and shows items filtered in the Pivot Table. It comes with different method of applying filters.
27. CTRL KEYBOARD
Multiple items can be selected by holding down the CTRL key, and selecting items with the mouse.
28. SHIFT KEYBOARD
You can select a slicer product by holding the SHIFT key and selecting another. This gives the possibility of selecting a range of products.
29. Creating Calculated Product
A Pivot Table enables calculating a list mathematically. There are various mathematical equations available such as +, %, and *.
30. Inserting a Pivot Chart
Pivot Charts are pivot table-s extension. It represents the values of the table in a graphical format. To insert, click Pivot Table - Pivot Table Tools - choose the Pivot Chart tab.
31. Pivot Chart and Slicer
When pivot slicers are inserted, it takes control of both the Pivot Chart and the Pivot Table. This is interactive, and a good analytical ability of the tool.
32. Using Conditional Formatting Guidelines.
Formatting based on specific parameters such as “If greater than Y" or "If less than X" format in this pattern. When cells are referenced with a particle criterion in such a manner, this is implemented likewise.
33. Directional Icons
These icons are helpful to show the variations from the sales of the previous month.
34. Data Bars, Icon Sets, and Color Scales
Introduction of data bars, scales and icon sets has brought improvement on conditional formatting. Data bars are graphical representations of the cell, with an equivalent value of the cell. Color scales include the background color equivalent to the value of the cell. Icon sets present icons in the cell. This is also proportional to the cell-s value.
This is a formula which extracts data stored within the Pivot Table.
36. Refresh All
When there are multiple Pivot Tables from the same data source, or a Pivot Table from varying data sources, the ‘Refresh All’ option works best to update the info.
37. Moving an Excel Pivot Table
If your Pivot Table is not exactly where you want it, it can be moved by using the ‘Move Pivot Table’ option. You can move upwards, downwards or any other direction.
Pivot Table is a useful addition to the Excel stable. It has brought ease and convenience to the manipulation of data. This tool can make data analysis quicker, easier, and more convenient.
Do you have more data manipulation methods to share? Let us know with a comment in the box below.