Here’s the formulas and features that the one to learn first and thoroughly to increase your skills and speed in data manipulation and analysis:
| Formula / Feature | What it’s used for |
|---|---|
| SUM / AVERAGE / MIN / MAX | Add up the values in a range of cells, or averages them, or shows the minimum or maximum values. |
| COUNT / COUNTA / COUNTBLANK | Counts the number of cells in a range which have a number in, or has any number of text value in, or how many are blank. |
| SUMIFS | Add up the values in a range of cells where another set of cells matches your criteria. You can have multiple criteria. Dates also work as criteria. Eg add up the values of sales in column C where the region in column A is equal XXXX and the town in column B is equal to YYYY. |
| COUNTIFS | Show the number of times that a range of cells matches your criteria. You can have multiple criteria. Dates also work as criteria. eg how many times is the word XXXX shown in column A at the same time that word YYYY is in column B. |
| VLOOKUP / HLOOKUP / XLOOKUP | VLOOKUP: Extract matching data from a specified column of a table where a specific value matches something you’re looking for in the left hand column. Also can be used to find value in the table based on your search value being between ranges of values HLOOKUP is the same as VLOOKUP but using rows instead of columns. XLOOKUP is the new version for Excel 365/2021+ onwards which is more flexible with more options. |
| IF | Output different values based on whether certain criteria are met eg if one cells is equal to a specific value then put a 1 and if not put a 0. |
| LEFT / RIGHT / MID | Extract text data from other cells eg the left 3 characters, the right 5 characters, or the middle 4 characters starting at character 6. |
| CONCATENATE / TEXTJOIN | Join text and value cells together eg join the First name and Surname cells into a full name cell. |
| TEXTSPLIT / TEXTBEFORE / TEXTAFTER | Splits and separates a cell’s text values based on a delimiter eg splits a cell that has a comma or dash separating the data inside it. |
| SUBSTITUTE | Replace part of text in one cell’s value with a different value (including blank). |
| ROUND / ROUNDUP / ROUNDDOWN | Make a value go up or down to the nearest decimal place eg to the nearest whole number or nearest 0.1. |
| FLOOR / CEILING | Make a value go up or down to the nearest multiple eg to the nearest multiple of 4. |
| AGGREGATE | Apply functions such AVERAGE, SUM, COUNT, MAX or MIN to a range of cells but can choose to ignore errors or hidden rows. Only for Excel 2010 and later to replace SUBTOTAL. |
| Data Validation | Shows dropdown lists of permitted values to prevent manual errors, or sets permitted values such as numbers, dates or text length. |
| Sort | Sorts the selected range of cells into increasing or decreasing order. Can have multiple levels of columns used for sorting. |
| Filters | Allows filtering of data columns to just show the value ranges you want to see. |
| Pivot Tables | Fantastic way of quickly analysing data to display summaries by any values in any columns and can be easily filtered to show what you want to see. The right data layout is vital. Pivot Charts are automatically linked. |
| Cell Locked property with Sheet Protection | Stop people changing values in cells by locking them. Only applied when the sheet is protected. |
| Conditional Formatting | Make the cells change colour (or have little bar charts in them) based on the value within the cells or nearby cells. |
| Charts / Graphs | Display your data in graphical form eg bar charts, pie charts, waterfall charts. |
| Freeze Panes | Stop the rows above and the columns to the left of the selected cell from moving so they stay visible on the screen when you move around the rest of the data. |
| Macros | Perform almost anything that you can’t already do with formulas and automate repetitive tasks. Needs computer coding skills to perform anything but the basic tasks. |