Core Pivot

Many Excel experts believe that pivot tables are the single most powerful tool in Excel. According to Bill Jelen (a.k.a. Mr. Excel): "No other tool in Excel gives you the flexibility and analytical power of a pivot table".

The basics

• Why pivot tables are so powerful, and how to set up a pivot table in less than 2 minutes.
• What it means to refresh a pivot table, and how to refresh a pivot table when your data changes (pivot tables work like a formula, but they don't automatically pick up new data).
• The goofy names you need to know to talk about (and understand) the parts of a pivot table.
• How to "drill down" into a pivot table to extract the data (incredibly useful way to get just a subset of your data).

Build

• Why it makes sense to use an Excel Table for your pivot table (this one tip can save you hours of frustration).
• How you can set up a pivot table so that you can add data every day, week, or month and have it work correctly every time.
• How to add new fields to a pivot table, and easily move a field from one place to another.
• How to use (and understand!) pivot table layouts. (The default layout is often *not* what you want).

Calculate

• Why sometimes you get a count, and sometimes you get a sum (and how to get what you want).
• The best way to format numbers in a pivot table (Hint: don't select the numbers and apply a format...there's a much better way!)
• How to add and remove grand totals and sub totals with ease.
• How to rename pivot table fields (It's easy, and it can make your pivot table much nicer to look at.)
• The easiest way to add the same field more than once (so you show sales by product as both $ and %, for example).
• How (and why) you might want to add a calculated item or a calculated field to a pivot table (hands down the most confusing thing in the entire pivot table universe).

Group

• Perhaps the coolest feature in pivot tables is grouping. This is what gives you clean breakdowns by month and year, by product, etc.
• How to group a pivot tables by number (great for showing, say, purchases by age-range, or by order size).
• How to easily group by year, month, and quarter, and how to arrange those groupings any way you want. (Even a cool way to group by day of week).
• How to group by just moving things around. (Most people don't know that you can do ad hoc grouping almost way you like).

Sort, filter, and Slice

• How to sort a pivot table alphabetically, or by value.
• A simple way to sort a pivot table with your own custom list, automatically.
• How to manually sort a pivot table, so you can arrange the data any way you like.
• How to filter a pivot table globally (great for building a quick report by region, or manager, or product).
• How to filter to show only top values, or bottom values.
• How to apply more than one filter at the same time.
• How to filter by name or date.
• Slicers are a cool tool that lets you filter a pivot table with large interactive buttons rather than tiny little filters (perfect for managers that want just want to click things).
• How to add a slicer to your pivot table, and make it look nice.
• How to make a slicer control more than one pivot table (this is, by far, the easiest way to make a simple, interactive dashboard).
• An easy way to add more than one slicer.

Professional polish

• How to apply a style to your pivot table (instant formatting).
• How to create your own styles (a huge time saver when you start using pivot tables more often and want them to look the same).
• An easy trick to move a pivot table style into a new workbook (strangely non-obvious).
• How to create pivot charts, and get rid of all the extra clutter they bring.

And more...

• How to easily clone a pivot table when you want another view of the same data (a great time saver).
• How to make a "standalone" pivot table that brings its own data with it.
• How to group a pivot table by age range (an eye-opener to the power of pivot tables).