Customize Excel’s pivot table


Excel is a common business tool that is used in an uncountable number of ways. Some spreadsheets are short, maybe one or two columns and 20 rows. Others can consist hundreds if not thousands of cells. When they get this big, it can be tough to quickly summarize or compare data. That’s where the pivot table comes in, unfortunately it isn’t perfect and may require a few modifications.

Here’s four ways you can modify Excel 2013 for Office 365′s pivot tables to meet your needs.

Make your tables look good

When you first create a new pivot table you may notice that the numbers aren’t formatted properly, or the table didn’t pull the format from the base spreadsheet. They may have too many decimal places, or are lacking currency figures. You can make edit this by:

  1. Selecting any of the numbers in the Value field.
  2. Clicking on the Analyze tab from the tabs above the table.
  3. Pressing on Field Settings in the Active Field group.

This will open the Field Settings panel and allow you to edit the whole Value field, which means that if you change numbers, location, etc, the formatting will stick. Pressing Number Format will bring up the Format Cell window where you can apply numbers, currencies, decimal places, etc. Pressing Ok will apply the formatting you have selected to the whole field.

You can also apply color schemes or styles by pressing the Design tab and selecting the theme that looks good to you. This will not usually change the layout, just the color and style.

Change the name of the table and fields

If you are going to be sending the table to clients or colleagues, you may want to change the name of the table and fields to something a little more user-friendly. You can change the names by:

  1. Clicking on the cell/name you would like to change.
  2. Pressing on the Analyze tab.
  3. Looking at the Active Field area of the Analyze tab and double clicking on the cell’s name. You can also click on the box beside Pivot Table Name if you want to change the table’s name.
  4. Entering a new name.

Add gridlines for easier viewing

If you need to separate the information in the cell, the easiest thing to do is to add gridlines. This can be done by:

  1. Clicking on a cell in the table
  2. Selecting the Design tab and choosing any style aside from the first one.
  3. Looking at the PivotTable Style Gallery and ticking the box that says Banded Rows.

Change blanks to 0s

There may be an issue where the data that’s used to create the table leave blank spaces when they should be 0s. This isn’t a big deal, but it would be a good idea to ensure that, for consistency, blank cells are represented as a 0, especially if other related cells are numbers. You can force Excel to fill blank cells with a 0 by:

  1. Right clicking on the table.
  2. Selecting Options followed by Layout & Format in the pop-up window.
  3. Entering 0 beside the box that says For empty cells show
  4. Pressing Ok.

Excel and the pivot table bring some excellent functionality to the spreadsheet, and having a properly formatted pivot table could go a long way in ensuring the information shown is easier to read.


Show Comments

No Responses Yet

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.