Conditional formatting is an awesome tool that can help make your spreadsheets more readable. In this article, I will tell you how to use conditional formatting in your spreadsheets.
Conditional Formatting is a quick way to make spreadsheets look pretty by applying a wide variety of formatting rules based on conditions. We used it here on alternativeway.com to make a simple chart that displays the average number of calories people burn each day in a single workout. If a user inputs a certain formula and a certain cell, the chart displays a certain result.
Conditional formatting is one of those things that are easy to implement, but hard to explain. That’s why we’ve decided to provide a free video guide that shows you exactly how to use conditional formatting in Google Sheets for the absolute beginner.. Read more about conditional formatting google sheets based on another cell and let us know what you think.Google Sheets is often used for counting numbers and is an acceptable alternative to Microsoft Excel. Of all the features it offers, perhaps none is as important as those related to conditional formatting. If users know how to use conditional formatting in Google Sheets, they can apply specific formatting to cells that meet certain criteria.
Conditional formatting lets you color individual cells in Google Sheets to highlight data that should stand out in a block. This method is also a good alternative to pivot tables, which are used to summarize large data tables. Highlighting data also makes it easier for the user to navigate through a complex data table. Before conditional formatting, you can also use the VLOOKUP formula in Google Sheets to complete the data analysis.
Conditional formatting can also be used to highlight duplicates. But you may know how to remove duplicates from Google Sheets if you don’t need them.
How to do conditional formatting in Google Sheets
Conditional formatting can seem very simple to a beginner. But the more you learn to use it in large data tables, the more complex it becomes. Let’s look at some ways to use conditional formatting in Google Sheets.
Three steps to conditional formatting in Google Sheets
1. Open Google Spreadsheets and select a range of cells.
2. Choose a style for the layout.
3. Use the formatting rules to apply the formatting.
Now that you know the basics of using conditional formatting in Google Sheets, let’s look at the process with detailed images.
1. Select field A
Let’s start with the basics: Let’s see how to highlight a range in Google Sheets using conditional formatting.
Open conditional formatting from the Format menu
Click the Formatting tab on the menu bar and select Conditional Formatting from the pop-up menu.
Another way to open it is to select a range of cells, right-click on them, and choose Conditional Formatting from the pop-up menu.Open Conditional Formatting
Data range selection
When the Conditional Formatting toolbar is open, enter a range of cells or a single cell in the Apply Range field in the right side panel.
If you z. For example, if you want to enter a range of cells that includes all cells from A2 to A11, you can do so in the format
A2:A11Cell range input
Adding multiple cell ranges
You can add multiple ranges by clicking the table icon to the right of the Apply Range field.Click on the table icon
This opens a dialog box where you can click the Add another range button and enter another range. Press OK to confirm the selection.Select Add another area
2. Select style
After you select a range of cells, choose the colors you want to use to label the cells. You can do this by choosing any color under Layout Style.Choose a layout style
You can also create your own style with options like bold, italic, underline and strike through.
Use of formatting rules
1. Reason for use FI
The IF reason is a basic feature that allows users to define conditions for conditional formatting in Google Sheets. The first and simplest method of formatting text using the IF reason is the empty/non-empty method.
Select cells
First, select the cell or range of cells you want to format.Selecting Cells
Select empty or not empty
After selecting a range of cells, choose whether to format the cells when they are empty or not. We decide to make up the cells and fill them green if they appear empty.Choose a condition
Show result
You will see that the empty cells in the selected range are filled with the color of the selected cell.Displaying the Results
To remove the conditional formatting, first select the cell in question. Then open the formatting toolbar on the right and click on the trash can icon to delete the formatting settings.
You can also use the keyboard shortcut Ctrl + for Windows PC or Command + for Mac to remove all formatting applied to a cell in Google Sheets.
2. Conditional formatting with text parameters
In Google Sheets, you can also apply conditional formatting based on the text entered in the cells. Let’s look at the easiest way to do it. Using this sales table, we will learn below how to perform conditional formatting in Google Sheets in different ways.Sales table for conditional formatting
Select cell range
With the Conditional Formatting sidebar open, select the range of cells you want to work with.Select a cell
Select Text Contains and enter the text
There are several ways to format text in Google Sheets. In this example, we have selected the Contains text option in the Cell Format When field. If you choose this option, you can select all cells that contain text for which you have specified formatting.The selection text contains the option
Let’s say you want to highlight the salespeople in a particular business unit. You can enter the device name in the Cell Format field as.
In this case, we have chosen to highlight the executives of the Denver unit. After you have selected your settings, click Done.Enter the text to be selected in the table
Search results
You can see that the cells with the defined term are highlighted in the Google Sheets spreadsheet.Viewing results in a worksheet
More options for text formatting: The text does not contain, the text begins with, the text ends with and the text exactly.
3. Format integer string
We’ve seen how to do conditional text formatting in Google Sheets. However, the specified method only selects cells that contain selected text.
What if you want to highlight the entire relevant line where this text appears? Here you need to use a custom formula to get the desired formatting. Let’s test this with the following example; the goal is to highlight all lines that contain the word Denver.
Select cell range
With the main text cells selected, click the Add another line button in the sidebar.Click Add New Ruler
Then select the entire table by entering the cell references in the Apply to range field. The cell references here are A1:F11.Enter the range to select the entire table
Select custom formula option
Under Cell Formatting in the sidebar, select Custom Formula.Selecting the custom formula is
Entering formula
Now enter the following formula to select all lines that contain text.
=$B1=DenverEnter the formula
Here is what the table should ideally look like after entering the correct formula.Displaying the result
Let’s see how the formula works. The = part of an equation that indicates the beginning of a formula. B2 – approximate data for the column. The $ sign in front of B2 tells Google Sheets to only look at column B. If you put another $ sign after B2, Google Sheets would only mark the term if it occurs in a cell in column B and in the second row.
However, at this time we only need to lock the entire column B for the sample data, so we do not need to insert $ after B2. Next, you need to insert the term you want to highlight with a double apostrophe. This tells Sheets exactly what text to look for.
4. Highlight the terms which exclude this text
Suppose you want to select text in all rows and columns except those that contain the text Denver. You have to change the formula a little bit. First, remove the conditional formatting rule you just applied to the table. Then add a new line and enter the specified formula.
=$B1<>Denver
This is what the result would look like after applying the above formula.Exclude selected text
The ‘<>’ symbol is inserted to tell Google Sheets to exclude the text specified in the formula from the conditional formatting rule.
4. How to do conditional formatting with numbers
In addition to text, you can also do conditional formatting of numbers in Google Sheets. In this example, we will try to highlight all leaders who have achieved 90% or more of their goals.
Select a column for conditional formatting in Google Sheets
First, select the range of cells you want to edit. Here we select column E, which contains the percentage of goal achievement.Select column
Selecting formatting options
Then in the Format Cells dialog box, choose Greater than or Equal to from the pop-up menu.Click greater than or equal to
Enter threshold number
Select the threshold, in this case 90%, and click Done to apply the formatting.Enter the threshold number
This is what the table should look like after all the steps are completed.Displaying the result
Perform conditional line formatting
To apply formatting to all rows that contain a cell value greater than or equal to 90%, use the following custom formula.
The method of applying the custom function is the same as described in the previous sections.
=$E1>=90%Enter the cell values and press Done.
View the result after the formula is applied.Display the result
Perform conditional formatting with a color scale
If you now want to mark the parts of the cells that contain numerical values with different colors, you can use the background color bar for that.
With the first area highlighted (all percentages here are 90 or greater), choose Color Scale from the Conditional Formatting menu.Click the color bar
You can now select Min Point, Mid Point and Max Point to separate the numbers in a given number range and display them in different colors.
Choose a unit to represent the three points; it can be a minimum value, a percentile, a number or a percentage. Here we have selected the percentage.Selecting the unit of measurement
Enter numeric ranges in the boxes next to the three dots, and then choose a color to represent each range. Select conditional formatting and select colors
Press the Done key to display the final result.Displaying the Result
5. Conditional formatting with dates
Sometimes in Google Sheets you need to do conditional formatting based on the data you enter into the spreadsheet. Before you begin this process, you must define a specific format for the data display.
To do so, select the Format tab, select Number, and then select Other Date and Time Formats.Select the options
Then enter a date format that you can easily read and click Apply.To enter a custom date format
If you have chosen the date format, make sure that the dates in the columns match the chosen format. Let’s move on to conditional formatting.
Select column
First, select the column containing the data and open the conditional formatting sidebar.Open Conditional Formatting
Choose date option
Now in the Cell Formatting tab, select an option related to sorting the date when. You have three options for formatting sheets by date: The date is, the date is before and the date is after.
Here all managers who have achieved the corresponding sales after the current date are highlighted. The current date is the 21st. March.Preselection
Search results
After you complete these steps, you can verify the results.Verifying Results
6. How to do conditional formatting based on another cell in Google Sheets
Google Spreadsheets also allows users to format cells according to their different values. Let’s see how this can be done using the example above.
The intention here is to highlight the names of executives with more than 100 sales.
Select cells
First select the column you want to work with. Here we need to highlight the frame names, so we select column A.Select column
Select and enter a user-defined formula
Open the Conditional Formatting sidebar and choose Custom Formula from the Format Cells When drop-down list.
Now enter the following formula. Cell C2 represents the C(sales) column of the sheet.
=C2>100Entering a formula
Search results
After you have entered the formula, click Done to retrieve the results.Show Results
You will see that the selected color has highlighted frame names.
7. Conditional formatting in Google Sheets based on values in another sheet
Users can also cross-reference between multiple sheets in the same file. However, this method contains a complex formula that must be entered correctly for this operation to succeed.
Identify the two sheets you want to work on
Make sure you have two Google Spreadsheets before performing this method. Here we want to assign data similar to column B of the Data List worksheet to the Original Data worksheet.
This is what the original datasheet looks like:Original datasheet
And this is what the second Google sheet looks like, which is the Data List worksheet -Data List.
Select the cells in the first data sheet and enter the formula
Our task is to select the terms in column A of the original data sheet that correspond to column B of the data sheet.
To begin, open the Conditional Formatting panel in the source data sheet and select the appropriate column to work with. Here we have chosen column A.
Then, in the Conditional formatting rules box, select the Custom formula is option.
Then enter the specified formula.
=match(A1,indirect(datalist!B2:B),0)
Here A1 indicates the selection of the first row in the first worksheet, and ‘DataList!B2:B’ indicates the range of cells used for reference in the second worksheet.
Once you have selected all the required parameters, click Finish.Enter a conditional formatting rule
Search results
You can see that the data matching between the two sheets is highlighted in the original data sheet.Check the result
You can easily see that the four names Tom, Mary, Wayne, and Charlotte were highlighted in the original datasheet after being referenced from the Data List worksheet.
Supplement
Conditional formatting is used only when users need to clarify and highlight data in a large data sheet. This allows you to easily navigate through a Google spreadsheet and quickly analyze the specific parameters you are looking for. It’s also a great way to track goals by giving you a visual representation of your progress against certain parameters.
When performing conditional formatting in Google Sheets, make sure you select the correct range of cells to highlight. If you do it wrong, you may inadvertently refer to the wrong data. You can select one of the Google Sheets conditional formatting rules above to format the data as desired.The Conditional Formatting feature in Google Sheets is a great way to format data based on different criteria. If you’re a Google Sheets user and like to use conditional formatting, then this article is for you. In this article, I’ll show you how to use conditional formatting in Google Sheets.. Read more about google sheets conditional formatting custom formula multiple conditions and let us know what you think.
Frequently Asked Questions
How do you apply conditional formatting in Google Sheets?
To apply conditional formatting in Google Sheets, you can use the following formulas: =IF(A1=”Yes”, “Red”, “Green”) =IF(A1=”No”, “Blue”, “Yellow”) =IF(A1=”Yes”, “Red”, “Green”) =IF(A1=”No”, “Blue”, “Yellow”) =IF(A1=”Yes”, “Red”, “Green”) =IF(A1=”No”, “Blue”, “Yellow”) =IF(A1=”Yes”, “Red”, “Green”) =IF(A1=”No”, “Blue”, “Yellow”) =IF(A1=”Yes”, “Red”, “Green”) =IF(A1=”No”, “Blue”, “Yellow”)
How do I apply conditional formatting to entire column in Google Sheets?
To apply conditional formatting to an entire column in Google Sheets, select the cells you want to format and then click on Conditional Formatting > New Rule.
Why is my conditional formatting not working in Google Sheets?
Conditional formatting is not supported in Google Sheets.
Related Tags:
google sheets conditional formatting custom formula multiple conditionsgoogle sheets conditional formatting row based on cellconditional formatting google sheets appgoogle sheets conditional formatting based on cell colorconditional formatting google sheets based on another cellconditional formatting in google sheets for duplicates,People also search for,Feedback,Privacy settings,How Search works,conditional formatting google sheets based on another cell,conditional formatting in google sheets for duplicates,google sheets conditional formatting if cell contains text,conditional formatting google sheets formula,google sheets conditional formatting custom formula multiple conditions,google sheets conditional formatting row based on cell,conditional formatting google sheets app,google sheets conditional formatting based on cell color
