Excel Bar Chart (Ultimate Guide)

This tutorial will provide an ultimate guide on Excel Bar Chart topics:

  • You will learn to insert a bar chart using features, shortcuts and VBA.
  • We’ll also walk you through various practical examples of using bar charts.

Assume you are analyzing sales, surveys, or market trends. A bar chart will present data in a more visually satisfying. Thus, it would be easy to identify patterns and trends from the data.

Bar Charts provide a clear, concise way to compare values across different categories or groups. By the end, you’ll be able to create visually compelling bar charts. Let’s dive in and unlock the potential of bar charts.

Excel Bar Chart

Download the Practice Workbook

You can download the Practice Workbook for free.


What Is a Bar Chart in Excel?

A bar chart uses rectangular bars to show and compare data for different categories or groups. The bars represent the values, and their length or height shows how big or small each deal is. Bar charts help us to see patterns and differences in the data.


Types of Bar Charts in Excel

Type 1 – Clustered Bar Charts

To insert a Clustered Bar, go to All Charts, choose Bar, click on the icon Clustered Bar, and hit OK.

Clustered Bar Charts

Likewise, you can also insert a 3-D Clustered Bar by clicking the icon.

3-D Clustered Bar


Type 2 – Stacked Bar Charts

To insert a Stacked Bar, go to All Charts, choose Bar, click on the icon Stacked Bar, and hit OK.

Stacked Bar Charts

You can insert a 3-D Stacked by following the previous steps and selecting the 3D Stacked Bar icon.

3-D Stacked


Type 3 – 100% Stacked Bar Charts

To generate a 100% Stacked Bar, go to All Charts, choose Bar, click on the icon 100% Stacked Bar, and hit OK.

100% Stacked Bar

Insert a 3D 100% Stacked Bar chart by clicking on its icon.

3D 100% Stacked Bar chart


Type 4 – Cylinder, Cone, and Pyramid Charts

Choose Series Options, then check Full Pyramid in the Format Data Series pane.

3D Full Pyramid bar chart

Select Series Options, and check Cylinder in the Format Data Series pane.

3D Cylinder bar chart

Choose Series Options, then check Partial Cone in the Format Data Series pane.

3D partial cone bar chart


How to Create a Bar Chart in Excel

Method 1 – Through Charts Group of Insert Tab

  • Choose your data range. Here, that’s B4:C14.
  • Navigate to the Insert tab and click on Column or Bar Chart.

navigate to the Insert tab

  • Choose a 2-D Bar chart to see the output.

insert a bar chart Through Charts Group of Insert Tab


Method 2 – Use a Shortcut Key

Case 2.1 – Insert a Bar Chart in an Existing Sheet

  • Select the range you want to chart.
  • Press Alt + F1.

Insert a Bar Chart in Existing Sheet using shortcut key

Note

You may get a Column chart. In that case, you must change the chart type into a Bar Chart.


Case 2.2 – Insert a Bar Chart in a New Sheet

  • Choose the range you want to chart and press F11.

choose desired cells and press F1

  • We will get an output like the below one.

Insert a Bar Chart in New Sheet


Method 3 – Run Excel VBA Code

  • To open the VBA editor, press Alt+F11.
  • Go to Insert and choose Module.
  • Copy the below code and choose Run.

Open VBA editor to insert a bar chart

  • You will see an output like the following.

Output of running excel vba code to insert a bar chart


How to Format a Bar Chart in Excel

Example 1 – Change the Chart Title

Click the Chart Title box and type the intended title.

Change Chart Title of a bar chart in Excel


Example 2 – Add and Edit Axis Labels

  • Click on Chart Elements.
  • Check Axis Titles.
  • Click on an Axis Title box to edit it.

Add and Edit Axis Labels of a bar chart


Example 3 – Change Chart Style and Colors

  • Click on Chart Styles.
  • Choose the Style tab.
  • Click on the desired style.

Change Chart Style of a bar chart

  • To change the color, navigate to the Color tab, and select the intended color set.

Change Chart Style of a bar chart


Example 4 – Open the Format Chart Area Pane to Add Gradient Fill

  • Right-click on Chart Area and choose Format Chart Area.

  • Go to Chart Options.
  • Click on Fill & Line.
  • Expand Fill and choose Gradient fill.

Open Format Chart Area Pane to Add Gradient Fill


Example 5 – Change the Bar Width and Spacing Between Bars

  • Insert a Bar Chart.

Insert a bar chart to Change Bar Width and Spacing Between Bars

  • Right-click on a Data Series.
  • Click Format Data Series.
  • Select Series Options.

Open Format Data Series Pane

  • Set Series Overlap to -100% and input Gap Width to 400%.

Change Bar Width and Spacing Between Bars


How to Sort Data on a Bar Chart in Excel

Case 1 – By Sorting Source Data

  • Select the chart’s range (B4:C14 in the example).
  • Go to Home.
  • Click on Sort & Filter and choose Filter.

apply filter in the data source to Sort Data on Bar Chart

  • Click on the Filter icon for a column and choose Sort Smallest to Largest.

Sort Data on Bar Chart in Excel by sorting the data source


Case 2 – Without Sorting Source Data

  • Right-click on a Vertical Axis and choose Format Axis.

right-click on format axis to open format axis pane

  • Click on Axis Options.
  • Check At maximum category.
  • Check Categories in reverse order.

Sort Data on Chart without sorting the data source

Read More: How to Sort Bar Chart in Excel without Sorting Data 


How to Change the Order of a Data Series in an Excel Bar Chart

Method 1 – Use the Select Data Source Dialog

  • Insert a bar chart for your range.

Insert a chart to change the order of data series

  • Click on Chart Filters.
  • Go to the Values tab.
  • Choose Select Data.

Click on Filters followed by values to open Select Data window

  • Click on Data Series.
  • Use the Up and Down arrows to change the order of the data series.
  • Click on OK.

Change Order of Data Series in Chart from Select Data window


Method 2 – Use Data Series Formulas

  • Click on a Data Series.
  • Adjust the formula shown in the formula bar.
  • Hit Enter.

Change Order of Data Series in Excel Bar Chart by adjusting Data Series Formula


How to Create Bar Charts with Negative Values

  • Right-click on a Data Series.
  • Click on Format Data Series.

Open Format Data Series pane to Create Bar Charts with Negative Values

  • Click Fill & Line.
  • Check Invert if negative.
  • Choose the colors for positive and negative values.

Create Bar Charts with Negative Values


Things to Remember

  • You can apply the Pyramid, Cylinder, and Cone features only in a 3D Bar Chart.
  • If you want to use the mentioned Excel VBA code for some other datasets, you must modify the code accordingly.

Frequently Asked Questions

What are the uses of bar charts?

Bar charts compare data, track progress, analyze surveys, and present rankings. We can also compare performance and display categorical information using bar charts. They simplify data interpretation and communication in various fields.

How to make a bar chart?

First, you must identify the categories or groups and their corresponding values to insert a bar chart. Next, you have to add the vertical axis with the groups. Lastly, adding the horizontal axis with the values will generate a bar chart.

What are the types of bar in Excel?

Excel provides four kinds of bar charts. A simple chart displays data bars for a single variable. A grouped bar diagram indicates data bars for several variables. A stacked bar graph shows the share of every factor in the total. The percentage chart depicts the percentage of overall assistance.


Excel Bar Chart: Knowledge Hub


<< Go Back to Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Lutfor Rahman Shimanto
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto, BSc, Information Technology, Jahangirnagar University, Bangladesh, has worked with the ExcelDemy project for over a year. He has written 50+ articles and provided solutions of 100+ comments for ExcelDemy. Currently, he works as an Excel & VBA Developer and provides support and solutions in the ExcelDemy Forum. He has solved 100+ ExcelDemy Forum problems. His work and learning interests are in developing various Excel & VBA and Desktop applications. Outside of work, he enjoys Chess... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo