1
00:00:00,120 --> 00:00:06,960
A common question I get is when can I use a pivot table and when should I use formulas?

2
00:00:07,020 --> 00:00:09,770
That generally depends on two things.

3
00:00:09,780 --> 00:00:18,000
Number one is your final design. Is your final report designed something like this? Something that's not

4
00:00:18,000 --> 00:00:19,610
pivot table friendly.

5
00:00:19,710 --> 00:00:25,650
For example here, I want to have America here, the percentage here, Europe on this side.

6
00:00:25,680 --> 00:00:30,820
This can't really be organized in a pivot table. To get this done,

7
00:00:30,900 --> 00:00:33,940
I need to use formulas like this.

8
00:00:34,120 --> 00:00:38,410
This also gives me the ability to be super flexible with my charts.

9
00:00:38,410 --> 00:00:44,060
So here I've created a standard chart and I've just defined the factors that make up this chart.

10
00:00:44,170 --> 00:00:46,270
So this is a stacked column chart.

11
00:00:46,270 --> 00:00:50,480
If you go to select data, I have two series here. In edit,

12
00:00:50,500 --> 00:00:57,610
you see the series name was fixed for America and the number should be this. And the same for the second series.

13
00:00:57,610 --> 00:01:05,880
Name is fixed and the value is this. So I'm very flexible in the design of my charts and the set up

14
00:01:05,880 --> 00:01:07,690
of my reports.

15
00:01:07,750 --> 00:01:11,920
The second report down here is a pivot table report.

16
00:01:11,920 --> 00:01:14,950
It shows the exact same information we have.

17
00:01:15,040 --> 00:01:17,200
It's just in this format.

18
00:01:17,360 --> 00:01:24,110
If it's okay to show the final report in a table format, you can go ahead and use a pivot table.

19
00:01:24,320 --> 00:01:32,440
The second defining factor is the fact that formulas are dynamic and pivot tables need to be refreshed.

20
00:01:32,760 --> 00:01:39,300
As long as no one has set the calculations status of your workbook to manual. Which you can do in the

21
00:01:39,300 --> 00:01:45,900
formulas tab here under calculation options. You can switch the calculation from automatic to manual.

22
00:01:45,900 --> 00:01:50,230
This means that formulas don't update if source data change.

23
00:01:50,250 --> 00:01:56,010
So if this hasn't been set to manual and it's on automatic, the moment you change your source data all

24
00:01:56,010 --> 00:02:02,190
your formula results update. You don't have to take any further action. Whereas with the pivot table you

25
00:02:02,190 --> 00:02:04,080
have to make sure you refresh them.

26
00:02:04,620 --> 00:02:11,280
So these are the two main defining factors that define whether you should use formulas or pivot table

27
00:02:11,340 --> 00:02:12,500
for your analysis.