Excel Sparklines: How to Make Horizontal Bar Charts

Excel for Microsoft 365 does not have a horizontal-bar sparkline type: its built-in sparklines are Line, Column, and Win/Loss. To show horizontal bars inside worksheet cells, select the values and choose Home > Conditional Formatting > Data Bars; these bars are useful, but they are conditional formatting rather than sparklines.

What Excel means by a sparkline

A sparkline is a compact chart that fits inside a worksheet cell, usually to show the shape or direction of a data series beside the numbers it summarizes. In Excel for Microsoft 365 on Windows, the built-in choices are Line, Column, and Win/Loss. There is no Bar option in the Sparkline menu, and changing a sparkline’s color or axis settings does not turn it into a horizontal bar chart.

The distinction matters because the tools answer different questions. A sparkline typically represents several values for one item, such as a department’s monthly sales, in a single cell. A conditional-formatting data bar typically represents one value in each cell, making it easy to compare several items down a list. Both are small and in-cell, so they can look similar in a compact report, but they are not interchangeable features.

Insert a built-in sparkline

Use a sparkline when you want to show a trend across a sequence, rather than one horizontal bar for each value. For example, if January through June figures are in B2:G2, you can put one sparkline in H2 and repeat the process for other rows.

Select the cell where the sparkline should appear, such as H2.
Choose Insert > Sparklines > Line, Column, or Win/Loss.
In the Create Sparklines dialog, check that the Data Range contains the values for the series, such as B2:G2, and that the Location Range is H2.
Select OK. To make more sparklines, select the corresponding data and destination ranges for the other rows.

A Line sparkline emphasizes movement over time. A Column sparkline draws a tiny vertical column for each value in the series. Win/Loss reduces each observation to a positive, negative, or zero outcome, so it is useful for results such as wins and losses but not for showing the magnitude of sales. If the goal is to compare one numeric value per product or person, use data bars instead.

Make horizontal bars with conditional formatting

For a conventional in-cell horizontal bar, place one numeric value in each cell of a range, then apply Data Bars to that range. For example, suppose product names are in A2:A6 and their sales totals are in B2:B6. Select B2:B6, then choose Home > Conditional Formatting > Data Bars and select a style. Each cell receives a bar whose length reflects its value.

Keep the numbers visible unless the bar alone is enough for the task. Data bars are most informative when a reader can see both the approximate magnitude and the exact value. If you want a cleaner bar-only display, you can use the conditional-formatting rule’s option to show the bar without the value, but consider keeping the figures in a neighboring column or making them available in a clear label.

For fair comparisons, apply one rule to the full set of values you intend readers to compare. If each row or category is formatted using a different scale, identical-looking bars may represent very different amounts. A single rule across B2:B6 makes the longest bar correspond to the largest value in that selected range and the shortest to the smallest, subject to the rule’s minimum and maximum settings.

Adjust the bar scale and appearance

Excel offers gradient-fill and solid-fill data bar styles. To control the scale or display, select a cell in the formatted range and open Home > Conditional Formatting > Data Bars > More Rules. In the formatting dialog, you can adjust the minimum and maximum, choose a fill and border, and set whether the cell’s value appears alongside the bar. The available settings help make a report more consistent, particularly when values are updated or when the same scale must be maintained across multiple ranges.

Automatic scale: Useful for a quick comparison within one range. The displayed lengths are based on that range’s values.
Fixed limits: Useful when readers compare separate tables or reporting periods. Set consistent minimum and maximum values so the same bar length has the same meaning in each table.
Negative values: Data bars can show values on either side of a zero axis. Check the axis and negative-bar appearance in the rule settings if the range includes both positive and negative numbers.
Cell space: The bar uses the cell’s available width. Widen the column if small differences are hard to distinguish, and keep enough room for displayed values if you show them.

Fixed limits are especially useful for progress measures. If every row is a percentage from 0 to 100%, setting the scale to those limits lets a 50% bar occupy half the available bar length even when the current data set contains no value near 100%. By contrast, an automatic scale might make the largest current value look full-length even if it is only 63%.

A practical comparison example

Imagine a weekly report with five support teams and their number of resolved tickets. Put team names in A2:A6 and ticket totals in B2:B6, then apply Data Bars to B2:B6. A reader can scan the bar lengths to spot which teams resolved more tickets while still reading the exact totals. Because all five cells share a rule, they use the same comparison range.

Now imagine the report also tracks each team’s resolved tickets for the last six weeks. Put those weekly figures in B2:G2 and insert a Column sparkline in H2. The sparkline shows the week-to-week pattern for that team; it does not replace the horizontal comparison bar for the current total. You can place both visualizations in the same report because they answer different questions: “How does this team compare?” and “How has this team’s result changed?”

This is a useful design principle for compact dashboards. Choose a data bar for a single measure compared across many rows. Choose a sparkline for a series of measures summarized in one cell. A normal chart is often better if the reader needs a legend, labeled axes, several series, or more space to inspect individual values.

Alternatives when you need a custom in-cell bar

If data bars do not provide the look you want, a text bar is another option. It repeats a block character according to a value, so it can be customized with a font or placed beside other text. It is not a chart or sparkline, however: its length depends on text rendering and cell width, and it does not provide the same automatic visual scale controls as conditional formatting.

For this example, assume B2:B6 contains values from 0 to 100 and D1 contains the fixed maximum, 100. In C2, use =REPT("■",ROUND(B2/$D$1*20,0)). If B2 is 60, the formula returns 12 block characters; copy it down to create bars for the other rows. Keep the scale value in D1 consistent if the bars will be compared across reports. If the maximum can be zero or values can exceed the chosen scale, account for those cases before relying on the display.

A standard chart is the more flexible alternative when you need a true horizontal bar chart with category labels and a value axis. Choose Insert > Insert Column or Bar Chart > 2-D Bar for a chart that can be resized and labeled. It takes more room than a cell-level data bar, but it makes categories and scale easier to read, especially with many items or long labels.

Common problems and misconceptions

Looking for a Bar option under Sparklines: Excel’s sparkline types are Line, Column, and Win/Loss. Use Data Bars or insert a regular bar chart instead.
Bars appear almost identical: Check whether the range contains values close together, whether the cells share one rule, and whether the minimum and maximum are appropriate. A wider column can also make modest differences easier to see.
Bars change meaning between tables: Separate automatically scaled ranges can give different visual meanings to the same length. Apply fixed minimum and maximum values when readers need to compare across tables.
Values are hidden by the bar: Review the rule’s display setting and widen the column. For exact reading, retain the numeric value or show it in a neighboring cell.
A sparkline does not update as expected: Check that the data range includes the intended cells and that the destination cell is in the correct row. A sparkline visualizes its assigned series; it does not automatically summarize an entire table unless that table is specified as its source.

Also remember that color alone should not carry essential meaning. Keep values, labels, or another clear cue in the worksheet, and choose colors with enough contrast for the intended audience. A data bar is a quick visual aid, not a replacement for accessible labels or a well-organized data table.

Frequently Asked Questions

Can Excel make a horizontal bar sparkline?
No. Excel for Microsoft 365 has Line, Column, and Win/Loss sparklines, but no horizontal Bar sparkline. Use conditional-formatting Data Bars for in-cell bars or insert a 2-D Bar chart for a conventional chart.

Are Excel data bars the same as sparklines?
No. Data bars are conditional formatting applied to numeric cells, usually one bar per value. A sparkline is a tiny chart in one cell that represents a data series. They are both compact visuals, but they have different purposes and controls.

Can I show the number as well as its data bar?
Yes. Data bars can appear with the cell’s value, which is useful when readers need both a quick comparison and an exact figure. If the number is hidden by the rule, open More Rules and review the option to show only the bar.

How do I make bars comparable across separate ranges?
Use the same fixed minimum and maximum in each data-bar rule. Automatic scales are convenient within a single range, but separate ranges may scale to their own values and make equal-length bars represent different numbers.

Can a sparkline show several months in one cell?
Yes. Select a destination cell and create a Line or Column sparkline using the cells containing the monthly values as its Data Range. That displays a compact trend; it does not create one horizontal bar for each month.

Which option should you choose?

Use a sparkline when the story is the pattern across a sequence, such as monthly revenue or weekly activity. Use Data Bars when the story is a comparison of individual values in a list, such as sales by product. Choose a regular horizontal bar chart when category names, a clear axis, labels, or more detailed comparison are important. Excel does not offer a horizontal-bar sparkline, but these alternatives cover the common reasons people look for one.

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.