Pivot Tables

Question 1

Why are pivot tables so important in data analysis?

Answer 1

Pivot Tables allow us to easily organize, filter, summarize and analyze data.

Question 2

What are the 5 key characteristics that make pivot tables so important?

Answer 2

1. Powerful: uncover insights and answer key questions about your data;
2. Beautiful: Apply custom styles and conditional formatting rules to bring your pivots to life;
3. Fast: Create custom views. filters and calculated fields on the fly;
4. Accurate: Automate calculations to minimize human error;
5. Flexible: Manipulate table layouts and create dynamic views in seconds.

Question 3

What 7 characteristics contitute a good data structure in Excel for Pivot Table purposes?

Answer 3

1. Rectangular with no gaps;
2. Columns represent variables;
3. Rows represent the observations of those variables;
4. No extra formating;
5. Contain only dimensions (categorical fields e.g. tall, short etc.) and measures (numerical fields e.g. 55, 134, 10.345);
6. Clear column headers;
7. No subtotal or calculated fields; just raw data.

Question 4

Explain each field on the Pivot Table Fields pane.

Answer 4

Question 5

How can you select, copy or clear from a pivot table?

Answer 5

Question 6

When working with Pivot Tables, what are the two types of source data?

Answer 6

Static and Dynamic

Question 7

What is a static data source?

Answer 7

When there is one data table in which the data never changes.

Question 8

What is a dynamic data source?

Answer 8

A table that contains data that changes i.e. data is updated or added or removed.

Question 9

How can Pivot Table data be refreshed or updated?

Answer 9

Question 10

What must you do after you change the data from the data source?

Answer 10

Hit the refresh button in the Pivot Table Analyse tab.

Question 11

What happens if you add a column or row within a data range being used by a Pivot Table?

Answer 11

The Pivot Table adapts to it and co-opts the new data.

Question 12

What happens if you add a column or row in the spaces adjacent to a data range being used by a Pivot Table?

Answer 12

The new data will not be used by the Pivot Table.

Question 13

What expedient should be used in the below image to deal with Pivot Table growing data sources?

What is another way to make the Pivot Table more responsive to changes in the source data?

Answer 13

Change it to column only absolute references. That way the Pivot will catch anu new data added to the variables (columns).

Format the data as a table.

Question 14

What are the 2 major advantages of formatting Pivot Table source data as a table?

Answer 14

1. Tables update automatically as you add data;
2. You can rename the table and use that name as the range for the Pivot Table - which will include any data inside the table (which auto-updates).

Question 15

What is the Pivot Table cache?

Answer 15

It is a copy of the source data - this copy is where the pivot table actually gets its working data from.

Question 16

If you delete the original data source used by a Pivot Table, how can you use the cache to recover that data?

Answer 16

Pivot Table Options > Options > “Data” tab > check “Save source data with file” and “Enable show details”

before you create your Pivot and delete your data.

If you ever need to recover your data, unfilter the Pivot Table and double-click on one of the totals.

Question 17

How Pivot Tables actually work?

Answer 17

Question 18

What happens when you double-click any value on a pivot table?

Answer 18

A new tab, showing the source data used for determining that value, will be generated. This will be a subset of the original data.

Question 19

How can you format numbers on a pivot table?

Answer 19

Right-click the header cell and select “Number Format…”

Question 20

How can you replace any blank cell in a pivot table with something else?

Answer 20

In the PivotTable Analyse tab, all the way to the left, use the Options button.

Question 21

What’s the default columns display style for pivot tables?

Answer 21

Compact table style.

Question 22

How can layouts be changed for pivot tables?

Answer 22

In the Design Tab, all the way to the left.

Question 23

What is the below pivot table layout called?

Answer 23

Outline

Question 24

What’s one use of rendering Pivot Table data in tabular formatting?

Answer 24

It allows us to copy the data (as a proper table) into a database.

Question 25

How do you customize column header labels?

Answer 25

Select the cell and then change in the formula bar.

Question 26

How can conditional formatting be applied to Pivot Tables?

Answer 26

Question 27

How can you achieve the below conditional formatting?

Answer 27

Duplicate the column with the data you want to conditionally format, select the data range and right-click to open the Number Format dialog box. In there, select Custom and then type three semicolon (;;;). This renders the text invisible.

Question 28

How can you sort and filter in Pivot Tables?

Answer 28

Question 29

Why do date names (e.g May, Sunday etc.) come before all else when you filter A to Z?

How do you change that?

Answer 29

Because they are part of Excel’s inbuilt custom lists. These have precedence over other list elements.

Go to PivotTable Options and, under Totals&Filters, uncheck the “Use Custom Lists when Sorting” box.

Question 30

With regards to Pivot Table filtering, what are the two wild-card options in the Label Filter section?

Answer 30

? - representing any single letter

* - representing any group of letters

Question 31

How are multiple fitlters enabled in Pivot Tables?

Answer 31

PivotTable Analyse > Options > Totals & Filters > check the “Allow Multiple Filters Per Field” box

Question 32

How can lines in a Pivot Table be grouped?

Answer 32

Select the group, right click and select “Group”

Question 33

In Pivot Tables, how can you manipulate Grouping settings?

Answer 33

Pivot Table analyse > Groups

Question 34

With Pivot Tables, what’s the default display of date fields?

Answer 34

They are grouped.

Question 35

In Pivot Tables, what are Slicers and Timelines?

Answer 35

Question 36

What does the “Show Report Filter Pages” option do?

Answer 36

Question 37

How is sumarization achieved in Pivot Tables?

Answer 37

Question 38

What summarizing issue occurs when there is one or more blank or non-numerical cells in a column of the raw data? How can you avoid it?

Answer 38

It automatically reverts to the “count-of trap”.

You avoid it with vigilance and then replacing empty cells with a zero.

Question 39

What is the “Show Values As…” option?

Answer 39

Question 40

Think of different ways the “Show Value As” option can be used, as opposed to just the standard “Sum of” or “Count of”.

Answer 40

Question 41

What should be done to the original Pivot Table before adding the “Show Value As” custom calculations?

Answer 41

Do any sorting that you need to do because afterwards you are not allowed.

Question 42

What is the “Show Value As: Index” used for?

Answer 42

Gauging the weight that a figure bears in relation to a total i.e. how much of a percentage change would there be if the figures were chaged.

Question 43

What are Calculated Fields?

Answer 43

Question 44

With “Calculated Fields”, how are the elements of the formula treated as by the formula?

Answer 44

They are all treated a the “sum”.

Question 45

How is percentage change calculated?

Answer 45

(A - B)/B

Question 46

How is ROI calculated?

Answer 46

Revenue/Investment