Customize Excel’s pivot table

Office365_April09_CIf you were to ask business owners or managers the number one program they use on a regular basis, many would probably say the spreadsheet. This versatile program helps managers and owners keep track of various information but some spreadsheets can reach such massive sizes that they become unwieldy. Excel has a function that can help you easily pull data from a large spreadsheet – the pivot table. While useful, the pivot table isn’t perfect and will usually require a some formatting.

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. If you would like to learn more about how Excel or any of the other Office 365 programs can help, please contact us today.

This entry was posted in Cloud - Office 365, General Articles C and tagged , , , , , , , . Bookmark the permalink. Both comments and trackbacks are currently closed.
  • Internet Presence Management for Small Business Owners

    pronto logoFull-service, pay-as-you-go all inclusive websites, from design and content to SEO and social media management for one low monthly price.

    Learn more about our small business online marketing services.