Open In App

How to use Conditional Formatting in Excel?

Last Updated: 16 Jun, 2021

D

Summarize
Comments
Improve
Suggest changes
Like Article
Like
Share
Report
News Follow

Microsoft Excel is a software that allows users to store or analyze the data in a proper systematic manner. It uses spreadsheets to organize numbers and data with formulas and functions. MS Excel has a collection of columns and rows that form a table. Generally, alphabetical letters are assigned to columns, and numbers are usually assigned to rows. The point where a column and a row meet is called a cell.

Conditional formatting is a feature in Excel that allows you to format/highlight few particular cells that meet the condition specified or selected by you. You can find it in the home tab under the Styles group.

Steps to use Conditional Formatting:

Step 1: Insert the data/values in the spreadsheet.

Step 2: Select the range of cells.

Step 3: Click on the Conditional formatting in the Home tab.

Step 4: Select any predefined condition or create your own condition(for this select New Rule).

For example, we need to:

  1. Highlight marks which are more than 70.
  2. Highlight marks which are between 50 and 80.
  3. Highlight marks that are below average.
  4. Set icons on the marks.
  5. Show data bars on the marks.

Steps to highlight marks that are more than 70

Step 1: Insert the data in the spreadsheet, we enter the marks in the spreadsheet.

Step 2: Select the range of cells(A2:A20).

Step 3: Select the Conditional formatting in the Home tab, click Highlight Cells Rules.

Step 4: Click on the Greater Than option.

Step 5: Enter the value for which you need greater value, under the “Format cells that are GREATER THAN, i.e. 70.

Step 6: You can select your desired color, we will go with the default color.

Step 7: Click OK.

Steps to highlight marks that are between 50 and 80

Step 1: Insert the data in the spreadsheet, we enter the marks in the spreadsheet.

Step 2: Select the range of cells(A2:A20).

Step 3: Select the Conditional formatting in the Home tab.

Step 4: Click Highlight Cells Rules.

Step 5: Click on Between option.

Step 6: Enter the values between which the values have to be highlighted.

Step 7: You can select your desired color, we will go with the default color.

Step 8: Click OK.

Steps to highlight marks that are below average

Step 1: Insert the data in the spreadsheet, we enter the marks in the spreadsheet.

Step 2: Select the range of cells(A2:A20).

Step 3: Select the Conditional formatting in the Home tab, click on Top/Bottom Rules.

Step 4: Click on Below average.

Step 5: Select your desired color, we will go with the default color.

Step 6: Click OK.

Steps to set icons on the marks

Step 1: Insert the data in the spreadsheet, we enter the marks in the spreadsheet.

Step 2: Select the range of cells(A2:A20).

Step 3: Select the Conditional formatting in the Home tab, click on Icon Sets.

Step 4: Select your desired icon(green shows the above average values, yellow shows the average value, red shows the below-average values).

Step 5: You can also define your own rules.

  • For example, we want a green symbol before the marks greater than 70, red before the marks less than 50, yellow before the marks between 50 and 70.
  • Click on More Rules(here you can change Icon styles, Reverse icon order, Show icon only(i.e. the data(s) will not be displayed), etc).

Step 6: Click OK.

Steps to show data bars on the marks

Step 1: Insert the data in the spreadsheet, we enter the marks in the spreadsheet.

Step 2: Select the range of cells(A2:A20).

Step 3: Select the Conditional formatting in the Home tab, click on the Data bars.

Step 4: Select your desired color for the bars.

Step 5: You can also define your own rules.

  • For example, we want the lowest bar on marks less than 30 and the longest bar on marks greater than 80.
  • Click on More Rules(here you can change Icon styles, Reverse icon order, Show icon only(i.e. the data(s) will not be displayed), etc).

Step 6: Click OK.



D

News
Improve
Discuss
Do you want to advertise with us?Click here to know more

How to Apply Conditional Formatting in a Pivot Table in Excel

article_img
One of the most useful ways to customize the pivot table formatting is using Conditional Formats. Conditional formatting rules can be applied to Pivot tables just like they can be applied to normal data ranges. So by using conditional formatting, we can highlight the cells with a certain color depending on the cell's value. Conditional FormattingWhat is Pivot Table Conditional Formatting In Excel, you have the power to make your data stand out using conditional formatting. This means you can automatically highlight cells that meet certain criteria. For example, you can easily spotlight cells that have values above the average or those that fall below a specific threshold. Now, when you apply this special formatting to a Pivot table, there is an important step to remember. After you've set up your conditional formatting, make sure to tweak the settings so that even after you refresh the Pivot table, the right cells maintain their distinctive appearance. This ensures that your highlighted information remains accurate and up-to-date as your data evolves. Types of Conditional Formatting in ExcelThere are many conditional formats that we can use on our data. Some of them are given below
Read More

I

How to Apply Conditional Formatting Based On VLookup in Excel?

VLOOKUP is an Excel function to lookup data in a table organized vertically. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. Conditional Formatting Based on Vlookup: 1. Using the Vlookup formula to compare values in 2 different tables and highlighting those values which is only present in table 1 using conditional formatting. We have a Table containing old products of any grocery shop in 'Old Product' sheet and an updated table having new products in worksheet 'New'. We want to highlight rows in New table containing those items which are not in Old Product Table. Old Product Select the data from New table except the Headers. (The table in which we want to highlight rows.)Go to Home->Conditional Formatting->New Rule. In the dialog box appeared, Select the rule type - "Use a formula to determine which cells to format;";Under Edit the rule description enters the following formula: =ISNA(VLOOKUP($A2,'Old Product'!$A$1:$B$8,1,FALSE))Then click Format. Formula explanation: Inside VLOOKUP, 1st parameter is $A2 which is first name in New table.2nd parameter is Old Product Table.3rd parameter is column we want to compare which is 1 as we wan
Read More

Conditional Formatting in Excel: Basic to Advanced Guide

article_img
How to Do Conditional Formatting in Excel: Quick StepsSelect the Cells to FormatGo to the Home Tab >> Click on Conditional FormattingChoose a Formatting RuleSet Your Condition and Formatting StyleClick OKEver stared at a spreadsheet full of numbers and wished it could show you what’s important instead of making you guess? Conditional formatting in Excel does exactly that—it turns raw data into visual insights with just a few clicks. Whether you’re tracking sales trends, monitoring project deadlines, or analyzing survey results, this feature helps you spot patterns, highlight outliers, and make data-driven decisions faster.This guide covers everything from basic conditional formatting to advanced conditional formatting techniques, including how to highlight cells based on values, use color scales to visualize data, and flag duplicate cells automatically. You’ll also learn to create custom rules with formulas and clean up your sheets by removing outdated formatting. How to Use Conditional Formatting in ExcelTable of ContentWhat is a Conditional Formatting in ExcelHow to Use Conditional Formatting to Highlight cells Based on Another Cell in Excel1. Highlight Cells Based on Value
Read More

How to Use Conditional Formatting in Google Sheets

article_img
Conditional formatting in Google Sheets automatically formats cells based on specific rules, highlighting essential data points, trends, or outliers for more accessible data analysis and interpretation. This article discusses conditional formatting in Google Sheets, from the basics to advanced techniques, providing a comprehensive understanding of how to use this tool to its greatest advantage. Conditional Formatting in Google Sheets Table of Content What is Conditional Formatting in Google SheetsHow to use Conditional Formatting in Google SheetsHow to apply conditional formatting with is empty/is not empty.How to apply conditional formatting based on textHow to apply conditional formatting based on number valuesHow to apply conditional formatting to an entire rowHow to apply conditional formatting based on datesHow to apply multiple conditional formatting rulesHow to use conditional formatting in Google Sheets to highlight errorsHow to use conditional formatting in Google Sheets based on time-related conditions What is Conditional Formatting in Google SheetsConditional formatting in Google Sheets is dynamic formatting of cells based on conditions, such as highlighting essential da
Read More

How to Use Conditional Formatting with Custom Formulas in Google Sheets

article_img
Conditional formatting with custom formulas in Google Sheets allows you to apply dynamic formatting based on specific criteria that you define. This powerful feature enables you to highlight cells, rows, or ranges that meet particular conditions, making your data visually appealing and easier to analyze. In this guide, we'll walk you through the process of using custom formulas for conditional formatting in Google Sheets, helping you streamline your workflow and enhance data visualization.Use a Custom Formula to Apply Conditional FormattingConditional formatting with custom formulas allows you to apply formatting rules based on specific conditions you define. Here's how to use it:Step 1: Select the Range for Conditional FormattingSelect the Priority column (B2:B6) to highlight tasks with a "High" priority.Select the RangeStep 2: Open the Conditional Formatting MenuGo to the Format menu at the top of Google Sheets.Select Conditional formatting from the dropdown menu. This will open the Conditional format rules panel on the right side.Open the Conditional Formatting MenuStep 3: Choose "Custom Formula Is"In the Conditional format rules panel, click the dropdown under Format cells if.S
Read More

Data Formatting in Excel

article_img
In Excel, the whole entered data in the sheet uses the same formatting by default which can make the data look monotonous, dull, and difficult to read. Excel provides a pool of tools called formatting tools which customize the data in such a way that it only affects the appearance of the data and not the content. How to apply formatting in Excel? Following are the steps to apply the formatting: Select the range of cells on which formatting needs to be done.Select the suitable formatting tool from the toolbox(ribbon).Formatting can be observed on the sheet. Formatting tools that work on the text and cell appearance can be found in Home Tab [ Font Group ] in the ribbon. Let's discuss some formatting tools in this article. Font: It changes the writing style of the data. Font Size: It changes the size of the data, can make it appear big or short. Increase/Decrease Font Size: It does the same work as by Font Size i.e adjusts the size of the font accordingly. However, it gets handy as in only one click, the size is getting changed. Bold and Italic: Bold thickens the letter and makes it appear darker than before while Italic, italicize the letters. Both are useful when some data needs t
Read More

How to Remove Table Formatting in Excel

article_img
Struggling with unwanted table formatting in Excel? Whether you're working on a crucial report or just cleaning up your spreadsheet, knowing how to remove table formatting in Excel can save you time and frustration. Excel tables come with predefined styles that can sometimes get in the way of your preferred presentation or data analysis methods.In this article, we'll show you the quickest and easiest ways to remove table formatting in Excel while keeping your data intact. Say goodbye to distracting table styles in Excel and hello to clean, organized spreadsheets. Let's dive into the best methods for transforming your tables into plain data, giving you full control over your worksheet's appearance and functionality.How to Remove Table Formatting in ExcelTable of ContentHow to Remove Table Formatting in ExcelHow to Clear All Tables in ExcelHow to Remove Excel Table Formatting (while keeping the Table)How to Change the Formatting of the Excel TableHow to Remove Format as Table in Excel (Convert Table to Range)How to Remove Table Formatting in ExcelStep 1: Launch the Excel spreadsheetTo remove your Excel table formatting you have to open your Excel spreadsheet from which you want to re
Read More

Excel Table Styles and Formatting: How to Apply, Change and Remove

article_img
In Excel, you can format tables using various styles, colors, fonts, and other visual elements to make your data more organized and visually appealing. However, there may be situations where you want to remove all the formatting from a table and revert it to its default appearance. Have you ever felt stuck in the maze of Excel tables? Let’s break free together! Discover the straightforward way to remove table formatting in Excel. No more complications—just a quick and easy guide to make your data dance without the formatting fuss. Ready to embark on this hassle-free journey? Let’s get started! Excel Table StylesIn Microsoft Excel, table styles are predefined formatting sets that allow you to quickly and easily apply a consistent and professional look to your data tables. These styles include various formatting elements such as bold headers, alternating row colors, and distinct column formatting. Here's a short note on Excel table styles: How to Choose a Table Style When Creating a TableStep 1: Launch Your Excel SpreadsheetTo Apply Excel table formatting you have to open your Excel spreadsheet from which you want to remove table formatting. Step 2: Select the Data Select the data fo
Read More

Excel: Formatting Cells

article_img
Excel Formatting ShortcutsIf you have closely followed the previous parts of this tutorial, you already know most of the Excel formatting shortcuts. The table below provides a summary. Shortcut FormatCtrl+Shift+~ General format Ctrl+Shift+! Number format with a thousand separators and two decimal places. Ctrl+Shift+$ Currency format with two decimal places and negative numbers displayed in parentheses Ctrl+Shift+% Percentage format with no decimal places Ctrl+Shift+^ Scientific notation format with two decimal places Ctrl+Shift+# Date format (dd-mmm-yy) Ctrl+Shift+@ Time format (hh: mm AM/PM) Excel opens with all the cells looking the same. Things can get hard to understand if you have a lot of them. Changing Excel's look is as simple as changing the font and size parameters to modify the appearance of your written work. The key points become more apparent, and the material becomes more accessible. Excel also lets you choose whether the numbers are money ($) or percentages (%). Then, it will know just what to display to them. After these easy changes, your Excel file will look better and be easier for everyone to understand. Format Cells in ExcelTable of Content How to Change the F
Read More

Formatting Charts in Excel

article_img
One of the major uses of Excel is to create different types of charts for a given data set. Excel provides us with a lot of modification options to perform on these charts to make them more insightful. In this article, we are going to see the most common "Formatting" performed on charts using a suitable example shown below. Example: Consider the performance of a batsman in a T20 match. The data set contains the runs scored by the batsman in various matches. Now we will insert a bar chart using the above table. To insert a bar chart : Select dataset -> Click on Insert -> Chart Sets Pop Down -> 2-D column The key steps to format anything on the chart is : Select the part of the chart which needs to be formatted.Right-click on it and select "Format". The format window will open.1. Formatting the Chart Title :Select the Title -> Right Click on it -> Format Chart Title In the Format Chart Title window, you can change the Font color, add Borders surrounding the title and various other modifications can be done. You can also rename the title by simply selecting and retyping a new name. 2. Formatting Axis Titles: You can add the axis title by using the "+" button in the to
Read More
three90RightbarBannerImg