Conditional formatting is a powerful way to incorporate formatting into your data analysis. When you use conditional formatting in Excel, you make your formatting dynamic, which enables your formatting to complement and add to your data analysis. If you use the four tips described in the video, you can quickly and easily enhance your data analysis and make it more useful for your end-users.
1. Pick Your Favorites With Highlight Cells
The Highlight Cells conditional formatting option allows you to easily identify subsets of data within your data set. If you want to quickly find all of the values in your data that are greater than 10, simply use Highlight Cells and use conditional formatting to make all cells with a value greater than 10 red. Highlight Cells also offers several text options, so you can use this function with either text strings or numbers.
2. Find the Outliers With Top/Bottom Rules
If you just need to identify the outliers in your data, simply pick the Top/Bottom Rules conditional formatting option. You can use this option to quickly identify the values at the top and/or bottom of your range. You can also use it to simply identify values that are above/below your range’s average.
3. Quickly Rank Your Data With Data Bars
If you are more graphically inclined, Data Bars are an invaluable conditional formatting option. With Data Bars, you can quickly and easily create mini “graphs” inside each cell in your range that allow you to see how individual values rank within your range (cells with larger values will have larger Data Bars and vice-versa). Data Bars are a great way to add a functional graphic to your analysis.
4. Add a Splash of Color With Color Scales
Color Scales work much like Data Bars, except they use different shades of color to indicate ranking rather than a bar graph. You can use the Color Scales conditional formatting option to quickly add a splash of color to your analysis while allowing your end-users to quickly see where individual items rank within the range.
Become a Master of Conditional Formatting
If you follow these four tips, you will be well on your way to learning how to master the conditional formatting feature in Excel. However, there is much more to conditional formatting than what is presented here.
To learn more, sign up for our free email newsletter, where you’ll get Excel tips and tricks delivered directly into your inbox. For a limited time, you’ll also receive a free Gantt Chart template when you sign up!