Without much delay, let’s continue from where we left off in our discussion about Excel and Google Sheets (Google Spreadsheets), particularly focusing on tips and insights that could make our work easier or more efficient.
In this article, the topic we’ll be focusing on is clearly indicated in the title: conditional formatting. Let’s first take a moment to understand what we mean by conditional formatting.
Conditional Formatting
We already use conditional logic in many mathematical and logical operations. We test whether conditions are true or false using formulas constructed with and, or, not, and if, and then perform logical comparisons between expressions. Now, when we want to apply different visual styles (such as background color, text color, etc.) based on these conditions, how can we leverage them? This is exactly what conditional formatting provides. In other words, we can dynamically change the appearance (formatting) of one or more cells based on specific conditions1.
Conditional formatting enables us to quickly highlight specific data within an Excel and/or Google Sheets spreadsheet. Let’s start with a simple example in Excel. Suppose we have a record of an annual recurring event, such as a birthday or the start of a new job. We’ll compare dates in the table against this recorded date and apply a formatting style when a match is found.
First, let’s select the cells we’ll use for the condition. Then, we can follow the steps: Home > Conditional Formatting > Highlight Cells Rules, where we’ll see various conditions. You can proceed with a selection that suits your needs. Let’s assume we’ve selected the Equal to… option. We can enter a value in the equality field, or select a cell to apply the condition when the cell’s value matches the specified value.
For any condition format other than the default 2-Color Scale, we can evaluate the options available in the Style section. Let’s proceed with the Data Bar option. Because this option is not available in Google Sheets, we’ll resolve it through an alternative method. When you set the value to the cell’s own value and select Automatic under Data Bar > Type, the relevant cells will be formatted with a bar length proportional to their content value.
At any time, you can adjust and reconfigure the relevant settings via the Manage Rules option following the path: Home > Conditional Formatting > Highlight Cells Rules. Now, let’s perform the same operation in Google Sheets. First, it’s worth noting that.
Google Sheets simplifies the formatting process and the use of multiple formatting options. However, on the other hand, it does not directly provide some features available in Excel. The Data Bar can be considered one of these features2. However, this is not a problem. We can also solve the issue using a formula and the char3 function. The formula we’ll use is as follows:
=if(A6<101;REPT(char(406);(A6))&char(10)&"%"&A6;"?")If the value in cell A6 is less than 101, the character (406) will be repeated a number of times equal to the cell value, followed by a newline character and the value displayed as a percentage (e.g., “%X”). The REPT function repeats a text string a specified number of times4.
Excel’s built-in functions (and Quick Solution5) are very useful for basic conditions. Therefore, we will continue with customized formulas that can be used in both Excel and Google Sheets. As mentioned above in the example, I’ve described the application processes for both tools. Thus, we can now focus on the formulas themselves rather than the application steps.
Advanced Readings
- Add, modify, or remove conditional formats
- Apply conditional formatting using Quick Analysis
- How to change font color based on cell value in Excel?
- Highlight EVERY other ROW in Excel
- How to use conditional formatting based on another cell in Google Sheets?
- How to apply conditional formatting across an entire row in Google Sheets
- Conditional formatting with formulas (10 examples)