Excel® Tips and Tricks

Category: Pivot Tables

Get the best in Excel® training anytime, anywhere.

Get Excel® Training

How to Calculate Median in an Excel PivotTable – Almost!





Categories: Excel®, Pivot Tables

A frequent question that comes up when working with Excel PivotTables is how to calculate median using the table’s filters and analysis. The short answer is “Excel doesn’t do that”. Meaning, there isn’t a simple way to change the Value Field Settings to calculate “Median”. The closest you can get is “Average”. However, averages aren’t […]

Read More

How to Use Slicers in Excel 2010 and 2013





Categories: Excel®, Pivot Tables

Excel’s Slicer Filter was added in Excel 2010 and enhanced in Excel 2013. This useful feature is excellent for viewing and comparing varied combinations of your data with a simple click. Something like a cross between a button and a filter, Slicers have become a prized tool for analyzing large data sets in dashboards and […]

Read More

How to Show Percentage of Total in an Excel PivotTable





Categories: Excel®, Pivot Tables

Once you learn how to create an Excel PivotTable, you’ll discover that organizing your information is only the first step in getting the most out of this useful feature. PivotTables then give you the ability to further manipulate the organized information. Value Field Settings let you perform different types of summarizations. Calculated Fields and Calculated […]

Read More

Create a PivotTable Calculated Item in Excel





Categories: Excel®, Pivot Tables

Excel’s PivotTable feature allows you to organize information in such a way that you can answer many questions about your data that would otherwise require lots of formulas, functions and “IF” statements. To get even more answers out of your data, PivotTables offer Calculated Fields and Calculated Items.

Read More

Grouping Excel PivotTable Data by Month





Categories: Excel®, Pivot Tables

PivotTables have some useful “hidden” features that can make interpreting your data even easier. One such feature allows you to view data by date groupings (such as month, quarter, even week and hour of day). By grouping within the PivotTable itself, you avoid constantly changing your source data and creating multiple PivotTables from the same […]

Read More

Dressing Up Your PivotTable Design





Categories: Excel®, Pivot Tables

Once you have a PivotTable created, you may find that Excel’s default formatting is bland at best, unreadable at worst. Here are five formatting options you can adjust when you need to get a PivotTable ready for a presentation or report, but don’t need to create something as visually elaborate as a PivotChart.

Read More

Tips for Creating Perfect Pivot Tables with a Macro





Categories: Pivot Tables

Once you’ve harnessed the power of PivotTables, you probably find yourself looking for ways to use them all the time. The same is true of macros – convenient shortcuts that make your life easier! But have you tried to combine the two? Unless you are turning the exact same format and type of data into […]

Read More