Hookups with data: (x, v, h) lookup 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. Once you select Consolidate from the data menu (shortcut keys: […]
3D Reference in Excel
You can create formula in excel that gets data from multiple worksheets without having to specifically reference each sheet by using 3D references. Example of 3D reference: =sum(Holiday1:Holiday3!C3) 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, […]
Super Powerful: 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 and is still often referred to as this.) It will change your life. The Basics You can find Get & Transform on the Data menu. Basically, Get & Transform is […]
Powerful Filter Button Options.
Once you have added Filter buttons to your data, either via the home menu or because you have a table, you have a lot of options on how to use them. Filter by Color – if you have colours on your cells, either from formatting or conditional formatting, you can use this to filter just […]
Getting started with Filter buttons.
Add Filter buttons to your data using Sort and Filter, then Filter. Found on the Home Menu. (Shortcut keys: Alt H, S, F) If you add filters to your data, you can access the sorting from the filter button. Sort A to Z (Smallest to Largest) and Z to A (Largest to Smallest) are available […]
Get Sorted, with Sort
If you have data in a list and you want to change the order, then you need to Sort. The simplest place to find sort is on the Home Menu, Under Sort & Filter. (Alt, H, S, S) If you have pre-selected a cell in the column you want to sort by, just choose Sort […]
Autosum shortcut key
Be Unique
The UNIQUE formula, in #excel, makes short work of removing duplicates from a list. It comes in two main formats, determined by the third input. You can totally remove any items that appear more than once in your list, as below, or just keep one copy of each item. =UNIQUE(The range that has the values […]