Subscriber Benefit
As a subscriber you can listen to articles at work, in the car, or while you work out. Subscribe Now
Scrolling through rows of billing data, discovery deadlines, and other Excel related information to manually highlight follow-up items can feel like a never-ending chore.
With the use of Excel’s conditional formatting, you can set the rules once and let Excel do the highlighting for you, automatically updating the records that meet your criteria even when values change.
What is conditional formatting?
Conditional formatting is an option within Excel that can automatically change the appearance of a cell including background color, font, borders, if the value meets certain rules you set. Essentially, it is asking Excel to automatically update cells in situations like the following:
• Update all duplicate case numbers with an orange background
• Flag with a yellow background all discovery deadlines next week
• Highlight any invoice over $10,000 with a red, bold font
Applying the formatting
There are three key options to set.
Location: First, we must tell Excel where to look. The easiest way to start is by selecting the cells that should be monitored. This can vary depending upon the rule type.
For example, if only the cells in Column D marked “Completed” should be green, then start by selecting just Column D.
If the entire corresponding row should be marked green for values marked “Completed” in Column D then select the entire portion of the dataset that should be updated in the event of a match.
The location can be adjusted at any time through the conditional formatting menu by updating the range of the desired rule.
Rule types: Next, we must set up rules to tell Excel what to monitor. Some rules compare the cell to a specified value set by you, while others compare the cell value to that of other cell values. While there are many rule types available, some popular options include:
Duplicates: Identify all duplicate Case Numbers
Blanks: Highlight in yellow all blank cells in the Date of Birth column
Text: Mark all cells with the word “privilege”
Dates: Flag in red all tasks with a deadline within the next week
Formula: Highlight the entire row if the status is marked “Complete”
In each of these scenarios, the color coding will automatically update as the value in the cells change or no longer meet the criteria. For example, entering the date of birth in a blank cell would automatically remove the yellow highlight. Likewise, if the spreadsheet is opened again next week and the date of the flagged task is now in the past, it will no longer be red.
While most of the options provide a rather straightforward approach, the formula can be a bit confusing, especially for those less familiar with formulas in Excel. Searching online for the desired conditional formatting formula will often get you very close to what you need with maybe a slight variation in variables.
Format types: In this section of the rule box, you will tell Excel how to format the data to stand out if it matches the rule criteria. By clicking the “Format” button, you can select any of the following and combine as desired:
Fill: The most popular option which updates the background color of cell
Font: Another popular option which updates the font type, color, size, or style
Border: Format any or all border lines around the cell
Number formatting: Format as a date, currency, percentage, or another format
Examples
By default, most of the rules will only highlight the specific cell that is responsive to the rule that was set. Examples include:
Update all duplicate case numbers with an orange background: Upon selecting the column to monitor. Choose “Format only unique or duplicate values”. Select duplicate and choose an orange background fill.
Flag with a yellow background all discovery deadlines next week: Select the deadline column, then choose “Format only cells that contain” then choose “Dates occurring” and set to “Next week”. Select a yellow background fill.
Using the “Formula” option can highlight the entire corresponding row of a matching value.
Highlight the entire row green if Column D is marked “Complete:” Select the entire data to potentially be updated. Choose “Use a formula to determine which cells to format”. Enter the following formula: =$D2=”complete” Select a green background fill.
Automate your Excel
Once conditional formatting has been applied, Excel will continue to monitor and update the dataset every time the spreadsheet is opened without any additional human intervention. This provides easy visual cues that can be caught at-a-glance or with the use of sorting or filtering, allowing you to stay in the details without consuming so much time.•
__________
Deanna Marquez ([email protected]) is a co-owner of the Indianapolis-based legal technology company, Modern Information Solutions, LLC.
Please enable JavaScript to view this content.