Data Bars

Data bars provide a quick way of visualizing the data at hand. In essence it provides an in-cell bar graph to represent the data of that cell. Length of the bar depends on the number contained in the cell and is relative to other cell values for which this visualization is selected.

We take you through a quick overview of this functionality through an example.

Let’s say we have region-wise month-wise sales for a highly seasonal product (say umbrellas, tour guides, school uniforms etc). You want to see the seasonality impact and seasonal period across these regions. How do you do that?

While you may actually do this by looking at the numbers carefully, excel’s data bars feature is made specifically for such scenarios, & it is quite awesome at that!

Let’s say we want to first analyze the sales pattern for East region (how much sold by month). To use data bars, select the data range to be analyzed (B3 to B14 in this case) go to Home->Conditional Formatting->Data Bars and choose a color.

Voila! Do you see those colors now?

Now this can be repeated for all the regions (select C3:C14 then apply data bars, next do the same for D3:D14 and E3:E14) and you will get the final outcome like this:

Now this is quite useful. In one shot we know that high sale season in North, South & West is during the same period(Mar/Apr), while East has the season in Nov-Jan period. Also we can say that Sept is when we see a mini peak for North, West & South. This is rich information, unearthed through data bars, and that too through just a few clicks.. impressive in deed!

If you notice, the bars reflect the data within the region across months (bigger bar means bigger month), but a similar comparison can’t be done across regions (for ex: C5’s bar is bigger than D6, while the number is smaller).This is because bar sizes are automatically decided for the range on which they are applied (cells selected while choosing data bars).

 If you want to see the relative numbers across regions & months, select all the cells (B3:E14) & then select the data bars, it will now consider all data values while deciding the bar length.

Excel 2009 onwards, there are more features added to data bars (like negative values, alignment etc). We’ll cover them in some other post.

Section: