How to Make a Bar Graph in Excel
This article explains how to make and format a Bar Graph in Excel, where horizontal bars are used to compare different categories of data.
A bar graph (also known as a bar chart) is a graphical representation of data as horizontal bars along two axes. Bar charts are used to graphically represent categorical data or to track changes over time or show differences in size, volume, or amount.
The bar charts are plotted horizontally with the horizontal (x) axis representing categories and the vertical (y) axis representing values for those categories.
Difference Between a Bar Graph and a Histogram
Bar Chart and Histogram (Column Chart) are both used to display data in the form of a bar diagram. As they both use bars to display data, people often confuse one for another.
- Bar graphs are plotted horizontally while histograms are plotted vertically.
- Histograms are used to show the frequency of numerical data whereas the Bar charts are good for comparing different categories of data.
- Histogram used for the distribution of continuous variables such as height, weight, temperature, and length, etc. while a bar chart is used for the comparison of discrete variables such as number of people in a class, number of items, types of movies, etc.
How to Create a Bar Chart in Excel
A bar graph/chart is very simple and easy to make in Excel. Let’s see how to create a bar chart in Microsoft Excel.
The first step in creating any chart in Excel is to enter the necessary data into the worksheet. To create a bar chart, you’ll need at least two columns of data – the independent values (in our example, the name of each book series), and the dependent values (the number of copies sold and in stock).
The dependent variables can be two or more columns, a bar is added for each variable (column).
Insert a Bar Chart in Excel
Highlight the data you want to plot in your chart and make sure to highlight the data with column headers. Then go to the Insert tab, in the Charts group on the ribbon, click the ‘Insert Column or Bar Chart’ icon to open a list of available chart types.
Just hover over a chart type with your cursor to read a description of the chart and get a preview of how that chart will look with your data. Choose a bar chart type from the 2-D or 3-D bar chart list.
For this tutorial, we are choosing standard ‘2-D Clustered Bar’ chart type as shown above.
Once it’s selected, a 2-D clustered bar graph will be inserted into your worksheet and it will look similar to this.
For every column of numerical values in your data set, your bar chart will have a data series/bar(one for each column), each shaded in a different color.
Formatting a Bar Graph in Excel
Once your bar chart is created, you can add chart elements, change the color of the bar graph, change the format of the chart, etc. There are various formatting tools in Excel to format a chart, including the Design tab, Format tab, Format pane, floating buttons, and contextual menu.
A bar chart will be created with the default title, ‘Chart Title’. You can change the chart title to something that suits your chart. Click once on the default chart title to select it and click a second time to edit it. Delete the default text and enter a new title.
Changing Chart Type
If you don’t like the new graph, you can always change it to some other chart type. Simply select your chart, go to the ‘Insert’ tab and choose another chart type in the Charts group. Or right-click anywhere on the graph and select ‘Change Chart Type’ from the context menu.
Next, the Change Chart Type window will appear on the worksheet. Here you can choose any other chart type. When you are satisfied with the new chart, click ‘OK’ so that you can change your graph type.
Changing the Bar Graph Layout and Style
If you are not happy with the default layout or style of the bar chart, you can use Excel’s preset layouts and styles to further improve your chart.
To change bar graph layouts, just click the ‘Quick Layout button’ in the Chart Layouts group under the ‘Design’ tab and choose your desired layout from the drop-down. To try a different chart style, choose a style in the ‘Charts Styles’ group on the ‘Design’ tab.
Changing Chart Colors
To change the default color of chart bars, click the ‘Change Colors’ icon in the Chart Styles group under the ‘Design’ tab and choose one of the available color combinations. And whatever you choose will be immediately reflected on your chart.
Alternatively, you can also click on the brush floating button on the right of the graph to choose color and style for your graph.
To change the color for each data series (bars) individually, select the data series that you want to change color, right-click and select the ‘Format Data Series’ option from the context menu.
A Format Data Series pane will appear on the right side of the window. Next, switch to the ‘Fill & Line’ tab (paint can icon), click on the ‘Color’ dropbox, and choose a new color from the color palette.
Swap X and Y Axes in the Chart
Sometimes, you don’t like the way the rows and columns are plotted on the graph by default, in that case, you can easily switch the vertical and horizontal axes with a mouse click. To switch axes, select the chart, go to the ‘Design’ tab and click the ‘Switch Row/Column’ button on the Data group.
Change Gridlines to Your Column Chart
Adding gridlines to your chart can make the data in your chart easier to read. Generally, appropriate gridlines are automatically added to your chart when it was created. But you can change it if you’re not satisfied with the default gridlines. If you don’t have gridlines in your chart, add them to make your chart easy to read.
To change the gridlines type, click the (+) floating button next to your chart and click the arrow next to ‘Gridlines’, and then choose the desired gridlines type from the list.
or click ‘More Options’ to open the Format panel on the right with Major Gridlines options. There you can further format your gridlines with more advanced options.
If you’d like to remove chart gridlines, select the gridlines, right-click and select ‘Delete’ or click the (+) floating button next to your chart and uncheck ‘Gridlines’.
Adding Axis Title
You can add titles to the X-axis and Y-axis makes Excel charts easier to understand and lets users know what the chart data is about.
To display either horizontal or vertical axis title, click the (+) floating button next to your chart, expand the Axis Titles option and check either ‘Primary Horizontal’ or ‘Primary ‘Vertical’ checkbox.
An axis title bar appears in your selected axis with the default title, ‘Axis Title’. Click once to select it and click again to edit it.
To format the axis title, right-click on it and select the ‘Format Axis Title’ option. The Format Axis Title pane will open up on the right with more advanced formatting options. You can try different formatting options to format your axis title.
Move Your Bar Graph
To move your bar graph to a new or existing worksheet, you need to select the bar graph, then right-click on the Chart Area and select the ‘Move Chart’ option from the contextual menu or click the ‘Move Chart’ button on the ‘Design’ tab.
In the Move Chart dialog, if you choose ’New Sheet’, the bar graph will be moved to a new sheet called ‘Chart1’; if you choose ‘Object in’, you can choose to move your bar graph into an existing worksheet.
Now, you know to make bar graphs in Excel.