In most organizations, to see the activities of staff members throughout the year, traditionally use the control from the authorities, corporate events and review of achieved goals.
Our organization struggled with interns rotating from year to year and constantly working at different paces. It was impossible to measure how quickly these employees were completing tasks and whether their results improved as they learned… until we entered all the data into Excel and realized that this way we could do this task more efficiently.
Conditional Formatting allows you to set a rule (one or more) in an Excel table cell and use it to control the background color, the contents of the cell, or the appearance of its borders. When the cell contains certain data, the rule will work and, as it did in our case, this formatting can help (and helped us in fact) to get to know our employees better.
Good news, conditional formatting is easy. Bad news, you have to enter your data into Excel. Trust me, this will only help you in the long run! Here’s how it works:
- On the Advanced tab Home (Home) in the section Styles (Styles) click Conditional Formatting (Conditional formatting). This example is based on Excel 2007.
- Next, select which rule to apply to the selected cell: Highlight (Highlight), Top/Bottom (first and last values), Data Bars (Histogram), Color Scales (Color bars) or Icon Sets (Icon sets).
To visually analyze data or quickly highlight trends, I like to use ready-made conditional formatting options: Color Scales (Color scales), Data Bars (Histograms) and Icon Sets (Icon sets). All three of these options have an item More Rules (Other rules) as seen in the figure.
Excel automatically selects the largest and smallest value among the data, but you can also do it manually. Using item More Rules (Other Rules), the user has the ability to set a lower and upper limit on the values to which the conditional formatting rule should be applied.
In the following examples, the result of the employee’s work from the example above will be taken as a basis. One of the benefits of using Color Scales (Color scales), Data Bars (Histograms) and Icon Sets (Icon sets) is that the desired data immediately catches the eye.
Icon sets
Icon sets will help you present data using 3-5 categories, and each icon will correspond to one of the ranges of values. Sounds a bit confusing? Imagine arrows, check marks, and checkboxes representing your data visually. The red arrow indicates the largest value, the yellow arrows pointing to the sides indicate the average values, and the green arrow indicates the smallest value.
Color scales
Color scales allow cells to be formatted with two or three colors. If you are a novice Excel user and do not want to complicate your work, then I advise you to choose one of the ready-made color scale options offered in Excel. If you want to capture someone’s imagination or this is your climactic presentation, try using the item More Rules (Other rules) and play a little with the colors. Here you can select colors, assign them to a numerical value, and also choose a two- or three-color scale to use in the table.
We decided to use a three-color scale to see how long it takes an employee to complete (Submit Date) an Assigned Date task. If the time improves (Diff), then the employee will last longer in our organization.
Assigned Date – date of issue of the task, Submit Date – delivery date, Diff – time taken to complete.
Histograms
Bar charts allow you to create a bar chart based on the value of a cell to visually represent its contents. The longer the histogram bar, the larger the value. The shortest bar corresponds to the smallest value. If your table contains a large amount of data and you want to quickly find the largest and smallest values, these histograms can be very useful.
Managing formatting rules
All the examples above can be used at the same time, but for this you need to open a dialog box that controls the rules of conditional formatting. Here you can set up several rules for a range of cells at once, and the values will be checked according to these rules, and in the order in which these rules are set. You can change the order of the rules if you wish. In the event that there is a conflict between the rules, the one that stands above will be applied.
Still not sure if you need it?
Think about how conditional formatting can help you with this:
- Budget: Use color scales or bar charts to show increases or decreases in spending.
- Presentation decoration: Add some shapes using icon sets to make your data speak.
- Indicators prnow: Use icon packs to see which products are in front, middle, and tail in terms of sales.
Excel has many practical features, and this use of conditional formatting is just one of the options that worked well in our office. And how will you use it?