1
00:00:03,040 --> 00:00:07,340
There are two essential rules when it comes to creating Excel reports.

2
00:00:07,410 --> 00:00:14,730
Number one: No constants inside formulas. Keep constants in separate cells with proper labelling so they

3
00:00:14,730 --> 00:00:17,620
can easily be adjusted when needed.

4
00:00:17,620 --> 00:00:24,150
And exceptions are universal constants like 24 hours in a day, seven days a week, twelve months in a year.

5
00:00:24,240 --> 00:00:27,800
Things that don't change you can hard code in the formula.

6
00:00:27,960 --> 00:00:31,830
Otherwise keep them separately in their own cells.

7
00:00:31,830 --> 00:00:33,520
Here's an example.

8
00:00:33,630 --> 00:00:37,140
We have the service amount and we have total charges here.

9
00:00:37,170 --> 00:00:40,260
We don't exactly see where the difference is coming from.

10
00:00:40,260 --> 00:00:42,180
It's been hardcoded in the formula.

11
00:00:42,180 --> 00:00:49,580
We have a 20% VAT rate. The right way of writing this is to put VAT separately in its own cell.

12
00:00:49,710 --> 00:00:55,260
So that is not only transparent but in case the VAT rate changes or we're dealing with other countries

13
00:00:55,260 --> 00:01:01,470
that have different VAT rates we can easily update that number here and the total charges dynamically

14
00:01:01,530 --> 00:01:02,880
reflect this.

15
00:01:02,880 --> 00:01:08,040
The second essential rule is that formulas in a range should be consistent.

16
00:01:08,040 --> 00:01:13,610
Don't adjust formulas in the middle of a range or remove them entirely if they result in an error.

17
00:01:13,620 --> 00:01:18,240
Always update the first formula in the range to handle different scenarios.

18
00:01:18,240 --> 00:01:23,580
So here you can use different functions that we're going to be covering in the next section.

19
00:01:23,580 --> 00:01:27,350
For example the IF function or the IFERROR function.

20
00:01:27,390 --> 00:01:30,200
Here's an example of what not to do.

21
00:01:30,280 --> 00:01:37,610
Here we have calculated percentage change but once we drag it down, this cell resulted in an error.

22
00:01:37,650 --> 00:01:38,030
Why?

23
00:01:38,040 --> 00:01:40,630
Because we have 200 divided by text.

24
00:01:40,680 --> 00:01:47,910
It's an error. So the wrong thing to do is to go in and delete that formula because the moment we get

25
00:01:47,910 --> 00:01:50,700
a number here. Let's just put a 100.

26
00:01:50,700 --> 00:01:58,080
Nothing is going to calculate here. The right way of doing this is to account for these different scenarios

27
00:01:58,110 --> 00:02:00,180
by using the right function.

28
00:02:00,180 --> 00:02:05,760
Here, I've used the IFERROR function and have actually dragged this all the way down.

29
00:02:05,790 --> 00:02:13,530
So now if we change this to 100 and press enter, this is going to calculate. I'm not gonna go in detail about

30
00:02:13,530 --> 00:02:15,250
the IFERROR function here.

31
00:02:15,360 --> 00:02:18,300
It's something we're going to cover in detail in the next section.

32
00:02:18,300 --> 00:02:24,540
I just wanted to show you that the right way of doing this is to find a function that you can apply

33
00:02:24,900 --> 00:02:30,570
in the first cell and drag everything down so all the formulas in that range are consistent.