Video lessons in this course

What you'll learn

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).

Q + A

Why Pivot tables?

The short answer: No other tool in Excel gives you the flexibility and analytical power of a pivot table.

Without any formulas at all, Pivot Tables let you quickly analyze data that contains hundreds, thousands, or even hundreds of thousands of rows.

Will the course work for both Windows and Mac?

Yes. There are some small differences in the Mac and Win interface with respect to formulas, but in general this is one area in Excel where there is very good overlap between the Mac and Windows versions. The videos were recorded in Excel 2010 on Windows 7. All videos highlight shortcuts for both Windows and Mac.

Do you have a guarantee?

Of course! If you aren't happy with the course, just let us know within 30 days and we'll give you a full refund. We're always interested in your feedback (so we can make the course better), but this a no-quibble guarantee. We want you to be completely happy.

How do I access the course content?

When you purchase the course, an account will be created for you automatically on the Exceljet website. You'll receive an email immediately with a link to login, and when you do log in, you'll have full access to all videos and practice worksheets for the course(s) you purchased.

Why short videos?

We think people learn quickly and effectively with a no-nonsense approach that is quick, clean, and to the point.

In other words: less is more. Excel brings plenty of complexity to the table already :)