Tips for Using Conditional Formatting in Excel

Posted by 110 Consulting on Wed, May 21, 2014 @ 08:05 AM

Using Conditional Formatting in ExcelIn almost every industry, Excel spreadsheets are the standard for viewing and working with data due to the ease of use and visual nature of Excel.  SQL databases offer more efficient and powerful ways to analyze large amounts of data, but Excel is hard to beat when it comes to visualizations, especially for self-service analytics.

Why Use Conditional Formatting?

Of course, how the data is formatted and laid out in an Excel spreadsheet makes a substantial difference in the usability of the data.  Whether you are dealing with simple datasets or complex workbooks, conditional formatting is an easy way to make important data points visible and actionable.

Conditional formatting is fairly simple to set up; just select the columns or rows you want to set the rules against and use the conditional formatting options to select the formatting appropriate for your needs, e.g. highlight your smallest numbers to find bottom performers or highlight the largest numbers to find top performers.  Conditional formatting can be leveraged to call out any data point that is significant. However, it’s important to not overuse it or your spreadsheet could turn into a rainbow of colors, making it impossible to know which color is actionable or important.

Another tip to using conditional formatting is to combine it with filters.  If you are trying to find a specific data point that is not filterable by searching or sorting, you can set conditions that match that data point. Then you can filter by the color you selected instead of searching or sorting.  A common example of this is filtering duplicates: there is no option to sort by duplicate values but if you use conditional formatting on all duplicate values, you can then filter on that color to see where your duplicates are.  This works best for smaller datasets. If you are trying to deal with duplicates in large amounts of data, SQL will provide a greater array of options to manage your data.

Importance of Optimizing Your Worksheets

If you use a lot of conditional formatting in your workbook, you may begin to experience significant lag over time.  The usual suspect is duplicate formatting rules, created by Excel’s smart auto formatting and formulas.  Auto formatting is a great tool, but the downside is that if you are copying cells with a conditional formatting rule Excel will automatically copy the formatting rule, which can result in redundant formatting rules in your table. 

Fortunately, Excel makes it easy to prevent this with the advanced paste options.  To view the advanced paste options in the Excel ribbon, simply click the down arrow and chose “Values (V)” under the “Paste Values” header.  When using keyboard shortcuts, start with the well-known “Crtl-V” to paste and then press “Ctrl” to open the advanced paste menu and press “V” again.  This will retroactively change your paste style, but only if you select the advanced option right after you paste. 

In Conclusion

Using conditional formatting in Excel can save you a lot of time as it will update in real time and allow you to skip many steps for individual formatting. The greatest benefit of all is that you'll ultimately be left with more time to actually analyze the data... which is the point in the first place. 

Great business intelligence and data management isn't always easy, but our eBook can help. Click here to download a free copy of Successful Business Intelligence, Start to Finish.


New Call-to-Action

Tags: business intelligence, Microsoft, Business Analytics

Follow Me

Subscribe via E-mail


Custom Search
Sort by:

Download the Leverage Nearshore Whitepaper Here:

Nearshore Outsourcing

Download Our Free BI Whitepaper Here:

New Call-to-Action