Learn how to use PivotTables to summarize, sort, count, and chart your data in Microsoft Excel 2016. Author Curt Frye shows you how to navigate the complexity of PivotTables while taking advantage of their power.

Figure 3: Follow these steps to highlight duplicate values in Excel 2003. Bass amp app for mac os x. You can access the Conditional Formatting feature from either the menu bar or from Excel’s ribbon in Excel for Mac 2011. If you use the ribbon, identifying duplicate values in Excel for Mac 2011 is identical to the steps in Excel 2007 and later. To keep track of which cells on a worksheet have formulas, select the entire worksheet and use Conditional Formatting, selecting the option to create a New Rule. Then, use a formula to determine which cells to format; =ISFORMULA(A1). Then select a format to be applied to all cells that meet this criterion.

This course shows how to build PivotTables from single or multiple data sources, add calculated fields, filter your results, and format your layout to make it more readable. Plus, learn how to create PivotCharts to visualize your data, enhance PivotTables with macros, and use the Data Model feature to build PivotTables from related tables. Instructor •. Curt Frye is a freelance course developer and writer.

He has developed more than 50 online courses on topics including Microsoft Excel, Tableau, Mathematica, and social network analysis. He has also written more than 40 books, with recent titles including Microsoft Excel 2016 Step by Step and Microsoft OneNote 2016 Step by Step, both for Microsoft Press. In addition to his writing and course development, Curt is a popular conference speaker and entertainer. His programs include his Improspectives® approach to teamwork and creativity, analyzing and presenting data in Microsoft Excel, and his interactive Magic of the Mind show. By: Dennis Taylor course • 1h 5m 11s • 44,252 viewers • Course Transcript - Pivot Tables help you summarize large data collections in Excel Worksheets. But it can hard to find data that matches specific criteria just by looking at the numbers. It's much easier to change a cell's filler or text color to indicate it's status relative to a rule.

Formats that change the appearance of cell's contents by applying rules are called Conditional Formats. And Excel 2016 let's you apply conditional formatting rules to Pivot Table cells and have the formats change position when you pivot the pivot table. I will demonstrate how to apply conditional formatting rules to a pivot table and I'll use the ConditionalRules Excel Workbook. That's a sample file you can find in the Chapter 5 folder of your Exercise Files collection.

Creating a conditional format is a lot like creating a filter. In both cases you create rules to indicate which cells you want to be affected by your action.

To apply a conditional format to a data field, click any cell in the data field part of the pivot table, and then on the Home tab of the ribbon, click the Conditional Formatting button to display the types of rules you can create. In this movie I will create a rule-based Conditional Format so I'll point to Highlight Cells Rules, and I can select from Greater than, Less than, Between and Equal to. In this case I will create a Greater Than Conditional Format, so I'll click that. Excel displays the Greater Than dialog box, and I can format cells that are greater than a particular value. In this case Excel used the value from the active cell, but 80,000 is much too low.