about Styles and how they can make your formatting more consistent
that colours can be used to filter, sort and even find things in your sheets
how to stop zeroes displaying plus other general formatting tips
a quick demo of Flash Fill
CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.
When you create an Excel file that handles inputs it is best practice to colour code the input cells. The colour you choose isn’t important but making sure you use it consistently is. You may need to unlock the input cells if you plan to add sheet protection to the file. Here’s a couple of ways to do that.
In July 2019 I explained and demonstrated a number of Excel’s financial functions – see below for more details.
CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.
In July 2019 following on from the response and feedback from my June webinar, I covered more financial functions. These are more related to comparing and analysing cash flows. I have also included a couple of requested schedules.
This session will cover the following functions
NPV – Net Present Value of regular cashflows (learn to trick to using it correctly)
XNPV – Net Present Value of irregular cashflows
IRR – Internal Rate of Return of regular periodic cashflows
XIRR – Internal Rate of Return of irregular periodic cashflows
Discounted Payback period schedule
Flexible Loan schedule – handles lump sum payments
As with all my sessions, I will throw in a few other shortcuts along the way.
Many of Excel’s functions have default arguments. What are arguments? Arguments are the sections within a function that you separate with commas and some of these arguments are optional.
In June 2019 I explained and demonstrated a number of Excel’s financial functions – see below for more details.
CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.
These functions take three or more arguments, but 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 covered the following six functions
PV – Present Value of future regular cash outflow
PMT – periodic loan repayment calculation
CUMIPMT – cumulative interest – great for loan schedules
RATE – interest rate
FV – future value of regular investment
NPER – number of periods
The session finishes with a loan model that calculates the “missing” value for a loan scenario based on two out of three inputs. The IFERROR function is also discussed.
Excel’s Format as Table feature on the Home ribbon has many advantages. One advantage that isn’t mentioned much is the automatic extension of Data Validations.
Fastest way to copy an image, graphic or chart
To copy an image, graphic or chart simply have the object selected and press Ctrl + D. You can press multiple time to paste multiple times.
If you line the first one up then the others will also line up as you duplicate them.
One of the cardinal rules of Excel is don’t key-in a value into a formula if that value could change. Tracking the value down could be problematic if you need to change the value. Tables can be the solution to avoiding keyed-in values.
In some cases you may have to make manual inputs across multiple cells that are spread across a sheet. Before making the entries you need to clear the existing entries. Creating a range name can make that process much quicker.
Inserting a Comment (renamed to Notes in newer versions of Excel) in the first row and then using Freeze Panes to keep that first row visible can cause issues displaying the Comment (Note). Here’s how to fix it.
