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.
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
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:
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):
Amber- when the number is smaller than 40 but bigger or equal than 10:
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:
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:
The result will be as follows:
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.
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:
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:
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:
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.