key: cord-0060696-u6dkkn1t
authors: Slager, David; Slager, Annette
title: Working with Charts
date: 2020-10-22
journal: Essential Excel 2019
DOI: 10.1007/978-1-4842-6209-2_13
sha: 64a331c26f023e98f976e178fa3a310847bacd18
doc_id: 60696
cord_uid: u6dkkn1t

Where would we be without charts? Not only are charts more appealing to our eye than raw data, but they also make it much simpler for us to spot trends. The old saying “a picture is worth a thousand words” also applies to charts. Managers don’t always want to look at every record in a spreadsheet; they would rather make their decisions based on trends, whether they be good or bad. By looking at trends, management can decide what actions the company should take in the future. Charts can summarize vast amounts of data and show relationships between data. Charts make it easier to see such things as what lines of product are more profitable, what the most profitable time of year is, whether the profitability of the company is on the rise or going down, what departments are doing well and which are not, and on and on. Types of charts covered in this chapter are pie, pie of pie, pie of a barchart, combination charts, treemap, sunburst, funnel, map and sparklines. The Bing Map and Map Chart allow you to chart data related to a geographical area. Sparklines are small charts displayed in individual cells. They are used for highlighting trends within a single row or single column. There are three types of sparklines: Line, Column, and Win/Loss.

Where would we be without charts? Not only are charts more appealing to our eye than raw data, but they also make it much simpler for us to spot trends. The old saying "a picture is worth a thousand words" also applies to charts. Managers don't always want to look at every record in a spreadsheet; they would rather make their decisions based on trends, whether they be good or bad. By looking at trends, management can decide what actions the company should take in the future. Charts can summarize vast amounts of data and show relationships between data. Charts make it easier to see such things as what lines of product are more profitable, what the most profitable time of year is, whether the profitability of the company is on the rise or going down, what departments are doing well and which are not, and on and on.

After reading and working through this chapter, you should be able to 

Excel provides a wide variety of chart types to choose from to best display your data.

What type of chart you should use depends upon the data you are charting. Before creating a chart, you need to decide what it is you want the chart to say. Once you have a chart selected, you can change the format of the entire chart by changing its theme, or you can change the individual components of the chart. Excel provides a wide variety of different chart options. As you will see later, Excel looks at the data you have selected, makes a quick analysis of it, and then provides recommended chart types for your data. The Ribbon's Insert tab in the Charts group displays the available chart types. Clicking a down arrow next to one of the chart types will display available variations of that chart type. See The Recommended Charts are just a suggestion. You don't have to use Excel's suggestion. The All Charts tab shows all the chart types in the left pane. Clicking a chart type in the left pane will display all the variations of that type in the right pane. See Figure 13 -4. The charts displayed here are using the data you currently have selected on your worksheet. In the left pane, there is an option to view your most recent chart. 

There are several ways to create a chart, including using the options on the Insert tab shown in the preceding section. The easiest way to create a default 2D Clustered bar chart is by selecting the data you want to display in your chart and then • Pressing the Alt + F1 key if you want to display the chart on the same worksheet as the data, or

• Pressing F11 if you want the chart to appear on a separate worksheet Once you have created your chart, you can change it in so many ways. Some of the changes you can make are the following: Note Don't get carried away with trying to make your charts too fancy and overdoing colors and so on. the most important thing is how well your chart conveys the information that you want it to and how easy it is for the intended audience to read and understand. the audience should be able to quickly grasp what the chart is conveying. 1. open workbook Chapter 13. Click the game sales tab. Figure 13 -5 shows the data.

2. select the cell range a2:g7. press alt + F1.

Your chart is in a moveable frame. Move your cursor on the chart frame. Your cursor should display as four arrows .

3. Drag and drop the chart to whatever location you want.

4. increase the size of the chart by placing your cursor on the bottom right of the chart frame and then hold down the shift key while you drag the cursor toward the bottom right. holding down the shift key makes the chart retain the same width-to-length ratio.

When a chart is selected, excel displays two new tabs on the ribbon: Design and Format. the two tabs are under the Chart tools grouping.

Note a chart consists of many different individual elements. What the elements are depends upon the type of chart and the data being charted.

on the ribbon, click the Format tab. in the Current selection group, the Chart elements drop-down box displays the current item selected in the chart. selecting an item from the Chart elements list will select that item on the chart. 11 . on the ribbon's Format tab, in the Current selection group, click Format Selection.

Clicking Format Selection formats the object selected in the Chart elements drop-down Box. since Chart title is the current object, clicking Format selection brings up the options for formatting the Chart title:

• the Title Options menu has options that affect the area inside the Chart title area but outside the text itself. see Figure 13 -7.

• the Text Options menu has options that affect only the text. 14. pick the color orange, accent 2, Lighter 60% from the Color option. see Figure 13 -8.

15. Change the transparency to 50%. e. select the last pattern in the last row (solid Diamond). see Figure 13 -12. excel has a series of predefined styles that you can select from. the style buttons reflect the type of the current chart. since we are using a bar chart, the style buttons display styles for bar charts. see Figure 13 -16. While working on a pie chart, the style buttons would display pie charts. 3. Move your mouse over the style buttons while noticing how it changes your chart. a tool tip displays the name assigned to each button such as style 1, style 2, and so on.

Click the Style 6 button. When the chart is selected, you will see three buttons to the right of it. see Figure 13 -18.

if you click anywhere off the chart, these three buttons will not display. 2. Move your cursor over each of the charts as you scroll through them to see how each option will affect your chart. these are the same styles that appear on the ribbon's Data tab in the Chart styles group.

3. Click style 14. Figure 13 -20 shows the results. 2. Move your cursor over each of the layouts to see how they alter the look of your chart.

Changing Chart Types 1. on the ribbon's Design tab, in the type group, click the Change Chart type button. a list of chart categories appears in the left pane of the Change Chart type window. the upper right pane contains variations of the chart type selected in the left pane.

Clicking one of the variations displays the results in the bottom right pane. see Figure 13 -23. there are two available charts in the lower right pane. the difference between these is that one uses the column headings for the horizontal axis and the other chart uses the row headings for the horizontal axis. Move your cursor over the two charts; they will enlarge so you can get a better view of them. 3. two clustered bar charts appear in the pane below. Click the chart on the right side.

4. Click the ok button. see Figure 13 -24.

notice that the buttons in the Chart styles group and those in the Quick Layout have changed to reflect that the chart is now a bar chart.

5. in the Data group, click the switch row/Column button.

the column headings from the spreadsheet are now used for the vertical axis and the row headings become the legend. see Figure 13 -25. this chart makes it easier to see how much difference there was in sales for each individual game during the six-month period. there is an up and down arrow to the right of the remove button. these buttons, as shown in Figure 13 -27, are for moving the current legend up or down along with its series.

originally, we had a column chart, and the risk game was represented by the first column in each month's group. When we changed to a bar chart, the series was reversed, and the risk game became the last bar in each month's group. it seems strange, but in the select Data source dialog box, risk is at the top even though it appears at the bottom of a bar chart. 12. Click the ok button.

notice that no changes have been made to the spreadsheet data, only to the chart. the legend and chart series for stratego have been removed, and all the June data has been removed from the chart.

13. Let's add another game to the spreadsheet. add the line for the game Battleship as shown in Figure 13 -28.

14. Click the chart to select it. Click the Design tab on the ribbon. in the Data group, click the select Data button.

15. Click the add button. the edit series dialog box appears. 16 . Click the series name text box.

17. Click the word Battleship in cell a8.

18. Delete the text that is currently in the series values: text box.

19. Drag across the cell range B8:g8 to get the sales data for the Battleship game. see Figure 13 -29. 21. Click the ok button for the select Data source dialog box.

the series color for risk and Monopoly are very close, and it is hard to distinguish one from another. We will change the color of the risk series to red.

1. on the ribbon's Format tab, in the Current selection group, click the down arrow for Chart elements and then select series "risk."

2. in the Current selection group, click the Format selection button.

3. in the Format Data series pane, click the Fill & Line button . You can make a bar color a solid color, a gradient, or fill it with a pattern or a picture.

4. select solid.

5. select the red color from the Color button. see Figure 13 -30.

You have learned how to create column and bar charts. Column and bar charts work well for multiple columns of numeric data. Next, you will learn how to create pie charts. Pie charts should only use a single column of numeric data.

Pie charts are used to show percentages. The entire circle represents 100%. Each item in a data series is represented by a slice of the pie. The size of each slice shows what part of the 100% it represents. Sometimes you may find that the information in one slice needs to be broken out into percentages itself. You can use the pie of pie subtype in such circumstances. In this section, we'll first look at the standard pie chart and then cover the pie of pie subtype.

The Standard Pie Chart

In Figure 13 -31, we have ten pieces of fruit. The entire pie represents 100% of our fruit. There are five bananas so they represent 5/10 or 50% of our fruit. Therefore, the bananas represent one-half of our pie. The slice for Apples takes up 30% of the size of our pie. excel recommends that you use a clustered column chart, a pie chart, or a clustered bar chart for the data you have selected. if you were to click the Clustered Column button, excel would create a clustered column chart using the selected data on the current worksheet.

4. Move your cursor over the chart buttons to see how the chart would look with your selected data.

5. Click the pie chart. Click the chart.

6. Click the Chart styles button to the right of the chart.

the Chart styles box shows various ways of displaying the current chart type. Let's select a chart that displays the label and percent on each slice of the pie. having a label on each slice of the pie makes having a legend unnecessary.

7. Click pie Chart style 10, which you can see in Figure 13 -33. 17. to create some separation between the pie slices, change pie explosion to 2%. see Figure 13 -36. 

A pie of pie chart takes a slice of a pie chart and then breaks that slice into another pie chart. One reason for doing this is that your pie chart contains too many items and some of the pie slices are so small that you can't tell what they are. Another reason might be that one of the pie slices is a category that you want further broken down. Annette owns a soup and sandwich shop. She wants to see the breakdown of her drink category. She wants to see what types of drinks customers purchased this month. This can be done by pulling the drink category piece of the pie away from the others. This is called exploding the pie. You can explode the entire pie or individual slices.

in this exercise, you will be creating a pie chart for a sandwich shop to show what percent of sales are represented by sandwiches, soup, ice cream, and drinks. the shop decided that it would also like to see a breakout of its drink sales to see what percentage of its drink sales is represented by pepsi, Coke, 7Up, and sprite. You will create a pie of pie chart to display each of the different types of drinks, and then you will represent the same drink data with a bar of pie chart. When creating a pie of pie chart, excel takes the items at the end of your selection for the second pie chart so your data needs to be entered accordingly.

2. select cell range a1:B8.

3. on the ribbon's insert tab, in the Charts group, click the pie Chart button. 4 . select the second option (pie of pie) in the 2-D pie area. see Figure 13 -39. excel took the last three items by default and made them the second chart. see Figure 13 -40. We are going to need to change that to the last four items. For this example, you can get the same result by changing the Split Series By to Value. When you select Value, excel displays a combo box asking for Values less than. since pepsi has a value of 13, you will need to change the value to 13.1 to move the pepsi slice to the second chart.

2. select Value for the Split Series By.

3. enter 13.1 for the Values less than. press enter. see Figure 13 -46.

1. Click the Undo button or press Ctrl + Z to remove the drinks from the second chart.

Changing the option for the Split Series By combo box to percentage value brings up the Values less than combo box just as it does when selecting Value. pepsi on the chart displays a percentage value of 13%. For this example, the values and the percentages are the same because percentage values were used for the spreadsheet data. to move the pepsi slice to the second chart, we need to change the value in the Values less than.

2. select percentage value for the Split Series By.

3. Click the up arrow for Values less than until it is at 14. see Figure 13 -47.

Changing the Value of Split Series By to Custom 1. Click the Undo button to undo moving pepsi to the second chart.

You can move slices from one chart to the other by selecting Custom from Split Series By and then clicking the pie slice you want moved to the second chart and then selecting which chart you want to move it to from the Point Belongs to combo box.

2. select Custom for the Split Series By.

3. Double-click the pepsi pie slice on the first chart. in the point Belongs to drop-down box, select second plot. see Figure 13 -48. 5. Click one of the charts. then in the Format Data series pane, try the following options:

• the Pie Explosion is used for putting spacing between the pie slices. the higher the number, the more the charts explode out.

• the Gap Width is used for changing the amount of space between the two charts.

• the Second Plot Size is used for changing the size of the second chart.

the bar of pie chart works the same way as the pie of pie chart.

1. select the Chart frame.

2. on the ribbon's insert tab, in the Charts group, click the pie Chart button.

3. select the third option (Bar of pie) in the 2-D pie group. see Figure 13 -50. You have learned how to create pie charts and even learned how to break down a slice of the pie chart into another pie chart or a bar chart. Next, you will learn how to combine two chart types into a single chart called a Combination Chart.

A combination chart combines more than one chart type in a single chart. The most common combination chart consists of a bar chart for one series and a line chart for another series. Figure 13 -52 shows a combination where the sales data and predicted sales data are shown on the same chart. The sales data is displayed in a column format and the predicted sales data in a line format. This makes it easy to see which predicted 

in this example, you will create a combination chart consisting of a column and a line chart. Looking at Figure 13 -53, you can see that there is a large disparity in the values between the two columns. as you will see this, disparity prevents us from creating an accurate column chart.

1. open workbook Chapter 13. select the worksheet named Combination. Figure 13 -53 shows the data. c. Click Clustered Column. Figure 13 -54 shows the result.

You can see that this chart isn't very useful. there is far too much disparity between the values for homes sold and the average price.

3. Click one of the average price columns in the chart to select the average price series. 6. Click the down arrow for Clustered Column to the right of average price. Under the Line group, select Line with Markers. see Figure 13 -55. e. Click the Bold button. see Figure 13 -57.

You have learned how to combine two chart types in a single combination chart. Next, you will learn how to create the two new hierarchical charts, the Treemap chart and the Sunburst chart.

Sometimes you will want to show the hierarchical relationships among data. In this section, we'll look at two common types of hierarchical charts: the Treemap and Sunburst chart types.

A Treemap is used for representing hierarchical data. The chart consists of rectangles whose size represents the value associated with a label. Figure 13 -59 shows a Treemap chart. in this exercise, you will create a treemap chart using the data in Figure 13 -58. since the largest value in the spreadsheet is 1000, this item will be represented by the largest rectangle. the rectangles representing the products will be organized within the states.

1. open workbook Chapter 13. select the worksheet named treemap.

our company sells snack products to gas stations in five states. Figure 13 -58 shows our profit for the past week.

a treemap chart will give us a quick view of the distribution of our profit for the various products across the five states.

Note the headings don't need to be selected when creating the chart. 3. select the all Charts tab and then select treemap in the left pane. Click the ok button. see Figure 13 -59. the largest rectangle represents the largest value. if you click on one of the state's rectangles and then click on the same rectangle then all the rectangles not related to that state will be dimmed. in Figure 13 -67, the legend item Michigan was clicked twice. once a group is selected, you can single-click an individual rectangle to select it or you can select another group by clicking its header. if you click one of the labels in a rectangle, everything becomes selected again. When a group of rectangles or an individual rectangle is selected, you can change its background to a different color, gradient, picture, or texture. Figure 13 -68 shows that a texture was selected for the Michigan group of rectangles. You can also apply various effects and alter its borders by clicking the ribbon's Format tab and then selecting one of the options from the shape styles group. 14. expand the Fill group.

15. select Picture or texture fill.

16. Click the drop-down box for texture shown in Figure 13 -69. select whatever texture you want to use. if not all your data values are showing, you may need to expand the size of the chart.

Like the Treemap chart, the Sunburst chart is also a hierarchical chart. Instead of using the rectangle sizes to represent a value, it uses the size of pie pieces. An advantage of the Sunburst chart over the Treemap chart is that it can represent more columns of labels. In the example for the Treemap chart, the first column (State) was used for the headings and the next column (Product) was used to represent the individual rectangles. If we wanted to break things down farther by going State, then Counties, and then Products within those counties, the Counties wouldn't display in the Treemap chart, but they would in a Sunburst chart.

Using the same data we used for the Treemap chart and selecting the same options, let's look at the result for the Sunburst chart in Figure 13 -70.

The Sunburst chart clearly shows that Indiana is our most profitable state. It's easy to see from the chart that donuts are the most profitable item we sell in Indiana. The chart bursts from the inside out. The largest grouping is in the center of the chart. The details are farthest from the center.

The pie slice for muffins in Michigan isn't large enough to display the label and the value. If we remove the values, the label for the muffins becomes visible. See Figure 13 -71.

The label for the muffins profit in Michigan is now visible. If you want to know the value associated with each slice, just move your cursor over one of the items. See What if we wanted the chart to display the pie slices based on the gross column?

3. Delete the sunburst chart.

4. select the cell range a2:C15, hold down the Ctrl key, and select the cell range F2:F15. Let's apply a cell style to the range so that we can easily identify which columns are being used for the chart.

5. on the ribbon's home tab in the styles group, click the Cell styles button. select accent5. see Figure 13 -75. We sell our product in new York City in a very upscale area at a higher price, which makes our new York pie slice much larger than it was for the number of dozens sold. 11. in excel 2016, selecting the last chart in the Chart styles group gave the chart a 3D appearance. this no longer works, but we will give it kind of a 3D appearance by formatting it. select the chart and then, from the ribbon's Design tab, select the last chart choice from the Chart styles group. rightclick the chart. select Format Data series. Click on the effects button. expand shadow. enter 132 for the angle. see Figure 13 -78. 

The Funnel chart gets its name from its appearance. The funnel starts with a wide bar, and each successive bar is narrower. Funnel charts are often used to show values for the various stages in a process. Funnel charts are made from two columns of data. One column contains the descriptive information and the other column the values.

Funnel charts are easy to create; just select the data, click the Ribbon's Insert tab, in the Chart group click the Insert Waterfall, Funnel, Stock, Surface or Radar Chart button, and then select Funnel. See Figure 13 -80. You have learned how to create a Funnel chart to show values for the various stages in a process. Next, you will look at Excel's two mapping charts: the Map Chart and the Bing Chart. The Bing Chart is not part of the Chart group; rather, it is an Add in. They are both discussed here so that you can compare the two to see when you need to use one or the other.

Excel's new Map Chart is used for showing geographical data by countries/regions, states, counties, or postal codes. You can use the Map Chart to display Numerical or Categorical data. It is used for showing a location and one variable such as a location's population, sales for that area, hospitals in that area, and so on. The Bing Map is discussed after this cart. The advantages and disadvantages of each are listed as follows:

• Bing Map

• Has a bird's-eye view. You can view streets.

• Can use mixed types such as using a data range that includes zip codes, states, and countries.

• Can use more than one variable such as being able to chart the population, revenue, and number of hospitals for the states.

• Disadvantages

• Can only represent Numerical data, not Categorical data.

• Can't use commands from the Design and Format tabs like other charts.

• Map Chart

• Advantages

• Big advantage is that you can use all the formatting and design tools like other charts you have work with use.

• Can use Categorical data as well as Numerical data.

• Disadvantages

• Doesn't have a bird's-eye view and you can't view any streets.

• Can't use mixed types.

• Can only use one variable.

Let's look at a map that shows a problem that has occurred while writing this book. That is the coronavirus. Figure 13 -83 shows some states and their current number of coronavirus cases.

We can chart this data by selecting the cell range A1:B7 and then clicking the Ribbon's Insert tab. In the Charts group, there is a Maps button. See Figure 13 You can use the Map Chart to display categories rather than a value. Each category will be assigned its own color. Figure 13 -86 shows a portion of a table used to create the map in Figure 13 

Using Excel's Bing Maps, you can create a visual representation of your data in any location in the world. The first column of the data must be an address, city, state, country, zip, latitude/longitude, or a combination. Any columns following that must be numeric data. The problem with using a city is that there may be many cities with the same name. You can concatenate a city with a state or country name. the examples in this worksheet use revenue for the numeric amount. the values could be anything (e.g., city or state populations, growth rates, death rates, number of employees in a company working in that location, etc.).

2. Click the ribbon's insert tab. in the add-ins group, click the Bing Maps button.

the Bing Map displays. see Figure 13 -89. 5 . if you click while on the border, the sizing handles display. When you move your cursor over a sizing handle, it changes to a double arrow. hold down the shift key while you drag one of the corner sizing handles so that the map keeps it proportions.

6. select the cell range a2:B4. Click the show Location button in the upper right corner.

Bing displays a map with a circle in each of the three states. see Figure 13 -90. the circle represents the amount of the numeric data. illinois had the largest amount, so its circle is the largest. 13 . Click one of the pie charts. the data associated with that pie chart shows. see Figure 13 -95. Click the close button.

14. select the cell range a19:B22. Click the show Location button . Because all the locations are in the same city, the map is zoomed in.

15. Click the settings button . Under Map type, select Bird's eye. Click the arrow within the circle to return to the map. Zoom in. see Figure 13 -96.

the locations don't have to be of the same type. You can have a mix of cities, states, countries, zip codes, addresses, and so on, as shown in table 13-1. 17. Delete the map by clicking the map border and then press the Delete key.

Next, you will learn how to create charts that represent a row of data and are stored in a single cell.

Sparklines are small charts displayed in individual cells. They are used for highlighting trends within a single row or single column. There are three types of sparklines: Line, Column, and Win/Loss. Sparklines are created by first selecting the cells where you want the sparklines to be located and then selecting the type of sparkline you want to create. The Sparklines group is located on the Insert tab. There are three buttons in the group: Line, Column, and Win/Loss. See Figure 13 -97. Column sparklines that are negative are displayed in the bottom half of the cell. Zero values are not given a sparkline; instead, a space is placed in that position. See Figure 13 -98.

Win/Loss sparklines show whether your data is positive (win) or negative (loss). See Figure 13 -99.

in this exercise, you will create sparklines for the row data in Figure 13 -100. You will group and ungroup the sparklines so that you can format some of your sparklines one way and some of them another way.

1. open the Chapter 13 workbook. select the worksheet named sparklines. Figure 13 -100 shows the data. 

Charts provide a quick way of viewing lots of data. They make it easy to see if you are meeting your goals and to spot problems. What chart you use depends upon the data that you want to represent. You have seen that column and bar charts can be used for many rows of numeric data, while a pie chart would represent only one row of numeric data. The Treemap and Sunburst charts work well for representing hierarchal data. Sparklines are stored in an individual cell and represent a row of data. If you are using Sparklines, you will want to make the cell large enough so that you can easily see what the chart is displaying. Pie charts represent data in pie slices. The pie slices represent a percent of the entire pie. The Treemap chart uses rectangles to represent values, while a Sunburst chart represents values with pie pieces. Sparklines can use one of three chart types: Line, Column, or Win/Loss. A pie chart slice can be broken down into another pie or bar chart so that you can see all of the items that were used to create it.

A Combination chart combines two charts into one. It usually consists of a bar chart for one series and a line chart for another.

The Bing Map and Map Chart allow you to chart data related to a geographical area. The fastest way to create a 2D clustered bar chart is to use the following shortcut keys:

• Press the Alt + F1 key if you want to display the chart on the same worksheet as the data.

• Press F11 if you want the chart to appear on a separate worksheet.

The next chapter details how to import data from various sources. 

Delete any text that is in the Data range text box. select the cell range

Delete any text that is in the Location range text box. select the cell range F2:F6. 5. Click the ok button

Under the sparkline tools contextual tab, click the Design tab. in the style group, click the down arrow for more line color options. select the sparkline style Colorful #4 in the last row. see Figure

Click the ribbon's Design tab. in the show group, click the high point and Low point check boxes. the sparkline lines should now have dots representing the highest and lowest points on the line as shown in Figure

Check the negative points. now the points on the line represent those cells that have negative values

Check the Markers. now all the cells are represented by points on the line. 14. in the style group, click the Marker Color button. select purple from the standard Colors. see Figure

Dots represent highest and lowest points Chapter 13 Working With Charts 15. in the style group, click the Marker Color button. Move your cursor over negative points

click the sparkline Color button. Move your cursor over Weight. select the 4½-pt Weight. Changing the Weight changes both the size of the line and the points. see Figure

Click the sparkline Color button again. in the theme colors, select Blue, accent 5, Lighter 40%

Let's change the style. Click the More button for style. Click sparkline style accent 5, Darker 50%. see Figure

Grouping Sparklines 1. select cells F2:F6. Click the group button

representing positive values (wins) are displayed in the top half of the cell. the points representing negative values (losses) are displayed in the bottom half of the cell

Click the down arrow to the right of the Clear button and then click Clear selected sparklines. see Figure

Changing the Data Location and Location of the Sparklines

Under sparkline tools, click the Design tab

Click the Collapse Dialog button to the right of the Data range text box. see Figure