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 Filter the 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?
Advanced Filter Keyboard Shortcut
To open the Advanced Filter dialog use Alt A Q pressed in sequence, not held down.
If your sheet has a lot of colour and you want to print it on a black laser printer, one of the Page Setup options can improve the print.
On the Sheet tab of the Page Setup dialog there is a Black And White option – see image below.
This removes all the colour and prints in black only,
You can access the Page Setup dialog from the Page Layout tab. Click the small arrow on the bottom right of the Page Setup section.
Data entry and calculating
In some large models Excel may calculate for a few seconds after you make an entry.
In most cases you don’t have to wait for Excel to finish calculating before you make your next entry.
Type your entries as fast as you like. Excel will finish calculating once you are done.
Show all comments
Cell comments are useful for instructions and documentation.
If you want to make all the comments on a sheet visible, use Alt v c pressed in sequence, not held down.
Once visible this shortcut also hides all the comments in one go.
This is an old Excel 2003 shortcut that still works.
Name Box and Macros
I have found out yet another feature of the Name Box.
The Name Box is on the left of the Formula Bar and above the column letters – see image below.
During a recent macro webinar I tried to create a new range name called Test using the Name Box.
But I also has already created a macro called Test as part of the training.
As soon as I pressed Enter after typing Test into the Name Box to create the Test range name, I was magically transported to the VBA window to the Test macro – Wow!
This means you can’t create a range name in the Name Box that is the same as a macro name.
You have to use the Define name icon on the Formulas tab to do that.
Easy Financial Year Formula
To get the Australian financial year from a date you usually use an IF function based on the month number.
I recently learned a new hack from Matt Allington of Exceleratorbi.
You can add 184 to the date and then use the YEAR function. See table and formulas below.
The formula in cell B2 is
The formula in cell C2 is
Both formulas have been copied down.
A simple solution to a frustrating issue. Thanks Matt.
Selecting a column
To quickly select a column of data in a formatted table you have a couple of options.
Select a cell in the column and press Ctrl + Space Bar.
This will select the column of data. If you want the heading too, press it again.
You can also select multiple columns before using the shortcut.
This technique can take practice if your headings are in row 1.
If the heading starts in row 2 or below it is easier. See image below.
If you point to just above the heading row you will see a downward facing, black arrow. Click this once to select just the data. Click it again to include the heading.
When the heading row is in row 1 you need to do the same but make sure the column letter doesn’t highlight.
The image below is the correct arrow – this will select the column in the table only.
In the image below the arrow shown (because the column letter is highlighted) will select the whole column, not just the data in the table.
VBA If statement tip
When creating long VBA code it is common that the start of an If statement and the matching End If statement may not be visible on the same screen.
When scrolling around trying to understand your code it can be useful to include the If statement itself as a comment following on the same line as the End If command – see examples below.
If x=0 Then
'lots of code
If y =1 Then
'lots of code
End If 'If y =1 then
End If 'If x=0 Then
The apostrophe is used to specify the start of a comment – you can have a comment following a line of code.
This structure can assist when trying to identify which End If statement relates to which If statement.
Entering Date and Time in Excel
There are shortcuts to enter
- the current Date Ctr + ;
- the current Time Ctrl + Shift + :
There isn’t one to enter both.
You can use them in sequence to achieve a date and a time entry.
In sequence press
Ctrl + ;
Ctrl + Shift + :
The space separates the date and time and Excel will recognise the entry as a date and time.
Filtering blanks made easy
In Excel the “Blanks” option is usually at the bottom of the list. This slows down selecting it.
If you have a lot of entries you need to scroll all the way down to bottom of the list to choose it – see image below.
But the word “Blanks” is searchable, so if you type b in the Search box – your work is done – no scrolling required – see image below.
If your column contains text you might need to type in bla.
Columns in a Text Box
You learn something new every day.
Today I discovered that you can have columns within a text box.
If you right click the text box and choose Format Shape the Task Pane below should open on the right.
Click the third icon (Size and Properties) at the top and then open up the Text Box options.
The Columns button allows you to specify how many columns plus the gap between them.
Copy Across Shortcut
In the structure below let’s assume you want to copy the SUM formula from cell B5 to cell C5.
Obviously you could use copy and paste, but that would require a few keyboard presses or mouse clicks.
Given that we already have cell C5 selected we can use
Ctrl + Shift + >
This copies whatever is in the cell on the left to the current cell.
Word tip – increase font size
I recently found this keyboard shortcut for MS Word to increase the font size of the selected word(s).
To reduce the font size use
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.
Press Enter and Stay in Current Cell
A trick to stay in the cell you are editing is to hold the Ctrl key down when you press Enter.