by Svetlana Cheusheva, updated on
In this tutorial, you will learn how to make a bar graph in Excel and have values sorted automatically descending or ascending, how to create a bar chart in Excel with negative values, how to change the bar width and colors, and much more.
Along with pie charts, bar graphs are one of the most commonly used chart types. They are simple to make and easy to understand. What kind of data are bar charts best suited for? Just any numeric data that you want to compare such as numbers, percentages, temperatures, frequencies or other measurements. Generally, you would create a bar graph to compare individual values across different data categories. A specific bar graph type called Gantt chart is often used in project management programs.
In this bar chart tutorial, we are going to explorer the following aspects of bar graphs in Excel:
A bar graph, or bar chart is a graph that displays different categories of data with rectangular bars, where the lengths of the bars are proportional to the size of the data category they represent. Bar graphs can be plotted vertically or horizontally. A vertical bar graph in Excel is a separate chart type, known as a column bar chart.
To make the rest of this bar chart tutorial easier to comprehend and to ensure that we are always on the same page, let's define the basic elements of an Excel bar graph. The following image shows the standard 2-D clustered bar chart with 3 data series (grey, green and blue) and 4 data categories (Jan - Apr).
Making a bar graph in Excel is as easy as it could possibly be. Just select the data you want to plot in your chart, go to the Insert tab > Charts group on the ribbon, and click the bar chart type you want to insert.
In this, example, we are creating the standard 2-D Bar chart:
The default 2-D clustered bar graph inserted in your Excel worksheet will look something like this:
The Excel bar graph above displays one data series because our source data contains just one column of numbers.
If your source data has two or more columns of numerical values, your Excel bar graph will contain several data series, each shaded in a different color:
To see all bar graph types available in Excel, click the More Column Charts... link, and choose one of the bar chart sub-types that are displayed at the top of the Insert Chart window:
If you are not fully satisfied with the default layout or style of the bar graph inserted in your Excel sheet, select it to activate the Chart Tools tabs on the ribbon. After that, go to Design tab and do any of the following:
When you make a bar chart in Excel, you can choose one of the following bar graph sub-types.
A clustered bar chart in Excel (2-D or 3-D) compares values across data categories. In a clustered bar graph, the categories are typically organized along the vertical axis (Y axis), and the values along the horizontal axis (X axis). A 3-D clustered bar chart does not display a 3rd axis, but rather presents horizontal rectangles in 3-D format.
A stacked bar graph in Excel shows the proportion of individual items to the whole. As well as clustered bar graphs, a stacked bar chart can be drawn in 2-D and 3-D format:
This type of bar graphs is similar to the above type, but it displays the percentage that each value contributes to a total in each data category.
Like standard rectangular Excel bar charts, cone, cylinder and pyramid graphs are available in clustered, stacked, and 100% stacked types. The only difference is that these chart types represent data series in the form or cylinder, cone, and pyramid shapes instead of bars.
In Excel 2010 and earlier versions, you can create a cylinder, cone, or pyramid chart in the usual way, by selecting the corresponding graph type in the Charts group on the Insert tab.
When creating a bar graph in Excel 2013 or Excel 2016, you won't find the cylinder, cone or pyramid type in the Charts group on the ribbon. According to Microsoft, these graph types were removed because there were too many chart choices in earlier Excel versions, which made it difficult for the user to choose the right chart type. And still, there is a way to draw a cylinder, cone or pyramid graph in the modern versions of Excel, this will just take a couple of extra steps.
To create a cylinder, cone or pyramid graph in Excel 2016 and 2013, make a 3-D bar chart of your preferred type (clustered, stacked or 100% stacked) in the usual way, and then change the shape type in the following way:
Note. If several data series are plotted in your Excel bar chart, you might need to repeat the above steps for each series.
Like other Excel chart types, bar graphs allow for many customizations with regard to the chart title, axes, data labels, and so on. The following resources explain the detailed steps:
And now, let's have a closer look at a couple of specific techniques pertaining to Excel bar charts.
When you do a bar graph in Excel, the default settings are such that there's quite lot of space between the bars. To make the bars wider and get them to appear closer to each other, perform the following steps. The same method can be used to make the bars thinner and increase the spacing between them. In 2-D bar charts, the bars can even overlap each other.
When you make a bar graph in Excel, the source values do not necessarily need to be greater than zero. Generally, Excel has no difficulty with displaying negative numbers on a standard bar graph, however the default chart inserted in your worksheet might leave much to be desired in terms of layout and formatting:
For the above bar chart to look better, firstly, you might want to move the vertical axis labels to the left so they won't overlay the negative bars, and secondly, you can consider using different colors for negative values.
To format the vertical axis, right click any of its labels, and select Format Axis... from the context menu (or simply double-click the axis labels). This will make the Format Axis pane appear on the right side of your worksheet.
On the pane, go to the Axis Options tab (the rightmost one), expand the Labels node, and set the Label Position to Low:
If you want to draw attention to the negative values in your Excel bar graph, changing the fill color of negative bars would make them stand out.
If your Excel bar chart has just one data series, you can shade negative values in standard red. If your bar graph contains several data series, then you will have to shade negative values in each series with a different color. For example, you can keep the original colors for positive values, and use lighter shades of the same colors for negative values.
To change the color of negative bars, perform the following steps:
Tip. If the second fill box does not show up, click the little black arrow in the only color option that you see, and choose any color you want for positive values (you can select the same color as was applied by default). Once you do this, the second color option for negative values will appear:
When you create a bar graph in Excel, by default the data categories appear in the reverse order on the chart. That is, if you sort the data A-Z on the spreadsheet, your Excel bar chart will show it Z-A. Why does Excel always put data categories backwards in bar charts? No one knows. But we do know is how to fix this :)
The easiest way to reverse the order of data categories on a bar chart is to do the opposite sort on the sheet.
Let's use some simple data to illustrate this. In a worksheet, I have a list of 10 largest cities in the world sorted by population in descending order, from highest to lowest. On the bar chart, however, the data appears in ascending order, from lowest to highest:
To have your Excel bar graph sorted from top down, you simply arrange the source data in the opposite way, i.e. from smallest to largest:
If sorting the data on the sheet is not an option, the following section explains how to change the sort order on an Excel bar graph without sorting the data source.
If the sort order on your worksheet does matter and cannot be changed, let's make the bars on the graph appear exactly in the same order. It's easy, and only requires selecting a couple of tick-box options.
Done! Your Excel bar graph will be immediately sorted in the same way as the data source, descending or ascending. As soon as you change the sort order on the sheet, the bar chart will be re-sorted automatically.
If your Excel bar graph contains several data series, they are also plotted backwards by default. For example, notice the reverse order of regions on the worksheet and on the bar chart:
To arrange the data series on the bar graph in the same order as they appear on the worksheet, you can check the At maximum category and Categories in reverse order options, as demonstrated in the previous example. This will also change the plot order of data categories, as shown in the following screenshot:
If you want to arrange the data series on the bar chart in a different order than the data is organized on the worksheet, you can do this by using:
This method allows you to change the plotting order of each individual data series on a bar graph and retain the original data arrangement on the worksheet.
Or, click the Chart Filters button on the right of the graph, and then click the Select Data... link at the bottom.
Since each data series in an Excel chart (not only in bar graphs, just in any chart) is defined by a formula, you can change the data series by modifying the corresponding formula. The detailed explanation of the data series formulas is provided here. For now, we are interested only in the last argument that determines the plot order of the series.
For example, the grey data series is plotted 3rd in the following Excel bar chart:
To change the plotting order of a given data series, select it on the chart, go to the formula bar, and replace the last argument in the formula with some other number. In this bar chart example, to move the grey data series one position up, type 2, to make it the first series in the graph, type 1:
As well as the Select Data Source dialog, editing the data series formulas changes the series order on the graph only, the source data on the worksheet remains intact.
This is how you make bar graphs in Excel. To learn more about Excel charts, I encourage you to check out a list of other resources published at the end of this tutorial. Thank you for reading and hope to see you on our blog next week!
Table of contents