These are used to apply a color gradient to a range of cells. Select ‘ Gradient fill’ or any other as per requirement.Īfter selecting the gradient fill the final output will be as shown in the image below.įrom the above example we can see that sales figure ‘ 3206’ of employee George has the longest data bar covering the entire cell as this value is the highest.Ĥ) Color Scales (Highlight all cells based on their values): To apply conditional formatting, select the range (E2:E10) and click condition formatting in the excel ribbon and select Data Bars Let’s take the same example of sales persons with their sales figures to illustrate Data Bars in conditional formatting. Let’s apply this type of conditional formatting to our sample data. The higher the value, the longer the bar inside the cell. These are used to fill the cell based on its value. There are many others similar options in ‘Top/ Bottom Rules’ like Bottom 10, Top and Bottom 10% and above and below average.ģ) Data Bars (Highlight all cells based on their values):ĭata Bars are of two types – Gradient Fill and Solid Fill type. The highlighted cells are top 3 sales numbers as shown in the image below. Select Top/ bottom Rules then select ‘ Top 10’ as we are interested in top 3 sales figures in our data.Įnter 3 (as we are interested in selecting top 3) and select how we want to fill the cell color as shown in the image below and click OK. To apply conditional formatting, select the range (E2:E10) and click condition formatting in the excel ribbon. Let’s take the same example of sales persons with their sales figures to illustrate this conditional formatting pre-format. These are also used to highlight above average and below average values. These rules are used to highlight Top 10 values or percentages and Bottom 10 values or percentages in a data list. Another two rules that come under this category are ‘ Date occurring’ and ‘ duplicate values’.Ģ) Top/Bottom Rules (Highlighting all cells which are top 10): There are many other similar options in ‘Highlight Cell Rules’ like ‘ less than’, ‘ between’ or ‘ equal to’ a value. Select ‘ Greater than’ as we are interested in finding the sales figure greater than 2000.Įnter 2000 and select how we want to fill the cell color as shown in the image below and click OK.Īs we can see that the cells having the values which are greater than 2000 are highlighted as shown in the image below. To apply conditional formatting, select the range (E2:E10) and click condition formatting in the excel ribbon and select Highlight Cell Rules
Let’s see how the predefined rules are applied to this particular example. We are interested in finding the sales figures which are more than 2000 in the given set of data. Let’s take a simple example of sales persons with their sales figures to illustrate conditional formatting in excel. 1) Highlight cell rules (Highlighting all cells which are more than a particular number): There are a lot of Preformats that Excel provides us. To access Conditional Formatting, Select HOME in the ribbon –> click Conditional Formatting to view the available preformats.
Where can I see conditional Formatting in Excel menu bar? This blog is to give you a brief overview of conditional formatting with few examples for better understanding. So, we add our own formula to the conditional formatting taking the formatting to a whole new level. But, at times only the built in formatting rules are not sufficient. Pre-defined rules are available to select from the list to be used in general. We can do a whole lot of things with this type of formatting. Conditional Formatting can quickly spot trends and patterns in your data with bars, colors and icons by visually highlights important values.Ĭonditional formatting is based on the cell rules we amend. Conditional formatting in Excel is used to highlight a cell or value/text in the cell, when certain conditions are met.