Are you using an Excel spreadsheet to keep track of data and want to highlight certain cells, values or calculations? There are a couple of easy ways to do this in Excel, but it depends on your data and exactly how you want to highlight information.
First, let’s clarify the differences between “Conditional Formatting” and “Cell Styles”.
This type of formatting is based on the information in the cell(s) meeting specific “conditions”. If the values change, then the formatting will change, disappear or appear. This is especially helpful when you are comparing calculated fields, such as totals, percentages or averages and the data being used to complete the calculation is changing.
Cell Styles are not dynamic, that is, they do not change if the data in the cell changes. As long as your data is not evolving or you simply want to draw attention to certain cells, then this is an easy and very quick way to do it. The trick is to be consistent.
Now, let’s look at each option more closely.
Conditional Formatting can be found on the Home tab of the ribbon in Excel. Depending on your version of Excel, it might look a little different, but here it is in Excel 2013:
From this menu, you can choose how you want the formatting of the cell to reflect values or changes. For example, under ‘Highlight Cells Rules”, you can select from traits such as Greater than, Less than, Between or Text Containing, among other choices. For example, if you want to highlight every negative value, select Less than and enter zero as the condition.
If you apply conditional formatting to a series of number, you can use Top/Bottom Rules to highlight cells that fall within the Top 10% of the values.
Now, let’s compare that to Cell Styles.
As I mentioned above, Cell Styles are static. If the value in the cell changes, the formatting will not change unless you do it manually. The menu looks like this in Excel 2013:
You may notice that some of the names that have been assigned to the different formats are somewhat arbitrary. As long as you are consistent, you can use them for whatever you want. Remember that these are just formats that you can apply – they are not tied to the actual values or calculations in the cells. For example, if you apply the “Bad” style to a cell, but then the value in that cell changes and could not be described as “Good”, you need to manually update the formatting. The “Titles and Headings” choices are very handy for keeping the design of your spreadsheets consistent with each other.
When you use Cell Styles, it’s very important to remember one thing. If you apply a Cell Style and it no longer applies, then use the “Normal” style to remove all the formatting. Some of the styles have formatting qualities that are not immediately visible, so if you manually change the colour or font, you may leave behind some formatting without realizing it. Using the “Normal” choice, takes everything back to your standard font and colour settings.
To learn more about formatting and managing your data in Excel, call today to schedule a personal, customized lesson.