One of the most useful features in Excel for laboratories is its ability to automatically format data based on a cell value. This is highly valuable, as it can create quick and important visual clues for laboratory operators or clients when something is out of spec.
This feature is super simple to setup in any spreadsheet, and can be used to make your spreadsheets significantly more user friendly.
Let’s start with a simple spreadsheet that includes results for 3 samples for a total of 12 analytes, and a QC recovery column that contains data for each of the analytes. If you want to follow along with this tutorial, here is the example spreadsheet to download.
Say our specification for all analytes is they need to be less than 900mg/L, and we would like anything more than 900 in each of the samples to be shaded with a red colour. Here's how we do it:
First select the cells you want to add the conditional formatting to (in this case it will be all values under ‘samples’) then on your ‘Home’ bar in Excel, look for the button that says “conditional formatting”. In Excel’s default layout this will be toward the center-right of the ribbon (if you can’t see it try expanding your window horizontally). Click this button.
On the list that comes up select “Highlight Cells Rules…” and then “Greater Than…”. As you can see in the list there are a lot of other choices to select from to flag your cells including values above or below a certain value, equal to, occurring within a certain range, or even certain dates and text.
Once we select that a window will pop up our formatting options. In this window we can set the value we want it to flag above (in our case this will be 900).
Click ok and... PRESTO! your table will now automatically highlight everything that has a value of more than 900.
Keep on reading, becuase we can do even more cool things with this feature...
If you notice in the New formatting rule window above, there is an option to select cells instead of entering a value. The cool thing here is you can actually select a value from your table to flag (ie if 900 was in cell H2 of our table and we would reference H2 here and it would flag above whatever value was entered in that cell). We can also use relative references which means we could a flag for a different specification level for each row in the table. First let’s set up a new ‘specification’ column in column H like so:
Next let’s clear the rule we already created. To do that click on the “Conditional Formatting” button up at the top and then select “Manage Rules”. The following window will come up that will allow you to delete or re-order conditional formatting rules. Make sure you change the selection at the top from “Current Selection” to “This worksheet” unless you still have the data cells selected.
To delete you just select the rule and hit the “-“ button down at the lower left. Note the other functions you can do with this window such as change the order of a formatting rule (rules will be applied in the order shown on the list), stop applying rules if a certain rule is true, and add and edit rules.
Once you delete your table should be clear of all formatting again. Let’s apply our specification rule now. First select the data under the sample columns again, then click “Conditional Formatting” > “Highlight Cells Rules” > “Greater Than…” now in the field where we set the value, instead of typing 900, I’m going to add the following cell reference exactly as written "=$H2"
A couple of things to note here… before the H I put a “$”. This is to specify an absolute reference for the column, so are values are always coming from column H. I deliberately do not put a “$” in front of the 2, because I do not want this to be an absolute reference. This means that the rule reference row will shift based on the row it’s being applied to (ie. Row 2 will use the value from H2 to apply the rule, but Row 5 would use the value from H5).
Once you click ok, your table will now be applying the formatting rule based on the values in the H column. If you change the specification values in column H, the flagging rule for each analyte will also automatically change… COOL!
Now let’s apply a different rule to our QC to flag any value that is below 90% or above 100%. First we’ll select the data in the “QC Recovery” column, then we’ll click “Conditional Formatting” > “Highlight Cells Rules” > “Between…”. In the window that pops up, instead of using the default between option, we’re going to change this to ‘Not Between’ as we want anything between 90-100 to not be flagged…
I also changed the conditional formatting to something different than we used before. If you click on the selector next to “Format with:” you can choose from pre-defined formats, or just create a custom one where you select the text/fill/border style for the flagged cells. For this example I’ve set up a quick custom format that flags out of spec cells with a gray background and bold red text.
