Using the IFS() function to work with multiple conditions
IFS() function In this tutorial I want to show you how to use the IFS() function to do a nested if. It has always been possible to do a nested if. It used to be seven levels. Then it moved to 64 levels (Don’t.Do.That). The current IFS() function has 127 levels. REALLY. Don’t. Do. That. […]
How to link files and change the file
How to link external files into an Excel file and then change if required A common requirement is to pull data from one Excel file into another. You should begin by setting the scene. i.e. make sure you have all the files you need open. Let’s call those source files. Our source files are: Mayo […]
02 – Recommended Pivot Tables and Analyze data – Let Excel help you get started
One of the most common questions I get asked in class about pivot tables is “Well, how do I know what goes where?” . In previous versions of Excel I would have recommended the following.. Go to the ever-wonderful Google and type in “How to plan a pivot table in Excel” (the “in Excel piece” […]
Some lesser known charts in Excel – Treemap and Funnel
Excel has really upped its games around charts in the last couple of editions but there are some charts that are not so well known. In this blog post I am going to introduce you to two of them: The Tree Map and the funnel. You can use this file to practise on and here […]
Two ways to group your numbers in Excel
I was recently asked in a class about grouping entries together. In that case it was fish lengths but what I show here applies to a wide range of options. The first method shows you how to use the inbuilt facilities of Excel pivot tables to group numbers into consistent size “buckets”. The second method […]
How to use Index Match
In this tutorial what I am looking at is the alternative to Vlookup() – Index/Match which is – astonishingly enough – a combination of two functions: Index and Match. I give an explanation of Match in this tutorial as well. You can download the completed file here.
17 – How to use the if function in Excel
This blog post came about as the result of my own incompetence. I made a rookie error – FORGOT TO PRESS RECORD….So this is an example of an exercise I would use in class. You can download the completed file here.. and if you are feeling full of enthusiasm – you can download the uncompleted […]
16 – Conditional formatting in Excel
So for this I am going to walk you through one of the exercises I do with my learners when I am teaching Conditional formatting – particularly using the Highlight Cells Rules options. This is a file with the exercises in it. Here are the questions The number of entries you should get is written […]
11 – How to freeze top row of large data set – View | Freeze Panes
Ah, yes that joyful scenario. You have a list – a great big juicy list and you want to scroll down that list but oh no my headings have disappeared…WHAT DO I DOOOOO? Well I *have* heard of people who have sellotaped the headings onto their monitor…..and that is indeed one option. However why don’t […]
06 – How to create a chart with a max/min/target line
The secret – usually forgotten sauce to creating charts in Excel is to make sure your data is properly organised – no blank rows, no blank columns and you need to go all – what I call – Judge Judy on it – “The data, all the data and nothing but the data”. This video […]