Conditional formatting allows you to focus on information based on specific conditions (or criteria). For this reason, it is extremely useful in spreadsheets. Rather than wading through thousands of data points just to find what you need, you can highlight rows or color-code specific cells using an Excel conditional formatting formula. Conditional formatting can be used for a wide range of purposes in Excel, from highlighting overdue dates to changing border styles to make your tables easier to read.
So, how do you use conditional formatting in Excel? What are the most common and useful formulas to know? Finally, how can you get professional help with more complex Excel functions? In today’s guide, we will answer all of these questions and more, but first, let’s take a look at what conditional formatting means when using Excel.
What Is Conditional Formatting In Excel?
As previously stated, conditional formatting is simply a way to focus on specific information through aesthetic changes to an Excel spreadsheet. These changes are controlled and initiated using algorithms and formulas. There are 3 built-in rules when it comes to conditional formatting in Excel: data bars, color scales, and highlights. Additionally, you can set custom rules to better control the look and layout of your formatting.
Add Data Bars
Data bars are best if you want to quickly view high or low data points that stand as outliers within your data set. For example, if you have a spreadsheet to track your household expenditures, you could make data bars to show your most and least expensive costs at a glance. The longer bars indicate higher values while the shorter bars indicate lower values.
Here is how you can insert data bars in your Excel spreadsheet:
- Select the part of your data set that you want to use with the conditional formatting (you can choose all or part of your spreadsheet)
- Under the Home tab, select “Conditional Formatting”
- In the dropdown menu, select “Drop Bars” and choose between Gradient or Solid Fill
Add Color Scales
Color scales offer a bit more nuance than data bars and make it easier to see gradual changes across your data set. For example, you might use color scales to see how well different investments have performed over time. With a color scale, you can choose multiple colors that will gradually change to show the lowest or highest value in a row and/or column. This way, you can see how data is distributed using color gradation, even finding data points that fall exactly in the middle.
Here is how you can add color scales to your Excel spreadsheet:
- Select the part of your data set that you want to use with the conditional formatting (you can choose all or part of your spreadsheet)
- Under the Home tab, select “Conditional Formatting”
- In the dropdown menu, select “Color Scales”
- Choose the colors you want to use; the top color is associated with higher values while the bottom color is associated with lower values (the optional middle color is associated with the values closest to the midpoint)
Highlight Cells
Sometimes, the simplest methods of formatting provide the best results. If you just want to make important data points stand out, you can highlight cells according to specific parameters. You can also use highlight cells to find duplicates or similar values in your data set.
Here is how you can highlight cells in your Excel spreadsheet:
- Select the part of your data set that you want to use with the conditional formatting (you can choose all or part of your spreadsheet)
- Under the Home tab, select “Conditional Formatting”
- In the dropdown menu, select “Highlight Cells Rules”
- From here, you’ll be presented with a dropdown menu with several options, including Greater Than, Less Than, Between, Equal To, Text That Contains, A Date Occurring, and Duplicate Values
- Depending on the option you choose, you’ll need to input one or more values to select the parameters you want to highlight
If you want to highlight the highest or lowest values in your spreadsheet’s data set, you can also take an alternate path:
- Select the part of your data set that you want to use with the conditional formatting (you can choose all or part of your spreadsheet)
- Under the Home tab, select “Conditional Formatting”
- In the dropdown menu, select “Top/Bottom Rules”
- From here, you’ll be presented with a dropdown menu with several options, including Top 10 Items, Top 10%, Bottom 10 Items, Bottom 10%, Above Average, and Below Average
- Input values based on the parameters you choose
Excel Conditional Formatting Based On Another Cell
The built-in rules outlined above are specifically used to format cells based on their internal values. But what if you want to use conditional formatting based on another cell? What about conditional formatting based on another column or row? To format based on the values in a different cell, column, or row, you must create formatting using one of these spreadsheet segments as a reference point.
Here is how you can format based on another cell, column, or row in your Excel spreadsheet:
- Select the part of your data set that you want to use with the conditional formatting (you can choose all or part of your spreadsheet)
- Under the Home tab, select “Conditional Formatting”
- In the dropdown menu, select “New Rule”
- This brings up the “New Formatting Rule” window, where you can select your rule type; to format based on another cell, select Use a formula to determine which cells to format
- Enter a formula that reads true when one cell is linked to another; for example, if your cell A3 had a value of 100 and you want to use the value that is in your A3 cell as a reference point and link it to all other rows in that column which also contain a value of 100, you would input this formula: =$A3=”100″
- Click on the “Format” button
- Customize your Font, Border, and Fill options
- Make sure that the Preview section looks correct and then click “Ok” to finalize your new rule
Conditional Formatting To Highlight Every Other Row In Excel
Highlighting every other row is a great way to make your spreadsheet easier to read, particularly if you have dozens of rows with important data points. You may also want to highlight alternating rows based on the kind of values displayed in each row.
Here is how you can highlight every other row in your Excel spreadsheet:
- Select the part of your data set that you want to use with the conditional formatting (you can choose all or part of your spreadsheet)
- Under the Home tab, select “Conditional Formatting”
- In the dropdown menu, select “New Rule”
- This brings up the “New Formatting Rule” window, where you can select your rule type; to format based on another cell, select Use a formula to determine which cells to format
- Enter the following formula in the box: =MOD(ROW(),2)=1
- Click on the “Format” button
- Customize your Font, Border, and Fill options
- Make sure that the Preview section looks correct and then click “Ok” to finalize your new rule
Conditional Formatting In A Pivot Table
A pivot table allows you to obtain summaries or categorizations of a spreadsheet’s data set. Pivot tables can have data which changes based on the setup of the pivot table. For this reason, you cannot just use the aforementioned formatting from a fixed cell or data range, as the formatting won’t be maintained as the underlying data in the pivot table is updated thereby changing the fixed cell’s position.
Here is how you can use conditional formatting in an Excel pivot table:
- Setup the conditional formatting using one of the methods outlined above
- Under the Home tab, select “Conditional Formatting”
- In the dropdown menu, select “Manage Rules”
- Choose the rule you want to change and click “Edit Rule”
- Select one of the pre-defined options at the top. This will remove the fixed cell aspect and its resulting issues if the fixed cells content were to change its position.
Note though, if you change the pivot table structure and it therefore removes one of the fields which the rule was applied to, the rule will get deleted at the same time and you would need to create the rule again to use it.
Need help from dependable software experts? Reach out to Geeker today for on-demand IT and software solutions!