The conditional formatting feature built into Excel enables you to change the style of a cell (or group of cells) based on the content within it. There are many different ways in which you can use the tool, from marking the highest or lowest values in a column to highlighting all the cells that match a particular text string. Conditional formatting also works in Excel tables and PivotTables built into your spreadsheet.
- Skill level:
- Moderately Easy
Other People Are Reading
Things you need
- Microsoft Excel
Launch Excel and open up the file you want to work with, either from list of recently opened documents on the start page or the "Open" command on the "File" menu. Alternatively create a new spreadsheet by selecting "Blank workbook" from the start page.
Highlight the range of cells, Excel table or PivotTable you wish to apply the formatting to, then select the "Conditional Formatting" icon under the "Home" tab on Excel's ribbon menu. A range of pre-built formats are shown for you to choose from. Select "Highlight Cells Rules" to find rules based on values, for example.
Select "Top/Bottom Rules" to find rules based on a cell's value in relation to the other cells in your selection. In each case, you are prompted to fine tune the conditional formatting -- in the case of the Top 10% option, for example, you can adjust the percentage used and the colours applied to any matching cells.
Choose one of the options from the "Data Bars," "Colour Scales" or "Icon Sets" menus on the "Conditional Formatting" drop-down menu and Excel applies a graphical embellishment that helps to visualise the data on screen. Choose one of the data bar options, for example, to insert coloured bars into the cells themselves, based on the values they contain.
Select "New Rule" from the "Conditional Formatting" drop-down menu to build your own rule from scratch. Choose the appropriate rule type from the list at the top of the dialog box, then tweak it as necessary using the settings underneath. The options change automatically depending on the rule type currently selected. Click or tap "OK" to confirm your chosen settings.
Click or tap "Conditional Formatting" then "Manage Rules" to see an overview of all the conditional formats applied to the selected cells. You can edit, delete and change the order of the rules you've configured (the conditional formatting rules are applied in order from the top of the list). New rules can also be created from here. Click or tap "OK" to close the dialog box.
Tips and warnings
- Conditional formatting rules provide plenty of ways to process your data. While creating your own rules gives you more control over rule settings and the end results, using the presets provided by Excel is a useful way of learning the ropes and understanding what is possible with the conditional formatting tool.
- Conditional formatting rules can also be added from the Quick Analysis button that pops up in the lower right corner whenever a group of cells are selected. As with any other rules, you can use the Manage Rules dialog to tweak a particular setting once it's applied, or clear the conditional formatting altogether.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for
- Microsoft Office Support: Add, change, find, or clear conditional formats
- Microsoft Office Support: Apply conditional formatting based on text in a cell
- Microsoft Office Support: Manage conditional formatting rule precedence
- Microsoft Office Support: Apply conditional formatting to quickly analyse data
- Microsoft Office Support: Remove conditional formatting