Power users like to talk about how powerful and ingenious Excel is, what happens with its pivot tables, nested formulas, and Boolean logic. But many of us barely know how to find the Autosum feature, let alone the Excel features for building powerful formulas.
If you're part of Excel 99 percent, there are 16 convenient Excel functions available. How to get to Spreadsheet
= SUM ()
The first function that everyone should know is Excel's basic calculation. For example, suppose you want the numbers in cell A2 and cell B2 in cell C2 to be added together. Just type
= SUM (A2: B2) in C2 and press Enter. In an instant you would see the sum of the two cells.
You can use this feature and most of the features listed here to include as many cells as you need. You can also select them with the mouse, but it is much easier to enter a series of cells.
= AVERAGE ()
Average does exactly what it says and works much like SUM. If you have a worksheet with your monthly earnings for the last year in columns A2 to A1
= AVERAGE (A2: A13)in A14 to get the average of all monthly earnings.
Once again, you can also use your mouse pointer to highlight a series of cells by clicking and dragging, or use Ctrl + Click to manually select individual cells
= MEDIAN ()  The median and the average are often confused, but they are not the same. The median takes a group of numbers such as 2, 6, 15, 31 and 56 (in our example below) and finds the center of the group. In other words, half of the numbers are above the median and the other half below. We compared the median with the average in the screenshot to show the difference.
In order to obtain the median for the above group in column E, we type in formula E [E9007] = MEDIAN (E2: C6) and Enter in cell E7. In our example, the median is 15. If there were an even number of numbers 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. With
= MIN (B3: B39) you get the smallest number contained in these cells.
= MAX ()
Max is the counterpart to MIN and the opposite shows you the largest number
= TRIM ()
When you copy text from another program in Excel, it can often get too excessive There are spaces that turn your spreadsheet into a visual horror. TRIM can help you to clean it up.
TRIM can only handle text from a single cell. So start by cleaning up the text in cell B1, for example, by typing
= TRIM (B1) in cell C1. The result will be clean text in cell C1. Repeat the function for all other cells that you need to clean up.
If you want to clean up line breaks, try CLEAN instead of TRIM
= COUNT ()
If you want to know how many cells in a given range contain numbers that do not count by hand, just use the COUNT function. For example, if you have a mix of numbers and text in cells A21-A50, enter
= COUNT (A21: 50) in cell A51 and you'll get the answer in no time.
= COUNTA ()
Similar to the example above, you can use
= COUNTA () to count the number of cells in a given range that contain characters such as numbers, text, or symbols and error values
= LEN ()
If you want to count the number of characters in a single cell, including spaces, read LEN. Would you like to know how many characters are in cell A1? Just type
= LEN (A1) into another cell and you'll find out
= CONCATENATE ()
This takes data from two cells and turns them into one. Take a look at our previous overview of Excel features to see how this can be useful.
= DAYS ()
Want to know how many days between two dates in a table? For example, if you had in cell A4 on September 5, 2018, and A5 on December 27, 2018, just use
= DAYS (A5, A4) to get the answer as a negative number – the order of Cells will be reversed would give you a positive number.
= NETWORK DAYS ()
Knowing the number of days is great, but if you want to know how many days of the week this area includes, change Instead, go to NETWORKDAYS (these are "net work days"). This function uses the same format as DAYS, but you must use an ascending cell order to get a non-negative number. So
= NETWORK DAYS (A4, A5) you get 80 and not -80
= SQRT ()
You need to know the square root of 1764? Enter
= SQRT (1764) in a cell press Enter and you will find the answer to life, the universe and everything – including the example formula.
= NOW ()
Want to see the current date and time when you open a specific worksheet? Type =
NOW () in the cell where you want the date and you're done. If you want to see a future date from now on you can use something like
= NOW () + 32 . The now function does not accept arguments, so do not put anything between parentheses
= ROUND ()
As the name implies, you can use this function to round numbers down. ROUND needs two arguments: a number or cell and the number of digits to round to. For example, if you have the number 231.852645 in A1,
= ROUND (A1, 0) returns 232,
= ROUND (A1, 1) returns 232.9 and
= ROUND (A1, -1) returns 230.
= ROUNDUP (), = ROUNDDOWN ()
If you want more direct control over rounding, there are also functions for it. ROUNDUP and ROUNDDOWN use exactly the same argument format as ROUND. For more information about the round and its counterparts, visit the Microsoft support pages.
This article was originally published on October 20, 2015 and has been updated to the current version of Excel, and we have added other useful features. 19659050] To comment on this article and other PCWorld content, visit our Facebook page or our Twitter feed.