There are a couple of techniques to automate a unique list of items in Excel. I have covered them in previous blog posts (see links below). I thought I would describe how to use Power Query to create a dynamic unique list.
What is the best layout when working with months/quarters/half years and full years? There are a few common structures. I prefer the one that lets you create single formulas that can be quickly copied across and down with as few copies as possible.
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.
I was working on a project for a client and receiving multiple files. Some of the sheets had hidden rows or columns. I realised there is no easy way to find out if a sheet has hidden rows or columns, so I wrote a macro.
It is our attitude at the beginning of a difficult undertaking which, more than anything else, will determine its successful outcome.
William James (1842 – 1910)
When you create formulas that refer to other sheets Excel typically includes the name of the current sheet when you return to the current sheet and refer to a cell.
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.
An Avo Chart
I wonder if we can get one of these on Power BI?
Saw this on the website below and liked it – I also like Avocados.
We must always change, renew, rejuvenate ourselves; otherwise we harden.
Johann von Goethe (1749 – 1832)
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.
Here’s the problem, we have four separate tables with the same layout. They hold four different metrics: Actuals, Budget, Forecast and Last Year. A column called Type is used to hold the metric name. We need to populate the Type column.
Financial Modeling in Excel for Dummies (Wiley, 2017)
It takes less time to do a thing right than if does to explain why you did it wrong.
Henry Wadsworth Longfellow (1807 – 1882)
When you copy a sheet that contains range names you usually end up making a duplicate of those names at the Worksheet level. I have written a macro that removes all duplicated sheet-based range names in a file.
Of all the liars in the world, sometimes the worst are your own fears.
Rudyard Kipling (1865 – 1936)
Correction does much but encouragement does more.
Johann von Goethe (1749 – 1832)
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.
The secret of joy in work is contained in one word – excellence. To know how to do something well is to enjoy it.
Pearl S. Buck (1892 – 1972)
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.
Which books have your fellow accountants found most helpful in advancing their careers? You may find the results surprising, with their top five covering everything from Excel and analytics to forecasting and getting your own way. Try these ebooks for yourself.
|Killer analytics: Top 20 metrics missing from your balance sheet|
|Persuasion equation: The subtle science of getting your way|
|Advanced Excel reporting for management accountants|
|Financial forecasting, analysis and modelling: A framework for long-term forecasting|