Change Column Colors & Show Percent Data Labels in Excel Column Chart

How to change the column color in an Excel chart

Hands working on a laptop with graphs and charts.

Ruthson Zimmerman / Unsplash 

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 the 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 a total amount

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

Instructions in this article apply to Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel for Office 365.

01
of 05

Starting the Column Chart

An Excel spreadsheet showing expenses in various categories in terms of dollars and percentages of the total

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 Step 1 of this tutorial.

  1. Select the Insert tab of the ribbon.
  2. In the Charts box of the ribbon, select 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. Select Clustered Column - the first option in the 2-d Column section of the list - to select it.

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. Select the default chart title. 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 Expenses - into the title box.
02
of 05

Adding Data Labels as Percents

Updated chart

Selecting 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 correct 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 can 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 = C2 into the formula bar and press the Enter key on the keyboard. The 25487 data label should change to read 46%.
  5. Click once on the 13275 data label above the Utilities column in the chart - only that data label should be selected.
  6. Enter the following formula = C3 into the formula bar and press the Enter key.
  7. The data label should change to read 24%.
  8. Click once on the 8547 data label above the Transportation column in the chart - only that data label should be selected.
  9. Enter the following formula = C4 into the formula bar and press the Enter key.
  10. The data label should change to read 16%.
  11. Click once on the 7526 data label above the Equipment column in the chart - only that data label should be selected.
  12. Enter the following formula = C5 into the formula bar and press the Enter key.
  13. 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 gridlines 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.

03
of 05

Changing the Chart Column Colors and Adding a Legend

Gradient chart colors

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 Step 1 above.

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
04
of 05

Moving the Data Labels and Widening the Chart's Columns

Formatted chart

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.

Moving the Data Labels

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

  1. Click once on the 46% data label above the Materials column in the chart - all four data labels in the chart should be selected.
  2. Select the Label Options icon in the pane to open the label options as shown in the image above.
  3. Select 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. A shadow should appear behind each of the chart's columns.
05
of 05

Adding a Background Color Gradient and Formatting the Text

Completed chart

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. Select the Fill & Line icon (the paint can) in the pane.
  3. Select the Fill heading to open the fill options.
  4. Select 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 Step 1.
  7. In the Gradient stops bar, select 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. Select the middle gradient stop.
  10. Ensure that its Position value is 50%, and set its fill color to Light Gray Background 2 Darker 10% to change middle gradient stop color to light gray.
  11. Select 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.

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. Select the Home tab of the ribbon.
  3. In the font section of the ribbon, select the Font box to open the drop-down list of available fonts.
  4. Scroll to find and select the font Bodoni 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 Bodoni 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 Bodoni 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.