Lesson 23


Customize a Chart – Practice Exercises 19 – 22

Practice Exercise 19 – Customize a chart by applying filtering

STEP 1: Select the Chart Filters icon to the right of the chart (bottom icon). We want to display (FIGURE 1):

  • Northern Sector only
  • Jan, Feb, Mar only

Under “SERIES”, you will have to uncheck Southern Sector box.
Under “CATEGORIES” you will have to uncheck Apr, May, Jun boxes.

Select Apply button below.

Figure 1

STEP 2: You can see that we only have the Northern Sector illustrated, with only the months of Jan, Feb, and Mar (FIGURE 2).

Figure 2

 STEP 3: ​Press the Undo button  on the Quick Access Toolbar to get our original chart back as illustrated on the right (FIGURE 3).

Figure 3

Practice Exercise 20 – Customize a chart by applying formating

STEP 1: ​On the Ribbon, select Format tab from the Chart Tools Tab.
On the Current Selection group, select Chart Area (FIGURE 4) from the drop down menu.
Select the Format Selection icon (FIGURE 5) below Chart Area.

Figure 4
Figure 5

STEP 2: ​On the Format Chart AreaChart Options (FIGURE 6) section, make the following selections:
Fill: Select Solid Fill
Color: White, Background 1, Darker 5%

Select the Paint Bucket icon to select the following changes (FIGURE 7):
Border: Select Solid line
Color: Black, Text 1
Width: 0.25 pt

Figure 6
Figure 7

STEP 3: ​Select the Size and Properties icon.
In the Size section, at the bottom, check the box “Lock aspect ratio” (FIGURE 8).
Note: Locking the aspect ratio prevents you from distorting the chart image when resizing the chart. 

Figure 8

Practice Exercise 21: Customize a Chart by Applying a Chart Title

STEP 1: ​Your chart should now look like the graphic on the right (FIGURE 9).

Figure 9

STEP 2: ​On the Ribbon, select Format tab from the Chart Tools Tab.
On the Current Selection group, select Chart Title from the drop down menu (FIGURE 10).

​With Chart Title now appearing in the window, select the Format Selection choice below it (FIGURE 11).
Note: If you don’t see Chart Title on the drop-down menu, select the Chart Elements icon (+) to the right of the chart and check the box, “Chart Title”

Figure 10
Figure 11

STEP 3: Change the Format Chart Title sub-category from Title Options to Text Options.
For Text Fill, select Solid fill (FIGURE 12).

​For Color, select: Blue, Accent 5. Notice that “Chart Title” (FIGURE 13) has changed from black to blue.

Figure 12
Figure 13

STEP 4: ​For the Chart Title, enter “Sector Sales”. As you’re typing, notice that Sector Sales is being entered into the Formula Bar.
When you have finished typing, press the ENTER key. “Chart Title” has now been replaced by “Sector Sales” (FIGURE 14).
Click mouse off of the chart to remove the Chart Title selection box.
Your chart now looks like this.

Figure 14

Practice Exercise 22: Customize a Chart by Addding Additional Data

STEP 1: ​On the Ribbon, ensure Format tab from the Chart Tools Tab is still selected.
On the Current Selection group, select Horizontal (Category) Axis (FIGURE 15) from the drop down menu.

Select Format Selection icon below Horizontal (Category) Axis (FIGURE 16).

Figure 15
Figure 16

STEP 2: ​We will add additional data to our chart. Up on Ribbon, select Design tab of Chart Tools. Select “Select Data” icon from the Data section (FIGURE 17).

Figure 17

STEP 3: ​The Select Data Source dialog box appears.
In the “Legend Entries” section, select the “Add” button (FIGURE 18).

Figure 18

STEP 4: The “Edit Series” dialog box appears (FIGURE 19).

  1. Click on the Series name window, click on cell D1 of the table with mouse. The Series name window will populate with data.
  2. Click on the Series values window (if you see any data already in the window, delete it now but leave the = symbol).
  3. Click and drag cells D2:D7 of table with mouse. The Series values window will populate with data.
  4. Select OK button.
Figure 19

You will see the select Data Source box again (FIGURE 20). Notice that in the Legend Entries section, Add-On Sales has been added.

Select the OK button.

Figure 20

STEP 5: Notice that our new data series, Add-on Sales (gray in color) column can be seen on the chart (FIGURE 21), although it’s very small compared to the other columns
Click on one of those small columns with your mouse.
When you click on one, all of them will be selected, as shown by the selection bars around them.

Figure 21

STEP 6: ​You will see the Format Data Series box appear.
Select the Series Options icon (icon symbol is 3 dark columns) (FIGURE 22)
Underneath “Plot Series On”, select the choice, “Secondary Axis.”
This will add another vertical axis on the right-side of chart.

Figure 22

STEP 7: Our additional data has been now added to our chart and the chart has a secondary vertical axis on the right side of the chart (FIGURE 23), scaled to the new lower data numbers (with much lower number scale).

Figure 23


Back to: Intermediate Excel 2016 > Module 3 – Intermediate Excel 2016

Leave a Reply