Lesson 18


Creating a Chart

Microsoft Excel has invested considerable effort in improving not only selecting various types of charts, but has made the interface cleaner. They continually add new charts as well.

The 4 most popular types of charts are:

  1. Column chart (overwhelmingly holds the number 1 spot): best for showing volume
  2. Line chart: best for showing trends over time
  3. Bar chart: shows volume, with data illustrated horizontally instead of vertically as column charts do
  4. Pie chart: best for comparison of data as a percentage

Chart Anatomy

We can see a typical chart’s anatomy in the graphic to the right. There are several elements to a chart and each element will be discussed in this course, either in the lesson content which follows or in the associated videos linked to the various lessons.

It would be wise to spend a few minutes to familiarize yourself with the various elements of this chart anatomy.

Chart Anatomy

Note: IF you make a mistake: If you accidentally select the wrong thing, rather than trying to get your Excel chart back to the way it’s supposed to look, the best thing to do is use the “Undo” icon on the Quick Access Toolbar, or press the keys CTRL + Z. Either action will return your chart to the way it looked prior to the mistake. In fact, you can press the “Undo” choice multiple times if need be. If you go back too far, you can also use the “Redo” icon (next to the Undo icon on the Quick Access Toolbar) to advance forward. See graphic below:

Quick Access Toolbar – Undo icon is the leftward-pointing curved arrow; Redo is rightward-pointing curved arrow.

Practice Exercise 10: Creating a Chart

STEP 1: Continue using Excel data file name: SALES DATA FOR SUM AVERAGE MAX MIN.xlsx

STEP 2A: With a mouse, select range A1:E4 so that it is highlighted (FIGURE 1).

STEP 2B: Select the “Insert” tab. In the Charts section of the Ribbon, select “Recommended Charts” icon.

Figure 1

STEP 2C: We are viewing the Insert Chart – Recommended Charts tab. We see a menu of chart types to choose from (FIGURE 2A). Select the default choice which is already highlighted with a border. Notice that the type of chart is called a “Clustered Column”. The Clustered Column type of column chart is the automatic Excel default choice for a column type of chart.

Select OK button.

Figure 2A – Recommended Charts tab

STEP 2D: Let’s select the other tab of this dialog box, named “All Charts”. Although we are not doing anything in this step, it’s worth noting that there is another tab, and it’s an important one. We are now viewing the Insert Chart – All Charts tab (FIGURE 2B).

The importance of this tab is that it allows us to select another type of chart other than a column chart. As we see, there are many other chart types to choose from on the left side of the dialog box. We also need to be aware that there are derivations of the chart type shown across the top of the dialog box, which are available for use. The most important aspect of knowing what type of chart to select is asking yourself the question: Will this type of chart best display my data so that viewers understand the data?

Figure 2B – All Charts tab

STEP 2E: We see the chart result displayed (FIGURE 3).

We need to understand early that data in Excel charts is displayed in what is known as a data series.This concept is shown in the “Chart Anatomy” graphic at the beginning of this lesson.

For example, notice that a blue color is representing the month of January. We can see this by looking at the legend at the bottom of the chart. Salesperson James had sales for Janary represented by a blue column. Salesperson Ed had sales for January represented by a blue column. The same applies to Robert and Susan. Therefore, all of the blue columns in the chart represent the same data series, which we could call the January Data Series. All of the rust-orange color columns in the chart represent another distinct data series, which we could call February. And finally, all of the gray color columns in the chart represent a third data series that we could call, the March Data Series.

Figure 3

STEP 3A: Use the 3 icons to the right of the chart to customize the chart (FIGURE 4). For example, let’s add titles to the vertical axis and horizontal axis. Select the top icon, “Chart Elements” to the right of the chart, and check the box, “Axis Titles” and “Chart Title”. We now have a vertical axis and horizontal axis labels appear, as well as the chart title label.

Figure 4

STEP 3B: Select the vertical axis title label placeholder with a mouse. Click your mouse inside of it and enter the name, “Sales” (FIGURE 5).

STEP 3C: Do the same thing to the horizontal axis title label placeholder and enter the name, “Salesperson” (FIGURE 5).

STEP 3D: Select the chart title label with your mouse and add the name, “Sales for 2023 1st Quarter” (FIGURE 5).

Figure 5

Chart Design Contextual Tab Toolbar

When you click on the chart, contextual tab(s) will appear (FIGURE 6) up on the Ribbon which includes 2 tabs for Chart customization, called Design and Format. Each of these tabs has its own toolbar. Contextual tabs are temporary and only appear when you are doing something specialized. In this case, we are now working with a chart. Let’s take a look at the Chart contextual tabs toolbars.

Figure 6

Due to the toolbar’s size, we will examine the Chart Design contextual toolbar in sections.

STEP 4A: The “Add Chart Elements” in the “Chart Layouts” section of the toolbar, is the same as the Chart Elements icon to the right of the chart, which we used earlier in this lesson. When we select the icon, we get a drop-down menu and when we select one of the menu choices, it shows a secondary menu (FIGURE 7).

On the drop-down menu, select “Data Labels” and on the secondary menu, select “Center” (FIGURE 7).

Figure 7

STEP 4B: The chart result from previous step, which we can see has added data labeling to each column (FIGURE 8).

Figure 8

STEP 5A: Select “Quick Layout” icon on the “Chart Design” toolbar. The “Quick Layout” in the “Chart Layouts” section of the toolbar offers a number of option variations of your chart. Move your mouse to each choice and notice that each time you select a different option, it actually illustrates it on your chart.

STEP 5B: Let’s select the menu choice (Layout 9) (FIGURE 9) . If we keep our mouse pointed at Layout 9, we see a list of the elements added to the chart. Since we already have most of them already in the chart, we see the apparent change is the legend has moved to the right of our columns.

Figure 9

STEP 5C: Here is our finished product with the legend relocated to the right (FIGURE 10).

Figure 10

STEP 6A: The next icon in the “Chart Stlyes” section of the Chart Design toolbar is named, “Change Colors”. FIGURE 11 shows a palette of colors. Our current set of colors is shown on the top row of colors, named “Color Palette 1”. We will select the third row down, “Color Palette 3” as shown in FIGURE 11.

Figure 11

STEP 6B: Our chart now has colors from “Color Palette 3” (FIGURE 12).

Figure 12

STEP 7A:”Chart Styles” takes up a big portion of the Chart Design toolbar. Let’s take a look at it now (FIGURE 13).

Figure 13

STEP 7B: For this step, we will not be changing our chart. However, we can check out the various styles by moving the mouse to each one and point at it with the mouse. We can see that each time we point at another style, our chart actually changes to that style. Notice the downward pointing arrow to the right of the styles. If we select the bottom arrow, we can view more remaining styles.

STEP 8A: Let’s move to the next section of the Chart Design toolbar; it is the “Data” section. The icon is named, “Switch Row/Column” (FIGURE 14). It takes the Row headers (in this case that would be “January, February March” and swap it with the Column headers, “James, Ed, Robert, Susan”. Click on the “Switch Row/Column” icon and observe the result.

Figure 14

STEP 8B: As we can see, the Row headers “January, February, March” are now labels at the bottom of the columns (FIGURE 15). The salespersons are represented by different colors, as shown in the legend, “James, Ed, Robert, Susan”.

Let’s momentarily regress back to our earlier discussion of a data series from Step 2E. A data series was based on the month. Now we have switched row/columns so that a data series is based on a salesperson.

For example, all of the rust-orange colored columns in the chart represent a data series that we could call, the James Data Series. All of the yellow colored columns represent a data series that we could call, the Ed Data Series. The same would apply for the green (Robert) and brown (Susan) colored columns; each color would constitute a data series containing data for a salesperson.

Figure 15

STEP 9A: Staying within the same section of the Chart Design toolbar; the “Data” section. The icon is named, “Select Data” (FIGURE 16). This is used when we need to adjust our data, whether it is to remove, edit, or add other data to our table, which will change our chart.

In this case, we forgot to include the salesperson, “Mary”, so we will add her and her sales data to our table, which will update our chart.

Figure 16

STEP 9B: First, we need to add the data to our data table. In the data table, select column F with mouse, right-button mouse click column F and select “Insert” from the mouse shortcut menu (FIGURE 17).

Figure 17

STEP 9C: In the newly inserted column F, enter “Mary” into cell address F1, and the numbers 28, 25, 27 into cell addresses: F2, F3, F4 respectively (FIGURE 18).

Figure 18

STEP 9D: To add Mary and her sales data we will use the “Select Data” icon in the “Data” section. Select the “Select Data” icon.
We see the “Select Data Source” dialog box (FIGURE 19). Since we want to add data, select the “Add” button in the “Legend Entries (Series)”.

Figure 19

STEP 9E: When we select the “Add” button, the “Edit Series” dialog box appears.

Click mouse inside the “Series name” block (FIGURE 20). The Series name will be “Mary”. However, instead of entering Mary, select the data table cell address F1 with your mouse. This action will enter, “=Sheet1!$F$1”

Click on the “Series values” block. The Series values will be the sales data for Mary. Instead of entering the data directly, select the cell address F2 with mouse, and drag mouse downward so that F2:F4 is highlighted. This action will enter: “=Sheet1!$F$2:$F$4”

Select the OK button.

Figure 20

STEP 9F: In the “Select Data Source” dialog box we now see “Mary” added in the “Legend Entries (Series) section (FIGURE 21).

Select OK button.

Figure 21

STEP 9G: We see on our chart that “Mary” has been added to the chart (FIGURE 22). An additional data series has been added and we also see “Mary” added to the legend.

Figure 22

STEP 10A: Let’s go to the next section of the Chart Design toolbar; this section is named, “Type” and the icon is named, “Change Chart Type” (FIGURE 24).

Figure 24

STEP 10B: When changing the chart type, the first type we see is the current type, “Clustered Column”. This type of column chart is the most popular chart used with Excel. Notice there are several deviations of a column chart that are illustrated across the top of the screen.

Figure 25

STEP 10C: Let’s look at another popular column chart, the “Stacked Column” chart (FIGURE 26). A Stacked Column chart is usually preferred when space is limited since several pieces of data can be shown in one column.

Let’s select the column sub-type, “Stacked Column”. It is the one at the top, next to the “Clustered Column” subtype.

Select OK button.

Figure 26

STEP 10D: Notice that all of the salepersons are represented on one column (FIGURE 27). What took 15 columns to illustrate with a Clustered Column chart now only requires 3 columns. Again, this Stacked Column chart is used when space is limited and/or there are many data series that need to be illustrated (January through December sales).

Figure 27

STEP 11A: Let’s change our chart type to a different chart type, named “Bar” (FIGURE 28). We can locate it in the column of chart types on the left. When we select “Bar” notice that what appears is named, “Clustered Bar”.

Select the OK button.

Figure 28

STEP 11B: We see the full Bar Chart now (FIGURE 29). Some people like the Bar Chart although many people are used to viewing columns of data vertically instead of horizontally.

Figure 29

STEP 11C: Select the “Undo” button on the Quick Access toolbar to go back to our “Clustered Columns” chart (FIGURE 30).

Figure 30

Keep clicking on the “Undo” icon until you see the “Clustered Column” type chart we were using before we began changing chart types, as shown in FIGURE 31.

Figure 31

STEP 12A: The last section of the Chart Design toolbar we will cover is the “Location”, and the icon to select is “Move Chart” (FIGURE 32). Let’s select it now.

Figure 32

STEP 12B: Notice that Excel has created an additional sheet named “Chart1” and our chart’s data table remains on “Sheet 1” (FIGURE 33).

Figure 33

Chart Format Contextual Tab Toolbar

When you click on a chart, contextual tab(s) will appear up on the Ribbon which includes 2 tabs for Chart customization, called Design and Format. We have covered the “Design” contextual tab. Now we will cover the other contextual tab and its toolbar, named “Format” (FIGURE 34). Much of the “Format” toolbar is design-oriented, so we will only briefly cover this toolbar. Due to the “Format” toolbar size, we will cover by section.

Figure 34

STEP 13A: We will use our chart from STEP 11C FIGURE 31, to customize the chart look using the “Format” contextual tab. Let’s focus on customizing the look of our chart (FIGURE 35).

Figure 35

STEP 13B: We will be customizing the chart look by applying format changes to specific elements of the chart. If we view the “Current Selection” section (FIGURE 35) of the “Format” toolbar, we see it shows the “Chart Area” as the chart element currently selected. If we select the nearby arrow, we see a long drop-down menu (FIGURE 36) showing all of the various elements of the chart. Let’s change the color of the Chart Area. The current selection already shows “Chart Area” as the element selected.

Figure 35
Figure 36

STEP 13C: In reference to FIGURE 35 above, underneath the selection window is the control icon named, “Format Selection”. Let’s click on it now. We see a large dialog box appear from the right-side of the screen, titled, “Format Chart Area” (FIGURE 37). Locate the “Color” section under category “Fill”. We will now change the color of our Chart Area.

Figure 37

STEP 13D: On the color dialog box click on the arrow and on the drop-down menu color palette, from the top row, select the color, “Light Gray, Background 2” (FIGURE 38).

Select OK button.

Figure 38

STEP 13E: We now see our Chart Area now has a gray color (FIGURE 39).

Figure 39

STEP 14A: If we examine the column color darkness for 2 of the salespersons, we see that the colors could be lighter to improve data label numbers readability (FIGURE 40). There is another way to select a chart element other than the “Format” toolbar. We can click on the chart element we wish to customize.

Click on the column for Susan (dark brown color). Notice that by clicking on one of those columns results in all 3 being selected. We can see they are selected by the selection handles (pointed by red arrows). All 3 columns are selected since they are all part of the same data series for Susan.

Figure 40

STEP 14B: On the “Format Data Series” dialog box on the right of your screen, ensure you have selected for Series Options, “Fill & line” (represented by a paint bucket). Ensure the choice “Solid Fill” is selected (FIGURE 41).

Locate the “Color” option and click on the arrow for the color palette drop-down menu. Select the color choice, “Blue, Accent 1, Lighter 60%”

Figure 41

STEP 14C: We can see that salesperson Susan’s columns have change to the new light-blue color. Notice that the legend has been updated with her new color also (FIGURE 42).

Figure 42

STEP 15A: Now we will change the dark-brown column color for salesperson Mary. Click on any of the dark-brown columns. All 3 of the dark-brown columns will be selected as indicated by the appearance of the selection handles (FIGURE 43).

Figure 43

STEP 15B: On the “Format Data Series” dialog box on the right of your screen, ensure you have selected for Series Options, “Fill & line” (represented by a paint bucket). Ensure the choice “Solid Fill” is selected (FIGURE 41).

Locate the “Color” option and click on the arrow for the color palette drop-down menu. Select the color choice, “Yellow” from the “Standard Colors” palette of colors (FIGURE 44).


Figure 44

STEP 15C: We can see that salesperson Mary’s columns have change to the new yellow color. Notice that the legend has been updated with her new color also (Figure 45).

Figure 45

STEP 16A: The next section of the Format toolbar is named, “Insert Shapes” (FIGURE 46). With it we can add just about any shape we want. An important one in the group is the “Text Box” (in the red box). With it we can draw a text box any size. It is very useful if we accidently delete one of our element labels and need to recreate one.

Figure 46

STEP 16B: Let’s say we accidently lost our “Chart Title” placeholder on our chart. Let’s select the “Text Box” choice and draw a new “Chart Title” box and enter whatever text we want.

Figure 47

STEP 16C: Let’s quickly look at the next section of the “Format” tab toolbar named, “Shape Styles” (FIGURE 48). At the end of the styles choices, we see 2 downward pointing arrow. Let’s select the one at the bottom. That will show many other styles choices.

Figure 48

STEP 16D: If we simply point (do not click your mouse to select it) at each theme with our mouse, notice that Excel will actually show us our chart with the new theme selected. Spend a minute selecting the various themes and see what choices are available. Sometimes this method of chart customization is quicker.

Figure 49

STEP 16E: To the right of our “Shape Styles” is a few options for adding color customization. The only one we will look at is “Shape Fill” (FIGURE 50). When we click on it, we see a drop-down color palette similar to STEP 15B, except this time, we only have to point at a color to actually see the result on our chart. Point mouse at a few color choices to see how this works.

Figure 50
Figure 51


Back to: Intro to Excel 2016 > Module 5 – Intro to Excel 2016

Leave a Reply