Change Column Colors and Show Percent Data Labels

Normally, a column chart or bar graph displays set amounts or the number of times a value occurs for a set period of time. The taller the column, the greater number of times the value occurs.

In addition, the chart usually displays multiple series of data with each column in the series being the same color.

Using the formatting features available in Excel, it is possible to have a column chart mimic a pie chart and display

  • different colored columns for each point of a single data series
  • the percent value that each column represents of a total amount

Following the steps in this tutorial walks you through creating and formatting the column chart shown in the image above.

Note:
* If you are only interested in changing the data labels to show percents, the information can be found on page 3 of this tutorial
* Changing the column colors can be found on page 4

01
of 06

6 Steps to Customizing a Column Chart in Excel

Change Colors and Show Percents in an Excel Column Chart
Change Colors and Show Percents in an Excel Column Chart. © Ted French

A Note on Excel's Theme Colors

Excel, like all of Microsoft Office programs, uses themes to set the look of its documents.

The theme used for this tutorial is the Wood Type theme.

If you use another theme while following this tutorial, the colors listed in the tutorial steps may not be available in the theme you are using. If not, just choose colors to your liking as substitutes and carry on.

02
of 06

Starting the Column Chart

Change Colors and Show Percents in an Excel Column Chart
Change Colors and Show Percents in an Excel Column Chart. © Ted French

Entering and Selecting the Tutorial Data

Entering the chart data is always the first step in creating a chart - no matter what type of chart is being created.

The second step is highlighting the data to be used in creating the chart.

  1. Enter the data shown in the image above into the correct worksheet cells
  2. Once entered, highlight the range of cells from A3 to B6

Creating the Basic Column Chart

The following steps will create a basic column chart - a plain, unformatted chart - that displays the selected data series and axes.

The steps following creating the basic chart cover how to use some of the more common formatting features, which, if followed, will alter the basic chart to match the column chart shown in page 1 of this tutorial.

  1. Click on the Insert tab of the ribbon
  2. In the Charts box of the ribbon, click on the Insert Column Chart icon to open the drop down list of available graph/chart types
  3. Hover your mouse pointer over a chart type to read a description of the chart
  4. Click on Clustered Column - the first option in the 2-d Column section of the list - to select it
  5. A basic column chart is created and placed on the worksheet

Adding the Chart Title

Edit the default Chart Title by clicking on it twice - but don't double click

  1. Click once on the default chart title to select it - a box should appear around the words Chart Title
  2. Click a second time to put Excel in edit mode, which places the cursor inside the title box
  3. Delete the default text using the Delete/Backspace keys on the keyboard
  4. Enter the chart title - July 2014 Expenses - into the title box
03
of 06

Adding Data Labels as Percents

Change Colors and Show Percents in an Excel Column Chart
Change Colors and Show Percents in an Excel Column Chart. © Ted French

Clicking on the Wrong Part of the Chart

There are many different parts to a chart in Excel - such as the plot area that contains the columns representing the selected data series, the horizontal and vertical axes, the chart title and labels, and the horizontal gridlines.

In the following steps, if your results do not resemble those listed in the tutorial, it is quite likely that you did not have the right part of the chart selected when you added the formatting option.

The most commonly made mistake is clicking on the plot area in the center of the graph when the intention is to select the entire graph.

The easiest way to select the entire graph is to click in the top left or right corner away from the chart title.

If a mistake is made, it can be quickly corrected using Excel's undo feature to undo the mistake. Following that, click on the right part of the chart and try again.

Adding the Data Labels

  1. Click once on the materials column in the chart - all four columns in the chart should be selected
  2. Right click on the Materials column to open the data series context menu
  3. In the context menu, hover the mouse above the Add Data Labels option to open a second context menu
  4. In the second context menu, click on Add Data Labels to add data labels above each column in the chart

Changing the Data Labels to Show Percent

The current data labels can be modified to show the percent each column in the chart represents of the total expenses by using the cell reference to the percent amounts listed in column C of the data table in a formula.

The default data labels will be edited by clicking twice one each one, but, again, don't double click.

  1. Click once on the 25487 data label above the Materials column in the chart - all four data labels in the chart should be selected
  2. Click a second time on the Materials data label - only the 25487 data label should be selected
  3. Click once in the formula bar below the ribbon
  4. Enter the formula = C3 into the formula bar and press the Enter key on the keyboard
  5. The 25487 data label should change to read 46%
  6. Click once on the 13275 data label above the Utilities column in the chart - only that data label should be selected
  7. Enter the following formula = C4 into the formula bar and press the Enter key
  8. The data label should change to read 24%
  9. Click once on the 8547 data label above the Transportation column in the chart - only that data label should be selected
  10. Enter the following formula = C5 into the formula bar and press the Enter key
  11. The data label should change to read 16%
  12. Click once on the 7526 data label above the Equipment column in the chart - only that data label should be selected
  13. Enter the following formula = C6 into the formula bar and press the Enter key
  14. The data label should change to read 14%

Deleting Gridlines and the Vertical Axis Labels

  1. In the chart, click once on the 20,000 gridline running through the middle of the graph - all the gridlines should be highlighted (small blue circles at the end of each gridline)
  2. Press the Delete key on the keyboard to delete the gridlines
  3. Click once on the Y axis labels - the numbers on the left side the chart - to select them
  4. Press the Delete key on the keyboard to delete these labels

At this point, if all the above steps have been followed, your column chart should resemble the chart in the image above.

04
of 06

Changing the Chart Column Colors and Adding a Legend

Changing the Chart Column Colors
Changing the Chart Column Colors. © Ted French

The Chart Tools Tabs

As shown in the image above, when a chart is created in Excel, or whenever an existing chart is selected by clicking on it, two additional tabs are added to the ribbon.

These Chart Tools tabs - design and format - contain formatting and layout options specifically for charts, and they will be used in the following steps to complete the column chart.

Changing the Chart Column Colors

In addition to changing the color of each column in the chart, a gradient is added to each column making formatting each column a two-step process.

  1. Click once on the materials column in the chart - all four columns in the chart should be selected
  2. Click a second time on the Materials column in the chart - only the Materials column should be selected
  3. Click on the Format tab of the ribbon
  4. Click on the Shape Fill icon to open the Fill Colors menu
  5. In the Standard Colors section of the menu choose Blue
  6. Click on the Shape Fill option a second time to re-open the menu
  7. Hover the mouse pointer over the Gradient option near the bottom of the menu to open the Gradient menu
  8. In the Light Variations section of the Gradient menu, click on the first option (Linear Diagonal - Top Left to Bottom Right) to add this gradient to the Materials column
  9. Click once on the Utilities column in the chart - only Utilities column should be selected
  10. Click on the Shape Fill icon and then select Red from the Standard Colors section of the menu
  11. Repeat steps 6 to 8 above to add the gradient to the Utilities column
  12. Click once on the Transportation column and repeat steps 10 and 11 above to change the Transportation column to Green and to add the gradient
  13. Click once on the Equipment column and repeat steps 10 and 11 above to change the Equipment column to Purple and to add the gradient
  14. The colors of the four columns in the chart should now match those shown in the image in page 1 of the tutorial

Adding a Legend and Deleting the X Axis Labels

Now that each column is a different color, a legend can be added below the chart title and the X axis labels below the chart deleted

  1. Click on the chart background to select the entire chart
  2. Click on the Design tab of the ribbon
  3. Click on the Add Chart Element icon on the left hand side of the ribbon to open the drop down menu
  4. Choose Legend > Top from the list to add a legend above the plot area
  5. Click once on the X axis labels - the column names below the chart - to select them
  6. Press the Delete key on the keyboard to delete these labels
05
of 06

Moving the Data Labels and Widening the Chart's Columns

Change Colors and Show Percents in an Excel Column Chart
Change Colors and Show Percents in an Excel Column Chart. © Ted French

Formatting Task Pane

The next few steps of the tutorial make use of the formatting task pane, which contains most of the formatting options available for charts.

In Excel 2013, when activated, the pane appears on the right-hand side of the Excel screen as shown in the image above. The heading and options appearing in the pane change depending upon the area of the chart that is selected.

Moving the Data Labels

This step will move the data labels inside the top of each column.

  1. Click once on the 64% data label above the Materials column in the chart - all four data labels in the chart should be selected
  2. Click on the Format tab of the ribbon if necessary
  3. Click on the Format Selection option on the left side of the ribbon to open the Formatting Task pane on the right side of the screen
  4. If necessary, click on the Options icon in the pane to open the label options as shown in the image above
  5. Click on the Inside end option in the label position area of the pane to move all four data labels to the inside top of their respective columns

Widening the Chart's Columns

Widening the chart's columns will allow us to increase the text size of the data labels, making them easier to read.

With the Formatting Task pane open,

  1. Click once on the Materials column in the chart - all four columns in the chart should be selected
  2. If necessary, click on the Options icon in the pane to open the series options
  3. Set the Gap Width to 40% to increase the width of all four columns in the chart

Adding a Shadow to Each Column

The step will add a shadow behind each of the columns in the chart.

With the Formatting Task pane open,

  1. Click once on the Materials column in the chart - all four columns in the chart should be selected
  2. Click once on the Effects icon in the formatting pane to open the series options
  3. Click once on the Shadow heading to open the shadow options
  4. Open the preset shadows panel by clicking on the Presets icon
  5. In the Perspectives section, click on the Perspective Diagonal Upper Right icon
  6. A shadow should appear behind each of the chart's columns
06
of 06

Adding a Background Color Gradient and Formatting the Text

Background Gradient Options
Background Gradient Options. © Ted French

Adding a Background Color Gradient

This step will add a color gradient to the background using options in the Formatting Task pane as shown in the image above.

If there are not three gradient stops present when the pane is opened, use the add/remove gradient stop icons next to the gradient stop bar to set the number to three.

With the Formatting Task pane open,

  1. Click on the background to select the entire graph
  2. Click on the Fill & Line icon (the paint can) in the pane
  3. Click on the Fill heading to open the fill options
  4. Click on the Gradient option in the list to open the gradient section below in the pane
  5. In the gradient section, check to make sure that the Type option is set to the default Linear
  6. Set the Direction option to Linear Down to create a horizontal background gradient as seen in the image on page 1
  7. In the Gradient stops bar, click on the left-most gradient stop
  8. Ensure that its Position value is 0%, and set its fill color to White Background 1 using the color option below the gradient stops
  9. Click on the middle gradient stop
  10. Ensure that its Position value is 50%, and set its fill color to Tan Background 2 Darker 10% to change middle gradient stop color to light tan
  11. Click on the right-most gradient stop
  12. Ensure that its Position value is 100%, and set its fill color to White Background 1

Changing the Font Type, Size, and Color

Changing the size and type of font used in the chart, will not only be an improvement over the default font used in the chart, but it will also make it easier to read the category names and data values in the chart.

Note: The size of a font is measured in points - often shortened to pt.
72 pt text is equal to one inch (2.5 cm) in size.

  1. Click once on the chart's title to select it
  2. Click on the Home tab of the ribbon
  3. In the font section of the ribbon, click on the Font box to open the drop down list of available fonts
  4. Scroll to find and click on the font Bondini MT Black in the list to change the title to this font
  5. In the Font Size box next to the font box, set the title font size to 18 pt
  6. Click once on the legend to select it
  7. Using the steps above, set the legend text to 10 pt Bondini MT Black
  8. Click once on the 64% data label in the Materials column in the chart - all four data labels in the chart should be selected
  9. Set the data labels to 10.5 pt Bondini MT Black
  10. With the data labels still selected, click on the Font Color icon the ribbon (the letter A) to open the Font Color panel
  11. Click on the White Background 1 color option in the panel to change data label font color to white

At this point, if you have followed all the steps in this tutorial, your chart should match the example displayed on page 1.