There are two essential rules when it comes to creating Excel reports.
Number one: No constants inside formulas. Keep constants in separate cells with proper labelling so they
can easily be adjusted when needed.
And exceptions are universal constants like 24 hours in a day, seven days a week, twelve months in a year.
Things that don't change you can hard code in the formula.
Otherwise keep them separately in their own cells.
Here's an example.
We have the service amount and we have total charges here.
We don't exactly see where the difference is coming from.
It's been hardcoded in the formula.
We have a 20% VAT rate. The right way of writing this is to put VAT separately in its own cell.
So that is not only transparent but in case the VAT rate changes or we're dealing with other countries
that have different VAT rates we can easily update that number here and the total charges dynamically
reflect this.
The second essential rule is that formulas in a range should be consistent.
Don't adjust formulas in the middle of a range or remove them entirely if they result in an error.
Always update the first formula in the range to handle different scenarios.
So here you can use different functions that we're going to be covering in the next section.
For example the IF function or the IFERROR function.
Here's an example of what not to do.
Here we have calculated percentage change but once we drag it down, this cell resulted in an error.
Why?
Because we have 200 divided by text.
It's an error. So the wrong thing to do is to go in and delete that formula because the moment we get
a number here. Let's just put a 100.
Nothing is going to calculate here. The right way of doing this is to account for these different scenarios
by using the right function.
Here, I've used the IFERROR function and have actually dragged this all the way down.
So now if we change this to 100 and press enter, this is going to calculate. I'm not gonna go in detail about
the IFERROR function here.
It's something we're going to cover in detail in the next section.
I just wanted to show you that the right way of doing this is to find a function that you can apply
in the first cell and drag everything down so all the formulas in that range are consistent.