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.
Excel lets you create many types of conditional formats and it gives you many ways to manage those rules. For example, in Excel 2003 and earlier each cell could have up to three conditional formatting rules applied to it. What's more, only one of those rules could be applied at the same time. When Excel discovered that one rule is true it simply stopped checking. In Excel 2010, there is no practical limit to the number of conditional formatting rules you can apply to a cell. It's also possible for more than one conditional formatting rule to be applied at the same time.
So let's say that you have applied a number of conditional formatting rules to your PivotTable and you want to go in and manage how they are applied. To do that click any cell in the body of the PivotTable and then on the Home tab click Conditional Formatting and then at the bottom of the menu that appears click Manage Rules. When you do the Conditional Formatting Rules Manager appears and it lists each of the formats you've created. The first thing you should do is ensure that you're working with the rules applied to the PivotTable.
To do that you can look at the value in the Show formatting rules for box and in this case it does say This PivotTable. If you click the down arrow you can see the other options, which are the Current Selection, This Worksheet, This PivotTable, which is currently selected, and then either Sheet2 or Table1. So all these are options and in this case this PivotTable selected and it's correct. So I'll click outside the list to close it. In Excel 2003 and earlier versions, the program stopped checking conditions when it found one that was true.
Excel 2010 doesn't stop checking conditions unless you tell it to. So for example, if you want Excel to stop checking conditions if a particular condition is true, you can check that condition Stop If True box. So for several let's say that if the cell value is greater than 110 and you wanted Excel to stop checking after that, then you could check the Stop If True box. In this case I don't want to make that change so I'll clear the box but it's there if you wanted it. You can also change the order in which Excel applies these rules.
So let's say for example that I have the bottom rule here which is called Top 7. So what it does is it finds the top seven monthly sales rise within the PivotTable and then it formats the number within itself as bold and italicized. So now let's say that you want Excel to check that rule first. To do that you can click the rule to select it as I've done and then click the Move Up arrow and I'll move it to the top of the list. So we have one, two, and three clicks and then that has moved to the top of the list.
If you want to move a condition down then you can click the condition and click the Move Down button. So that let's say that Excel has discovered a value that's in the top seven and I only wanted to apply this formatting as opposed to the color fill down below. Now I can check the Stop If True box and then click OK. When I do, Excel re-applies the conditional formats as I edited them. You see that the top seven values in the PivotTable which include 130, 140, 128, and so on, don't have the color fills as the other cells do; instead they simply have their text in bold and italic type.
Managing conditional formats is a little complicated but that's the price you pay for a lot more flexibility. If you take the time to work with your rules will discover many benefits to the additional visualizations offered by Excel's conditional formats.
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.