Excel has over 475 formulas in its Functions Library, from simple mathematics to very complex statistical, logical, and engineering tasks such as IF statements (one of our perennial favorite stories); AND, OR, NOT functions; COUNT, AVERAGE, and MIN/MAX.
The basic functions covered below are the top 12 most popular formulas in Excel. To help you learn, we’ve also provided a spreadsheet with all the formula examples we cover below.
There are 24 Date and Time functions listed on the drop-down menu under Formulas > Date & Time: 11 Date formats, 10 Time formats, and as many user-defined custom formats you can create. The TODAY function reveals the current month, day, and year; while the NOW function reveals the current month, day, year, and time of day. This is a handy function if you’re one of those individuals who always forgets to date your work.
1. Enter the following formula in cell A1: =TODAY() and press Enter.
2. Next, type over that function in A1 with =NOW().
IMPORTANT NOTE: Why type over? In order for these two formulas to work properly, they must be entered in the Home cell, that is, A1, otherwise, they won’t update automatically when the spreadsheet recalculates. Press Shift- F9 to calculate/recalculate the active spreadsheet only, or press F9 for the entire workbook.
After you enter one of these functions in A1, you can then reformat the Date and Time or use the system default. The default format for the TODAY function is 8/29/18, and the default for NOW is 8/29/18 21:57. If these don’t work for you, change them.
3. Position your cursor on the Date or Time you want changed and choose Home > Format > Format Cells.
4. In the Format Cells dialog window, choose Date (or Time) from the Category panel under the Number tab.
5. Scroll through the list of Date/Time formats in the Type dialog pane and select the format that best fits your project.
2. SUM functions
Probably the most frequently used function in Excel (or any other spreadsheet program), =SUM does just that: It sums a column, row, or range of numbers—but it doesn’t just sum. It also subtracts, multiplies, divides, and uses any of the comparison operators to return a result of 1 (true) or 0 (false).
You can also get the same results just using the plus (+) sign in place of the function SUM. For example, both of these formulas produce the same answer: =SUM(J7*9) and =+(J7*9). In the spreadsheet graphic below, notice that cells E3 through E8 use the SUM function, while cells E9 through E9 through E14 use the plus (+) sign and the results are the same.
You can enter the SUM function (or + sign) manually or select it from the Ribbon menu under Formulas > Math & Trig (button), then choose from the drop-down list; or choose (from the Ribbon menu) Formulas > Insert Function, then scroll down the list and select it from there.
If you just want to add a single column of numbers, position your cursor in the cell at the bottom of that column, click the AutoSum button > SUM, and press Enter. Excel frames the column of numbers in green borders and displays the formula in the current cell.
The problem comes when the range of numbers you need to calculate gets complicated with multiple calculation operators over multiple cellsFor example: =SUM(H1+I1*J1-M1*J1. Remember your high school math? If the numbers inside the formula are not grouped properly, the answer will be wrong. Notice the screenshot below (figure 2).
Enter the following column headers in H2 through P2 (use Alt+ Enter to stack headers in a single cell): Daily Earnings, Plus Bonuses, Times Days Worked, Gross Pay, (formula), Minus Meals at $9.00 per day, Total Monthly Earnings, Formula, and Comment.
NOTE: The formula columns are FYI only and provide no intrinsic value to the spreadsheet. They just “display” the formula for your benefit (so you can see the syntax of each formula used).
For this exercise, you can enter the same values in H3:11, I3:11, and J3:11, with or without the blank rows in between (again, added for easier viewing). Complete as follows: $86.00, $20.00, 22.0 workdays, and the rest are formulas. Note that as we build each formula, we are combining the steps, eventually, into a single formula.
We start out with three separate formulas. The first is to add the Daily earnings, plus Bonuses, multiplied by the number of days worked in a month, which equals Gross Pay: =SUM(H3+I3*J3) in cell K3. Notice that the answer is $526.00. That just doesn’t look right.
Use your calculator to check the formulas to ensure they’re correct BEFORE you copy them to the rest of the cells in the column.
The formula in K3 is wrong. It requires grouping the numbers according to the order of calculation using commas or parentheses.
Note the corrected formula in cell K4: =SUM(H4+I4)*J4. Check your numbers again (with your calculator) and note that this formula is correct. The correct answer is $2,332.00.
5. The second formula (in M4) is =SUM(J4*9) multiplies the workdays (22) times $9.00, the cost of meals per day. The correct answer is $198.00.
6. The third formula (in N4) calculates the monthly earnings minus the meals: =SUM(K4-M4); answer is $2,134.00.
7. In the next group (H6:N8), the formulas in M6:M8 remain the same: =SUM(J7*9), etc.—again that’s the number of workdays times the cost of meals. But the formulas in column K are eliminated and then combined with the formulas in column M: =SUM(H7+I7)*J7-M7. Note that the syntax (the structure or layout of the formula) is correct in cells N7 and N8, but incorrect in N6.
8. The next group (H10:H11) combines the formulas in column M with the formulas in column N: =SUM(H11+I11)*J11-(M11*J11)—note that the formula in N10 is incorrect. By combining these formulas into one, you can eliminate columns K and L.
9. Also, instead of “hardcoding” the price of the meals (as shown in M3:M4 and M6:M8), you can now change the price of the meals in column M (M10:M11) when inflation dictates an increase instead of changing the formula.
3. RAND function
The RAND function is really simple and traditionally used for statistical analysis, cryptography, gaming, gambling, and probability theory, among dozens of other things. In Excel, the RAND function generates a random number between 0 and 1. Note; however, that every time you enter new data and press the Enter key, the list of random numbers you just created changes. If you need to maintain your random numbers lists, you must format the cells as values.
1. Enter the function =RAND() in columns A3 through A14. Select that column and press Ctrl+C (for copy) or click the Copy button under the Home tab and choose Copy from the drop-down menu. Move your cursor to cell B3 and select Home > Paste > Paste Special. Click the Values button from the Paste Special dialog window, then click OK.
2. Now the list contains values instead of functions, so it will not change. Notice (in the formula bar) that the random numbers have 15 digits after the decimal (Excel defaults to 9), which you can change, if necessary (as displayed in cell F3). Just click the Increase Decimal button in the Number group under the Home tab.
3. If you prefer to work with whole numbers, enter this formula in cell F3: =INT(RAND()*999) and you get a 3-digit random number. Copy the formula down through F12, then add another ‘9’ to the string to add another digit to your random number—e.g., four nines equal four digits, five nines equal five digits. Again, you must copy the list and Paste as Values to maintain a static list.
4. COUNT functions
Use the COUNT function to count the number of numeric values in a range of cells; for example: C4:C15 contains the quantity of garden tools Mr. McGregor needs to order for his shop. Note that the answer is 10 (out of 12), because the COUNT function does not include blank cells. However, if you enter a zero, a numeric code, or a date, Excel counts it as an “occupied” cell and includes it in its answer.
Enter 10 numbers into column C (Quantity). Replace one number with a space (or a tap on the spacebar), then replace another number with a semicolon, and then enter a date into cell C7.
Enter this formula at the bottom of the number list (C16): =COUNT(C4:C15). The answer is 10 (out of 12) because Excel counted all the numbers and the date, but ignored the blank cell (containing the space) and the punctuation in cell C8.
Use the COUNTA function if you want to include numeric values, logical or error values, text, a space (from the spacebar), punctuation, symbols, or any other character on your keyboard.
1. Enter 12 dollar amounts into column D (Price). Replace one cell with a question mark, another cell with a symbol, and another cell with some text.
2. Enter this formula in D16: =COUNTA(C4:C15). The answer is 12 (out of 12) because Excel included all the “non-numeric” values and characters.
3. Notice that row 18 (C and D) displays the actual formulas that are in C and D 16.
5. AVERAGE function
Most everyone knows that an average is determined by adding all the values in a list, then dividing by the number of values listed; e.g., 4+5+3=12/3=4, which is the average. You can use the SUM function and add the division all in one formula, or you can just use the AVERAGE function. The syntax is: =AVERAGE(range).
1. Enter some numbers in column A. Enter the AVERAGE function at the bottom of the list: =AVERAGE(A4:A13) and note the answer (in our case) is 53. You can verify your answer with the SUM function; that is: =SUM(A4:A13/10) = 53.
2. Next enter some more numbers in column C but, this time, add some text to one cell, punctuation to another, and a space to another. Enter the same formula: =AVERAGE(A4:A15), and note the answer is 78. To verify, enter the SUM formula omitting the cells that contain non-numeric characters:
Cells that contain text, logical values, punctuation, or empty cells are disregarded; but cells with the zeros (as a number, but not as text) are included. A text zero would have an apostrophe in front of the zero, which you cannot see in the cell, but is visible in the Formula Bar.
IMPORTANT NOTE: If you’re importing huge databases from a mainframe or an outside, external source, sometimes the numbers export as text. How can you know if a number is really text? Generally, text is left-justified and numbers are right-justified but, because everyone formats their spreadsheets for aesthetics now, that method is unreliable. Another option is to scroll quickly through a long list of imported numbers and watch the Formula Bar. If you see apostrophes before any of the numbers, those entries are text. Last, look for the green triangle in the top left corner of the cell. Unless the previous owner of the spreadsheet instructed Excel to ignore this error, then the contents of the cell are text.
If the values are text, you must convert them to numbers immediately. To do this, move down to the first number in the list that’s actually text. Highlight the range of text that’s impersonating numbers. Right-click the yellow warning sign that’s left of the first text cell in the range. Click Convert to Number from the pop-up list, and it’s done.
6. MIN/MAX functions
Use the MIN function to find the smallest number in a range of values, and the MAX function to find the highest. The syntax for these functions are: =MIN(range); =MAX(range) where range equals the list of numbers you’re calculating.
Common uses of this function are; for example, find the highest/lowest grade in a classroom; the highest/lowest sales dollars in a store; the highest/lowest batting averages of your favorite baseball team; and so on.
Some would ask, why not just sort the data? You could, but every time the numbers changed, you’d have to re-sort. And, if you’re sorting multiple columns/fields with a lot of records/rows, the sort option could get cumbersome.
The MIN/MAX functions remain the same regardless of the changes in the data, even if you add more rows (as long as you add the rows using the Insert > Row feature within the existing range–that is, above the cell that contains the formula).
Enter some numbers in column A4:A11, then enter this formula in A13: =MIN(A4:A11) and this formula in A14: =MAX(A4:A11).
NOTE: The MIN/MAX functions disregard empty cells, TRUE/FALSE answers, text, text impersonating numbers, symbols, and punctuation.
The functions CONCAT and CONCATENATE do the same thing: They both combine multiple cells, ranges, or strings of data into one cell. The most common use of this function is to combine first and last name into one cell or join the city, state, and ZIP code into one cell.