This page contains resources that I have found useful in my personal and professional life. It contains everything from quotes and books that I like to podcasts and videos.
If you don’t want to scroll through the entire list, you can click on one of the links below to filter the resources by specific category.
I will update this list as I discover new resources. If you have a resource you think I should add, please email me.
You can close Excel down (with multiple files open) by double clicking the Excel icon – top left of screen.
This works for the other Office apps too.
If you haven’t saved a file Excel will ask if you want to.
To close a single file down use the X on the top right of screen.
To clear all the formats for a cell or a range select the cell/range and press in sequence Alt H E F don’t hold the keys down.
All the formats will be removed.
If you need to see the underlying number for a date this is an easy way to find it.
Woohoo!
It has taken a decade or so but Excel finally has an in-cell drop down that you can type a letter and reduce the entries listed – see screen shot below.
Woohoo, I don’t know when this happened, but you can now get Excel to extend your ordinals when you drag with the Fill Handle and use things like 1st, 2nd, 3rd and 4th etc.
Type 1st January in a cell and drag the cell down.
It seems to work with ordinals at the start rather than at the end of a text string. So January 1st doesn’t work. 1st by itself does work.
Even now sometimes a full restart is a possible solution.
I recently had a macro returning a weird error message about an action taking too long. Restarting Excel did not fix it.
So, I restarted Windows and the macro worked as expected.
These days with so many apps running in the background, sometimes the only solution is a restart of Excel and Windows.
Turning it off and turning it on can still solve issues.
Woohoo ! A new shortcut to open the Power Query window.
Works in the latest subscription version of Excel.
Alt + F12
I just found out you can split the VBA code window. See images below.
Use the small icon above the right side scroll bar.
This can be useful if you have a long block of code and need to look at separate parts together.
Thanks to Bob Umlas the Excel MVP for sharing.
You can double click the split bar to remove it.
Wow – if you right click the Taskbar (bottom of the Windows screen) you can access the Task Manager.
No more Ctrl + Alt + Del – woohoo!
In a browser you can open a link in a new tab by middle clicking – (mouse wheel click) – the link.
Woohoo!
The standard colour for a formatted table is blue.
If you use Ctrl + T to create a table, that’s the colour Excel uses.
You can change the default.
On the Home ribbon tab, click the Format as Table drop-down and right click the colour scheme you want to set as the default.
Select Set As Default from the menu.
Done!
The General format is Excel’s default format.
It doesn’t really do much. It can be handy to use though if Excel applies a format you don’t want, like a date or a percentage.
The keyboard shortcut to apply the General format is
Ctrl + Shift + ~
Be careful if you forget the Shift key as it will display formulas. If that happens, press it again (without the Shift key) and it will revert back to normal.
If you use the old Excel macro language – known as XL4 macros – you may need to update a setting to keep using them.
This is the macro language before VBA was introduced in the Excel 5 back in the 90’s.
Not many people use these macros any more but there a couple of techniques that they are used for.
Microsoft will soon disable them automatically and you will need to turn them back on if you want to use them.
The setting to update is in the Trust Center Macro settings – see image below.
Have you tried right clicking a formatted table recently?
There is a new option to Get Data from Table/Range – which means to import the table into Power Query so you can data cleanse the table.
I used this today in a live webinar.
I zoomed into the Power Query window in Excel to make it easier to see.
Ctrl + Shift + + (plus)
You need the Developer ribbon tab visible to record a macro, or do you?
This old-fashioned keyboard shortcut will open the record macro dialog. Pressed in sequence, not held down.
Alt T M R
You can also use the little icon next to the Ready at the bottom left corner of the screen.
Once you start recording the small square icon to stop recording appears in the same spot.
Did you know the right-hand side drop-down, above the code window, lists all the Subs and Functions in a module? Now you do.
Just saw an Excel newsletter post from Kevin Jones from https://www.dataautopros.com/about-us/
He found that turning off the VBA interactive setting can speed up some macros. I tested it on a one minute macro and it cut it down to 40 seconds.
Worth a try if you have a longer running macro. You can add it to your opening and closing routines.
Code to turn it off
Application.Interactive = False
And then turn it back on at the end of your code.
Application.Interactive = True
Thanks Kevin for sharing.
Just found out you can double click the re-size control on the Name Box. This quickly shrinks the Name Box width.
I have no idea how long that feature has been there, but I have just found it. Woohoo!
If you need to paste values and formats then you can use a single keyboard shortcut after you have copied.
Alt H V E will paste both.
If you have cell selected in the output table from a Power Query, you can press, in sequence (not held down) the following keys Alt P U E to open the Power Query Editor window.
I typically use the shortcut Alt A V V pressed in sequence (not held down) to open the Data Validation dialog.
I like it because you can do it one-handed. The A and V are close together.
There is another shortcut that works the same. Again, pressed in sequence and not held down. Alt D L
Use the one that is easiest for you.
Here’s a couple of useful keyboard shortcuts for Pivot Tables.
Display/Hide the Pivot Table Field List – this list lets you create or change the Pivot Table.
Alt J T L – pressed in sequence, not held down.
To add Subtotals above the entries in an existing Pivot Table.
Alt J Y T T – again pressed in sequence, not held down.
The Formula Bar can be expanded using the icon on the end. But there is a keyboard shortcut as well.
You can expand it or return it to one line using the keyboard shortcut Ctrl + Shift + U.
Thanks to Excel MVP Tom Urtis for sharing this shortcut recently on LinkedIn.
Well, the wait is finally over in the subscription version.
You can now unhide more than one sheet at a time – woohoo!
Seems new icons have been added in the right of screen to allow you to switch easily between Task Panes in a recent upgrade.
This is in the subscription version of Excel.
The shortcut to display the Queries & Connections Task Pane is easy to remember. It is Adults Only!
To display or hide the Queries & Connections Task Pane you use. These keys are pressed in sequence, not held down.
So only adults allowed in Power Query.
The Goal Seek feature can save you a lot of trial and error when you want a calculated cell to equal a specific value.
The keyboard shortcut to open the Goal Seek dialog is Alt T G.
The keyboard shortcut to unlock or lock a cell or a range (its a toggle, so it switches between the two) is Alt H O L pressed in sequence, not held down.
Did you know you can accumulate copied entries in the Windows clipboard?
The default setting is to only have the last thing you copied, but a setting change can give you access to multiple items to paste.
You can also pin items to the clipboard to keep them there.
To change the setting.
Click the Windows button and choose the Settings icon
The select System.
Then select Clipboard (bottom left) and change the Clipboard History to On – done.
If you copy multiple items you can press the Windows key and V to access what’s on the clipboard.
Then you can click the top right elipse icon to Pin the item to the clipboard.
If you frequently export files as pdfs then this keyboard shortcut may save you a little time.
Pressed in sequence, not held down use
Alt F E A
