My consulting work recently highlighted a stark contrast in different Excel models and the effort it takes to create or change them. I make some recommendations to make things easier for yourself at the end of this post. (more…)
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. The accounting books CPAs love Which books have your fellow accountants found most helpful in advancing their careers? You may…
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…
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. (more…)
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…
We’ve all heard the term “A month of Sundays” to describe a long time. Well what if you wanted to count how many Sundays between two dates? (more…)
Let's say you have an input cell that someone enters multiple values into eg =10+5+34 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…
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. (more…)
The keyboard short for Find is Ctrl + f. For Find & Replace it is Ctrl + h. Tips 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…
Inquire is a new add-in in some versions of Excel 2013 and later versions It is an auditing Add-in that can analyse and report on your Excel files. (more…)