Top 5 Books
A recent email from CPA Australia listed my Excel book (
Advanced Excel Reporting for Management Accountants) in the top 5 of all books – see list below.
Members of CPA Australia can access electronic books for
FREE via ProQuest – links are below.
You will need to log in to the CPA Australia site to be able to access them.
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.
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.
Sometimes Excel surprises me. In this case it sorts in a way I didn’t expect, but in a good way. Thanks to Mr Excel for the tip.
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.
Adding Values to Values
Let’s say you have an input cell that someone enters multiple values into eg
You may have many such cells.
Now let’s say you want to add 20 to all these cells but keep the original values that have been entered in those cells.
Paste Special to the rescue.
Enter 20 in a blank cell and then copy the cell
Select the cells you want to amend – you can hold the Ctrl key down to select multiple cells with the mouse
Open the Paste Special dialog
Click the options Values and Add – as per image below – then click OK – done!
The resulting formula will be something like
If you are using date-based headings in your reporting models please consider using dates in the headings rather than text. I’ll explain why.
Find and Replace Tips, Tricks and Traps
The keyboard short for Find is Ctrl + f.
For Find & Replace it is Ctrl + h.
Always, I mean
ALWAYS, select the range you are working with before you run Find and Replace.
If you have a single cell selected it will affect the whole sheet – maybe not what you want.
Leave the Dialog Open
In the old days we used to close the Find dialog. Now you can leave it open if you need to change things in multiple files or sheets. You can navigate around with the dialog left open.
When using Find if you want to select all the cells it has found, click in the bottom section of the Find dialog where the cells are listed and press Ctrl + a this will select all the cells at once.
Be aware that sometimes numbers won’t be found due to formatting. eg if you search for 1000 but you have used the comma format eg 1,000 then the number might not be found.
You might need to do two Finds, with and without commas.
Formulas vs Values
Click on the Options button to see these options.
The default Look in: setting for Find (unfortunately) is Formulas. See image below.
This means if a formula returns what you are looking for it won’t be found. You need to switch this Look in: setting to Values.
Time to upgrade my Spreadsheet Mug
The dishwasher has taken a heavy toll on my old mug.
I think the new version has a slightly lower capacity.
The NETWORKDAYS.INTL function was added in Excel 2010. It allows to calculate how may work days between two dates using non-standard weekends. Some countries don’t have Saturday/Sunday weekends.
I found a good shortcut today to increase the decimal places.
Alt H 0
For example you might use these two shortcuts one after the other to format a range as a percentage with one decimal place.
Ctrl + Shift + %
Alt H 0
Some Accounting systems (I think SAP is one) downloads negative values with a trailing minus sign. Excel doesn’t recognise this as a number. When you import TXT files, negatives are handled correctly. CSV files don’t.
Making Subtotals Bold
When you use the SUBTOTAL feature in the Data ribbon tab it automatically inserts subtotals in your list – see
blog post on it here.
One problem with this is that is only makes the cell with the word Total bold – it doesn’t make the whole row bold.
If you want the whole row to be bold it isn’t hard to fix.
Select the whole range involved.
Use the grouping button 2 top left corner. See image below.
Then hold the Alt key down and press the ; (semicolon key) – this selects just the visible cells.
Then press Ctrl + b to bold it.
Click another cell to reset the range and you are done.
I use Alt key shortcuts a lot when I am working and I have found a couple more useful ones.
Printing Separate Print Areas
If you need to print a number of separate print areas from the one worksheet in one step there is an option that allows you to create a print area that includes additional ranges.
Create your first print area as per normal.
Select another print range and click the Page Layout ribbon and use the Print Area drop down and choose Add to Print Area. Keep adding ranges as you require.
You can also use the Ctrl key and mouse to select multiple separate ranges and then use Set Print Area.
The above technique allows you to add to that Print Area if you miss a range.
The only problem with this option is that each separate range is printed on a separate page(s).
I have previously posted about using CutePDF to create pdfs from Excel sheets. There is another way, but it takes a few clicks and it only works in Excel 2010 and later versions. (It may work in Excel 2007 but I have taken that version off my PC so I can’t test it.)
Close All Icon
If you have a lot of files open and you want to close them all and still leave Excel open you can add this icon to the Quick Access toolbar.
this post if you are not sure about using the Quick Access Toolbar.
Do you use the “Filter by Selected Cell’s Value” option? If you do then you will be pleased to know there is a Quick Access Toolbar icon that applies it in one click.