There are many ways to format your existing database in Microsoft Excel, and Conditional Formatting is the most common feature that is used to format and present the data in very effective and interactive manner.
Conditional Formatting option is available in Menu -> Home section and provide various of conditions from which we can format the database. It comes with variety of parameters, like we can highlight the cell with conditions of, Greater than, less than, Between, Equal to, Text that contains and so on. We can also highlight the cells with conditions of, Top/Bottom 10% etc.
Conditional formatting helps to create such types of visualization:
– Performance Report
– Attendance Tracker
– Grade/ Rating visualization
– Highest or Lowest values, Top or Bottom % report
– Sales summary
– Monthly expenditure summary
Conditional Formatting comes with limitation that, since formatting or visualizations are prepared based on the existing database, sometimes it may lead to incorrect presentation if database is not accurate. So always keep in mind that database is accurate before preparing presentations.
Kinds of Conditional Formatting:
There are variety of types of Conditional formatting like, formatting basis on
-> Highlight Cells by Rules:
-> Top/Bottom Rules
-> Data Bars
-> Color Scales
-> Icon Sets
We will cover all of the above types in each of examples. Keep reading the Conditional Formatting lesson:
– Greater Than…
– Less Than…
– Between…
– Equal To…
– Text that Contains…
– A Date Occurring…
– Duplicate Values…
We will start with below example:
-> Highlight Cells by Rules -> Greater Than…
We have list of total 10 products and would need to highlight the products for which sales are greater than $8000. Follow the below steps:
Select the Sales values in Column B (in below example)
Click to Menu -> Conditional Formatting -> Highlight Cells by Rules ->Greater Than…
Input the values as per your requirement, 8000 as per the example. We can also change the colors to highlight, it is “Light Red Fill with Dark Red Text” as per the example
Click to “OK”
All the products greater than sales values 8000 will be highlighted in “Light Red Fill with Dark Red Text”
We can simply use the above steps to make the conditional formatting to below remaining parameters:
-> Highlight Cells by Rules:
– Less Than…
– Between…
– Equal To…
– Text that Contains…
– A Date Occurring…
– Duplicate Values…
We can also place conditional formatting basis on Top/Bottom Rules as follows:
-> Top/Bottom Rules
– Top 10 Items…
– Top 10%…
– Bottom 10 Items…
– Bottom 10 %…
– Above Average…
– Below Average…
We will start with -> Top/Bottom Rules -> Top 10 Items…
We have sales volumes for two period and now we want to highlight the Top 10 items in the total database. Follow below steps:
Select the Sales values in Column B and C (in below example)
Click to Menu -> Conditional Formatting -> Top/Bottom Rules -> Top 10 Items…
Input the values as per your requirement, i.e. 10 (for Top 10) as per the example. We can also change the colors to highlight, it is “Light Red Fill with Dark Red Text” as per the example:
Click to “OK”
Top 10 items will be highlighted in “Light Red Fill with Dark Red Text”
We can simply use the above steps to make the conditional formatting to below remaining parameters:
-> Top/Bottom Rules
– Top 10%…
– Bottom 10 Items…
– Bottom 10 %…
– Above Average…
– Below Average…
We can also place conditional formatting basis on Data Bars presentation.
-> Gradient Fill : This shows the Data Bars in two shades
-> Solid Fill : This will show the Data Bars in one color
Follow below procedure:
Select the Sales values in Column B (in below example)
Click to Menu -> Conditional Formatting -> Data Bars -> Blue Gradient… (You can choose any of given color i.e. Green, Red, Orange, Light Blue and Purple)
Click to “OK”
Sales volume will be presented in Data Bars format, higher values will have the larger bars and lowest values will have lower bars.
Example 4: Color Scales Rules
We can also place conditional formatting basis on Data Bars presentation. Follow below steps:
Select the Sales values in Column B (in below example)
Click to Menu -> Conditional Formatting -> Color Scales -> Green-Yellow-Red Color Scales… (You can choose any of given colors)
Click to “OK”
Sales volume will be presented in Color Scales format, higher values will have the larger bars and lowest values will have lower bars.
Explanation to Color Scales:
There are three colors available in Color Scales i.e. Red, Yellow and Green. Minimum value is colored as “RED”. Mid value is colored as “YELLOW” and Highest value is colored as “GREEN”. Rest other cells are colored as per the appropriate propositions.
Example 5: Icon Sets Rules
We can also place conditional formatting basis on Icon Sets presentation. Follow below steps:
Select the Sales values in Column B (in below example)
Click to Menu -> Conditional Formatting -> Icon Sets -> 3 Arrows (Colored) (You can choose any of given Icon Sets)
Click to “OK”
Sales volume will be presented in Icon Sets format, higher values will have the “UP” Sign/Arrow and lowest values will have “DOWN” Sign/Arrow.
Explanation to Icon Sets:
There are Icons available in Icon Sets i.e. DOWN, FLAT and UP Arrow. Minimum value is shown with “DOWN Arrow” (RED). Mid value is shown with “FLAT Arrow” (YELLOW) and Highest value is shown with “UP Arrow” (GREEN). Rest other cells are mentioned with Icon sets as per the appropriate propositions.
Example 6: Clear Rules
We can also “Clear Rules” of existing conditional formatting, which are available in the excel spreadsheet. “Clear Rules” will remove all the formatting placed in the highlighted area or from entire sheet. It is very useful and easy to apply, and all formatting can be removed in one go. Follow the below procedure to apply “Clear Rules:
Select the Sales values in Column B (in below example)
Click to Menu -> Conditional Formatting -> Clear Rules ->
-> Clear Rules from Selected Cells
-> Clear Rules from Entire Sheet
All existing Rules in the Selected Cells will be removed. We can also remove the rules for entire worksheet by clicking “->Clear Rules from Entire Sheet” option.
Example 7: Manage Rules
If we need to edit the existing Rules available in the excel sheet, we can edit them with the help of “Manage Rules”. Follow the below steps:
Select the Sales values in Column B (in below example)
Click to Menu -> Conditional Formatting -> Manage Rules ->
Click to “Edit Rules”
Edit Rules as per the requirements ->
Click to “OK”
Hope you liked. Happy Learning.
Don’t forget to leave your valuable comments!
What is COUNTIFS in Excel? The Microsoft Excel COUNTIFS function counts the number of cells in a range, that meets a single or multiple criteria and adjacent or non-adjacent. As a Statistical function of Excel,…
Understand how to find median in Excel with simple steps. Understanding the middle value in a set of numbers, known as the median, is important in the data industry. Professionals often use Microsoft Excel to calculate this. Excel’s MEDIAN function helps quickly find this value from long lists of numbers. This saves time and allows for further calculations using the median value. In this article, we explain what the MEDIAN function in Excel does, why it’s useful, and two methods to find the median in your data.
If you have data on paper that you want to add to Excel, you can take a photo of it, save the photo to your computer, and upload the data into Excel. This saves time…
Print Row Header on Each Page in Excel means that when you print your worksheet, the selected row (often the top row with column headers) will repeat at the top of every printed page. This ensures…
This tutorial introduces XLOOKUP, a new function in Excel for both vertical and horizontal lookups. Tasks that used to feel super complicated, like left-side lookups, finding the last match, or using VLOOKUP with multiple criteria, are now much easier with XLOOKUP.
Before, you had to choose between VLOOKUP for vertical lookups, HLOOKUP for horizontal ones, or more complex options like INDEX MATCH or Power Query. But now, you don’t have to pick anymore. XLOOKUP can handle all those tasks in one simple function.
This step-by-step tutorial empowers you to leverage Power Query’s robust filtering capabilities. Learn to filter by date, text, numbers, and more, streamlining your workflow and unlocking deeper insights from your data. Watch now and elevate your Excel expertise!