OBINTUSACADEMY
HOME CONTACT SERVICES PRIVACY
COMPUTER BASICS MS-PACKAGES
Excel - Conditional Formatting

MICROSOFT EXCEL

MODULE ONE

Prerequisite Introduction to Microsoft Excel Getting Started MS Excel Environment Title Bar Quick Access Toolbar Search Account Name Buttons Menu Tab Ribbons Dialog Box Launcher Name Box Insert Function Formula Bar Worksheet Area Worksheet Navigation Creating New Sheet Status Bar Worksheet View Format Zoom Slider MS Excel File Tab Entering Data Saving Your Workbook Opening Saved Workbook Copy, Delete and Hiding Sheet Closing Entire Workbook

MODULE TWO

Cells, Rows and Columns Resizing Rows and Columns Inserting Data Select, Delete and Move Data Copy, Paste and Cut Find and Replace Checking Spellings Inserting TextBox Inserting Symbols Inserting Special Characters Zooming Undoing and Redoing Changes

MODULE THREE

Mini Toolbar Setting Fonts Text Decoration Setting Text Case Text Alignments Text Orientation Increase and Decrease Indentation Wraping Text Merging Cells Cells Borders Cells Shades Copy Formatting Insert and Delete Worksheet Format Worksheet, Cells, Rows and Columns

MODULE FOUR

Insert Tables Insert Pictures Insert Shapes and Smart Art Insert Screenshots Insert Icons Insert 3D-Models Using Add-ins Insert Charts 3D Maps Creating Sparklines Insert WordArt

MODULE FIVE

Addition Subtraction Multiplication Division Using Formulas Using Functions Quick Functions

MODULE SIX

Fill Handling Sorting Filtering References (Relative and Absolute) Conditional Formatting Applying Styles

MODULE SEVEN

Page Themes Page Colors Page Fonts Page Effects Page Header and Footer Page Numbering Page Margins Page Orientation and Size Print Area Page Breaks Setting Background Print Titles Preview and Print Share Workbook Freezing Panes Range Data Validation Excel Templates MS Excel Shortcuts Conclusion

HOW TO

Excel How-To Send Email Separate Text Date and Time Format Macro Thesaurus Insert Comments Password Workbook Count Characters Count Words Add and Remove Hyperlinks Insert PDF in Excel Editing and Formatting Restrictions How to Compare Columns How to Compare Worksheets Add Signature in Worksheet Calculate Age Calculate Mean Calculate Median Calculate Standard Deviation Find the Mode Add and Remove Commas How to Remove Filter Convert Excel to CSV Excel to PDF Excel to Ms-Word Document Excel to Picture Formats Duplicate Rows and Columns Hide and Unhide Rows and Columns Rows and Columns Conversion How to Remove Gridlines Create Graphs Insert Check Box Add and Remove Watermark Logical Operators Logical Functions Text Functions Square Symbol Pi Symbol Selecting Different Columns Creating QR Code Creating Bar Code Insert Bullet Points Generate Random Numbers Combine Two Columns Wrap Text Create Dropdown List

Conditional Formatting

PREVIOUS NEXT

Conditional formatting in Excel is used in worksheets to change the look or appearance of cells based certain conditions that is specified by the user. Using conditional formatting helps the user to also spot out duplicates, make data unique and easy to read.

In this tutorial I will be showing you how to apply conditional formatting to your worksheet.

  • USING CONDITIONAL FORMATTING
  • Step 1 - For this illustration I will be using a worksheet that contains examination scores of students as shown in the image below.
    • conditional-formatting
  • Step 2 - Select the range of scores for the student as shown in the image below.
    • conditional-formatting
  • Step 3 - In the Home menu, go to the Styles group and click on the Conditional Formatting button. A dropdown list of options will be displayed. Here we have the Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales, Icon Sets and other options to create new rules, clear and manage rules. They are very easy to understand, though I will be picking one in each category to show you so that you will have an idea of all the categories.
    • conditional-formatting
  • Step 4 (Highlight Cell Rules) - The Highlight cell rules option is used to set a particular rule that the data must meet in order to give that specific formatting.
  • Step 4 - 1 (Highlight Cell Rules) - Move your mouse pointer to the option Highlight Cell Rules and this will display further options to select from.
    • conditional-formatting
  • Step 4 - 2 (Highlight Cell Rules) - All the options work in similar way, you can use the same method for all of them. So, when you click on the first option, which is the Greater Than option, a dialog box will be display for you to input a value. Any value in the selected range on your worksheet than is greater than the value you have input will be given a specific formatting
    I have typed 50, and in my worksheet, all the values above 50 have been given a specific formatting.
    • conditional-formatting
  • Step 4 - 3 (Highlight Cell Rules) - Now in the dialog box, you can change the formatting by clicking on the selection box beside the with label. A list of formatting options will be displayed, you can click on any one to apply it. I have clicked on the Green Fill With Dark Green Text and as you can see in the image below, it has been applied as the formatting.
    • conditional-formatting
  • Step 4 (Top/Bottom Rules) - The Top/Bottom rules option is used to give that specific formatting to specified range of highest (top) or lowest (bottom) values.
  • Step 4 - 1 (Top/Bottom Rules) - Move your mouse pointer to the option Top Bottom Rules and this will display further options to select from.
    • conditional-formatting
  • Step 4 - 2 (Top/Bottom Rules) - All the options work in similar way, you can use the same method for all of them. So, when you click on the first option, which is the Top 10 Items option, a dialog box will be display for you to input a value.
    This will by default select the top 10 items, but you can change the value in the input field if you want to. For the image below, I have changed the it to 3, so what will be formatted will be the highest 3 values in the scores column which are: 100, 87 and 80.
    • conditional-formatting
  • Step 4 - 3 (Top/Bottom Rules) - Next you can change the formatting by clicking on the selection box beside the with label. A list of formatting options will be displayed, you can click on any one to apply it. I have clicked on the Green Fill With Dark Green Text and as you can see in the image below, it has been applied as the formatting.
    • conditional-formatting
  • Step 4 (Data Bars) - The Data Bars options is just similar to an horizontal bar chart. Its color fills each bar based on the data values.
  • Step 4 - 1 (Data Bar) - Move your mouse pointer to the option Data Bars and this will display options you can select from to apply the conditional formatting.
    • conditional-formatting
  • Step 4 - 2 (Data) - You can select from the Gradient Fill Options any of the colors you want. Once you place your mouse pointer over the gradient color, it will reflect on the data in the worksheet. In my own case, I placed my mouse on the orange gradient fill. Remember to click it when you want to apply it.
    • conditional-formatting
  • Step 4 - 3 (Data) - You can select from the Solid Fill Options any of the colors you want. Once you place your mouse pointer over the solid color, it will reflect on the data in the worksheet. In my own case, I placed my mouse on the Light Blue solid color. Remember to click it when you want to apply it.
    • conditional-formatting
  • Step 4 (Color Scales) - The Color scale is used to apply different colors to different categories of data in the selected range of data based on its values.
  • Step 4 - 1 (Color Scales) - Move your mouse pointer to the option Color Scales and this will display color options you can select from to apply the conditional formatting.
    In the image below, I have selected the Green, White, Red Color Scale. The Green will applied to the highest values gradually fading out when it is getting to the middle values. White will be in the range of the middle values. Then red will gradually sets in when it is approaching the lowest values.
    Major color concentration: Green: 100, White: 50 Red: 0.
    • conditional-formatting
  • Step 4 (Icon Sets) - The Icon Sets is used to apply different icons to different categories of data in the selected range of data based on its values.
  • Step 4 - 1 (Icon Sets) - Move your mouse pointer to the option Color Scales and this will display color options you can select from to apply the conditional formatting.
    • conditional-formatting
  • Step 4 - 2 (Icon Sets) - In the icon sets we have the categories: Directional, Shapes, Indicators and Ratings. Once you place your mouse pointer over any of the options it will reflect on the data in your worksheet, but remember to click it when you want to apply it.
  • DIRECTIONAL
    • conditional-formatting
  • SHAPES
    • conditional-formatting
  • INDICATORS
    • conditional-formatting
  • RATINGS
    • conditional-formatting
note
NOTE:

Every conditional formatting option has the option More Rules which is the last option in the list of sub options. This option is used to create custom formatting rules (i.e you can create your own conditional formatting rules and use it for your data)

  • CREATING NEW RULES
  • Step 1 - In the Home menu, go to the Styles group and click on the Conditional Formatting button. A dropdown list of options will be displayed. Click the New Rule option.
    • conditional-formatting
  • Step 2 - The New Formatting Rule dialog box will be displayed. Now inside this box there are two sections, the first section is where you will have to select the Rule Type that you want to use for your new rule. The second is where you have to edit the description of your rule type, depending on the rule type you chose.
    After setting this, click the OK button to save your new rule.
    • conditional-formatting
  • CLEAR RULES
  • Step 1 - In the Home menu, go to the Styles group and click on the Conditional Formatting button. A dropdown list of options will be displayed.
    Move your mouse pointer to the Clear Rules option, this will display the options to clear rules for selected cells, entire sheet, table and pivot table
    • conditional-formatting
  • MANAGE RULES
  • Step 1 - In the Home menu, go to the Styles group and click on the Conditional Formatting button. A dropdown list of options will be displayed. Click the Manage Rules option.
    • conditional-formatting
  • Step 2 - The Conditional Formatting Rule Manager dialog box will be displayed. Inside this dialog box you will see the rules you have created if any, you can create new rule from here, edit and delete rules by using the buttons there. After performing the operation here, make sure to click the Apply button or the OK button. If you change your mind and you don't want to perform any operation, just click the Cancel button.
    • conditional-formatting
PREVIOUS NEXT
ADVERTISEMENTS

LEARNING IS A CONTINOUS PROCESS - PRACTICE MAKES PERFECT