Join Curt Frye for an in-depth discussion in this video Applying a PivotTable style, part of Excel 2016: Pivot Tables in Depth.
- When you create a pivot table Excel applies some basic formatting so you can distinguish the labels and organizational layers from the data in the body of the pivot table. Excel 2016 does come with a substantial number of built in styles from which to choose. So if you do want to change your pivot table's formatting you can do it easily. I will demonstrate the technique for applying existing pivot table styles using the apply style workbook. That's an Excel file you can find in the chapter four folder of your exercise files collection.
As you can see this pivot table has the most bare-bones style that I could find. It only uses borders on cells to distinguish between the data area and the labels. If you want to apply a different pivot table style to this pivot table click any cell within the pivot table and then on the ribbon click the design tab and then click the style that you want from the pivot tables style gallery.
To display more of the gallery than just a single end ribbon row click the more button at the bottom-right corner and you can see a more complete list of the styles that are available. And you can scroll down to display the styles that are in the gallery. To apply a style just click it. So I'll click this style, Pivot Style Medium 14 and you can see the result there. When you click a style in the pivot table styles gallery Excel applies the style to the pivot table, but it doesn't override any existing formatting.
If you do want the style that you apply to overwrite an existing formatting then you can right click the style, and I'll just right click the style, and from the menu that appears I will click apply and clear formatting. So there it is. There's also the option in some pivot table styles to apply banded rows. That means that alternate rows will have a slightly different color so it's easier to distinguish one row of data from another. To apply banded rows on the design contextual tab check the banded rows box.
If the style has banded rows available it applies them. Some styles also have banded columns, but I find them to be less useful. There is one other aspect of pivot table formatting that I'd like to cover. If you apply a format to a particular data cell within a pivot table that formatting will stay with the cell as long as it's within the body of the pivot table, and all of the fields that were present when you applied the style remain.
So let me show you what I mean. I will click cell C-6 which contains the data for Oxford rooms for January of 2014 and I'll go to the Home tab and apply a yellow fill. So that cell now has that particular formatting. If I pivot the pivot table by dragging the room type field from the columns area to the rows area below month, as I'll do here, you can see that the organization of the pivot table changes, but because the same fields are still used in this arrangement, the formatting stays with that cell.
If I were to remove room type altogether by dragging it from the rows area to the field list then the formatting goes away because that cell, that data at that level no longer exists within the pivot table. If I drag room type back to the columns area then even though the organization is reset the formatting has been lost. Regardless of the style you apply you can always change the formatting of individual cells.
Once you find an existing pivot table style that you like you might want to write down it's designation so that you can find it again.
- Identify how to format data for use in a PivotTable.
- Discover how to create a new PivotTable.
- Define how to connect to data sources.
- Evaluate and consolidate data from multiple data sources.
- Identify how to create calculated fields.
- Summarize field data.
- Review PivotTable sorting.
- Review PivotTable filters.
- Discover how to work with Excel slicers.
- Recall how to format PivotTables with styles and formats.
- Assess how to build PivotCharts.
- Assess how to run macros.
- Evaluate how to create a PivotTable using the data model.