Excel Formulas and Functions
- 6 years ago
Five ways to enter formulas
1. Manually enter Excel formulas:
Long Lists: =SUM(B4:B13)
Short Lists: =SUM(B4,B5,B6,B7); =SUM(B4+B5+B6+B7). Or, place your cursor in the first empty cell at the bottom of your list (or any cell, really) and press the plus sign, then click B4; press the plus sign again and click B5; and so on to the end; then press Enter. Excel adds/totals this list you just “pointed to:” =+B4+B5+B6+B7.
[ Further reading: Your new PC needs these 15 free, excellent programs ]
Excel formulas
2. Click the Insert Function button
Use the Insert Function button under the Formulas tab to select a function from Excel’s menu list:
=COUNT(B4:B13) Counts the numbers in a range (ignores blank/empty cells).
=COUNTA(B3:B13) Counts all characters in a range (also ignores blank/empty cells).
3. Select a function from a group (Formulas tab)
Narrow your search a bit and choose a formula subset for Financial, Logical, or Date/Time, for example.
=TODAY() Inserts today’s date.
4. The Recently Used button
Click the Recently Used button to show functions you've used recently. It's a welcome timesaver, especially when wrestling with an extra-hairy spreadsheet.
=AVERAGE(B4:B13) adds the list, divides by the number of values, then provides the average.
5. Auto functions under the AutoSum button
Auto functions are my editor's personal favorite, because they're so fast. Select a cell range and a function, and your result appears with no muss or fuss. Here are a few examples:
=MAX(B4:B13) returns the highest value in the list.
=MIN(B4:B13) returns the lowest value in the list.
AutoSum in Excel
JD Sartain
Use the AutoSum button to calculate basic formulas such as SUM, AVERAGE, COUNT, etc.
Note: If your cursor is positioned in the empty cell just below your range of numbers, Excel determines that this is the range you want to calculate and automatically highlights the range, or enters the range cell addresses in the corresponding dialog boxes.
Bonus tip: With basic formulas, the AutoSum button is the top choice. It’s faster to click AutoSum>SUM (notice that Excel highlights the range for you) and press Enter.
Another bonus tip: The quickest way to add/total a list of numbers is to position your cursor at the bottom of the list and press Alt+ = (press the Alt key and hold, press the equal sign, release both keys), then press Enter. Excel highlights the range and totals the column.
Five handy formulas for common tasks
The five formulas below may have somewhat inscrutable names, but their functions save time and data entry on a daily basis.
Note: Some formulas require you to input the single cell or range address of the values or text you want calculated. When Excel displays the various cell/range dialog boxes, you can either manually enter the cell/range address, or cursor and point to it. Pointing means you click the field box first, then click the corresponding cell over in the worksheet. Repeat this process for formulas that calculate a range of
1. Manually enter Excel formulas:
Long Lists: =SUM(B4:B13)
Short Lists: =SUM(B4,B5,B6,B7); =SUM(B4+B5+B6+B7). Or, place your cursor in the first empty cell at the bottom of your list (or any cell, really) and press the plus sign, then click B4; press the plus sign again and click B5; and so on to the end; then press Enter. Excel adds/totals this list you just “pointed to:” =+B4+B5+B6+B7.
[ Further reading: Your new PC needs these 15 free, excellent programs ]
Excel formulas
2. Click the Insert Function button
Use the Insert Function button under the Formulas tab to select a function from Excel’s menu list:
=COUNT(B4:B13) Counts the numbers in a range (ignores blank/empty cells).
=COUNTA(B3:B13) Counts all characters in a range (also ignores blank/empty cells).
3. Select a function from a group (Formulas tab)
Narrow your search a bit and choose a formula subset for Financial, Logical, or Date/Time, for example.
=TODAY() Inserts today’s date.
4. The Recently Used button
Click the Recently Used button to show functions you've used recently. It's a welcome timesaver, especially when wrestling with an extra-hairy spreadsheet.
=AVERAGE(B4:B13) adds the list, divides by the number of values, then provides the average.
5. Auto functions under the AutoSum button
Auto functions are my editor's personal favorite, because they're so fast. Select a cell range and a function, and your result appears with no muss or fuss. Here are a few examples:
=MAX(B4:B13) returns the highest value in the list.
=MIN(B4:B13) returns the lowest value in the list.
AutoSum in Excel
JD Sartain
Use the AutoSum button to calculate basic formulas such as SUM, AVERAGE, COUNT, etc.
Note: If your cursor is positioned in the empty cell just below your range of numbers, Excel determines that this is the range you want to calculate and automatically highlights the range, or enters the range cell addresses in the corresponding dialog boxes.
Bonus tip: With basic formulas, the AutoSum button is the top choice. It’s faster to click AutoSum>SUM (notice that Excel highlights the range for you) and press Enter.
Another bonus tip: The quickest way to add/total a list of numbers is to position your cursor at the bottom of the list and press Alt+ = (press the Alt key and hold, press the equal sign, release both keys), then press Enter. Excel highlights the range and totals the column.
Five handy formulas for common tasks
The five formulas below may have somewhat inscrutable names, but their functions save time and data entry on a daily basis.
Note: Some formulas require you to input the single cell or range address of the values or text you want calculated. When Excel displays the various cell/range dialog boxes, you can either manually enter the cell/range address, or cursor and point to it. Pointing means you click the field box first, then click the corresponding cell over in the worksheet. Repeat this process for formulas that calculate a range of