Bar Chart Formula in Excel: How to Create One

There is no special Excel formula that creates a bar chart: a chart is an object you insert from selected data. In Excel for Microsoft 365 on Windows, select the category labels and values, then choose Insert > Insert Column or Bar Chart > 2-D Bar > Clustered Bar; use a worksheet formula only if you need to calculate or summarize the values first.

What a bar chart formula means

People searching for a bar chart formula often mean one of two things: a formula that makes the chart, or a formula that prepares numbers for a chart. Excel does not provide a worksheet function that turns a cell range into a chart. Charts are created with the ribbon, and can then be edited, moved, resized, or linked to different cells.

Formulas can still be useful in the process. For example, if a worksheet contains many transactions, a formula can total sales for each region. You can then chart those totals. The chart displays the calculated results, while the formula remains in the worksheet cells.

In Excel terminology, a bar chart has horizontal bars. A column chart has vertical bars. Both can compare values across categories, and Excel places their options together under the Insert Column or Bar Chart menu. This distinction matters when following instructions or choosing a chart type.

Arrange the data before charting

A basic chart needs a category column and a value column. Put one category and its corresponding number on each row, with a clear header above each column. For example, a table could have Region in A1, Sales in B1, and region names and sales amounts underneath. Avoid blank rows inside the data, merged cells, and multiple unrelated value columns unless you intend to plot multiple series.

Use numbers for values, not number-like text. If Excel treats 120 as text, it may not chart or total it as expected. Currency formatting does not change a number into text; it simply changes how the number appears. Likewise, use one consistent spelling for each category. Excel treats East and east, or East and East with a trailing space, as potentially separate labels in a summary.

For data that will grow over time, formatting the range as an Excel Table can make the source easier to maintain. Select the data and choose Insert > Table, then confirm whether the table has headers. A chart created from a table can generally include added table rows as the table expands, though verify the chart after changing the source or layout.

Create a bar chart in Excel

Arrange the labels and values in adjacent columns, including descriptive headers.
Select the cells you want to chart. For a simple chart, select both the category labels and their values, including the headers.
Choose Insert > Insert Column or Bar Chart in the Charts group.
Under 2-D Bar, select Clustered Bar for separate horizontal bars. Choose Stacked Bar or 100% Stacked Bar only when the data calls for those comparisons.
Review the chart. If Excel has assigned the categories or values incorrectly, select the chart and use Chart Design > Switch Row/Column or Chart Design > Select Data to adjust its source.
Add a useful title and, where needed, axis titles or data labels using the chart controls or the Chart Design tab.

On Excel for Mac, the ribbon layout differs; a common route is Insert > Recommended Charts, then All Charts > Bar and the desired subtype. The exact presentation can vary slightly by update. The chart itself is still based on worksheet cells, not a chart-making formula.

Worked example: summarize values with a formula

Suppose A2:A7 contains the regions North, South, East, North, South, and East, while B2:B7 contains sales of 120, 95, 140, 80, 110, and 70. To make a chart of totals by region, enter East, North, and South in D2:D4. The summary formula below assumes those source ranges and puts the first region label in D2; enter it in E2 and fill it down through E4.

With region names in A2:A7, sales in B2:B7, and the first summary label in D2, enter =SUMIF($A$2:$A$7,D2,$B$2:$B$7) in E2. It returns 210 for East because 140 + 70 = 210. Filled down, the results are 200 for North and 205 for South.

Select D1:E4 if you have added headers such as Region and Total Sales in D1 and E1, then insert a clustered bar chart. The chart will compare three totals. This is an example of a formula preparing chart data; SUMIF does not create or format the chart itself. If your Excel uses a regional list separator other than a comma, its formula entry conventions may differ.

Format the chart so it communicates clearly

Start with a title that says what the bars represent, such as Total sales by region. Keep category names readable and use a sensible number format for the value axis. If values are currency, format the underlying cells as currency or adjust the axis number format so readers do not mistake dollars for counts. The chart’s appearance should reinforce the data, not add decoration that distracts from it.

For a bar chart, category labels appear along the vertical axis and values along the horizontal axis. Excel may place the first category at the bottom or top depending on settings. If the order reads unexpectedly, right-click the category axis, choose Format Axis, and inspect the category order options. Do not reverse the order without checking that the labels still match the intended reading.

Data labels can help when exact values matter, but they can clutter a chart with many categories. To add them, select the chart and use Chart Design > Add Chart Element > Data Labels. A legend is useful when the chart has multiple series; for a single series, it often adds little. Avoid relying on color alone to distinguish information, especially if the chart may be printed in grayscale.

Choose between bar, column, and stacked charts

Use a horizontal bar chart when category names are long or when you want readers to rank many categories. Bars make it easier to compare values against a shared baseline. A column chart can suit a small set of categories or values organized by time, but long category labels may be difficult to fit beneath vertical columns.

Clustered bars are appropriate when you want to compare separate series within each category, such as sales for two years in each region. A stacked bar shows how series contribute to each category total. A 100% stacked bar compares proportions rather than raw totals, so categories with very different overall sizes can still be compared by share. That also means it no longer communicates the difference in total size as directly.

For a simple ranking, sorting the source values from largest to smallest can make the result easier to scan. If the chart is based on a fixed range, sort the entire source table so each label stays paired with its value. Sorting only the values column would misrepresent the categories.

Common problems and how to fix them

The chart shows the wrong labels: use Chart Design > Select Data to check the category axis label range and the series values. Make sure the selected range includes the intended label column.
Excel plots categories as separate series: choose Chart Design > Switch Row/Column. This is common when the source data is laid out across rows rather than down columns.
A bar or category is missing: check for blank cells, text-formatted numbers, filters, or a source range that stops too early. Use Select Data to inspect the chart’s source range.
The bars are too crowded: reduce the number of categories shown, enlarge the chart, or use a horizontal bar chart for long labels. Do not shrink the text until it becomes hard to read.
The axis starts at an unexpected value: right-click the value axis and choose Format Axis. Check the minimum and maximum bounds. A truncated scale can exaggerate small differences, so use it only when the scale is clearly explained and appropriate.
The chart does not update after adding data: confirm that the new rows are inside the chart’s source range. A table-based source may expand more conveniently than a manually selected fixed range.

Keep the chart current as data changes

A chart is linked to a cell range. When a value in that range changes, the chart normally reflects the new value. Adding a row is different: a chart built from an ordinary fixed range may not include cells added below or beside that range. Check the chart’s source with Chart Design > Select Data if new records are not appearing.

For a regularly updated report, an Excel Table is often a straightforward option because it expands as records are added. Another approach is to maintain a compact summary area and chart that area rather than a long transaction list. Whichever setup you choose, test it by adding a sample row and confirming that both the summary and chart include it before relying on the workbook as a recurring report.

Frequently Asked Questions

What is the formula for a bar chart in Excel?
There is no worksheet formula that creates a bar chart. Select the chart data and use Insert > Insert Column or Bar Chart > 2-D Bar. Use formulas such as SUMIF to calculate the values you want the chart to display.

How do I make a bar chart from two columns?
Put category names in one column and their numeric values in the next, select both columns including headers, and insert a 2-D clustered bar chart. Confirm that Excel uses the first column for category labels and the second for values.

How do I make a bar chart show totals by category?
First summarize the records by category, using a PivotTable or a formula such as SUMIF. Chart the resulting category-and-total table. Check that the summary includes every category and that the values are numeric.

How do I change a column chart to a bar chart?
Select the chart, then choose Chart Design > Change Chart Type and select a bar subtype. A bar chart uses horizontal bars; a column chart uses vertical columns.

Why does my Excel bar chart show the wrong order?
Check the order of the source data and the category-axis settings. A horizontal chart may display the first category at the opposite end from what you expect; right-click the category axis, choose Format Axis, and review its category-order options.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.