Tech and Science News
16 simple, yet powerful Excel functions you need to know
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 you're part of the Excel 99 percent, here are 16 handy Excel functions that will get you well on your way to spreadsheet mastery.
=SUM()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 C2. All you’d do is enter =SUM(A2:B2) into C2, 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. You can also select them with your mouse, but I find it's far easier to type in a range of cells.
=AVERAGE()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 of all monthly earnings.
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.
=MEDIAN()The median and the average are often confused, but they are not the same thing. The median takes a group of numbers such as (in our example below) 2, 6, 15, 31, and 56, and figures out the middle point of the group. In other words, half the numbers are above the median, and half are below. We've compared the median to the average in the screenshot to show you the difference.
Average and median are often confused, but this example shows how the average represents a sum of numbers divided by their quantity. The median represents a middle point in the series of numerals, regardless of how they add up.
To get the median for the above group in column E, in cell E7 we typed the formula =MEDIAN(E2:C6) and hit Enter. In our example case, the median is 15. Had there been an even number of numerals in the series, the median would have been the midpoint between the two middle numbers.
=MIN()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()Max is the counterpart to MIN and does the opposite showing you the largest number contained in a range of cells.
=TRIM()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. The result will be cleaned up text in cell C1. 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.
=COUNT()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 A21-A50, for example, type into cell A51 =COUNT(A21:50) and you’ll have the answer in no time.
Excel's COUNT function can tell you how many cells in a series contain numbers rather than words, dates or other kinds of entries. This can be helpful when you're managing a complex spreadsheet.
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, as well as error values.
=LEN()If you want to count the number of characters in a single cell, including white spaces, check out LEN. Want to know how many characters are in cell A1? Just type =LEN(A1) into a different cell and you’ll find out.
=CONCATENATE()This takes data from two cells and turns it into one. Check out our previous look at Excel functions to see how this can be useful.
=DAYS()Want to know the number of days between two dates in a spreadsheet? If you had for example, September 5, 2018 in cell A4 and December 27, 2018 in A5 just use =DAYS(A5, A4) to get the answer as a negative number--reversing the order of cells would give you a positive number.
=NETWORKDAYS()
Calculating the weekdays in 2019.
Knowing the number of days is great, but if you need to know how many weekdays that range encompasses, turn to NETWORKDAYS (that’s ‘net work days’) instead. This function uses the same format as DAYS, but you need to use an ascending cell order to get a non-negative number. So =NETWORKDAYS(A4, A5) will give you 80 and not -80.
=SQRT()Need to know the square root of 1764? Type =SQRT(1764) into a cell, hit Enter, and you’ll find the answer to life, the universe, and everything—including the example formula.
=NOW()Want to see the current date and time whenever you open a particular worksheet? Type =NOW() into the cell where you want the date and you’re done. If you want to see a future date from right now, you can use something like =NOW()+32. The now function does not take any arguments so don’t put anything in between the brackets.
=ROUND()As its name suggests, this function lets you round off numbers. ROUND requires two arguments: a number or cell, and the number of digits to round to. If you have the number 231.852645 in A1, for example, =ROUND(A1, 0) gives you 232, =ROUND(A1, 1) gives you 232.9, and =ROUND(A1, -1) returns 230.
=ROUNDUP(), =ROUNDDOWN()If you want more direct control over rounding up or down there are functions for that too. ROUNDUP and ROUNDDOWN use exactly the same argument format as ROUND. To learn more about round and its counterparts, check out Microsoft’s support pages.
This article originally published October 20, 2015, and has been updated to the current Excel version, plus we've added more useful functions.
To comment on this article and other PCWorld content, visit our Facebook page or our Twitter feed.