3.3 Customize a Chart – Practice Exercises 19 – 22 – Intermediate Excel 2016

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

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 1) from the drop down menu.
Select the Format Selection icon (FIGURE 2) below Chart Area.
                               FIGURE 1
             FIGURE 2
STEP 2: ​On the Format Chart Area Chart Options (FIGURE 3) 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 4):
Border: Select Solid line
Color: Black, Text 1
Width: 0.25 pt

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

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

​With Chart Title now appearing in the window, select the Format Selection choice below it (FIGURE 3).
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 2
          FIGURE 3
STEP 3: Change the Format Chart Title sub-category from Title Options to Text Options.
For Text Fill, select Solid fill (FIGURE 4).

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

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 6).
Click mouse off of the chart to remove the Chart Title selection box.
Your chart now looks like this.
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 1) from the drop down menu.

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

                               FIGURE 1
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 3).
                                                                             FIGURE 3
STEP 3: ​The Select Data Source dialog box appears.
In the “Legend Entries” section, select the “Add” button (FIGURE 4).
STEP 4: The “Edit Series” dialog box appears (FIGURE 5).

  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.

Select OK button.

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

Select OK button.

STEP 5: Notice that our new data series, Add-on Sales (gray in color) column can be seen on the chart (FIGURE 7), 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.
STEP 6: ​You will see the Format Data Series box appear.
Select the Series Options icon (icon symbol is 3 dark columns) (FIGURE 8)
Underneath “Plot Series On”, select the choice, “Secondary Axis.”
This will add another vertical axis on the right-side of chart.
                                                        FIGURE 8
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 9), scaled to the new lower data numbers (with much lower number scale).
    FIGURE 9
Module 3 completed. Take the Module 3 quiz here.