Home and Learn: Microsoft Excel Course

Format your spreadsheet cells with Data Bars

You can format the cells on your spreadsheet so that they have bars in them, or even set up a range of numbers to have a colour scale. You can do all that with Conditional Formatting.

In the image below, we've formatted a set of sales figures so that the cells contain data bars:

Data BArs in an Excel cell

Create a new spreadsheet. In A1 enter the text heading January. Widen the A column and then enter any numbers you like in cells A2 to A7:

A simple Excel spreadsheet of one column

Now select the cells A2 to A7. From the Home ribbon at the top of Excel, locate the Styles panel and select the Conditional Formatting item. From the menu, select Data Bars. You'll then see some formatting options appear:

The Data Bars option on the Conditional Formatting menu

You can select a gradient fill for your bars, or a solid fill. Click on one of the Fills to see how it looks on your spreadsheet.

If you click on More Rules, you'll see the following dialogue box appear:

The New Formatting Rules dialogue box

Here, you can select from a wider range of colours by clicking on the Fill dropdown box at the bottom, under Bar Appearance:

The solid fill option for data bars

The Fill dropdown says Solid Fill. Click this dropdown and you can change it to Gradient Fill:

The Gradient Fill option

In the image above, green will be mixed with white to create a gradient colour. (You can't change the white as a gradient colour.)

To get a border colour for your bars, select Solid Border from the Border dropdown list:

The Solid Border option

Once you've selected Solid Border, click the colour square to the right of the dropdown. Select a new colour:

Select a colour for your Solid Border

Click OK on the dialogue box to see your new formatting applied:

Data Bars formatting for an Excel cell

If you have negative values in a cell, you'll see bars to the left and right of a zero point. In the image below, we have some values that are negative and positive:

Negative numbers in an Excel cell

When we add Data Bars conditional formatting, the cell will look like this:

Data Bars for negative numbers

In the next part, you'll see how colour scales can be applied to Excel cells.

Excel and Colour Scales -->

<--Back to the Excel Contents Page