Excel 2010 Using Conditional Formatting

Author: mety Nagm Labels::

Excel 2010

Using Conditional Formatting


Lesson 19 
Imagine you have a spreadsheet with thousands of rows of data. It would be extremely difficult to see patterns and trends just from examining the raw data. Excel gives us several tools that will make this task easier. One of these tools is called conditional formatting. With conditional formatting, you can apply formatting to one or more cells based on the value of the cell. You can highlightinteresting or unusual cell values, and visualize the data using formatting such as colorsicons, and data bars.

In this lesson, you will learn how to applymodify, and remove conditional formatting rules.

Conditional Formatting

Launch video!Watch the video (3:17). Need help?
Conditional formatting applies one or more rules to any cells that you want. An example of a rule might be "If the value is greater than 5,000, color the cell yellow." By applying this rule to the cells in a worksheet, you'll be able to see at a glance which cells are over 5,000. There are also rules that can mark the top 10 items, all cells that arebelow the average, cells that are within a certain date range, and many more.
Watch the video to learn about creating conditional formatting

To Create a Conditional Formatting Rule:

  1. Select the cells that you want to add the formatting to.
  2. In the Home tab, click the Conditional Formatting command. A drop-down menu will appear.
  3. Select Highlight Cells Rules or Top/Bottom Rules. We will choose Highlight Cells Rules for this example. A menu will appear with several rules.
  4. Select the desired rule (Greater Than, for example).
    Selecting the Greater Than ruleSelecting the Greater Than rule
  5. From the dialog box, enter a value in the space provided, if applicable. In this example, we want to format cells that are greater than $5,000, so we'll enter 5000 as our value. If you want, you can enter a cell referenceinstead of a number.
  6. Select a formatting style from the drop-down menu.
    Entering a value and formatting styleEntering a value and formatting style
  7. The formatting will be applied to the selected cells.
    The formatted cellsThe formatted cells
If you want, you can apply more than one rule to your cells.

Conditional Formatting Presets

Excel has a number of presets that you can use to quickly apply conditional formatting to your cells. They are grouped into three categories:
  • Data Bars are horizontal bars added to each cell, much like a bar graph.
    Data BarsData Bars
  • Color Scales change the color of each cell based on its value. Each color scale uses a two or three color gradient. For example, in the Green - Yellow - Red color scale, the highest values are green, averagevalues are yellow, and the lowest values are red.
    Color ScalesColor Scales
  • Icon Sets add a specific icon to each cell based on its value.
    Icon SetsIcon Sets

To Use Preset Conditional Formatting:

  1. Select the cells you want to add the formatting to.
  2. In the Home tab, click the Conditional Formatting command. A drop-down menu will appear.
  3. Select Data BarsColor Scales or Icon Sets (Data Bars, for example). Then, select the desired preset.
    Selecting a formatting presetSelecting a formatting preset
  4. The conditional formatting will be applied to the selected cells.
    The finished Data BarsThe finished Data Bars

    To Remove Conditional Formatting Rules:

    1. Select the cells that have conditional formatting.
    2. In the Home tab, click the Conditional Formatting command. A drop-down menu will appear.
    3. Select Clear Rules.
    4. A menu will appear. You can choose to clear rules from the Selected CellsEntire SheetThis Table, orThis PivotTable. In this example, we will clear rules from the entire sheet.
      Clearing RulesClearing Rules
    You can edit or delete individual rules by clicking on the Conditional Formatting command and selectingManage Rules. This is especially useful if you have applied multiple rules to the cells.



Blog Archive

Powered by Blogger.

I made these pages for me and my friends to help solving the problem we face regarding Computer & internet, if anyone wants me to answer a question or find out about some information please send me email and I will try to reply.*P.S. some of the article I wrote and the other I found on the internet I posted them in sprit of learning and shearing, please forgive me if you found something you don’t want to be in my blog, email me and I will delete them. Thank you for your interest in my pages.امل نجم Amal Nagm

banner 1 banner 2