Nice Bar Chart Data Labels

The following chart illustrates a nice configuration for horizontal bar chart labels. These labels take the place of category labels and value data labels, and they are nicely aligned above the corresponding bars.

These labels are not hard to do in Excel, and I’ll show you how.

Start with the following data. Note that the data is repeated in duplicate columns, labeled Blank and Data. Ultimately the Blank series will have hidden bars and data labels. Create a bar chart, shown next to the data.

First, format the vertical axis. Select the axis and press Ctrl+1. Under the Axis Option icon, check the box for Categories in Reverse Order; under Labels, select No Labels. Under the paint can (Fill & Line) icon, select No Line.

Now hide some unneeded chart elements. Select the horizontal axis, press Ctrl+1. Under Axis Options, for Labels select No Labels. Select and delete the horizontal gridlines, then select and delete the legend.

Next, adjust the spacing of the bars. Select either series and press Ctrl+1. Under Series Options, enter a Gap Width of 100% and a Series Overlap of -20%. You may later adjust these settings to fine-tune your label configuration.

Apply series fill colors. For the Data series, apply the desired fill color (I’ve used a medium gray). For the Blank series, select No Fill.

Now apply and format your data labels. Select the blank series (you should be able to click on it even if it’s blank, but if not select the visible series and press Ctrl and the down arrow key). Click the Chart Elements skittle (the plus icon next to the chart), select Data Labels > More Options.

Under Label Options, check Value and Category Name. Enter the pipe character | for your separator, and choose the Inside Base position. Enter an appropriate number format for your data labels. To show the values in millions of dollars, I used this format:

$0.0,, \M

Under Text Options > Textbox, uncheck Wrap Text in Shape, and enter zero for the Left Margin.

Finally enter your desired chart title, and drag the chart title to the left of the chart (hold Shift while dragging so the title moves horizontally).

Now your bar chart has nicely arranged labels, and it only took a minute or two.