Conditionally Format Your Data in Excel
Posted 03-24-2013 at 07:21 PM by gregm
Introduction
Hi, I’m Greg and my blogs will deal exclusively with providing insights and recommendations to the VA community of how to get more out of your Excel spreadsheets.
Everyone in the administrative world either uses or has used Excel to some extent. However, I’ve found that a lot of users, if not most, quickly reach a point where they’re baffled or overwhelmed by the complexities and nuances of the application, and as a result, they only use a small subset of the features available and so they fail to leverage the power that the tool has to offer. This can lead to missed opportunities when providing services to clients. What I’m seeking to do with my blog is to explain some of the features that the VA might not be familiar or comfortable with using. Hope you get some benefit out of these.
Conditionally Formatting Data
All users of Excel know how to format a cell or cells of data. By using visual clues and identifiers, it makes the data stand out and indicates some special importance to be attributed to the data. This is fine for a simple one-shot presentation of a data table. What conditional formatting provides is the ability to change the presentation of the data based on criteria that you specify. This can be especially useful where you have a large amount of data to display or else where the data is subject to change; to manually go through all the cells locating the ones that meet your criteria is too time consuming. By putting some conditions around the cells, Excel will take care of the task of applying the logic to determine what should be formatted and how.
Example 1: Performance Results. A good example is a table of salespersons results. Periodically the sales results get updated with new figures and managers always want to see how their sales force is performing in terms of achieving goals as well as being measured against each other. Instead of having to manually search through each entry and highlight based on a client’s criteria, you can set the criteria through a conditional format, and then let Excel worry about whether the criteria has been met. You just enter the data as it changes, Excel will handle the rest. In the example below, the formatting rule applied to sales results quickly identifies those who excel, the mediocre, and the laggards (D’Antonio in this case).
Example 2: Finances and Budgeting. The financial status of an organization can fluctuate over time, and it’s beneficial to highlight pieces of the financial picture as they compare against thresholds and targets. Cash flow getting too low to meet next week’s expenses? Next month’s projections are off target? The use of conditional formatting can draw attention to key areas that need addressed. I work as a volunteer financial coach to provide assistance to people needing help with keeping to their budgets. Below is a sample of a spreadsheet I use with conditional formatting to call attention to areas that are raising flags.
Example 3: Dates and Time. Another example of when conditional formatting can be beneficial is where dates are involved. We all have deadlines or milestones to meet, and the priority of a task is often determined by the current date in relation to a task goal. Conditional logic can be used in conjunction with the Excel date functions to highlight time sensitive areas. The following figure is from a spreadsheet that I use to help manage my ticket sales on a ticket exchange. It’s essential to know when the tickets need sold by or else money will be lost. My website provides the example shown below so that you can download in order to see how the rules are set up in order to control the coloring.
Accessing the Conditional Rules
Excel provides dozens of “standard” rules that the user can apply to their data without having to write any customized rules or formulas. These standard rules are accessed through the ribbon panel (Home -> Conditional Formatting) and the drop down that appears allows for the user to create their own custom rules. Please see the figure below.
What to Watch Out For
1. The rule must evaluate to True or False or the results are unpredictable.
2. If you copy another cell into a conditionally formatted cell, the rule(s) are lost.
The post on my website contains additional screen shots and instruction if you would like more information. Here’s the link to the article: http://excelgrm.com/blog
Thank you,
Greg Matoka
http://www.excelgrm.com
Hi, I’m Greg and my blogs will deal exclusively with providing insights and recommendations to the VA community of how to get more out of your Excel spreadsheets.
Everyone in the administrative world either uses or has used Excel to some extent. However, I’ve found that a lot of users, if not most, quickly reach a point where they’re baffled or overwhelmed by the complexities and nuances of the application, and as a result, they only use a small subset of the features available and so they fail to leverage the power that the tool has to offer. This can lead to missed opportunities when providing services to clients. What I’m seeking to do with my blog is to explain some of the features that the VA might not be familiar or comfortable with using. Hope you get some benefit out of these.
Conditionally Formatting Data
All users of Excel know how to format a cell or cells of data. By using visual clues and identifiers, it makes the data stand out and indicates some special importance to be attributed to the data. This is fine for a simple one-shot presentation of a data table. What conditional formatting provides is the ability to change the presentation of the data based on criteria that you specify. This can be especially useful where you have a large amount of data to display or else where the data is subject to change; to manually go through all the cells locating the ones that meet your criteria is too time consuming. By putting some conditions around the cells, Excel will take care of the task of applying the logic to determine what should be formatted and how.
Example 1: Performance Results. A good example is a table of salespersons results. Periodically the sales results get updated with new figures and managers always want to see how their sales force is performing in terms of achieving goals as well as being measured against each other. Instead of having to manually search through each entry and highlight based on a client’s criteria, you can set the criteria through a conditional format, and then let Excel worry about whether the criteria has been met. You just enter the data as it changes, Excel will handle the rest. In the example below, the formatting rule applied to sales results quickly identifies those who excel, the mediocre, and the laggards (D’Antonio in this case).
Example 2: Finances and Budgeting. The financial status of an organization can fluctuate over time, and it’s beneficial to highlight pieces of the financial picture as they compare against thresholds and targets. Cash flow getting too low to meet next week’s expenses? Next month’s projections are off target? The use of conditional formatting can draw attention to key areas that need addressed. I work as a volunteer financial coach to provide assistance to people needing help with keeping to their budgets. Below is a sample of a spreadsheet I use with conditional formatting to call attention to areas that are raising flags.
Example 3: Dates and Time. Another example of when conditional formatting can be beneficial is where dates are involved. We all have deadlines or milestones to meet, and the priority of a task is often determined by the current date in relation to a task goal. Conditional logic can be used in conjunction with the Excel date functions to highlight time sensitive areas. The following figure is from a spreadsheet that I use to help manage my ticket sales on a ticket exchange. It’s essential to know when the tickets need sold by or else money will be lost. My website provides the example shown below so that you can download in order to see how the rules are set up in order to control the coloring.
Accessing the Conditional Rules
Excel provides dozens of “standard” rules that the user can apply to their data without having to write any customized rules or formulas. These standard rules are accessed through the ribbon panel (Home -> Conditional Formatting) and the drop down that appears allows for the user to create their own custom rules. Please see the figure below.
What to Watch Out For
1. The rule must evaluate to True or False or the results are unpredictable.
2. If you copy another cell into a conditionally formatted cell, the rule(s) are lost.
The post on my website contains additional screen shots and instruction if you would like more information. Here’s the link to the article: http://excelgrm.com/blog
Thank you,
Greg Matoka
http://www.excelgrm.com
Total Comments 8
Comments
|
Excel is my favorite. Great post. I'm glad I checked out the full post on your blog because I didn't know how easy it was to get the arrows. Is that something that's new? I think the last version of Excel that I used was 2010 but never got a chance to explore all the new features. I use Numbers now. Thanks for the info.
|
 
|
|
|
Amanda - Thanks, glad you got something out of it. There are so many features with Excel and that's what I want to share with the VA world because I sincerely believe they could leverage many of them to improve their Excel deliverables. Enjoy.
|
|
|
|
|
Great post. I love working with Excel also.
|
|
|
|
|
Debbie - Thanks for the response. It seems like folks either love working with Excel, they tolerate it or they avoid it. Do you have any particular "pain points" with using Excel, i.e., areas that you don't use or would like to know more about? I enjoy writing these blog posts, and in the future I would like to focus on areas that might be of a more general interest.
-Greg |
|
|
|
|
Hi Greg, I currently do not use Excel as much as I would like to. In my current job there is not much need. I have used it a good bit in the past, especially for creating tables to be imported into geodatabases. I am hoping to use it more once I get my business up and running.
One area that interests me but that I have not used much are the statistical functions. Debbie |
|
|
|
|
Hi Greg! Thank you so much for this wonderful post. It is something that I have forgotten since I graduated a year ago. I appreciate your work and look forward to following your blog.
|
|
|
|
|
Hi, thank you Greg. I use Excel and never used this feature. Very helpful!
|
|
|
|
|
Carlise - Thanks for the comment and I'm glad you got something out of the blog. Even with all the years that I've been working with Excel I'm still learning new features!
-Greg |
|
|
Recent Blog Entries by gregm
- Validating Excel Data (05-23-2013)
- Track and Trend Your Project With Excel (04-09-2013)
- Conditionally Format Your Data in Excel (03-24-2013)
Uncategorized
(3)
