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

=YEAR(A2)

The formula in cell C2 is

=YEAR(A2+184)

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.

Keyboard

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.

Mouse

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 + ;

Space bar

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.

Have fun.

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.

 

 

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.

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

VBA to Clear a Filter

Using Excel’s built-in filtering can speed up your VBA code.

It is important if you are applying filters that you clear any existing filters before you apply a new filter. Otherwise the existing filters will usually affect a new filter you apply.

The line of code below will remove filters on Sheet1 (Sheet1 is the sheet code name that you see on the left side of the VBA screen – it may not be the sheet tab name).

If Sheet1.FilterMode Then Sheet1.ShowAllData

The .FilterMode property is True if a filter is in place on the sheet and False if not.

The .ShowAllData method will return an error if no filter is in place – hence the use of the If statement.

Instant Format in Excel

You may know the two keyboard shortcuts below for currency and percentage.

But what you may NOT know is a technique that has been around since the early versions of Excel.

The technique allows you to automatically apply these two formats after you type an entry.

It you type $1000 into a cell and press Enter. Excel will automatically apply the $ format to the cell. The $ sign will not display in the Formula Bar – see below.

If you type 2.5% into a cell. Excel will automatically apply the standard % format to the cell. The % sign will display in the Formula Bar – see below.

As I mentioned these are really old skills that have been lost over the years since we no longer have Excel manuals – shows my age.

Range Selection Tip and Trick

You know how when you press Enter you usually select the cell below? You can override that without changing a single setting.

When you select a range Excel can behave differently when you press the Enter key. Not many users know this trick.

Select the range B2:G2 in a blank sheet and press Enter. The cell selected will be the one on the right or back at the start of the range depending which cell was active when the range was selected.

Pressing Enter cycles through all the cells in the range.

This works for two dimensional ranges as well – in that case the cell below is selected until the bottom of the range is reached then the top of the next column within the range is selected.

This is handy for entering data into input ranges.

Try This

Let’s say range A2:D2 has basic links referencing the cell above so cell A2 has =A1 in it.

What if you wanted to change all those relative references to fixed references?

Select the range A2:D2 and then press these three keys in sequence

F2 F4 Enter

Repeat three times – job done!

F2 is the Edit command.

F4 converts a relative reference into a fixed reference.

Enter accepts the change.

You can often achieve very fast changes with keyboard techniques like this.

For example if a cell contains an email address  or a web address but it isn’t recognised as a link, simply select the cell and press F2 then press Enter to convert it into a link.

If there is a column of them, just keeping pressing F2 and Enter to convert them all. You can become quite fast.

Marking cells Good-Bad-Neutral

Sometimes when you are reviewing a file against a printed report, you may need to identify when cell values are correct, wrong or close.

Consider using the built-in Styles Good/Bad/Neutral on the Home ribbon tab – see below.

These can be quicker than using the usual fill colour icon.

Remember they may not be useful for colour blind readers if you are sharing with other people.

Don’t forget once applied if you want to apply the same format to another cell press the F4 function key.

Once formatted you can sort or filter by colour – see right click options below.