How to use icons for Red/Amber/Green indicators in Excel

Welcome to Excel 3 tip Friday from DataMinded. This week we cover how to use icons in conditional formatting, how to refresh the calculations in a workbook, and how to use the COUNTIFS function.

Icon Sets - using conditional formatting to visualise data

Icon sets are a conditional formatting tool in excel to help visualising data in an easy and quick way.

We will use an example to see how to use the Icon sets:

Select the data and click on Conditional Formatting under the Home section in the toolbar and Icons Sets in the drop down. Click on the very first Icon set (the arrows). We will be able to change what icon we use later so we will choose this for now.

1.png

You will see that your data will be formatted with an arrow next to it, but at this point we don’t really know what this arrows mean.

Excel will use a default rule to assign this arrows, but this is not helpful for us so we will go back and change them.

To do this select the data again, under Conditional Formatting click on Manage Rules

In the dialogue box that will appear, click on your rule and click Edit Rule

2.png

First we will decide if we will keep on using the arrows or change to a different icon, we can do this under the Icon Style drop down. Let’s choose the traffic lights for this example:

3.png

Next we will need to decide what rules to apply to this data:

Green- when the cell value is bigger or equal to 40 (number):

4.png

Amber- when the number is smaller than 40 but bigger or equal than 10:

5.png

Red – when the number is smaller than 10, you will see this rule is automatic as we can only divide the numbers in 3 value ranges:

6.png

We can also decide if we want to show the values and the icons (default) or just the icons, you can change this by clicking the Show Icon Only check box. We will display both for our example so no need to check this box:

7.png

The result will be as follows:

8.png
 

You can read more about this feature by visiting the Microsoft guide here.


A Shortcut to calculate the workbook/worksheet.

Occasionally, users of large spreadsheets turn Excel calculation to manual, rather than automatic so that their spreadsheet doesn’t take forever to refresh after every edit. This can be done in Excel’s options menu.

If this is the case, you’ll need to tell Excel to recalculate formulas when required. Just press F9 and all your formulas in all your open workbooks will be calculated.

To only calculate the formulas in the current worksheet, press Shift+F9.

To get a full list of Excel shortcuts, check out the Microsoft help article

 

How to use the COUNTIFS function in excel

The COUNTIFS function in excel calculates the number of cells in a range that fulfil one or more criteria.

The syntax for this function is as follows:

9.png

criteria_range1 this is the first range we will evaluate if our first condition is met

criteria1 – this is the first criteria

criteria_range2 – this is the second range where we will check for our second criteria

criteria2- this is the second criteria that our data needs to meet to be counted

Only the first criteria range and the first criteria are mandatory, the rest is optional and you can have as many criteria as you need.

In the example below we want to find out how many employees live in UK and earning more than £25000:

10.png

criteria_range1: B2:B15

criteria1: “Uk”

criteria_range2: D2:D15

criteria2: >25000 (note “>”&)

This formula will evaluate to 3, 3 employees live in UK and have a salary bigger than 25000.

Let’s add another condition to the formula and find out the number of employees living in UK, have a salary bigger than 25000 but smaller than 65000:

11.png

This formula will evaluate to 2.

Please note, all the criteria ranges must have the same number of columns and rows otherwise the formula will give you an error.

To read more about this function please click here.

Get More Exclusive Content

Sent Directly to your inbox with Excel 3 Tip Friday. A weekly dose of Functions shortcuts and tips for getting more out of Excel.

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit