Advertisement

Blog

Excel: Conditional Formatting

We all know that, when you right click on a cell in an Excel worksheet, it is possible to change the format of the cell, from its numeric type, alignment, and text property to its surround and background color, to name just a few. But did you know that you can have the cell change its format if different conditions are met? As a simple example, if you have an income and expenses statement, you may want the bottom cell (the proverbial bottom line) to turn red if the net income is negative. This feature in Excel is called Conditional Formatting.

Though the following example is not quite in the analog sphere, I think most people will get the gist. With today's microcontrollers, the serial communications controller is included in the peripheral set, and the baud rate is generated by dividing down some central clock. Asynchronous serial communications is quite tolerant of baud rate error, but it is better to minimize this error to allow for fewer connection issues with other devices. In addition, it is often necessary to support several baud rates.

Let's assume that the frequency at the input of an eight-bit divider is 1.5 MHz (set up as a cell, C1), and that the UART operates at eight times the baud rate. We also limit ourselves to an acceptable error of 3%, which appears as a value in cell F1, as you can see in Figure 1.

Figure 1

Laying out how we get to the results in the table will take too long for this blog. Suffice it to say that I have calculated the results for all possible eight-bit values and considered the error from the nearest standard baud rate. There are many other ways to approach this, but let's take this simplistic approach to demonstrate conditional formatting. I want to use some kind of visual indication for the different divisors I could use to be within my 3% error. I can then scroll down and easily see which rows are valid for my purpose.

It is possible to format one cell or a bunch of cells conditionally. When you copy a cell, like ordinary formatting, the conditional formatting comes along. Select all the cells in the column from H11 to H265. Select the Home tab of the ribbon, and then click on the Conditional Formatting icon. That will cause a dialogue to pop up — hopefully like the one in Figure 2.

Figure 2

The first five entries are shortcuts to different effects, but they all translate to an entry on the New Rule… dialogue, so you may as well select that. Five different types of rules (which correspond to the entries mentioned above) will pop up. I selected Format only cells that contain option . In Figure 3, you can see the dialogue that is presented as a response.

Figure 3

What I have formatted is that the cell will turn yellow if the value is between -3% and 3%. (In cell F11, notice that it is fixed to that cell.) Also notice that, when you click on the Format… button, you get the standard Format Cells dialogue, though a few options are grayed out. You can change the format to anything that is allowed in the dialogue. If you scroll down, you will notice some cells that are yellowed.

You can also set up multiple formats for the same cell. Let's say I want the text to be blue when the error is positive and red when it is negative. Figure 4 shows the formatting dialogue to do that. You can see the effects in the table to the left of the dialogue.

Figure 4

The dropdowns in Figure 5 will give you an idea of some of the options you can set up with the Conditional Formatting feature.

Figure 5

But an intriguing and potentially powerful tool is the last option in the New Formatting Rule dialogue shown in Figure 6. You can use any formula including any of Excel's functions in here. One example would be to create an alternating pattern of shaded rows, as used to be the case with fanfold computer paper. (The formula would be =row()=odd(row()) .) Where will your imagination take you?

Figure 6

I hope you noticed in passing in Figure 2 that it is possible to erase and edit cells that have been formatted in this way. However, it is not readily obvious which cells have been formatted conditionally. To find out, in the Home tab of the ribbon, click on the Find & Select icon in the Editing group (normally on the extreme right). Click on Go To… in the resulting menu and then the Special button in the next dialogue. As a last step, select Conditional Formats and then OK. As a result of this long-winded sequence, all the cells that have been conditionally formatted are selected.

For those who are interested, I will add a brief description of the creation of the table data in the comments below.

40 comments on “Excel: Conditional Formatting

  1. antedeluvian
    March 13, 2014

    As I mention in the last paragraph, here is a description of how the worksheet was constructed.

     

    I created a table of all possible divisors for the 8 bit counter which you can see from cell C11 to C265 (you do know how to do this simply with Excel right?). Next we create the actual baud rate based on the frequency in cell C1 and the divisor starting in B4 and down. The formula in cell D11 is

    =($C$1/C11)/8

    And that is copied through to the bottom of the table. As you know the “$” signs in the formula indicate that the cell reference is not to be adjusted while the C11 will change to match the cell to the left of the formula. The factor of 8 represents the 8x sampling rate of the UART,

    In my blog on lookup tables, you remember when I mentioned that in looking up the value below the searched value is found and I discussed that you would have to find a method of finding the upper value if you wanted it. Here is one possible approach. – this is the reason for the second row in the standard baud rates (row 5 of the worksheet). I am going to look up the value below (or equal to) the actual baud rate and place it in column F. The cell E11 contains the formula

    =HLOOKUP(D11,$B$4:$K$5,1,TRUE)

    The $ signs must be used or the transposition will carry through to the range selected as well. Copy the cells down column F. In order to get the standard value above the selected value, enter the following formula in cell F11

    =HLOOKUP(D11,$B$4:$K$5,2,TRUE)

    In other words, find the cell you want and then look at the cell below it- sneaky, right? Copy this down column G as well.

    Now we have to decide which one is closer to the actual baud rate. It is possible to use the MIN statement, but it feels like overkill to me so I will just use the IF statement. Cell G11 has the formula

    =IF((ABS(D11-E11)>ABS(D11-F11)),F11,E11)

    Column H from row 11 down is formatted as a percentage and contains the error calculation. In cell H11 this is

    =(D11-G11)/G11

    and is copied down for the rest of the column.

  2. RedDerek
    March 13, 2014

    I have not set it up yet, but will do so at the next opportunity… I can see this being useful when looking at a long list of parts and identifying what parts are short by conditionally formating the cells to highlight parts with negative inventory.

  3. eafpres
    March 14, 2014

    Hi Aubrey–conditional formatting is something every Excel user in engineering should learn how to use.  I use it all the time.  However, I have a lot of issues.  I nearly always use the formula option.  Sometimes after entering the formula, then setting up the format, when I get back to the list of conditional formats the rule, i.e. the formula, has a crazy cell range not anywhere near what I gave it.  Doing edit rule and manually correcting that usually fixes it.

    However, I've had other cases where for some reason the conditional formatting appears only on some of the range I've selected.  

    The other big headache for me is that once you set conditional formatting, you are correct that you can copy/paste, fill down or across, etc. and the format comes along.  IF you have your relative and absolute reference syntax right, sometimes the expected happens.  But if you move, copy, paste etc. conditionally formatted cells, Excel starts chopping up the rule into more rules for subsets of cells.  I've had cases where I start with 2 or 3 rules, then when I go back into the dialogue, there are 10 or 15!  This makes it a royal PIA to update a conditional format.  Often in these cases I'll just remove all conditoinal formats and start over.

  4. Davidled
    March 14, 2014

    Excel is naturally very powerful tool for implementing Database in order to sort and tag the name for each list. All warehouse engineer should know how to use excel to pull out all parts and update it.  In the future, data could be stored in Cloud.  Actual data and data list could be matched through an appropriate process.

  5. Netcrawl
    March 14, 2014

    @Daej you're its right its a powerful tool, perfect for engineering works. Since Excel 2007, Excel has a number of preset conditional formatting options that makes it easy to apply commonly conditions to data, this options include findingt numbers that are above of below the average value for a selected range of data. And then there's another one, its is also possible to create custom conditonal formatting using Excel formulas.  

  6. Netcrawl
    March 14, 2014

    @Daej cloud-based platform is a new way to connect businesses, research labs, effective sharing and design, this would definitely improve the way engineers design, visualize, test and share ideas with others anytime and anywhere.  

  7. antedeluvian
    March 14, 2014

    Hi Blaine

    Once again, the voice of experience. Thanks for your input and I hope the readers are paying attention.

  8. eafpres
    March 14, 2014

    @Netcrawl & @Daej–Excel is very powerful.  However, most engineers who use it (as well as financial people, HR, etc.) don't do QA testing on their work in Excel.  Here is an article that says 88% of such work has errors:

    88% of spreadsheets have errors

  9. eafpres
    March 14, 2014

    @Netcrawl–cloud is useful, but also creates new issues.  I don't want to go to Microsoft's cloud application suite becuase I'm often working without internet connection.  Also, suites like Google are way behind in features; the dedicated PC versions of software are much better.  In Google's case I have used Google docs a bit and I could not undo changes.

    I think we are still some time before these cloud platforms are really as good as PC based.

  10. eafpres
    March 14, 2014

    Hi Aubrey.  For the benefit of those reading, I created an example of the kind of issues I frequently see in conditional formatting:

    Excel Conditional Formatting errors

    On this collage, from the top left:

    Screen shot workbook with 2 columns “Start” “Due”.  In these columns I'm using numeric data instead of dates becuase sometimes I find that easier.  So December 4, 2013 is 20131204.  Now I'm going to format the Open column so that all dates in 2014 or later are highlighted green.  The next images:

    Open Conditional Formatting Manager as Aubrey describes.  Notice there are many formulas there already–they are formatting closed items in grey, FYI.  (Note: I have obscured all the data except for dates and the file name as that info is confidential; I'm using a real file I work with every day.) You seen in the pane to the right of the conditional formatting list that I have created a formula =B2>20139999, and format to fill the cells green.  The 20139999 ensures that ony dates after 2013 will be highlighted. On the next row of images:

    On the left you see that the formula appears OK; in “Applies to” I have entered the range to which I want this to apply.  Note that Excel often defaults to absolute references in conditional formatting which may not be what you want; I frequently re-type the data to remove the $.  On the right we see the result in the list–Excel has changed my formula to say =A3040433… which is some crazy cell reference.

    When this happens, usually I just re-edit the rule and type in the correct formula again, and it fixes it.  The bottom image shows the desired result.

     

  11. amrutah
    March 14, 2014

    I too use conditional fomatting a lot and along with that I make use of Name Box (one at the left, beside the formula bar).  Once you name any cell, you can use the same name everywhere (on all the sheets).

  12. amrutah
    March 14, 2014

    In excel 2003 and below, we could nest and use only 2 levels of conditional formatting, but looks like in 2007 and onwards this is no more a limitation.   I had troubles in the past with only 2 levels of nesting.

  13. Netcrawl
    March 14, 2014

    I agree with you @easfres1 Google is way behind in features and functionalities, Google can't compare to the powerful features and formulas of Microsoft's tools. Microsoft's Excel also has the advantage for a number of users who have been familiar with Microsoft Office Suite, less of a learning curve. But Microsoft's Excel is notorious for being loaded with complex features, that in my own view can cause confusion.

    @easpres1, the best things about using cloud-based platform is that they can be customized depending on the users or company needs, new features and skill sets. Microsoft also offer add-ons, storage and professional services as well as app that you can use to enhance your project       

  14. eafpres
    March 14, 2014

    Hi amrutah – you are correct that in Excel 2010 and later, the conditional formatting rules can be very complex.  But as I noted, they can be hard to maintain.  

    One suggestion I can make (and how often do I wish I would follow my own advice!):

    Wait until you workbook is very stable before you add much conditional formatting.  Then you will spend less time fixing the formatting.

  15. eafpres
    March 14, 2014

    @Netcrawl–it seems the promise of constant updates is another suggested benefit of cloud applications.  I think the cloud platforms make sense for large data, applications backed by big databases, and web-based data collection (also known as Internet of Things).  But if I'm working on a small project, that all fits into a few files on my laptop, why would I use the cloud?  Local is faster, always ready, I can save lots of revisions to revert, etc.

    However, for large enterprises (I'm an indpendent consultant) the situation may be quite different.  In the past, the IT departments had to manage keeping everyone on the same version, dealing with updates and patches etc.  In bigger firms, they become “Microsoft shops”, and they get enterprise support and can push updates over their company intranets etc.  But still it is a lot of work.  Moving to a cloud suite takes almost all the work off of IT.  And the cost to deploy can be a lot less in big companies.

    I hope that Microsoft keeps supporting the stand-alone versions for the large number of us who are small businesses and depend on the powerful tools on our PCs.

  16. antedeluvian
    March 15, 2014

    amrutah

    Once you name any cell, you can use the same name everywhere (on all the sheets).

    Absolutely. In writing this series of blogs I have presumed a basic functioning knowledge of Excel and I have tried to avoid getting involved with too much of the basics. But it did occur to me a while ago that I might be taking too much for granted and so I wrote a 3 part blog on it. You should see it in April.

  17. antedeluvian
    March 15, 2014

    Blaine

    I hope that Microsoft keeps supporting the stand-alone versions for the large number of us who are small businesses and depend on the powerful tools on our PCs


    Me too! I also hope they revisit their Windows 8 policy and separate between a touch screen and desk top. I did read somewhere about the change in leadership in Microsoft and the implications were that this may in fact, be on the cards.

     

     

  18. eafpres
    March 15, 2014

    Hey Aubrey, I'm with you on the Windows 8 thing.  I'm running Windows 7, and have gotten to where I'm very productive with it.  Windows 8 would cripple me.  When you are Apple and essentially you have 2 mobile compute product lines, you can have more or less one OS for both.  But even Apple has an OS for their desktops/laptops.  Microsoft may be proven visionary, eventually, but right now the idea of one OS for everything is way to premature.  As an aside, when Windows 8 came out they had already “upgraded” the Xbox OS, and had similarities with their mobile OS.  I said in some comments at that time that they clearly were heading towars a common UI.  Unfortunately I was right.

  19. antedeluvian
    March 15, 2014

    Blaine

     

    On Black Friday Microsoft made me an offer on a Surface RT (ARM based) that I could not refuse. I am busy evaluating it and I feel that Win8 is in fcat quite good in that environment.  In addition you get Office thrown in. Everything that I have covered in my blogs with the exception of add-ins (that means Solver) will run on the Surface making a real work tool. Contrast that with Numbers on the iPad which is really basic. Watch for my blog on it in MCU Designlines after EE Live (I am using the trip to firm up my impressions).

    By contrast I don't think Win8 is very good on a PC (if you saw my blog Whither Electronic Development With Windows 8?)

  20. eafpres
    March 15, 2014

    Aubrey–Is the Surface RT the one with the stripped down OS and not quite full Office software?  I've been wondering about the Surface Pro, which sounds like it can run real software.  I read your blog; I haven't looked into 3rd party keyboards for the Surface.  I cannot deal with undersize non-tactile feedback keyboards.

    I also use several add ins and macro code in many Excel workbooks.  I use a lot of memory too.  And frequently have many internet tabs open and a bunch of apps.  A tablet just cannot cut it right now.

    I have Numbers on an iPhone and it wasn't worth the $10 but I needed something to open Excel workbooks in email.  It's pretty pathetic, but it does the one thing I bought it for.

  21. antedeluvian
    March 16, 2014

    Blaine

    Is the Surface RT the one with the stripped down OS and not quite full Office software? 

    Yes.

    I've been wondering about the Surface Pro, which sounds like it can run real software

     

    Me too, but I haven't been able to justify the cost just so I can write a blog about it. I am normally chained to my desk at work so there doesn't seem much reason for it and it also runs Windows 8 so you face the same limitations as I discuss in the blog. From what I have read it is also quite a power hog and needs recharging quite frequently rather like a laptop.

    I haven't looked into 3rd party keyboards for the Surface.  I cannot deal with undersize non-tactile feedback keyboards.

    They first generation keyboards are surprisingly good. There is a mouse pad as well and I am baffled as to how it works. I understand your need for the tactile feedback and it requires a 3 figure combination for the function keys, which it seeems to me from your level of expertise, you use frequently. I also use it on my lap or when recumbent and I would prefer it if it were a little bit more rigid. The next generation and 3rd party may be better. But the way it locates and clips into the tablet is a work of sheer genius.

    I also use several add ins and macro code in many Excel workbooks.

    Macros also won't run on Excel under Windows RT. Of course the Pro is full WIndows. 

    I use a lot of memory too.  And frequently have many internet tabs open and a bunch of apps.  A tablet just cannot cut it right now.

    I hear you.

    By the way, Office doesn't come free with the Pro. It does with RT.

     

  22. eafpres
    March 16, 2014

    @Aubrey–I think Big Brother Microsoft must be watching.  Nearly coincident with my negative comments on their OS plans etc., the latest Windows Update killed Chrome which is my preferred browser.  The “fix” is to do a system restore to before the update and wait for an update that specifically fixes this.  Oddly, while my configuration is common, so this must be happening to a lot of users, it was VERY hard finding the information running IE10 and searching on Google.  Makes me wonder if Google limits search quality if it detects a non-Chrome browser…

  23. Davidled
    March 16, 2014

    I did not hear any window update killing or suspending Chrome. One of files would be missed from Microsoft download center.  Anyway, I was told that person who developed and managed window RT surface project in MS was stepped down from his position.  I think that window RT Surface based on ARM 32 bit architecture is not a popular product to attract customer, except engineer.

  24. chirshadblog
    March 17, 2014

    @DaeJ: Yes for a normal user it is very complicated. Not sure even by looking at the help, a normal user would dare to take the challenge. 

  25. geek
    March 18, 2014

     

    “In addition you get Office thrown in. Everything that I have covered in my blogs with the exception of add-ins (that means Solver) will run on the Surface making a real work tool”

    @antedeluvian: I think that's one of the most competitive advantages that a Windows RT tablet has over iPad or other Android-based tablets. The utility of being able to to use Office on the tablet in the same way as on your computer does make it quite handy to possess a Microsoft tablet. The only concern I feel is the lack of other apps that I will normally be needing on a daily basis.

     

  26. geek
    March 18, 2014

    “I think that window RT Surface based on ARM 32 bit architecture is not a popular product to attract customer, except engineer.”

    @DaeJ: I'm not sure if I'd agree with this. Are you saying that the RT tablets are not attractive because their hardware side is not very strong? If that's the case then I don't think a lot of customers actually pay a lot of attention to the underlying hardware in the tablet. For them the visual display and look and feel of the device matters more. I do think MS has done a good job with that at least.

  27. geek
    March 18, 2014

    @Aubrey: Thanks for illustrating the use of Excel's conditional formatting through your example. I've use it on a daily basis and I find it to be one of the most powerful tools in excel especially while dealing with large amounts of data that keep changing regularly. One other exciting feature within conditional formatting is the use of Heat Maps. I have used it for my projects several times and it gives a very good impression.

  28. antedeluvian
    March 18, 2014

    tzubair

    One other exciting feature within conditional formatting is the use of Heat Maps.

    Thanks for the tip. I will look into it.

     

  29. geek
    March 20, 2014

    @antedeluvian: It's a very effective tool when you're modeling something over a map perhaps something like a network map of an office where different areas within the office space may have different number of network outlets or access points. Indicating each area with a different color shade to represent the concentration of a network is very effectively done through a heat map. The best part is that it's generated automatically without a lot of intervention.

  30. Sachin
    March 31, 2014

    When dealing with large amounts of data, this feature could be very handy. I notice that you mentioned that this example was not exactly within the analog design space but after reading through the entire piece it is easy to see that this feature can be applied in very many different ways especially when it comes to design testing without the use of computer simulations that give a visual picture of expected responses to various adjustments.

  31. SunitaT
    March 31, 2014

    It amuses me the way Conditional Formating works in Excel. It makes life much easier when you are dealing with huge data where mapping is required. Heat maps comes to your rescue when comprehending wide range of volatile data. I will sure give a try using this option in coming days.

     

     

     

  32. SunitaT
    March 31, 2014

    In terms of efficiency when using any of the Microsoft office applications, the Surface RT is a very poor excuse for a computer. Even after installing so many add-ins and what not, I still could never get it to function as efficiently as my little chromebook which has elaborate office applications. The Surface Pro is more like it and even though I have not yet bought one, I was really impressed the first time I used the office suite, with an external keyboard of course.

  33. yalanand
    March 31, 2014

    @antedeluvian, thanks for the post. Have you used conditional formatting on google docs ? I tried using google docs conditional formatting but felt features are not as powerful as Excel.

  34. yalanand
    March 31, 2014

    The best part is that it's generated automatically without a lot of intervention.

    @tzubair, I agree with you. I think best part is we dont need to code macros to do this which will take lot of run time. Best part about conditional formatting is that it is fast and such formatting can be copied very easily.

  35. yalanand
    March 31, 2014

    The utility of being able to to use Office on the tablet in the same way as on your computer does make it quite handy to possess a Microsoft tablet.

    @tzubair, agree with you. Recently Microsoft released Office for the iPad which will definitely help the iPad users because Office provides some very useful features which are not available on other softwares.

  36. yalanand
    March 31, 2014

    @tzubair, One more best feature of Conditional formatting is that we can display icons that are often easier to interpret than the values they represent. 

  37. amrutah
    April 1, 2014

    Yalanand: I have used conditional formatting in Google docs, but it is not as strong as MS-excel.

    More-over the Google docs is just a basic interface and I think same is the case with windows run-time.

  38. Netcrawl
    April 2, 2014

    Yeah it can't match Microsoft Excel, everything from peformance on large data to formulas, the range data analysis in Excel  is too powerful it could blow Google Docs away. The best things about Google Docs is that its free but in term of performance it simply can't match Excel.   

  39. Netcrawl
    April 2, 2014

    @yalanand that's great news! I though excel software can be installed only on Windows-powered tablets, Microsoft's Excel is still the market leader and a good option when you're working with others, the drawback is the price, Google is free and a much cheaper alternative, but it has some limitations.  

  40. antedeluvian
    April 2, 2014

    Netcrawl

    ! I though excel software can be installed only on Windows-powered tablets, Microsoft's Excel is still the market leader and a good option when you're working with others, the drawback is the price,

    I am not sure about other machines running Windows 8 RT, but on the Microsoft Surface (not the Pro) Microsoft Office is free. An enormous plus in my opinion.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.