This video demonstrates how to create a PivotTable style. The built-in PivotTable style gallery contains a lot of good color schemes, but they are, by design, somewhat generic. When you want to create your own PivotTable style to reflect your personal aesthetic or your company’s graphic art guidelines, you can do so using the New PivotTable Quick Style dialog box.
- [Instructor] The built in PivotTable style gallery contains a lot of good color schemes but they are by design somewhat generic. When you want to create your own PivotTable style to reflect your personal aesthetic or your company's graphic art guidelines you can do so quickly. I'll demonstrate that process in this movie. My sample file is CreateStyle_04_02 My sample file is CreateStyle_04_02 and you can find it in the chapter four folder of the exercise files collection. In this workbook I have a PivotTable that has an existing style applied.
Let's say that I want to define my own custom PivotTable style. To do that, click any cell in the PivotTable, and then on the design contractual tab of the ribbon click the PivotTable styles galleries more button at the bottom right corner, and then at the bottom of the list click new PivotTable style. That displays the new PivotTable style button and you can give your style a name. In this case I will call it PTCustom In this case I will call it PTCustom and now I can set the format.
I'll just make a few changes but you can see all of the elements that are available to you. So the first thing I'll do is select what I will change, is select what I will change, and in this case I will change the header row, so I'll click that and then I'll click the format button. I will make the color instead of automatic I'll make it white and then for the fill, this is the color that will fill the body of the cell, the background color, I'll click purple, and then I will click OK.
You can see in the preview that I have purple as the background and white text for the header. Let's say that I also want to have rows that are striped and we'll stripe the second row. So I'll click Second Row Stripe and click format. For the background color lets say that I'll go for a light gray, I'll just click that color here, a light gray, I'll just click that color here, and of course you can pick any color you want. I can also, if I want, change the stripe size.
For example, if you look at the preview you'll see that there is one gray row then one white row and so on. If I want to change the stripe size I can click that control and say change it to two. You see that I have two rows that are striped and then one that isn't. In this case I'll switch it back to one so I have an alternating striping. That's all I want to do for now so I'll click OK. I created the style but I haven't applied it. To apply it I'll go back to the PivotTable styles galleries more button and then at the top you'll see that there is the custom style that I created.
I'll click that and there it is. I'll click that and there it is. You see that I have purple as the header with white text and I have striped rows. If you want to edit or modify a style you can do that by right clicking it in the gallery and then click modify. That opens the PivotTable style dialogue box again except now instead of create or new it's modify. It's exactly the same controls as you had before so there is no new explanation required and I'll click cancel.
If you want to delete a custom PivotTable style, you can once again right click that style and click delete. I won't do that so I'll just click away. Finally if you want to remove all formatting from your PivotTable you should click any cell in the PivotTable then click the styles galleries more button and at the very bottom click clear and that removes all formatting. Excel gives you the tools to control exactly how your PivotTable appears in your workbook.
Creating your own format enables you to control how your PivotTable appears in your company's documents and presentations. That enhances your corporate identity while making the data easier for viewers to comprehend.
Note: This course was recorded in Office 365. However, anyone using Excel 2019, Excel 2016, and even prior editions of Excel should be able to follow along with the course contents.
- Determine when it is necessary to click Defer Layout Update in the fields area.
- Recall the easiest way to create a PivotTable from external data that cannot be imported directly as an Excel table.
- Explain the configuration that takes up the least width on the screen while keeping the Fields List area visible.
- Identify the mistake users often make when creating filters.
- Explore the function of slicers.
- Review the use of data bars with a PivotTable.
- Name the first step in viewing all the conditional rules applied to a PivotTable.
- Recognize the appropriate way to save a macro.