Drop Down Selection update
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.
Tips
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.
Excel Has Ordinals
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.
Restarting Excel and Windows
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.
Open Power Query Shortcut
Woohoo ! A new shortcut to open the Power Query window.
Works in the latest subscription version of Excel.
Alt + F12
VBA Window Split
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.
Task Manager
Wow – if you right click the Taskbar (bottom of the Windows screen) you can access the Task Manager.
No more Ctrl + Alt + Del – woohoo!
Related Posts
Browser Mouse Tip
In a browser you can open a link in a new tab by middle clicking – (mouse wheel click) – the link.
Woohoo!
Formatted Table – Change Default Colour
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!
Related Posts
General Format
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.
Old Macros XL4 macros
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.
Related Posts
Power Query Shortcut
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.
Related Posts
Power Query Zoom
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)
Keyboard Shortcut to Record a Macro
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.
VBA Window Tip
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.
Related Posts
Speed up a macro
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.
Name box shortcut
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!
Related Posts
Paste Values and Formats
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.
Open the Power Query Editor
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.
Related Posts
Data Validation Shortcut
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.
Pivot Table Shortcuts
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.
Expand the Formula Bar
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.
The wait is over
Well, the wait is finally over in the subscription version.
You can now unhide more than one sheet at a time – woohoo!
New Task Pane Icons
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.
Power Query shortcut for Adults
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.
Related Posts
Goal Seek
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.
Shortcut to lock or unlock a cell
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.
Related Posts
Windows Clipboard
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.
Export as PDF
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
Restart a Pivot Table in one go
If you want to clear all the fields from a Pivot Table you can use the clear all option.
On the PivotTable Analyze or Analyze tab click the Clear drop down and select Clear All.
This removes all the fields and allows you to start again from scratch.
Some actions clear the clipboard
Some actions in Excel will clear the clipboard after you have copied or cut.
Two common ones are
- inserting a new sheet
- clearing a filter – using Clear in the Data menu (keyboard shortcut Alt a c )
So if you are going to do either of these, do them before copying or cutting.