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 […]
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 […]
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 […]