Excel Functions Tutorials
- Excel Tools
- Excel Ribbons and Tabs
- Quick Access Toolbar in Excel (QAT)
- "Save As" Shortcut in Excel
- Accounting Number Format in Excel
- Add-Ins in excel
- Add Filter in Excel
- Advanced Filter in Excel
- Auto Filter In Excel
- Auto Format Excel
- AutoFill in Excel
- Analysis ToolPak in Excel
- ANOVA in Excel
- Border in Excel
- Checkbox in Excel
- Check Mark in Excel (? Tick Symbol)
- Combo Box in Excel and VBA
- Conditional Formatting in Excel
- Conditional Formatting with Formulas
- Conditional Formatting for Blank Cells
- Conditional Formatting Based on Another Cell Value
- Conditional Formatting in Pivot Table
- Consolidate Data in Excel
- Comma Style in Excel
- CSV vs Excel
- Data Bars in Excel
- Data Table in Excel
- Data Validation Excel
- Data Model in Excel
- Developer Tab in Excel
- Descriptive Statistics in Excel
- Dynamic Named Range in Excel
- Drawing in Excel
- Excel Fill Handle
- Excel Fill Down
- Error Bars in Excel
- Excel Forms for Data Entry
- Excel Tables
- Excel Power View
- Exponential Smoothing in Excel
- Filters in Excel
- Flash Fill in Excel
- Freeze Panes in Excel
- Freeze Columns in Excel
- Freeze Cells in Excel
- Format Painter in Excel
- Shortcut for Format Painter in Excel
- F-Test in Excel
- Goal Seek in Excel
- Gridlines in Excel
- Heat Map in Excel
- 3D Maps in Excel
- Header and Footer in Excel
- Insert Button in Excel
- Insert / Draw Line in Excel
- Insert Function in Excel
- List Box in Excel VBA
- Lock Cells in Excel
- Macros in Excel
- Enable Macros in Excel
- Merge and Center in Excel
- Merge Cells in Excel
- Merge Tables in Excel
- Name Box in Excel
- Name Range in Excel
- Null in Excel
- One Variable Data Table in Excel
- OneDrive Excel
- Protect Workbook in Excel
- Pivot Table in Excel
- Pivot Table Examples
- Pivot Table Filter
- Pivot Table Slicer
- Paste Special in Excel (With Top 10 Shortcuts)
- Quick Analysis Tools in Excel
- Radio Button in Excel
- Recording Macros in Excel
- Regression Analysis in Excel
- Scenario Manager in Excel
- Scroll Bars in Excel
- Scroll Lock in Excel
- Slicers in Excel
- Solver in Excel
- Sort by Color in Excel
- Sort by Number in Excel
- Sort Data in Excel
- Sparklines in Excel
- Spell Check in Excel
- Split Panes in Excel (Horizontally, Vertically, Cross Split)
- Status Bar in Excel
- Text to Columns in Excel
- Timeline in Excel
- Toolbar on Excel
- Track Changes in Excel
- Trend Line in Excel
- Two-Variable Data Table in Excel
- Watch Window in Excel
- Wrap Text in Excel
- XML in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tips (178+)
- VBA (162+)
Data bars in excel are a type of conditional formatting options available in excel which are used to highlight the cells or data range in a worksheet based on some conditions, to make it more clearly visible it is advised to make the bars in the column wider, data bars are available in the conditional formatting tab in excel in the home tab.
Table of Contents
What are Data Bars in Excel?
Data bars in excel belong to the conditional formatting functions that allow us to insert a bar chart, but the main thing that makes data bars different from bar chart is this, that the data bars are inserted inside the cells instead of a different location. The bar charts are inserted in a new location and they are an object to the excel but data bars resides in the cell and are not object to the excel.
Data bars insert a bar chart inside excel and this helps to visualize the values of the cells. The larger value will have a large bar line and the lesser value will have a small bar line. This way data bar helps a user to visualize the numbers and help them to save time. Data bars also helps a user to save worksheet area as the data bars resides in the cell only.
How to Add Data Bars in Excel?
Below are the examples to add data bars in Excel.
Example #1 – Data Bars along With the Values
Step 1: Select the range where we want to insert the data bars.
Step 2: Go to the home tab and select conditional formatting.
Step 3: Select the Data bars option from the conditional formatting options.
Step 4: From the available default data bars charts, choose any one as per the required color theme.
Step 5: After the data bars are inserted we will get the result as below.
Example #2 – Data Bars Without the Values
In this method, we will hide the values that are in the cell.
First, we will insert the Data bars as per the above steps and then follow the below steps.
Step 1: Select the data bar chart.
Step 2: Go to the conditional formatting and choose “Manage Rules”.
Step 3: From the “Manages rules tab” choose to “Edit Rules”.
Step 4: Now we need to select the option of “Show Bar only” so that the value that is in the cells are not visible.
Step 5: After the above steps, we will get the below result.
Example #3 – Negative and Positive Data Bars
In this case, we just need some negative values and some positive values.
To create a data bar chart with a negative and positive value, simply select the range of cell and follow steps of method 1.
After the above steps, we will get the below result.
Example #4 – Data Bars Above a Certain Value
In this case, we need to add a condition that only those cells will be formatted that met the prescribed condition.
Step 1: From the “Manage Rule” option of conditional formatting choose to “Edit the rule”.
Step 2: From the edit rule window we need to make some changes to the condition as per our requirement.
Step 3: Now only those cells will be formatted that have a value greater than 30.
Explanation of Data Bars in Excel
- Data bars allows us to save time and create a visualization effect in just some couple of minutes only. Data bars are same as we have bar chart but the difference is this that the bar charts are objects to excel but data bars are only the interior of cells and they reside in the cell.
- Data bars works only when we have quantitative data and it is not possible to use the data bars on qualitative data. When we insert data bars in excel then the complete range of cells on which we have inserted the data bar is treated as one set and the excel allocates the length of the bar based on the complete data.
- The data that we use, will be first analyzed by excel and then the min and max values will be identified by excel. Based on the min and max values, bar length is decided by excel.
- In the data bars, we have many other features that can also be used. We can choose to format only that cells that have value above some certain limit, we can choose to format that cells only that has a value that is between given conditions.
- So in this way, we can also choose to give some conditions to the data bars and make them more dynamic. Since we can add conditions to the data bars, that is why this function belongs to conditional formatting class.
- Like we have the positive and negative axis in a bar chart we can also choose to show the negative and positive value in the same data chart. The only difference will be this that there will only be one axis instead of two axes and this makes the data bars more useful to use in cases where we have the negative values also.
Things to Remember About Data Bars in Excel
- Data bars only work with the quantitative data.
- Data bars are not an object to excel.
- Data bars can also be used for negative numbers also.
- There is only one axis in data bars.
- Data bars work best in a case where there is less variation in the values of the data.
This has been a guide to Data Bars in Excel. Here we discuss how to add Data Bars in Excel using Conditional Formatting along with practical examples and downloadable excel template. You may learn more about excel from the following articles –