Below is a short 5-minute video that accompanies our tutorial on how to highlight in Excel using conditional formating. The video transcript is below. You may search within the video by turning on the captions and hitting the arrow in the caption box.
This video will show you four ways to highlight cells in Microsoft Excel based on various conditions. I’m using Excel for Office 365, but these steps work in earlier versions. I’ve downloaded the sample file listed at the bottom of the tutorial. The sample file has tabs for the completed examples, but I’ll be dealing with the tab with the raw data.
The first example will be to highlight states whose population is 2% or greater. I’ll highlight the cells in column “F” in green. Start with the “state’s raw data” tab, click cell “F2”. Now click “control”, “shift” and the down arrow. This should highlight all the states down through Wyoming. From the “home” tab, click the “conditional formatting” button. From the dropdown menu, select “highlight cell rules”. Now from the side menu, select “greater than”. Enter “2%” in the “greater than” text box. Change the cell color to green, fill with dark green text. You should see the cells that match this condition turn green. If you like your selection, click “okay”. I’m going to hit “cancel” so I can do the next example.
In this second example, I’m going to use one of Microsoft Excel’s pre-made conditions. To find the top 10 states by the number of house seats in column “D”. Again, I’m using the “raw data” tab. Click cell “D2”. As before, I’ll click “control”, “shift” and my down arrow key to highlight the whole column. Click the “conditional formatting” button on the “home” menu. Select “top bottom rules”. Notice we have a set of predefined lists here. Even though the group was top bottom, you can see other items for average and even more rules. From the side menu select “top 10 items”. Excel has already highlighted the cells that meet our criteria. If I want, I can change the number to something other than 10 or change my color. To keep this video short I’ll click “cancel” and go on to the next example.
In this third example, I’ll switch from highlighting cells with color to using data bars. This style may appeal to users, but you really should test. This format works best when you have a large range in the cells. The format does have some differences, which I will point out. In this example, I’m going to use column “I”, which represents the change in state population. To begin click cell “I2”. Press “control”, “shift”, down arrow to highlight the column. Click “conditional formatting”. From the dropdown menu, select “data bars”.
Notice how various color options show for gradients and solid colors. If I hover my mouse over an option, you can see the data bar overlaid on the text. I find this distracting so I’m going to take another approach. If you notice, column “H” and column “I” are identical. As you may recall, by default data bars will show on top of the cell data. I prefer to have the numeric data separated from the data bar. This time, move to the bottom of the menu and select “more rules”. The new formatting rule dialogue opens. Check the box that reads “show bar only”. Edit your color choice if needed. Click the “negative value and access” button. Select a color for negative numbers or keep the default. I’m using red. Click “okay” twice. Notice how West Virginia stands out in red because it had a negative growth. In addition, each cell has a data bar.
Okay, let’s move to our last example. In this last conditional formatting example, I’ll show you how to highlight the row instead of the cell. This format works well if it’s a regular report and people know the condition. Otherwise, you may get questions about why some rows are colored. Again, I’m starting from the “raw data” tab.
Click cell “I2”. This time we want to select all rows. Click “control”, “shift”, down arrow, left arrow. All your data cells should be highlighted. From the home menu, click “conditional formatting”. Scroll down to “new rule”. In the “select a rule type” list, choose “use a formula” to determine which cells to format. Now we can enter a formula in the “format values where this formula is true” text box. Let’s use the formula from our first example. Type “=$F2>2%”. Click the “format” button. Click the “fill” tab. Select your background color. Click “okay” twice. You should now see the same states in example two highlighted accepted as the entire row, not the cell in column “F”.
Thanks for watching. Check out the full tutorial, which includes more conditional formatting tips for Microsoft Excel.