FREE Webinar – Financial Functions
Excel has many functions dedicated to financial calculations. These functions take three or more arguments and there are a few things you need to know to use them correctly.
Most involve loans or calculations associated with the time value of money.
This session will cover the following functions
PV – Present Value
PMT – loan payments
CUMIPMT – cumulative interest for loan schedules
RATE – interest rate
FV – future value
NPER – number of period
The session finishes with a loan model that calculates the “missing” value for a loan scenario.
These sessions fill up very quickly, so please register early to secure your place.
February 26, 2016
1:00 PM AEDST
FREE Webinar - Excel Financial Functions
here to register.
PDF and Power BI
Looks like Power BI will soon be able to extract data from tables in PDF documents.
Great to see requests being acted on.
Katherine S. Rowell, M.S., M.H.A.
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.
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.
Clear Borders In Excel
If you need to clear all the borders from a selected range use
Ctrl + Shift + _ (underline)
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.
Adam Saxton - Microsoft
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.
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)
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.
Avocado article on the ABC site
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.
Disclaimer: I received a free copy of this book to review.
This book is a great introduction to Financial Modeling.
The content is easy to follow and gets you started on the right foot with lots of best practice advice.
The only downside of the book is the screen shots tend to include the whole screen rather than zoom in on the important part of the screen – many images are hard to read.
The book is a great primer for
Danielle’s first book
which allows you to learn even more about financial modeling.
It takes less time to do a thing right than if does to explain why you did it wrong.
Henry Wadsworth Longfellow (1807 – 1882)
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)
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.