50 Possible Things You Can Implement Within Excel Pivot Tables

Publicerades den - Senast redigerad

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:

  1. Click on one of the cells in the dataset

  2. Find insert, then Pivot Table

  3. Fill in the dialog box, and place the table in Existing or New worksheet

  4. Click okay

  5. 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.

2. Refresh

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

  • Press ALT+F5

  • Right-click on the table, then choose Refresh

3. Subtotal 

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.

Disadvantages:

  • 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.

Disadvantages:

  • 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

Disadvantages:

  • 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.

11. Average

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.

35. GEPIVOTDATA

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. 

Upplagt 27 september, 2017

TomCoulter

Designer // Writer // Creative

Tom is a Design Correspondent for Freelancer.com. He is currently based in Melbourne and spends most of his non-work moments trying to find the best coffee.

Nästa artikel

3 Lesser Known SEO Mistakes That You May Be Committing