Data bars in Excel - Step-by-Step Guide

Introduction

Data bars add color bars inside cells to show the size of numbers visually. They help you quickly see which numbers are bigger or smaller without reading each value.

When you want to compare sales numbers across months at a glance.

When you have a list of test scores and want to highlight the highest and lowest.

When you track expenses and want to see which categories cost more.

When you create a report and want to add visual impact to numeric data.

When you want to spot trends or outliers in a table quickly.

Steps

Step 1: Select the cells with numbers you want to visualize

- Worksheet grid

The selected cells are highlighted

💡 Make sure to select only the numeric cells you want to compare

Step 2: Click the Home tab

- Ribbon at the top

Home tab options appear

Step 3: Click Conditional Formatting

- Styles group on the Home tab

A dropdown menu with formatting options appears

Step 4: Hover over Data Bars

- Conditional Formatting dropdown menu

A submenu with color bar styles appears

Step 5: Click a color style you like

- Data Bars submenu

Colored bars appear inside the selected cells showing relative values

Step 6: To customize, click Conditional Formatting > Manage Rules

- Home tab > Styles group > Conditional Formatting

Conditional Formatting Rules Manager window opens

💡 Here you can edit bar colors, minimum and maximum values, and bar direction

Before vs After

Before

Cells A2 to A6 show numbers: 10, 50, 30, 70, 90 with no color

After

Cells A2 to A6 show the same numbers with colored bars inside each cell. The cell with 90 has the longest bar, and the cell with 10 has the shortest bar

Settings Reference

Bar Color

📍 Conditional Formatting Rules Manager > Edit Rule > Bar Appearance

Changes the color and style of the data bars

Default: Gradient Fill with blue color

Minimum and Maximum Values

📍 Conditional Formatting Rules Manager > Edit Rule > Minimum and Maximum

Sets the scale for the shortest and longest bars

Default: Automatic (based on selected data)

Show Bar Only

📍 Conditional Formatting Rules Manager > Edit Rule

If checked, hides the number and shows only the bar

Default: Unchecked

Bar Direction

📍 Conditional Formatting Rules Manager > Edit Rule

Controls which side the bar grows from

Default: Left to Right

Common Mistakes

Selecting cells with text or empty cells along with numbers

Data bars only work with numbers, so text or blanks can cause unexpected results or no bars

Select only the numeric cells you want to visualize before applying data bars

Not adjusting minimum and maximum values when data has outliers

Automatic scaling can make most bars look very short if one number is extremely large

Manually set minimum and maximum values in the rule to better represent your data range

Checking 'Show Bar Only' without realizing numbers are hidden

Users may think numbers disappeared by mistake

Only use 'Show Bar Only' if you want a clean look without numbers, otherwise leave it unchecked

Summary

Data bars add colored bars inside cells to show relative sizes of numbers visually.

They help you quickly compare values without reading each number.

Remember to select only numeric cells and adjust settings for best results.