Setting up “Courteous” (and Efficient and Accurate) Data Entry
This Tech Talk blog shares how to create custom, Conditional Formatting in a Microsoft Excel template to make manual data entry efficient and accurate. See the background blog on the user interface design motivation for this, and let us know if we can help you with your data situation!
Excel and Google sheets both allow creating Conditional Formatting rules for a range of cells to highlight specific cells when a condition is true based on a custom formula. In the example below, the data entry column, Panel_Score (Column D), needs two such rules –one to set the dark blue box highlighting the next cell as values are entered, and another to highlight remaining cells in light blue. The example also shows a rule that further highlights the current row by putting light blue shading on columns A to C adjacent to the darker blue box. With this in place, the dark blue highlighting will move as new data are entered.
In Excel, the Conditional Formatting feature is in the Home menu tab. Our formatting rules will use relative references to look at what’s going on in the current and previous row cells in the column where data is being entered. The best way to avoid getting confused while setting this up is to create the rules in just cell D2 and then paste its formatting (Copy / Paste Special / Formats) to the remaining cells in the column.
To start, type in values and column headings to make a mockup of the template shown above. Select cell D2 and choose Home tab / Conditional Formatting / New Rule. Click on “Use a formula to determine which cells to format” as shown above. For the dark blue box, the formula needs to check whether two conditions are true. We want the dark blue highlight if the previous cell contains a value and if the current cell is still blank. Since both need to be true, we use Excel’s AND() function to contain them. We want NOT(ISBLANK(D1)) and ISBLANK(D2) –with the two conditions separated by a comma in the AND function as shown below. An “=” character is required at the beginning of the formula. After entering the formula. Set the Format to fill cells with blue and click the OK button
That takes care of the darker blue highlighting on the current cell. To also highlight the remaining cells in the column with light blue, we need to enter a second rule. That one’s formula is shown below in the dialog box –to the left of the light blue shading. It is not an issue here but note that Conditional Formatting rules apply themselves in order –with the last one taking priority if multiple rules evaluate to True for a cell. Once you have created both rules, you can copy D2 and Paste its format to the other cells.
Conditional formatting gets a lot of glory as a feature for creating heat maps and other highlights on regions of cells, but this shows how to make it useful as a user interface guide for data entry. Similarly, it can be used to do limits checking and highlight non-valid values such as a ‘6’ when the data should conform to a 0 to 5 scale.