Creating a Bar Chart

We will explore some advanced chart formatting options by creating a series of charts for the Continuous Education Department in the University of the Midwest. After collecting some survey data from some of the workshops that were offered, we want to present that data in a format appropriate for PowerPoint slides. We will use bar charts to present the results of survey questions about the workshops.

In this section, we will create a bar chart, and then customize it. Once it is formatted as we wish, we can save it as a chart template, so it may be easily reused. Once a customized chart exists as a template, it can be applied to an existing chart, and the formatting will be applied instantly using the new data.This is great way to create a series of similar-looking charts very quickly.

NOTE: This project was inspired by examples used in John Walkenbach's Excel Charts book.

Step1. To switch to the appropriate worksheet,

Click the Survey Data worksheet tab

We see a data set representing the responses of a survey collected from students for courses offered through the Continuous Education Department. We want to create a series of bar charts from this data, one for each set of responses, all similar in appearance to each other.

The initial chart with default formatting will look something like this:

The final destination for these charts is a PowerPoint slide show, so we will want these to be formatted for visual impact and ease of reading.

The formatted chart may look something like this (colors may vary):

Charting the Data

First we will create a chart that uses Excel's default values as an embedded chart on the worksheet. We will select the column headings and the first row of data in order to chart the survey responses for "The instructor explained the material clearly." Then later we will create charts for the remaining survey responses.

Step1. To select the correct range,

Click cell A1, press: Shift key and Click cell F2

The data labels and values are all selected.

Step2. To switch to the Insert tab, on the Ribbon,

Click the Insert tab

Step3. To choose a chart type, in the Charts group,

Click

Step4. To choose the first chart,

Double-Click the first bar chart

The bar chart appears in your worksheet with your selected theme.

Step5. Resize the chart so that all of the horizontal axis labels are displayed, if necessary.

Applying a Chart Style

The styles displayed in the Styles gallery depend on the workbook's theme. If we apply a different theme, we will have a different set of styles to choose from.

Next we will change the appearance of the bars on this chart by applying a different style.

Step1. To view the chart styles, to the right of the chart,

Click

All of the various chart styles are displayed that correspond with the selected theme.

NOTE for MacOS Users:To begin to change the chart style to a line with markers, Click the Chart Design tab on Ribbon, if necessary, then choose from the styles gallery in the center of the Ribbon.

Step2. To select a chart style,

Click Style 5

The style is applied to this chart. Styles do not affect other worksheets like themes do.

NOTE: From the Format tab on the Ribbon, you can also Click Shape Fill, Shape Outline, or Shape Effects in the Shape Styles group to select the formatting options that you want.

Applying Data Labels and a Legend

We will display data labels for each bar. This will allow the actual values of a particular response to be seen more readily in our PowerPoint slide show.

Step1. To display data labels at the end of each bar, if necessary,

Click, Click the Data Labels checkbox

NOTE for MacOS Users:To display data labels at the end of each bar, on the Chart Design tab, Click Add Chart Element, Point to Data Labels, Click the Outside End option.

NOTE: If you have the entire chart selected, data labels will be applied to all data series. If you have a single data point selected, data labels will only be applied to the selected data series or data point.

The data labels are displayed at the end of each of the data points.

Next we will display the legend at the top of the chart.

Step2. To display a legend,

Click the Legend checkbox

NOTE for MacOS Users: To add a legend at the top of the Chart Area, on the Ribbon, Click Add Chart Element, Point to Legend, Click Top.

Step3. To display the legend at the top of the chart,

Click, Click Top

The legend is placed at the top of the chart underneath of the title. We will remove the title and use the legend as the title.

Step4. To remove the title,

Click the title, press: Delete key

The chart should look something like this:

5. Save the workbook ChartData.xlsx.

Formatting the Chart Area

Even though we have created the bar chart, there is still much formatting that needs to be done. For example, the background and bar colors can be added or changed to make the chart more visually appealing to the audience.

If we have formatting that should apply to most of the chart, it is often easiest to apply that change to the entire Chart Area, and then format the exceptions.

Step1. To select the Chart Area, if necessary,

Click the outer region of the chart

We will make changes to the fonts used in the chart. These changes will affect all places where fonts are used in the chart. We could also accomplish this by formatting each piece of the chart individually, but this global change to the font settings will provide a more efficient workflow.

Step2. To increase the font size, in the Size field,

Right-Click in the chart area (outside of the plot area)
Click Font..., Press & Drag the Size value, type: 12 Enter

NOTE for MacOS Users: To increase the font size, press: Control key+Click the chart area, Click Font... , the Font dialog box opens. Press & Drag the Size value, type: 12 Enter.

Step3. To format the chart area,

Right-Click the chart area, Click Format Chart Area...

NOTE for MacOS Users: To format the chart area, press: Control key+Click the chart area, Click Format Chart Area... .

The Format Chart Area task pane opens.

Step4. To expand the FILL section, in the task pane,

Click, Click the "Picture or texture fill" radio button

A texture fill color is applied to the chart area.

Step5. To apply a different texture fill,

Click, Clickany texture

NOTE: A lighter texture fill will be less likely to interfere with understanding the charted data. The screen captures here use the Stationary texture fill.

Next we will add rounded corners to the chart.

Step6. To add rounded corners, in the task pane,

Click, Click the "Rounded corners" checkbox

The rounded corners appear, but may be obscured by the selection handles around the chart.

We will add a soft glow to the outer chart.

Step7. To add a glow color to the outer border, at the top of the task pane,

Click, Click,
Click, Clickany color

NOTE: You can also change the size of the glow and soft edges or increase the transparency, and add border colors, shadows, and 3-D formats.

NOTE: If you want to reset the formatting of a chart element to its original state, just Right-Click the chart element and choose "Reset to Match Style."

Step8. Save the workbook ChartData.xlsx.

Working with the Horizontal and Vertical Axes

We can have a great deal of control over the format, position, and scale of the chart's axes. We can specify the line style, color, and weight of the axes, as well as the presence or absence of tick marks and tick labels, which are related to the axes. We can also establish the positions at which vertical and horizontal axes intersect.

NOTE: Remember that in bar charts, the axes are reversed from column charts; the category axis is the vertical axis.

Step1. To view the axis settings,

Click the horizontal axis

The Format Axis task pane is displayed.

Step2. To display the axis options, at the top of the Format Axis task pane,

Click

Step3. To expand the axis options,

Click

Before deciding on a particular minimum or maximum value, always examine your entire data set to be charted. Excel determines these axis default values based on the numerical range of the data, but you can override these values.

NOTE: Sometimes data can appear distorted or manipulated if the minimum and maximum values are not set appropriately to match the values, especially on line or column charts.

Setting a Fixed Maximum Value for the Horizontal Axis

For our chart template, across all the charts it creates, we want to keep the axis value scales the same so that our charts can be visually compared more easily. By having all of the charts use the same value scales, they will create an easier to understand PowerPoint presentation. Instead of modifying every chart separately, we can create our chart template to include a maximum value for the horizontal axis. In this data set, none of the data to be charted exceeds 60%, so that value (.6) can provide a convenient maximum.

Even though the current value.6 is correct in the Maximum field, based on the currently selected data, that Maximum field is currently set to be automatic and would likely change if we copy the chart and use other data. Therefore, we will re-enter this value in that Maximum field, which will remove that automatic setting, and that desired value will remain fixed.

It may seem counter-intuitive to be entering the same value to maintain that value, however, manually entering a value in the Maximum field will not only set it to be that value, but it will also change the field setting to be fixed, rather than change automatically.

Step1. To set the fixed maximum value for the horizontal axis, in the Maximum field,

Press & Drag the value, type.6 Enter

You will see:

This maximum value will now remain fixed if we copy other charts and replace the values.

Re-ordering the Category Labels

By default, category labels are arranged from bottom to top on the vertical axis because labels are referenced from the 0 reference point (usually the lower left corner of the chart). We will reverse the values on the vertical axis so that Strongly Agree is placed at the top of the axis instead of at the bottom.

Step1. To select the vertical axis, on the chart,

Click the vertical axis

The vertical axis options are displayed in the Format Axis task pane.

We want the Strongly Agree category to appear at the top of the category axis, instead of at the bottom.

Step2. To reverse the categories on the category axis, beneath the Axis position section of the Format Axis task pane,

Click the "Categories in reverse order" checkbox

This causes the Strongly Agree category labels to appear at the top of the vertical axis. However, the labels on the horizontal axis followed the largest - or maximum - category (i.e., the bottom category).

Step3. To retain the labels on the horizontal axis at the bottom of the chart, in the "Horizontal axis crosses" section,

Click the "At maximum category" radio button

Step4. Close the Format Axis task pane.