Jefferson City,
08:00 AM

Microsoft Excel – spot trends and key points with conditional formatting

Check out this quick video tip about conditional formatting in Microsoft Excel from the Affinity Insight course live training session Excel - Readily Readable Data. 

By Danielle DavisRoe, Affinity Consulting  

Color draws our eyes and attention: stop signs are red, construction workers wear yellow, and sticky notes are bright and colorful. You can also use color to draw attention to your data with Excel's conditional formatting feature. Conditional formatting uses rules to color code your data and automatically update the colors as your data changes. 

To get started, select the cells that you want to be conditionally formatted. On the Home ribbon, in the Styles group, click on Conditional Formatting to see your options. 

1. Amounts greater than or less than a certain amount

Use if you have a list of numbers (for example, fair market values of assets in an estate or marital property in a divorce) and need to know if any of them are worth more than a certain amount. 

2. Specific values

Use if you want to draw attention to cells with a specific value (such as property in an estate or cells containing "N/A".) 

3. Highest or lowest amounts

Use if you have a list of numbers (for example, a list of attorneys and their billable hours) and want to quickly see which ones are the largest or smallest. You can format a specific percentage or certain number of cells that fall at the top or bottom of the range. 

4. Color scales

Use color scales to color code a series of numbers based on their values. When color coding using red, yellow, green, the largest number is green, and the smallest number is red. The numbers in between are assigned a shade based on where they fall between the lowest and highest numbers. 

5. Custom rules

Set up conditional formatting to work based on custom rules that you create. 

6. Managing rules

You can apply multiple rules to the same cells. The Rules Manager allows you control the order in which they apply. 


If you’re interested in additional videos and lessons like the one above, head over to Affinity Insight and register. As a Missouri Bar member, you get unlimited free access to the video library. Use the coupon code MOBARMEMBER at checkout to drop the cost to $0. Affinity Insight Standard is included with your bar membership at no cost to you. 

Looking for additional resources to help open, build, manage, protect, or wind down a practice? Missouri Bar members have access to more than 100 whitepapers, comparison charts, checklists and more available from Missouri Bar Practice Management