Filter formula for fun.
The filter function enables you to display some information from a list based on specified selection criteria. This simple FILTER formula returns the Names of the people from the list above that are in the team specified in B10, Red in this case. This is an example of a formula that will “SPILL” down into […]
XLOOKUP- You have the power!
XLOOKUP allows you to search through a list, find what you are looking for and return a value from a corresponding list…. and some other things that VLOOKUP couldn’t do. =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929 You need to provide the lookup_value – the thing you are trying to find. The lookup_array – where you are looking for […]
Lookups – x, v, h, and index match.
When you have data in one place, and you want to collect a certain piece of that information and return it somewhere else based on a given value, you probably want a lookup. Depending on the situation there are options about how to do that. The VLOOKUP (the V stands for Vertical) formula used to […]
Maths and Paste Special, together! What?
You can use Paste Special to do maths! Say you have a list of numbers, and for some reason you want to divide them all by 100. You could write a formula to do that, you could type a few answers and use flash fill, or you can use Paste Special. Type 100 into a […]
An easy way to pull information together: Use consolidate.
If you have data in different places that you would like to bring together as one, Consolidate is a great option. You can choose data from different ranges and combine it together either by the headings in the top row, the left column or both. Note that the ranges you select, do not need to […]
3D Reference in Excel
You can create formula in excel that get data from multiple worksheets without having to specifically reference each sheet by using 3D references. In the example above all the values in C3 will be added together from each sheet from Holiday1 to Holiday3. No matter what the sheets are called, if they sit between Holiday1 […]
Power Query
If you are having to collate information from different sources into one place, learn about Get & Transform. (It used to be called Power Query in #excel.) It will change your life. Here is an example: Seven spreadsheets, all with some columns in common, but inconsistent column headings, and the need for the client to […]