Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
In Excel 2010: Pivot Tables in Depth, author Curt Frye provides comprehensive, hands-on tutorials on Excel PivotTables, including more advanced techniques such as using macros and the new PowerPivot add-in. The course shows how to connect and consolidate data sources to power PivotTables, sort and filter records, display data in a PivotChart, print tables and charts, and also introduces the DAX language for performing advanced summaries in PowerPivot. Exercise files are included with the course.
When you create a PivotTable, Excel applies some basic formatting so you can easily distinguish with the labels and organizational layers from the data in the body of the PivotTable. Excel does come with a substantial number of built-in styles from which to choose, so if you do want to change your PivotTable's formatting you can do so easily. Before I get into styles I do want to point out that you can format individual cells within a PivotTable and have that formatting stay with us when you pivot the table. So for example let's say that I wanted to highlight the value 130 that is currently in cell C6 and I will do that just by applying a yellow fill color to that cell.
When I pivot the PivotTable, that yellow formatting will go along with that cell. So let's say that I put Company on top of the Row Labels area and when I do, the formatting has stayed with the cell that contains the value 130 and I just scroll down using my mouse wheel to display it. Then if I put Company back in the Column Labels area and scroll back up, then you can see that the cell has retained its formatting. And I will just go ahead and clear that by clicking the cell and clicking No Fill in the Cell Fill button.
If you want, you can change the formatting of your entire PivotTable by apply a style which is available from the Styles gallery. Click any cell in the PivotTable and then on the Design contextual tab, click the More button in the PivotTable Styles gallery to see the styles that are available to you. So I will just pick another style at random and I will select one here that has kind of an orangey burnt umber color. And when I click it Excel applies that style to the PivotTable. And you might've noticed when I move my mouse pointer over any of the styles in the Styles gallery that Excel displays a live preview of how that style would like if it were applied to the PivotTable.
So if I move the mouse pointer over this style or this style I don't need to actually apply it to have Excel let me know what it would look like if I were to apply it. But now let's say that I have some existing formatting applied to a PivotTable. What I will do now is press Ctrl+Z once to remove the style that I applied and I'll change the formatting of the FirmA and FirmB cells by pressing Ctrl+I to make the text italic in addition to being bold.
Now there are two ways that I can apply another PivotTable style. I can either click the style like I did before, which applies the style and does not overwrite any existing formatting. So let's say that I apply this style. When I do, Excel retains the italic formatting for FirmA and FirmB. However if I right-click the style, then a shortcut list of options appears and I can either choose to apply and maintain the formatting, which is the default choice, or apply and clear formatting, which if I select will apply the style that I just selected and also remove an existing formatting.
So whereas the values FirmA and FirmB where italics before, now they're not. Now PivotTable styles are part of Office themes, which are collections of predefined color schemes. If you apply a built-in PivotTable style and then change in the Office theme applied to your workbook, the built- in PivotTable style changes as well. So for example I'll apply another PivotTable style and I'll make it Medium 2, which is blue. If I go to the Page Layout tab of the ribbon and then in the Themes group click the Themes button to display other Office themes and I will select another theme, say Austin. When I hover my mouse pointer over that theme you can see how in the background it would affect the PivotTable style that I previously selected.
So if I were to hover over Black Tie, you can see how that would change. Concourse, Composite, Clarity and so on. I am going to exit out without actually applying another theme, but hopefully you'll get the idea of what would happen if you were to change your theme while a PivotTable Style was applied. After you apply a PivotTable style, you can turn individual elements on and off. For example, let's say that you apply a style where the even and odd rows are formatted differently. Excel refers to that scheme as banding.
So to illustrate the point I will go to the Design tab and then in the PivotTable Styles gallery I will apply the style called PivotTable Light 16. So I will come down to the third row. There is 15 and there is 16. When I click it, Excel applies it to the PivotTable. Now this is a banded style but the banding doesn't appear because the Banded Rows checkbox on the Design contextual tab isn't selected. If I select it you can see that the alternate rows are dark and light to help distinguish them within the PivotTable.
Regardless of the style that you apply, you can always change the formatting of individual cells. You can also create your own custom styles. I will show you how to do that in the next movie.
There are currently no FAQs about Excel 2010: Pivot Tables in Depth.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.