MrExcel Publishing
Your One Stop for Excel Tips & Solutions

A New Record - Pivot Table in 4 Clicks


July 26, 2012 - by Bill Jelen

I just created a cross-tab pivot table in four clicks using Excel 2013.

For the last 17 years, 35 books, and a thousand seminars, I always brag that I can create that pivot table in six clicks:

  1. Click the Insert tab
  2. Choose Pivot Table
  3. Click OK
  4. Checkmark Revenue
  5. Checkmark Region
  6. Drag Product to the Column Labels drop zone

If you walk up to me and say 'pivot table', I will automatically say 'six clicks'. It is like a knee-jerk reaction.

I don't currently have a tattoo, but if I were to get one, it would say 'I can do that report in six clicks'.

I am currently 331 pages into the massive project to update all the books for Excel 2013. I am working on the introduction for Pivot Table Data Crunching, where I compare the non-pivot table method (77 clicks) to the pivot table method (6 clicks). This time, I tried out this new Excel 2013 method instead:

  1. Click the Insert tab
  2. Choose Recommended Pivot Table. At this point, Excel offers me 10 to choose from, but I can't afford the time to scroll through all 10 and select one, because that would add two clicks
  3. Click OK to accept the first pivot table in the list. It is a pivot table with Region in the Row area and Revenue in the Values area. That magically wipes out steps 4 & 5 above.
  4. Drag Product to the Column Labels drop zone.

Call me a geek, but setting a new record in pivot table creation is cause for celebration!

PS – I tried to improve on the four clicks using keyboard shortcuts. Alt+N for Insert, but then the Recommended Pivot Table choice requires two keystrokes SP. Since the OK button does not have focus, you end up with tab, tab, tab, enter... Never mind...