Excel Data Bars (Table of Contents)
Data Bars in Excel
Conditional formatting is the ultimate thing, most of the people use in their dashboard creation. Conditional formatting includes several kinds of formatting techniques like Duplicate highlight, Top N values highlight, highlighting particular text, and many more formatting rules we have in conditional formatting. In this article, we are concentrating on Data bars in Excel.
I personally say Data Bars are in cell chart. Instead of creating a separate chart for your data you can create in cell chart by using Data bars under conditional formatting in Excel. Sometimes we cannot identify the top value by just looking at the numbers, we need some kind of color which can highlight the top value.
In order to highlight the top value, we have a tool called highlight top N values in the selected range of cells. This is a different technique usually we show a simple bar chart to represent the data graphically, instead of showing a separate chart we can represent the graphics in the cell itself.
How to Add Data Bars in Excel?
Adding Data bars in Excel is very simple and easy. Let’s understand the working of adding data bars in excel with some examples.
Example #1 – Difference between Chart & Data Bars
Now see the difference between a chart and a data bar. For this, I have taken some sales data to show the numbers in graphics.
For this data let me apply a simple Column Bar Chart.
Step 1: Go to Insert and Click on a Column chart.
Step 2: Now we have a simple Column chart for our data.
Wow!! Looks good to go and present. But wait we have more interesting graphics which shows the bars inside the respective cell itself, I am sure you will be amused at seeing them.
We will add excel Data bars for this data which shows the bars inside the cell along with the numbers. Follow the below steps to add data bars in Excel.
Step 3: Select the number range from B2 to B11.
Step 4: Go to the HOME tab. Select Conditional Formatting and then select Data Bars. Here we have two different categories to highlight select the first one.
Step 5: Now we have a beautiful bar inside the cells.
Highest value has the largest bar in this group, the least value has the short bar. This is automatically picked by excels conditional formatting only.
Show Only Bars & No Numbers
Showing in cell bars is not the end of the Data bar technique it has many more techniques in it.
In case if you do not want to see numbers but want to see only bars in the cell you can choose to show only bars instead of showing both of them. In order to show only bars, you can follow the below steps.
Step 1: Select the number range from B2:B11.
Step 2: Go to Conditional Formatting and click on Manage Rules.
Step 3: As shown below double click on the rule.
Step 4: Now in the below window select Show Bars Only and then click OK.
Step 5: Now we will see only bars instead of both numbers and bars.
Example #2 – With Negative Numbers
Excel Data bars works for negative numbers as well. I have a student’s six competitive exam scores. Due to negative marking, some of the students in some of the exams got negative marks.
By looking at the data suddenly you cannot tell which student got negative marks in exams. We need some highlight here also to identify the top scores in each exam very quickly. This can be done by using the Data Bar technique in conditional formatting.
Step 1: Select the Exam 1 Scores range of cells.
Step 2: Go to Conditional Formatting and select Data bars. Select gradient fills but do not select red bars because we have negative numbers here. All the negative numbers will be represented by red bars.
Step 3: Now we have bars. We can quickly identify the negative scores easily.
Step 4: For other remaining 5 exams you need not apply conditional formatting one by one rather you can copy the currently applied conditional formatting range and paste as formats.
Copy the current conditional formatting range by pressing Ctrl + C.
Step 5: Select the remaining 5 exam range. Press ALT + E + S + T. This is the shortcut key for pasting the only format of the copied cells.
Step 6: We will have conditional formatting data bars for all the exams now.
Things to Remember About Data Bars in Excel
- There are two kinds of Data Bars available in Excel. Select Gradient if you present both bar and numbers together or if you are showing only bars select Solid.
- You can change the color of the bar under Manage Rule and change the color there.
- When the data includes negative values, bars will be created from half of the cell, not from the left-hand side of the cell.
This has been a guide to Data Bars in Excel. Here we discuss how to add Data Bars in Excel along with excel examples and downloadable excel template. You may also look at these useful charts in excel –