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.
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.
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.
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.
The dropdowns in Figure 5 will give you an idea of some of the options you can set up with the Conditional Formatting feature.
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?
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.