Sorted! Complex sorting with SORTBY
SORTBY is a formula you can use to sort data by multiple columns at once or return one column of data sorted by another column. We can return the Company “Names” in order of market share without having to return the market share column. Anatomy of SORTBY(Array to sort: Just the company names for us, […]
Sorted! Get sorted with SORT function.
Sorting used to have to be done in place, but the Excel SORT functions allows you to present data from one place, in another place sorted by your specifications. Here is a simple example. This is a list of companies, in company order, there listings and market share. What if we want to list the […]
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 […]
Excel App – productivity on the go – Card View.
To make it easier to read data on a smaller screen, you can use Card View on the Excel app. Your background data needs to be in a table for it to work. Here is the table view of the data And, here is the Card View How to select Card View. Once in Card […]
Excel App – Productivity on the go.
Did you know that the excel app has powers that not even the desktop version has? (Update 20/03/2023: Now on desktop version too!) You can take a photo of some data and the Excel app will turn it into a spreadsheet. It doesn’t always get it 100% correct, but depending on the layout of the […]
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 […]