This is the third post in a series on how nonprofits can leverage Excel to better make sense of, share, and use their data. The IllumiLab is grateful to have one of our clients, Megan Ondr-Cooper from CASA of St. Louis, sharing her enthusiasm and expertise with us. Here’s Megan . . .
Today I’ll be sharing conditional formatting in Excel. This is the third post in a series of five that covers tips and tricks on Microsoft Excel. Check out the last post to learn more about filtering.
When I begin analyzing data, I start to look for meaning. What do I need to pay attention to? Which data stand out? Are there any trends here? Conditional formatting helps me see and start to make sense of my data. I also use conditional formatting when sharing data with others. When I put a spreadsheet filled with a lot of numbers and text in front of staff or leaders, they can get lost or lose interest (especially if they’re not as excited about the data as I am, which is pretty much always the case because of my uncommon enthusiasm for spreadsheets). Conditional formatting can make content more digestible by formatting cells (font color, shading, font, etc.) based on the cell’s contents. This is a great way to draw attention to particular cells (such as duplicates), compare cells, or to visualize patterns.
There are various types of conditional formatting rules that tell Excel, “If this, then format like this.”
- Highlight Cell rules tell Excel to highlight cells that meet certain criteria with certain font and background colors. This is a great way to draw your attention to cells that contain high, low, or missing values. It’s automated color-coding!
- Top/Bottom rules help you compare and rank cells. Use these to identify your top 10 performers, your 10 lowest cost items, etc. The IllumiLab uses this in our monthly dashboard to highlight the 10 blog posts with the most views each month.
- Color Scales and Data Bars help you easily visualize patterns in your data. Color scales can turn your data into a heat map and help you understand the spread of the data.
Conditional Formatting with Data Bars
The tool that I use most often are data bars. Data bars help you quickly see the variation between multiple cells containing numbers. I use data bars both in reports to create the look of a bar chart within the cells of my worksheet and also to quickly see the pattern of numeric data during analysis. Below is an example of how adding data bars can help you quickly understand your data.
Without data bars, you must review each row and then figure out which staff member has the largest caseload, which staff member has the lowest caseload, and how big the spread is between them. With data bars, you can quickly see that Williams has the largest caseload and that there’s a lot of variety between the staff caseloads.
Many different modification options are available for data bars. When I use conditional formatting in my reports, I often remove cell borders, add data bars to cells showing the bar only (and not cell values), and then add values to a column to the right to create a clean, simple visualization (below).
Click here to learn to use conditional formatting.
Try it out!
The next time you bring data to share with staff at a meeting, try adding conditional formatting to help staff quickly make sense of the information in front of them. Minimizing time spent explaining the data will allow for more time to discuss the meaning of the data – the key to using data for decision-making and improvements!
You won’t want to miss the next post in the series. When I shared my next Excel tip with Sarah at The IllumiLab, her response was, “OMG! I never knew this! This is a game changer!”