MyRentedRoom!

Tuesday, January 3, 2012

Microsoft Excel Tutorial: How to use Conditional Formatting

HOW TO USE CONDITIONAL FORMATTING IN EXCEL


Hello! I'm Back with a tutorial! If you noticed, I didn't have any post last December 2010. I had a lot of things to do, well, because of the Holidays. Before I forget, let me greet you a very happy new year! My first post for this year is a tutorial on how to use conditional formatting in EXCEL


What is Conditional Formatting?


Let us check what Microsoft has to say about this feature. In Excel Help, they said:


Use a conditional format to help you visually explore and analyze data, detect critical issues, and identify patterns and trends.

If I, personally, would read that definition, I still will have a hard time figuring out what it does exactly. Basically, Conditional Formatting is about visual identification. Let me give you a scenario:


If you have a sales business, let's say, Perfumes. And you would like to know, in one look, which perfume sells more. I have a simple example about what this "in one look" is about, Graphically:
Excel, Excel How to, Excel Tutorial, How to use Conditional Formatting in Excel, in Excel, In Microsoft excel, Microsoft Excel, Microsoft excel tutorial


As you can see, the above graph would explain how Conditional Formatting in Excel can help you check your products in just one look. Let me teach you how to do it in 10 Simple Steps:



Here is how you can use Conditional Format in Excel


First, list down the categories you would like to analyse or present and make a target for your metric. In this case, Monthly Sales. Our target is 50 Sales in one month. We should identify what number of sales is considered below target as well. I assigned this one 10 Sales.






Excel, Excel How to, Excel Tutorial, How to use Conditional Formatting in Excel, in Excel, In Microsoft excel, Microsoft Excel, Microsoft excel tutorial


Second, is to highlight the fields that we are going to use for Conditional Formatting. They are usually the column with numbers. The picture below will show you that the highlighted cells are the numbers below the No. of Sales column.


Excel, Excel How to, Excel Tutorial, How to use Conditional Formatting in Excel, in Excel, In Microsoft excel, Microsoft Excel, Microsoft excel tutorial




Third Step, is to click the Conditional Formatting button in the Home menu. Now, to better understand how Conditional Formatting works, we will use the "New Rule" option because the other options use automatic highlights. The only thing you'll do then, is to input numbers. It is better to learn how it really works.


Excel, Excel How to, Excel Tutorial, How to use Conditional Formatting in Excel, in Excel, In Microsoft excel, Microsoft Excel, Microsoft excel tutorial






Fourth. After clicking New Rule, another menu will pop-up which will ask you to Select a Rule Type. in our example, we will use the "Format only cells that contain" option. Click on it, and a Rule Description Menu will show below the window.


Excel, Excel How to, Excel Tutorial, How to use Conditional Formatting in Excel, in Excel, In Microsoft excel, Microsoft Excel, Microsoft excel tutorial






Fifth. Remember the Target and Below Target that we identified awhile ago? this is where we'll use them. Click on the Second drop down menu and you will see a list of rules to choose from. Our first Rule should be identifying the products which were able to meet or exceed the target. Click on greater than or equal to.




Excel, Excel How to, Excel Tutorial, How to use Conditional Formatting in Excel, in Excel, In Microsoft excel, Microsoft Excel, Microsoft excel tutorial






Sixth step will be assigning the target for our rule. Our target is 50 Sales, so just type in 50 in the text box.




Excel, Excel How to, Excel Tutorial, How to use Conditional Formatting in Excel, in Excel, In Microsoft excel, Microsoft Excel, Microsoft excel tutorial






Seven, will be the Formatting part. click on the Format button and another menu will pop out. It will automatically bring you to the second tab of the Formatting Menu to format the font style, color or size.




Excel, Excel How to, Excel Tutorial, How to use Conditional Formatting in Excel, in Excel, In Microsoft excel, Microsoft Excel, Microsoft excel tutorial








Eighth Step. I will teach you how to do a basic Conditional Formatting for Categories that meets or exceeds the target. Basically, you would want to see which product is meeting the target in one glance, so you should click on the Bold Font Style






Excel, Excel How to, Excel Tutorial, How to use Conditional Formatting in Excel, in Excel, In Microsoft excel, Microsoft Excel, Microsoft excel tutorial








Nine. Click on the Color drop-down menu to choose a color for your product that meets or exceeds the target. Ideally, we would color them green. I don't have an explanation for that. Maybe because of money? or It's just pleasant in the eyes.




Excel, Excel How to, Excel Tutorial, How to use Conditional Formatting in Excel, in Excel, In Microsoft excel, Microsoft Excel, Microsoft excel tutorial








Tenth Step! Finally, once you hit the OK button. You'll see a preview on what will be presented, if a product is greater than or equal to 50. Try hitting the OK button once more and you'll see, in your table, which products were able to meet the green status. Here is how it should look like:




Excel, Excel How to, Excel Tutorial, How to use Conditional Formatting in Excel, in Excel, In Microsoft excel, Microsoft Excel, Microsoft excel tutorial






Now if you can see, this table is somewhat different to what I presented you awhile ago. You saw red! why didn't you see red in the tenth step? Here is another feature of Conditional Formatting in Excel.






Conditional Formatting - Multiple Rules




In order to add Different/Multiple Conditional Formatting rules in excel, you should have another target. In our case, the target is Below 10 Sales which is labeled Below Target. To add another rule, this time, you should choose Manage Rules, instead of New Rule, in the Conditional Formatting Menu. Once clicked, a menu will pop-out which is the Conditional Formatting Rules Manager. Click on "New Rule", Again, choose Format only cells that contain, and now choose Less than or Equal to, in our case 10.




Excel, Excel How to, Excel Tutorial, How to use Conditional Formatting in Excel, in Excel, In Microsoft excel, Microsoft Excel, Microsoft excel tutorial






In the Format Cells option, Just choose whether it should be bold as well and the color i chose is Red for it to have an alarming effect on the viewer. 




Excel, Excel How to, Excel Tutorial, How to use Conditional Formatting in Excel, in Excel, In Microsoft excel, Microsoft Excel, Microsoft excel tutorial






Now, once you press all the OK's you'll see the table which I showed to you awhile ago.




Excel, Excel How to, Excel Tutorial, How to use Conditional Formatting in Excel, in Excel, In Microsoft excel, Microsoft Excel, Microsoft excel tutorial






There are more Rules that can be added in Conditional Formatting, like, adding a Yellow mark to show those sales who are within the target but not below 10. Just choose the "Between" options when assigning a rule, then input both 50 and 10 for the identifiers.




Excel, Excel How to, Excel Tutorial, How to use Conditional Formatting in Excel, in Excel, In Microsoft excel, Microsoft Excel, Microsoft excel tutorial






You can also use the Fill Tab which is located in the last tab of the Format Cells menu. Just make sure your color combination is suitable enough so that the numbers are still readable. This is my favorite rule in Conditional Formatting as it is very easy to identify which one is passing or failing.








These are some tricks on how to use Conditional Formatting in Excel. There are tons of ways to use this option however, this tutorial that I provided is one of the most utilized techniques when presenting tables with Conditional Formats. You can also use this feature with you work from home tasks. It would be fairly easy to present reports with Conditional Formatting.

Now you have an idea on how to use it, I'll leave you with this tutorial and try to experiment some more. Have a great day and may the power of Excel be bestowed upon you!



How about you? have you ever encountered tables in excel were you were able to use Conditional Formatting? Please share it with us! We would like to learn more.





Michael Banawa
Hire Virtual Administrative Assistants at PAOutsource


6 comments:

  1. very informative.. nice one, Mike!

    ReplyDelete
  2. Heya¡­my very first comment on your site. ,I have been reading your blog for a while and thought I would completely pop in

    Appear fervent about it. I’m developing a fresh blog plus I’m struggling to make it look good, as well as offer the best

    quality content. I have learned much at your web site and also I anticipate alot more articles and will be coming back

    soon. Thanks you.


    Journal Editing and Formatting Service

    ReplyDelete
    Replies
    1. Hello vadi! I have been busy an haven't check things here in my blog. But i'm actually planning to start posting again. Thanks!

      Delete
  3. Good site! I truly love how it is simple on my eyes and the data are well written. I’m wondering how I might be notified whenever a new post has been made. I’ve subscribed to your RSS feed which must do the trick! Have a great day
    cottage rentals

    ReplyDelete