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.

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.

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.

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?

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.

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.

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).

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).

STEP 4B: The chart result from previous step, which we can see has added data labeling to each column (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.

STEP 5C: Here is our finished product with the legend relocated to the right (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.

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

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.

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.

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.

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).

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).

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)”.

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.

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.

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.

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).

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.

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.

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).

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.

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.

STEP 11C: Select the “Undo” button on the Quick Access toolbar to go back to our “Clustered Columns” chart (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.

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.

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

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).

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.

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.

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.

STEP 13E: We now see our Chart Area now has a gray color (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.

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%”

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).

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).

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).

.

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).

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.

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.

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.

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.

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.