Credit: Image: Rob Schultz
Power users love to talk about how powerful and awesome Excel is, what with its Pivot Tables, nested formulas, and Boolean logic. But many of us barely know how to find the Autosum feature, let alone use Excel’s functions to create powerful formulas.
If that’s you, here are 15 handy Excel functions that will get you well on your way to spreadsheet mastery.
The first function anyone should know tells Excel to carry out basic arithmetic. Let’s say you wanted the numbers in cell A2 and cell B2 to be added together in cell B3. All you’d do is enter
=SUM into B3, click the
=SUM entry in the pop-up that appears, Control + Click in cell A2 and B2 to highlight both, then hit Enter. In an instant you’d see sum of the two cells appear.
You can use this function, and most of the functions listed here, to encompass as many cells as you require—just select all the ones you need included.
Average does exactly what it says and works similarly to SUM. If you have a worksheet containing your monthly earnings over the past year in columns A2 through A13, type
=AVERAGE(A2:A13) into A14 to get the average.
Again, you can also use your mouse cursor to click-and-drag highlight a range of cells, or use Control + Click to handpick individual cells.
If you need to find the smallest number in a range of cells, MIN can help you do that. Using
=MIN(B3:B39) will give you the smallest number contained in those cells.
Max is the counterpart to MIN and does the opposite showing you the largest number contained in a range of cells.
If you copy text from another program into Excel you can often end up with excess whitespace that turns your spreadsheet into a visual horror. TRIM can help you clean it up.
TRIM can only deal with text from a single cell. So start by cleaning up the text in cell B1 by typing
=TRIM(B1) into cell C1, for example, and then repeat the function for any other cells you need tidied up.
If you’re looking to clean up line breaks, try CLEAN instead of TRIM.
If you need to know how many cells in a given range contain numbers, don’t bother counting by hand—just use the COUNT function. If you have a mix of numbers and text in cells A1-A20, for example, type into cell A21
=COUNT(A1:20) and you’ll have the answer in no time.
Similar to the above example, you can use
=COUNTA() to count the number of cells in a given range that contain characters such as numbers, text, or symbols.
Sign up for CIO Asia eNewsletters.