Home › Welcome to ExcelMonkey.org › Master Classes › How to get the best out of pivot tables › Other Features of Pivot Tables

Other Features of Pivot Tables

Other features of Pivot Tables

Automatically format all value fields

Any time you add a numeric field as a Value in a pivot table, you should set the number format directly on the field.

 You may be tempted to format the values you see in the pivot table directly, but this is not a good idea, because it’s not reliable as the pivot table changes.

Setting the format directly on the field will ensure that the field is displayed using the format you want, no matter how big or small the pivot table becomes.

For example, assume a pivot table that shows a breakdown of sales by Region. When you first add the Sales field to the pivot table, it will be displayed in General number format, since it’s a numeric field.

To apply the Accounting number format to the field itself:

  • Right-click on the Sales field and select Value Field Settings from the menu
  • Click the Number Format button in the Value field settings dialog that appears
  • Set the format to Accounting and click OK to exit

2. Add a field more than once to a pivot table

There are many situations when it makes sense to add the same field to a pivot table more than once. It may seem odd, but you can indeed add the same field to a pivot table more than once. For example suppose you have a pivot table that shows a count of employees by department.

The count works fine, but you also want to show the count as a percentage of total employees. In this case, the simplest solution is to add the same field twice as a Value field:

  • Add a text field to the Value area (e.g. First name, Name, etc.)
  • By default, you’ll get a count for text fields
  • Add the same field again to the Value area
  • Right-click the second instance, and change Show Values As to “% of Grand Total”
  • Rename both fields as you wish

3. Count the data first

When you first create a pivot table, use it to generate a simple count first to make sure the pivot table is processing the data as you expect. To do this,

  • simply add any text field as a Value field.
  • You’ll see a very small pivot table that displays the total record count, that is, the total number of rows in your data.
  • If this number makes sense to you, you’re good to go.
  • If the number doesn’t make sense to you, it’s possible the pivot table is not reading the data correctly or that the data has not been defined correctly.

4. Group a pivot table manually

Although pivot tables automatically group data in many ways, you can also group items manually into your own custom groups. For example, assume you have a pivot table that shows a breakdown of employees by department. Suppose you want to further group the Engineering, Fulfillment, and Support departments into Group 1, and Sales and Marketing into Group 2. Group 1 and Group 2 don’t appear in the data, they are your own custom groups. To group the pivot table into the ad hoc groups, Group 1 and Group 2:

  • Control-click to select each item in the first group
  • Right-click one of the items and choose Group from the menu
  • Excel creates a new group, “Group1”
  • Select Marketing and Sales in column B, and group as above
  • Excel creates another group, “Group2”

5. Show totals as a percentage

In many pivot tables, you’ll want to show a percentage rather than a count. For example, perhaps you want to show a breakdown of sales by product. But, rather than show the total sales for each product, you want to show sales as a percentage of the total sales. Assuming you have a field called Sales in your data, just follow these steps:

  • Add Product to the pivot table as a Row Label
  • Add Sales to the pivot table as a Value
  • Right-click the Sales field, and set “Show Values As” to “% of Grand Total”

6. Using a pivot table to link two files

Linking files in excel is a useful feature in excel but needs to be used with some degree of caution.

Links are usually created using formulas (there are a number of other ways that are not discussed here) and whilst this is fine for most formulas there are some that will only work when the source file is open – for example SUMIF(), SUMIFS(), COUNTIF(), etc. this can be frustrating. If the source file is shut then when you refresh the links Value# errors or similar are produced. A pivot table is an alternate way of achieving want a SUMIFS() formula will do and has the advantage of not falling over when you update links.

To create a linked pivot table:

  • Select the tab and cell in the file where you wish to see the pivot table
  • From the insert ribbon select ‘insert pivot table’
  • In the ‘insert pivot table’ dialogue box make sure the ‘table or range’ has a data range referring to the file with that contains the data.
  • Then complete the set up of the pivot table in the normal way (see the pivot table basics section if you are unsure how to do this)

Linking pivot tables is useful when you have a number of users who have files that all require the data same data set. This means that the data need only be refreshed once each time it needs updating, you ensure all users are working with the same set of data and that each user has their own file which can be password protected if required.  IN this situation its best to have all of the files in one network or SharePoint location.

%d bloggers like this: