Data Bars in Excel (Insert, Format, and Deal with Negative Values)

In this Excel tutorial, you will learn in detail about data bars in Excel including inserting, making custom data bars, based on another cell, using formulas, setting minimum and maximum values, and customizing for negative values.

We used Microsoft 365 to prepare this article. But you can apply the operations in Excel versions from Excel 2007 onwards.

In order to compare values, identify trends, and highlight maximum and minimum points there is no other better alternative except data bars. These horizontal bars can enhance data visualization and gain valuable insights. While analyzing data, reports, and dashboards these data bars play a vital role.

In the following, you will find an overview of how to insert and format data bars in Excel.

Download Practice Workbook

Data Bars.xlsx

What Are Data Bars in Excel?

Data bars in Excel are a type of conditional formatting that represents data values using horizontal bars within cells. They are customizable, allowing you to adjust the color, style, and placement of the bars to suit your needs. These horizontal bars are mostly used for financial analysis, project tracking, or performance evaluation in Excel.

How to Insert Data Bars in Excel?

You can use the Conditional Formatting feature to insert data bars in Excel. Conditional formatting is a useful feature that allows you to automatically format cells based on specified conditions.

Simply, select data and choose one of the Gradient Fill by visiting the Home>>Conditional Formatting>>Data Bars.

As a result, data bars will be inserted for the selected data.

You can also insert solid fill to visualize data in a different way.
To do so, choose any of your desired colors from the Solid Fill window, by visiting the Home>>Conditional Formatting>>Data Bars.

In summary, you will get the data filled with solid data bars. It’s that simple.

How to Format Data Bars in Excel?

Formatting data bars is a technique used to visually represent and compare data values within a selected cell range. You can quickly understand data relationships, trends, and variations, making your spreadsheet more informative by changing the formats of data bars.

There are several ways to format data bars in Excel.

Making custom data bars
Format data bars using formula
Format data bars based on another cell value
Setting minimum and maximum value

1. Can We Format Making Custom Data Bars?

You can format by creating custom data bars in Excel. Creating your own customized data bars with various colors, styles, and appearance is an easy task.

From the Home tab, open the Conditional Formatting drop-down list.
Now, choose More Rules from the Data Bars list.

In the New Formatting Rule window, choose Format all cells based on their values.
Next, customize according to your choice. Here I have chosen Gradient Fill from the Fill feature.
Then, change the Color and Direction following the image below and hit OK.

Finally, we have successfully created our own customized data bars in Excel. Simple isn’t it?

2. What Are the Ways to Format Data Bars Using Formula?

You can format your data bars using Excel formulas. This is used to provide a flexible and customized way to visually represent data based on specific conditions.

Similar to the previous method, Visit More Rules from the Conditional Formatting feature.
Inside the New Formatting Rule click Format all cells based on their values.
Change the type to Formula.
Place the below formula with the MIN and MAX functions, and press OK.

=MIN($C$5:$C$16)*0.8

=MAX($C$5:$C$16)*1.07

Finally, you will get the data bars according to the formulas.

3. How to Format Data Bars Based on Another Cell Value?

You can also format data bars based on another cell value. By referencing another cell’s value in the conditional formatting rules, you can ensure that data bars adjust automatically as the referenced cell changes. This feature is used to create dynamic data representation.

Here we selected a cell D5 and wrote the below formula;
Then, drag down the Fill handle to fill the cells.

Now, visiting the New Formatting Rule we will choose Format all cells based on their values.
Then, checkmark the Show Bar Only and hit OK.

As a result, we will get the data bars according to the immediate cells to the left.

4. Can We Format Setting Minimum and Maximum Value?

You can set the minimum and maximum values for data bars for formatting. This is used to control the range over which the visual representation is applied.

Opening the New Formatting Rule window, select Format all cells based on their values.
Then, choose the Number type.
Insert your desired minimum and maximum values to display, and hit OK.

Within a glimpse, you will get your desired data bars ready in your hands.

5. How to Format Data Bars for Negative Values?

You can also format data bars for negative values. This is particularly useful when you want to highlight losses, deficits, or unfavorable outcomes in your data.

Suppose we have a dataset of WALMART stock prices for multiple Months where we have some negative prices too.

Similar to method 2, open the New Formatting Rule window.
Choose Format all cells based on their values.
Hence, choose your desired color for the positive values and visit the Negative Value and Axis options.

There choose your desired Fill color for the negative values and click OK.

Within the blink of an eye, you will get to see the data bars for both positive and negative values.

Which Things to Remember While Using Data Bars in Excel?

If you want you can also change the orientation of the data bars to vertical position. This is mostly used when comparing data across different columns.

Frequently Asked Questions

1. How do I remove data bars from a cell or range in Excel?

Answer: To remove data bars, select the cells or range with the data bars applied, go to the Home tab, click on Conditional Formatting, and choose Clear Rules from the menu.

2. What types of data can be represented using data bars in Excel?

Answer: Data bars can be used to represent numerical data such as values, percentages, or other numerical inputs with formulas.

3. How do I adjust the width or length of data bars in Excel?

Answer: To adjust the width or length of data bars, select the cells or range with data bars, visit the Home tab, hit Conditional Formatting, and select Manage Rules to adjust.

Conclusion

In conclusion, data bars in Excel are one of the best tools to present your data in a visually appealing manner. You can use gradient fill data bars when you want to show the intensity or degree of a value in a single dataset. Custom data bars offer flexibility for tailoring the visualization to your needs. Please inform us in the comment section about your experience. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.

Data Bars in Excel: Knowledge Hub

<< Go Back to Conditional Formatting | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!