How To Create Conditional Formatting In Excel

Download Article

Download Commodity

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 gear up for the formatting.

Steps Download Article

1. 1

   Open up your document in Excel. Double-click the Excel spreadsheet that you want to format.

   • If you haven't yet created your document, open up a new blank spreadsheet in Excel and enter your data earlier continuing.

2. ii

   Select your data. Click and drag your mouse from the height-left cell in your information grouping to the lesser-right cell in your information group. Your data should now be highlighted.

   Advertizing

3. 3

   Click the Domicile tab. It's at the top of the Excel window. This is where you'll find the Conditional Formatting option.

4. 4

   Click Conditional Formatting . You'll observe this in the "Styles" section of the Home toolbar. Clicking it prompts a driblet-down menu to appear.

5. 5

   Click New Rule… . Information technology's near the lesser of the driblet-downwardly card. Doing and so opens the Conditional Formatting window.

6. 6

   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 prison cell in your data. This is the best option for creating a visual gradient when organizing data past average, etc.
   • Format only cells that incorporate - Applies conditional formatting only to cells containing your specified parameters (east.g., numbers higher than 100).
   • Format only acme or bottom ranked values - Applies conditional formatting to the specified peak- or bottom-ranked number (or percentage) of cells.
   • Format merely values that are above or below average - Applies conditional formatting to cells falling in a higher place or beneath the average every bit calculated past Excel.
   • Format merely unique or duplicate values - Applies conditional formatting to either unique or duplicate values.
   • Utilise a formula to make up one's mind which cells to format - Applies provisional formatting to cells based on a formula that you take to enter.

7. 7

   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 drib-down boxes. Yous can also change the color used for each value in the "Color" drop-downwardly box.
   • Format but cells that contain - Select the type of cell that you want to format, then select other rules in the drop-down boxes that announced based on your choice.
   • Format only top or bottom ranked values - Select either Tiptop or Lesser, then enter a number of cells to format. Yous can also enter a per centum number and cheque the "% of the selected range" box.
   • Format only values that are above or below average - Select an higher up or below boilerplate value.
   • Format just unique or duplicate values - Select either duplicate or unique in the drop-downward box.
   • Use a formula to make up one's mind which cells to format - Enter your preferred formula in the text box.

8. eight

   Click Format… . It'southward in the lower-right side of the window. A new window will open up.

9. ix

   Click the Fill tab. This tab is in the upper-right side of the new window.

10. ten

    Select a color. Click a colour that you lot want to use for the provisional formatting. This is the color that cells matching your formatting parameters will display.

    • Err on the side of light colors (e.1000., yellow, calorie-free-dark-green, lite-blue), as darker colors tend to obscure the text in the cells—especially if you impress the document after.

11. 11

    Click OK . It'southward at the bottom of the window. Doing and so closes the "Format" window.

12. 12

    Click OK to utilise the formatting. Y'all should meet any cells matching your formatting criteria become highlighted with your chosen color.

    • If you make up one's mind that you want to erase the formatting and start over, click Conditional Formatting, select Clear Rules, and click Articulate Rules from Entire Sheet.

13. 13

    Salvage your spreadsheet. Click File, then click Save to save your changes, or printing Ctrl+S (or ⌘ Command+South on a Mac). If you want to salve this document as a new certificate, do the post-obit:

    • Windows - Click File, click Save Equally, double-click This PC, click a salve location on the left side of the window, type the document's name into the "File name" text box, and click Salvage.
    • Mac - Click File, click Salve As..., enter the document's proper noun in the "Save As" field, select a save location past clicking the "Where" box and clicking a folder, and click Save.

    Advertisement

Add New Question

• Question

  How do I get Excel to highlight a particular jail cell?

  

  Just click on the particular cell y'all want to highlight, using the mouse pointer.

• Question

  How practise I convert an Excel formula into conditional formatting?

  

  It is not and then much converting a formula as using the conditional formatting to display the results of the data (in this case, through a formula). Let's say, for example, you have an entire cavalcade (column C) that is the sum of columns A and B. You can use provisional formatting to cavalcade C to search out certain results, similar showing cells greater than 10 with a red groundwork.

• Question

  I have created a sheet to manage my stock levels, I have formatted columns J, M, O, and Due south to highlight red if the decease date has been exceeded and green if the death date falls inside ii months from today. When there is no engagement in these columns they show cerise. I want the columns to stay white. What can I do?

  

  You can create a new rule -- "Format only cells that contain", hit "cell value" and choose "bare" instead. Pick whatever formatting you want. And so ensure this rule is applied first (Applied in Social club Shown), and that should solve your problem.

• Question

  How can I type a bold underlined word it Excel and save it?

  

  NurtureTech University

  Customs Answer

  You can set font and size past default. However, underline cannot fix the by default.

• Question

  Why does Excel go along formatting "0" with the conditional format set for the cells?

  

  NurtureTech University

  Customs Answer

  Because Excel understands 0 every bit a number, so it colors the data. Conditional formatting always works greater than less than concepts.

• Question

  How practice I format a cell based on the engagement in another cell?

  

  Add a column and make a link with the desired date column cells. Now, select the newest cavalcade and use conditional formatting, select "highlight colors based on," and and then choose "a date occurring."

• Question

  Excel keeps formatting my changes at 100% viewing. How do I keep my view at 150%?

  

  Menus are slightly unlike in different versions, but generally - the File > Options card allows you to ready the default behavior of your workbooks and worksheets. Likewise, if you lot have a format that you lot prefer or use often, you could Relieve As a *.xltx filetype (template). This mode you could apply it when desired, but it wouldn't have to exist your default, either.

• Question

  Tin I add together a 2nd dominion to an already existing rule?

  

  Yep! I've added upward to 3 rules, depending on the machine-color I wanted for a given value. It may be possible to add more.

• Question

  Tin I apply conditional formatting to just cells with a formula?

  

  No. Information technology can be applied to cells with or without formulas. It simply matters what rules are applied to the selected cells.

Ask a Question

200 characters left

Include your email accost to get a message when this question is answered.

Submit

Advert

wikiHow Video: How to Apply Conditional Formatting in Excel

• The Conditional Formatting drop-down menu has several shortcut options (e.g., Highlight Cells Rules) that you tin utilise to rapidly format your information.

• 1 applied employ of conditional formatting is using it to identify cells containing negative numbers in a budget (or zeroes in a store inventory sheet, etc.) then that y'all don't have to hunt manually for discrepancies.

Ad

• Avoid formatting colors or options that make the cells to which they're applied difficult to read.

Advertizing

About This Article

Article Summary 10

You can utilise conditional formatting in Excel to automatically add together colour and style to cells that incorporate certain values or encounter other criteria you define. To get started, open your worksheet in Excel and highlight the cells you want to format. On the Home tab, click the Conditional Formatting button on the toolbar, and then select New Rule from the menu. Now, click the type of rule you'd similar to set, and ascertain how it should work. For example, let's say you want all cells containing positive numbers to have blue backgrounds. In this case, you'd cull the Format only cells that contain rule, and and then select Prison cell Value from the first drop-down card. Select greater than from the next menu, and then type ""0"" into the bare field. Now, permit's add the blue background. Click the Format button, and so click the Make full tab. Select a prissy low-cal shade of bluish from the colour palette, and click OK to add it to your dominion. If you're happy with your rule, click OK to apply the new formatting atmospheric condition to the selected cells.

Did this summary help yous?

Thanks to all authors for creating a folio that has been read 1,934,914 times.

Is this article up to date?

Source: https://www.wikihow.com/Apply-Conditional-Formatting-in-Excel

Posted by: ortegoounge1939.blogspot.com

Share this post