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.
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.
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.
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!
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.
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.
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.
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.
Windows File Explorer – New Folder
To insert a new folder in Windows File Explorer use Ctrl + Shift + N.
To switch to Full Screen mode – great for presentations – use
Ctrl + Shift + F1
Press it again to revert to the normal view.
Keyboard shortcut to insert a Text Box
A quick way to insert a Text Box is by pressing, in sequence (not held down) Alt N X.
A blank Text Box is placed in the middle of the sheet.
Fastest way to copy an image, graphic or chart
To copy an image, graphic or chart simply have the object selected and press Ctrl + D. You can press multiple time to paste multiple times.
If you line the first one up then the others will also line up as you duplicate them.
Make a Dent
Do you use Indenting in reports? Example below.
If you do, you make like this keyboard shortcut.
To increase Indenting press Alt H 6 pressed in sequence, not held down.
To decrease Indenting press Alt H 5 (is that an Outdent?)
Open the Filter Drop Down
If you have filters turned on and you are in the heading row of the table you can press Alt + down arrow to open the filter drop down.
You can then use the arrow keys to move up and down.
Bold Your Headings
Apparently this is not widely known, but you should always bold the headings in your tables.
Then when you use Format as Table (Ctrl + t) on the Home ribbon tab the header row will be correctly identified.
This also applies to the Ctrl + Shift + L shortcut to insert the filter drop downs.
It also applies to the ranges used for charts.
In general ALWAYS BOLD your headings – it is something Excel looks for.
Ctrl + b is the bold shortcut.
Delete a cell comment
From a question in a recent webinar I found a keyboard shortcut to delete a cell comment.
Alt r d pressed in sequence, not held down.
Adding current date to a cell comment
You may know that Ctrl + ; (semi-colon) will insert the current date in a cell.
Did you know it also works in a cell comment?