Sum, Sum, Sum: Autosum: how to add in Excel.
Adding a column (or row) of numbers is possibly the most common thing you first come across when learning about spreadsheet formulas. There are multiple ways to enter the SUM function. Shortcut Key: ALT + = (That is holding down the ALT key, and the pressing the = key). Make sure you have selected the […]
Be Unique
The UNIQUE formula, in Excel, makes short work of removing duplicates from a list. It comes in two main formats. You can totally remove any items that appear more than once in your list, or just keep one instance of each item from the list. Here is an example where the duplicated items are removed […]
Sorted! Get sorted with SORT function.
Sorting used to have to be done in place, but the Excel SORT function 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, their listings and market share. This is our original data. What if […]
Filter function: Return just the data you want to see.
A lot of users of Excel know about the filter buttons. You can read about them here: The filter function allows you to filter data into another place in your workbook, leaving your original data as is. Just like the filter buttons you can display some information from a list based on specified selection criteria. […]
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. (To see a shorter introduction to XLOOKUP and how it compares to other LOOKUPS, see Hookups with data. =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 […]
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 […]