Filtering Tip

Make your headings bold.

This tip applies to tables and to the structures you use for charts.

Excel looks for the bold format when it reviews tables and layouts to figure out if your table has a headings row.

You can use Ctrl + Shift + L to add or remove the filter icons to a data table. There is also an icon on Data ribbon tab.

This will work more reliably if the headings are bold.

I use the following keyboard combination on the top left corner of the table.

Ctrl + Shift + right arrow (this selects all the headings)

Ctrl + b (this applies bold to the headings)

Ctrl + Shift + L (to turn on filters)

This combination can be done very quickly.

You can just use Ctrl + Shift + L within the table, but sometimes this applies the filter to the wrong row.

Replace Merged Cells with Center Across Selection

Let the macro do the work

Unfortunately lots of people use the Merge & Center format in their spreadsheets. When working with other people’s files that contain Merged cells I will often remove the Merged cells format and apply Center Across Selection which is the preferred format to use. The macro below will convert Merged cells to Center Across Selection.

You Can Undo After You Save

I am amazed how few people know this.

Way back in Office 2007 Microsoft changed the Undo List so that it is NOT cleared whenever you save a file.

You can use Ctrl + z or the Undo icon to undo things you did before you saved the file.

If you close the file that obviously clears the Undo List.

Please let people know this as I find so many people in my training sessions do not know things have changed since Office 2003.

This applies to all MS Office apps.


Make Excel VBA Pause

Sometimes when running a macro you need to make sure Excel has had time to do something before progressing.

This is typically in large models were it can take time (a few seconds) to do a specific task eg removing a filter or updating an external data source.

You can pause a macro to allow Excel to do something by using the Wait command.

Application.Wait (Now + TimeValue("0:00:02"))

The above code will pause the macro for 2 seconds.

Ctrl + Alt + F5 Not Working

The keyboard shortcut to Refresh All in Excel is

This refreshes all the data connections in the file in one step.

The problem is that on some systems (like mine) this conflicts with an Intel Graphics hot key.

To turn off the graphics hotkeys right click the Desktop and choose Graphics Options, then Hot Keys then Disable. See below.

Big thanks to StackOverflow for covering this issue – link below.

https://stackoverflow.com/questions/29149366/default-excel-keyboard-shortcut-ctrl-alt-f5-no-longer-works

It is our attitude at the beginning of a difficult undertaking which, more than anything else, will determine its successful outcome.

William James (1842 – 1910)