Excel Spreadsheet Resources – most useful formulas and features

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 / FeatureWhat it’s used for
SUM / AVERAGE / MIN / MAXAdd up the values in a range of cells, or averages them, or shows the minimum or maximum values.
COUNT / COUNTA / COUNTBLANKCounts 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.
SUMIFSAdd 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.
COUNTIFSShow 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 / XLOOKUPVLOOKUP: 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.
IFOutput 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 / MIDExtract text data from other cells eg the left 3 characters, the right 5 characters, or the middle 4 characters starting at character 6.
CONCATENATE / TEXTJOINJoin text and value cells together eg join the First name and Surname cells into a full name cell.
TEXTSPLIT / TEXTBEFORE / TEXTAFTERSplits 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.
SUBSTITUTEReplace part of text in one cell’s value with a different value (including blank).
ROUND / ROUNDUP / ROUNDDOWNMake a value go up or down to the nearest decimal place eg to the nearest whole number or nearest 0.1.
FLOOR / CEILINGMake a value go up or down to the nearest multiple eg to the nearest multiple of 4.
AGGREGATEApply 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 ValidationShows dropdown lists of permitted values to prevent manual errors, or sets permitted values such as numbers, dates or text length.
SortSorts the selected range of cells into increasing or decreasing order. Can have multiple levels of columns used for sorting.
FiltersAllows filtering of data columns to just show the value ranges you want to see.
Pivot TablesFantastic 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 ProtectionStop people changing values in cells by locking them. Only applied when the sheet is protected.
Conditional FormattingMake the cells change colour (or have little bar charts in them) based on the value within the cells or nearby cells.
Charts / GraphsDisplay your data in graphical form eg bar charts, pie charts, waterfall charts.
Freeze PanesStop 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.
MacrosPerform 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.
Scroll to Top