Start learning with our library of video tutorials taught by experts. Get started
Viewed by members. in countries. members currently watching.
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.
Filtering a PivotTable using a slicer helps you and your colleagues visualize which values the filter includes and excludes. You have a lot of control over the slicer's appearance and other characteristics so you can customize it to meet your needs. In this case, I have the Year and Month fields displayed within the slicers, so I can use them to filter my PivotTable. One thing that jumps out immediately is that the slicers aren't visually distinguished from the body of the PivotTable. They are formatted in pretty much the same way. In other words, so they sort of blend in. What I'd like to do is change the style of the slicers so that they stand out a bit more and makes it easier to see which values are included and excluded from the PivotTable.
To change the appearance of the slicer, just click the Slicer. In this case, I'll work with Month and then click the Options contextual tab that appears and then in the Slicer Styles gallery click a new style. To see all the styles available to you, click the More button at the bottom-right corner of the gallery. And in this case, I'll select the dark red style just because it stands out the most. I'll do the same thing for the Year slicer. So, on the Options tab, I'll click the Dark 2 style which incorporates dark red.
If you want you can create your own custom slicer style. To do that, click the More button at the bottom-right corner of the Slicer Styles gallery, and then click New Slicer Style, and then use the controls to create the style that you want. In this case, I will call the style New Custom. Then I'll just change the color of the header by clicking that header within the slicer element list and then click Format and I'll change the fill color to a burnt umber.
When I click OK, Excel displays a preview of the style here and that's the only change I'll make. You can experiment with any other changes that you want to make with any of the other elements and then click OK. That slicer style appears in your gallery. Clicking the More button you can see it up here in Custom. So I'll apply that to the active slicer which is here. If you want, you can also change the caption of a slicer. So for example the slicer with the orange header has Year as its caption.
If I want to change that to Fiscal Year, then with the slicer selected on the Options contextual tab, I can go over to the Slicer Caption box and edit the value. So in this case, instead of making it Year, I'll make it Fiscal Year. So Fiscal Year. When I press Enter, Excel changes the value in the header of the slicer. You can also change a slicer's layout. So for example in the Month slicer, we have a list of 12 months. But let's say that I wanted to divide those into two columns instead of just one.
To do that, I could select the slicer so that it was active, and you can tell a slicer is active by the border that appears around it. It's called a selection border. And then on the Options contextual tab in the Buttons group, edit the value in the Columns box so that you have the number of columns you want and in this case we'll just make it 2. You can also change the Height and Width of an individual button. So because I'm making it 2 columns rather than having the width of each individual button be 1.82 inches like it is now, I'll just reduce it to 1.
So I'll edit that value, type 1 and press Enter. Slicers provide a visual representation of a PivotTable filter. So you should take care to format and customize your slicers so they are visually distinctive, but don't overwhelm the data within the PivotTable.
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.