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

Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.