Charts

Question 1

1. What is the name of the feature that allows you to quickly preview your data in various ways (charts, data bars, etc.)?
2. How do you invoke this feature?
3. What is the best practice when previewing data in this way?

Answer 1

1. Quick Analysis
2. Select cells containing data, then use the Ctrl + Q keyboard shortcut.

Alternatively, you can hover your mouse over the Quick Analysis tooltip that appears after selecting data and click the button - then you select Bars or Charts and hover your mouse over the various options to preview the data.

3. For best results, ensure that there is is a header row and header column as these will be used as the X and Y Axis labels for charts

Question 2

What are two best practices when working with data that will be used in charts?

Answer 2

1. When selecting data, do capture row and column headers as these will affect the chart labels
2. When selecting data, do not capture any totals as this will skew the graph

Question 3

What type of chert works best for data that uses time as a measurement?

Answer 3

Line Charts - specifically Line With Markers is often a good choice for reviewing time based data.

Question 4

1. What two types of data are best for Pie Charts?
2. What is the limitation of pie charts?

Answer 4

1. Budgets and Revenue by Business Line or Sector
2. You can only show data for one row or one column; you also can’t have negative values - Pie Charts show absolute valuies

Question 5

What is the mouseover icon that quickly reveals which Chart Elements can be turned on or off?

Answer 5

The plus + symbol

Question 6

1. What is the feature called where you can add trendlines, bar columns, or win/loss visuals within individual cells to show data patterns?
2. How do you invoke this feature?

Answer 6

1. Sparklines
2. You select data > click on the Quick Analysis mouseover icon > go to the Sparklines tab > Select Line, Column, or Win/Loss

Alternatively, you can go to the Insertribbon tab and click onLine,Column, orWin/Lossin theSparklinesub-section > then either select yourData Range(which theSparklineswill be based on) and/orLocation Range(where you want theSparklines to appear)

Question 7

1. What can be added within a cell to show trends in data?
2. Why is this useful?
3. What was the old term for this in Excel
4. Describe the steps for the simplest method to add these ‘in cell’ visuals in Excel

Answer 7

1. Sparklines
2. This is useful if you want to see trends in data without creating a full blown chart
3. Select your data > hover over the Analysis icon > select Sparklines tab > select Line, Column, or Win/Loss > select the area you want the Sparklines to be added |

Question 8

1. What can be added the Sparklines to give you a point of reference to your data?
2. When would you want to do this?
3. What Sparkline type would this work especially well with?
4. How do you add this?

Answer 8

1. Axis
2. If you have data that goes below zero
3. Column Sparklines
4. Select your Sparkline data > Sparkline (conditinal) ribbon tab > Axis button

Question 9

In which ribbon tab can see which Chart Element is currently selected (or change the selected Chart Element)?

Answer 9

The Format ribbon tab - this will conditionally show up when you’ve clicked within a chart.

Question 10

How do you toggle chart elements on and off?

Answer 10

The + mouseover icon quickly reveals which Chart Elements can be turned on or off

Question 11

How do you reveal more granluar options for Chart Elements?

Answer 11

Either Double Clicking on a Chart Element or Selecting the Chart Element from the Format ribbon tab Chart Element Dropdown List will reveal more granular configuration options & controls on the Right Toolbar

Question 12

Name at least six types of charts in Excel.

Answer 12

1. Treemap
2. Sunburst
3. Histogram
4. Pareto
5. Box and Whisker
6. Waterfall
7. Pie chart
8. Line chart
9. Bar chart