This wikiHow teaches you how to add conditional formatting to a Microsoft Excel spreadsheet on both Windows and Mac computers. Conditional formatting will highlight cells that contain data matching the parameters that you set for the formatting.
- Open your document in Excel. Double-click the Excel spreadsheet that you want to format.
- If you haven’t yet created your document, open a new blank spreadsheet in Excel and enter your data before continuing.
- Select your data. Click and drag your mouse from the top-left cell in your data group to the bottom-right cell in your data group. Your data should now be highlighted.
- Click the tab. It’s at the top of the Excel window. This is where you’ll find the Conditional Formatting option.
- Click . You’ll find this in the “Styles” section of the Home toolbar. Clicking it prompts a drop-down menu to appear.
- Click . It’s near the bottom of the drop-down menu. Doing so opens the Conditional Formatting window.
- Select a rule type. In the “Select a Rule Type” section, click one of the following rules:
- Format all cells based on their values – Applies conditional formatting to every cell in your data. This is the best option for creating a visual gradient when organizing data by average, etc.
- Format only cells that contain – Applies conditional formatting only to cells containing your specified parameters (e.g., numbers higher than 100).
- Format only top or bottom ranked values – Applies conditional formatting to the specified top- or bottom-ranked number (or percentage) of cells.
- Format only values that are above or below average – Applies conditional formatting to cells falling above or below the average as calculated by Excel.
- Format only unique or duplicate values – Applies conditional formatting to either unique or duplicate values.
- Use a formula to determine which cells to format – Applies conditional formatting to cells based on a formula that you have to enter.
- Edit your rule. This step will vary based on the rule that you selected:
- Format all cells based on their values – Select a “Minimum” and a “Maximum” value using the drop-down boxes. You can also change the color used for each value in the “Color” drop-down box.
- Format only cells that contain – Select the type of cell that you want to format, then select other rules in the drop-down boxes that appear based on your choice.
- Format only top or bottom ranked values – Select either Top or Bottom, then enter a number of cells to format. You can also enter a percentage number and check the “% of the selected range” box.
- Format only values that are above or below average – Select an above or below average value.
- Format only unique or duplicate values – Select either duplicate or unique in the drop-down box.
- Use a formula to determine which cells to format – Enter your preferred formula in the text box.
- Click . It’s in the lower-right side of the window. A new window will open.
- Click the tab. This tab is in the upper-right side of the new window.
- Select a color. Click a color that you want to use for the conditional formatting. This is the color that cells matching your formatting parameters will display.
- Err on the side of light colors (e.g., yellow, light-green, light-blue), as darker colors tend to obscure the text in the cells—especially if you print the document later.
- Click . It’s at the bottom of the window. Doing so closes the “Format” window.
- Click to apply the formatting. You should see any cells matching your formatting criteria become highlighted with your chosen color.
- If you decide that you want to erase the formatting and start over, click Conditional Formatting, select Clear Rules, and click Clear Rules from Entire Sheet.
- Save your spreadsheet. Click File, then click Save to save your changes, or press (or on a Mac). If you want to save this document as a new document, do the following:
- Windows – Click File, click Save As, double-click This PC, click a save location on the left side of the window, type the document’s name into the “File name” text box, and click Save.
- Mac – Click File, click Save As…, enter the document’s name in the “Save As” field, select a save location by clicking the “Where” box and clicking a folder, and click Save.
- The Conditional Formatting drop-down menu has several shortcut options (e.g., Highlight Cells Rules) that you can use to quickly format your data.
- One practical use of conditional formatting is using it to identify cells containing negative numbers in a budget (or zeroes in a store inventory sheet, etc.) so that you don’t have to hunt manually for discrepancies.
- Avoid formatting colors or options that make the cells to which they’re applied difficult to read.