One of my all-time favorite quotes about data visualization comes from legend Edward Tufte:
At the heart of quantitative reasoning lies a single question: ‘Compared to what?’
In Excel, conditional formatting proves to be an invaluable tool for this purpose, allowing users to easily explore data variations and trends. Furthermore, Copilot remarkably streamlines this process. Let’s give it a try with a dataset about the largest cities in the United States:
Make sure that you load the data properly into Copilot first before continuing:
Start by selecting “Highlight” from the menu of options in Copilot, just to prime it a bit about what we actually want to achieve.
To begin, I’ll highlight each city that is also a state capital. By instructing Copilot and specifying the highlight color, I make the task more efficient.
Excellent work! The key here is to be very intentional with the type of conditional formatting you want to add. Be specific, for example, name the columns specifically and state what should be formatted—whether it’s the fill, the text, or something else.
Let’s try another prompt: bold the three highest values in the density column. Again, we are going to try to be as precise with our language here as possible.
Similarly, underlining the values corresponding to areas ranging from 300 to 400 square miles can be achieved effortlessly:
Excel provides a wide range of conditional formatting options to enhance your data visualization. For example, we can introduce blue data bars to represent population figures.
When you need to manage conditional formatting rules, such as deleting, editing, or modifying them, Excel’s legacy tools are your go-to resource. Navigate to the Home tab, select the Styles group, and choose Conditional Formatting > Manage Rules from the ribbon:
While asking Copilot to modify specific conditional formats often yields unsatisfactory results, you can effectively use it to remove all conditional formatting from a table.
Conclusion
By leveraging Excel and Copilot for conditional formatting, you can transform complex datasets into compelling visual narratives. This approach facilitates immediate understanding and enables quicker, more informed decisions.
However, this does suffer from a bit of the paradox that affects many Copilot features—at least at the time of writing. The primary beneficiaries are absolute beginners, as most individuals with even a minimal amount of Excel experience are likely already familiar with conditional formatting or can figure it out independently. If you are truly a novice to conditional formatting, however, you might not be familiar with enough of the available tools even to prompt Copilot effectively. For example, how would you know to ask for “Data Bars,” or to specify whether you want the font or fill colored, if you’ve never used conditional formatting before?
What questions do you have about using conditional formatting with Copilot in Excel? Or what else would you like to explore with Copilot in Excel? Let me know in the comments.
Leave a Reply