Views > Excel charts > How to create a bar chart with labels above bar in excel

Sometimes—due to space constraints—it makes sense to put the category and data labels above the bar in a bar chart as per the graph below.

Let’s build this chart in Excel.

Here is the dataset for the graphic in the range A5:B16—showing, as per The Guardian, the distribution of the world’s top 100 footballers by country—as per the screenshot below:

1. Highlight the range A5:B16 and then, on the Insert tab, in the Charts group, click Insert Column or Bar Chart > Stacked Bar. The chart should look like this:

2. Next, lets do some cleaning. Delete the vertical gridlines, the horizontal value axis and the vertical category axis.
3. Delete the Chart Title and turn off the border of the Chart Area by setting the Border to No line. The chart should look like this:

HOW TO CREATE A BAR CHART WITH LABELS ABOVE BAR IN EXCEL

4. Next, insert two empty rows after each country as per the screen shot below:

5. Then, rearrange the data by adding zeros, values and category labels as per the screenshot below:

6. Next, select the range C5:C37, press Ctrl + C to copy the cells into the clipboard, then select the chart and press Ctrl + V to paste the data into the chart. The chart should look like this:

7. In the chart, right-click the Series “# Footballers” data series and then, on the shortcut menu, click Add Data Labels. The chart should look like this:

8. In the chart, right-click the Series “# Footballers” Data Labels and then, on the short-cut menu, click Format Data Labels.
9. In the Format Data Labels pane, under Label Options selected, set the Label Position to Inside Base.
10. Then, under Label Contains, check the Category Name option and uncheck the Value and Show Leader Lines options.
11. Next, while the labels are still selected, click on Text Options, and then click on the Textbox icon.
12. Uncheck the Wrap text in shape option and set all the Margins to zero. The chart should look like this:

13. In the chart, right-click the Series “Dummy” data series and then, on the shortcut menu, click Add Data Labels. The chart should look like this:

14. In the chart, right-click the Series “Dummy” Data Labels and then, on the short-cut menu, click Format Data Labels.
15. In the Format Data Labels pane, under Label Options selected, set the Label Position to Inside End.
16. Next, while the labels are still selected, click on Text Options, and then click on the Textbox icon.
17. Uncheck the Wrap text in shape option and set all the Margins to zero. The chart should look like this:

18. In the chart, select the Series “Dummy” data series and then, on the Format tab, in the Shape Styles group, set the Shape Fill to No Fill. The chart should look like this:

19. Now we need to hide the zeros overlapping with the blue bars. To do that, I need to change the number formatting of certain numbers. Select cell C6, and on the Home tab, in the Number group, click on More Number Formats at the bottom of the Number Format list.
20. In the Format Cells dialog box, under Category, click Custom.
21. In the Type input line, type ;;;. Click Ok to close the Format Cells dialog box.
22. While cell C6 is still selected, double-click the Format Painter icon in the Clipboard group on the Home tab. Next, click on the other cells (C9, C12, C15, etc.) to automatically apply the same custom number format to these cells. When done, press the Esc key on the keyboard and the worksheet should look like this: