Table of Contents
- How to Highlight Cells in Excel
- How to Color Excel Cells Based on Value
- How to Color Excel Cells with Formula
- How to Highlight Cell Borders in Excel
- How to Highlight Selected Excel Cells
How to Highlight Cells in Excel
Highlighting Excel cells by emphasizing their values is an extremely simple operation: here’s how to do it from both computer and smartphone.
From PC
To color the background of a cell in Excel for Windows and macOS, simply select the cell with the mouse and click on the Fill Color button, located in the Font section of the toolbar (the icon representing a tilted bucket at the top); to change the color, click on the downward arrow next to the aforementioned button.
Similarly, you can highlight (and thus color) entire rows or columns: all you have to do is click on the letter of the column or the number of the row to highlight, and then click on the Fill Color button. If you wish, you can select multiple cells, rows, or columns at once, using the Ctrl/cmd keys on your keyboard.
If you prefer, you can highlight the desired elements using the color styles available in the program or creating custom ones: to apply an existing style, select the items to color, click on the Cell Styles button (found at the top right within the Styles box), and choose the color combination that you like best.
To create a new custom style, manually defining the background color and the formatting of the text contained in the cell, select the New Cell Style option located at the bottom of the color selection box.
Note: all the steps seen so far can also be applied in Excel Online, Microsoft’s free web spreadsheet editor.
From Smartphone and Tablet
Coloring a cell in the Excel app for smartphones and tablets (available for Android and iOS and iPadOS) is just as simple.
From your smartphone, tap on the cell you want to highlight, bring up the main Excel menu by tapping the (…) button located in the bottom right corner, and select Home from the dropdown menu that appears on the side. On a tablet, you will find the Home tab at the top area.
Then, scroll through the options in the lower panel until you find Fill Color, tap on it, and select the color to fill the cell. If instead, you prefer to use a pre-made style, select the Cell Styles option and use the displayed boxes on the screen to apply your preferred graphics. That’s it!
How to Color Excel Cells Based on Value
If you need to color Excel cells based on value, conditional formatting options can help: with these, you can automatically highlight the cells (as well as entire rows and columns) that meet specific parameters.
In the following chapters, I will explain in detail how to highlight cells based on certain conditions. The procedures are valid whether you are using the desktop software of Microsoft Excel for Windows and macOS or through Excel Online.
How to Highlight Empty Excel Cells
The conditional formatting can be applied to a spreadsheet to highlight all empty cells in a range, allowing you to quickly see if any cells in your table have no data entered.
The first thing you need to do is open the spreadsheet and highlight the cells or the range where you want the formatting rule to be applied. Once done, move to the Home tab at the top and click on the Conditional Formatting > New Rule.
In the screen that appears, select the option Format only cells that contain and, in the lower area, from the Format only cells with dropdown menu, choose Blanks.
Now, you need to set the formatting for these cells: click on the Format button at the bottom and go to the Fill tab; choose a color from the palette on the left and click the OK button twice, and you’re done.
How to Highlight Duplicate Excel Cells
If you want to know how to highlight duplicate cells in Excel, you should know that conditional formatting will help here as well.
First, after opening the Excel document, highlight the range of cells on which you want the duplicate values analysis to happen. Then, go to the Home tab at the top and select Conditional Formatting > Highlight Cell Rules > Duplicate Values.
At this point, in the panel that appears, make sure that the first dropdown menu shows Duplicates and then select a fill rule from the options available in the right dropdown menu. All that’s left is to click the OK button to confirm the new rule for highlighting cells with duplicate values.
How to Highlight Different Excel Cells
Another method to use conditional formatting is by checking if the cells meet certain conditions, such as having values greater or less than a certain number or containing specified data.
To perform this operation, after opening your Excel document, first select the range of cells to examine, then select the Home tab in Excel and click the Conditional Formatting button.
After this step, use the dropdown menu on the left side of the next window to define the condition that the cell’s value must meet to be automatically highlighted (e.g., greater/less than value, text equal to, etc.), and the menu immediately next to it to choose the color style to apply.
In the Conditional Formatting menu, under Top/Bottom Rules, you can also apply rules to highlight the top or bottom 10 values, the top or bottom 10%, or the values below or above average.
Alternatively, keep in mind that by selecting the Conditional Formatting > New Rule options, you can manage the previously mentioned rules with greater customization. You just need to select the rule you want to use from the appropriate panel and then customize from the area below.
How to Color Excel Cells with Formula
If the conditional formatting options are not sufficient to highlight the elements you wish, you can try to color Excel cells with a custom formula, defining a new formatting rule. This allows you to compare, for example, the values of two cells belonging to a spreadsheet and color the one of interest based on the comparison result.
If you have never used Excel’s formulas before, I strongly recommend reading the in-depth guide I dedicated to the topic to better understand how they are structured. Keep in mind that the formulas used within conditional formatting must return a boolean value, that is TRUE or FALSE.
To create a custom rule for conditional formatting in Excel for Windows and macOS, first select the data range to analyze, then click on the Conditional Formatting button located in the Styles panel and then on the New Rule option in the appearing menu.
Now, in the New Formatting Rule screen, choose the option Use a formula to determine which cells to format and enter your custom rule, that is the comparison formula, into the text field Format values where this formula is true.
For example, if you want to compare values in column B that are greater than or equal to 50 and at the same time less than or equal to 80, you would enter =IF(AND(B1>=50,B1<=80), TRUE, FALSE) in the formula entry field. I know, initially creating formulas may seem challenging, but I assure you that with a little practice, things will get better in no time.
In any case, once you have defined the rule, click on the Format button, then in the next window on the Fill tab, and define the background color to apply to the cell. Finally, click the OK button twice to apply the conditional formatting.
How to Highlight Cell Borders in Excel
In the previous chapters, I have explained how to highlight cells by modifying their formatting. I mainly spoke about changing the cell color, to verify at a glance if the cell actually meets your specified requirements. Among the formatting customization options, you can also request Excel to highlight the borders of the cells.
To achieve your intent, if you are working manually, as I explained in this chapter, what you need to do is first highlight the cell or the range and then press the ▼ button next to the Borders button in the Home tab. You just have to choose one of the preset border styles to apply it to the cell.
If you are using conditional formatting (including those with formula), after selecting the New Rule option and choosing the rule you want to apply to your dataset, press the Format button and then select the Border tab at the top. Then choose a style, the color, and how it should be applied to the cell, and click the OK button twice.
How to Highlight Selected Excel Cells
Let me teach you a trick that you can implement on Excel to always highlight the last cell you selected. This is a method that you can apply on any version of the desktop software of Excel, except for Excel Online and the mobile version. I warn you that there are actually several methods, but this is the easiest and safest to implement.
First, launch Excel, open the document you want to work on, and press the triangle icon found at the top left, between row 1 and column A. At this point, go to the Home tab at the top and select Conditional Formatting > New Rule and choose the option Use a formula to determine which cells to format.
Now, in the Format values where this formula is true field, type the following formula.
=OR(CELL("row")=ROW(); CELL("col")=COLUMN())
Then press the Format button and, in the Fill tab, choose a color for the cells. Click the OK button three times in succession.
Now, if you click on a cell to select it, Excel will intercept this highlighting the entire row and column. By clicking on a new cell, press the F9 key simultaneously to refresh the information on-screen, to display the new targeting.