John Walkenbach

The Excel legend, John Walkenbach is the most respected Excel author. He has been writing about Excel for decades. I have many of his books. Look up Excel Guru and you’ll see his picture. I was honored that his review is on the back of my first book.

His site contains lots of Excel resources – his Power Utility Pack Add-in a worthwhile purchase.

Hide Grouping Icons

Grouping icons allow you to hide and unhide rows and columns really quickly. Sometimes you might want to hide the grouping icons to maximise the Excel grid.

The keyboard shortcut to hide the grouping icons is

Ctrl + 8

The same shortcut makes them visible again.


Pivot Table Wizard

You may have thought the Pivot Table Wizard had been removed from Excel, but it hasn’t. You can use the following keyboard shortcut to activate it.

Alt d p

Pressed in sequence, not held down.

The Pivot Table Wizard doesn’t recognise formatted tables, and you should only use it if you need to define Multiple Consolidation Ranges.


 

TED Talks

I LOVE TED Talks, they cover so many different topics and are, on the whole, brilliant! I have been watching then for years and highly recommend you check a few out. You will be amazed. They are short and usually very well presented and often humorous.

Insert Subtotals Dialog

In a sorted list you can have Excel automatically insert subtotals based on the sorted column.

With a cell selected in the table, the keyboard shortcut for the Subtotals Dialog is

Alt a b

Pressed in sequence, not held down.


Data Validation Dialog

To open the Data Validation dialog use the following keyboard shortcut.

Alt a v v

Pressed in sequence, not held down.

Data Validation enables you to limit what a user can enter in a cell.


Move between open Excel files

The keyboard shortcut to move between open workbooks is

Ctrl + Tab

Sometimes this can be quicker than using the mouse.

Ctrl + Shift + Tab will go through the files in the reverse direction.


 

Advanced Excel Reporting For Management Accountants (Wiley, 2014)

In this book I share the reporting techniques that I’ve learned over the 20 years that I’ve been using spreadsheets.

You don’t need to be an advanced user to take advantage of this book.

If you have basic skills, the book with guide you through the techniques required to create advanced, automated reports.

See all cell comments

If you want to see all the cell comments in an Excel sheet, in one step use

Alt r a

Keys are pressed in sequence, not help down. Pressing again, will hide all the cell comments.

(Cell comments are like sticky notes for cells)

Note in the most recent versions of Excel Comments have been renamed to Notes. The terms Comments is now used for threaded (linked) Comments.


Excel and Xero integration

Quick Win Development

I saw a demo of this Excel Add-in and it looked pretty good. I met the developer and he is keen to develop the package further. I don’t do any work with Xero, but if you do, it might be worth investigating – it is reasonably priced. It brings in data directly  from Xero into Excel tables ready for Pivot Table analysis or formula based reports.

Row and Column Grouping

In Excel to apply row or column grouping, select the range and press

Shift + Alt + right arrow

To remove grouping use

Shift + Alt + left arrow

After you use it you can use function key F4 to repeat it.


 

An athlete may run ten thousand miles in order to prepare for one hundred yards. Quantity gives experience.

Ray Bradbury (1920–2012)