Many of us fell in love with Excel as we delved into its deep and
sophisticated formula features. Because there are multiple ways to get
results, you can decide which method works best for you. For example,
there are several ways to enter formulas and calculate numbers in Excel.
1. Manually enter Excel formulas:
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.
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 ExcelJD 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 cells (e.g., beginning date,
ending date, etc.)
1. =DAYS
This is a handy formula to calculate the number of days between two
dates (so there’s no worries about how many days are in each month of
the range).
Example: End Date October 12, 2015 minus Start Date March 31, 2015 = 195 days
Formula: =DAYS(A30,A29)
2. =NETWORKDAYS
This similar formula calculates the number of workdays (i.e., a five-day
workweek) within a specified timeframe. It also includes an option to
subtract the holidays from the total, but this must be entered as a
range of dates.
Example: Start Date March 31, 2015 minus End Date October 12, 2015 = 140 days
Formula: =NETWORKDAYS(A33,A34)
3. =TRIM
TRIM is a lifesaver if you’re always importing or pasting text into
Excel (such as from a database, website, word processing software, or
other text-based program). So often, the imported text is filled with
extra spaces scattered throughout the list. TRIM removes the extra
spaces in seconds. In this case, just enter the formula once, then copy
it down to the end of the list.
Example: =TRIM plus the cell address inside parenthesis.
Formula: =TRIM(A39)
Excel formulas
4. =CONCATENATE
This is another keeper if you import a lot of data into Excel. This
formula joins (or merges) the contents of two or more fields/cells into
one. For example: In databases; dates, times, phone numbers, and other
multiple data records are often entered in separate fields, which is a
real inconvenience. To add spaces between words or punctuation between
fields, just surround this data with quotation marks.
Example: =CONCATENATE plus (month,”space”,day,”comma space”,year) where
month, day, and year are cell addresses and the info inside the
quotation marks is actually a space and a comma.
Formula: For dates enter: =CONCATENATE(E33,” “,F33,”, “,G33)
Formula: For phone numbers enter: =CONCATENATE(E37,”-“,F37,”-“,G37)
5. =DATEVALUE
DATEVALUE converts the above formula into an Excel date, which is
necessary if you plan to use this date for calculations. This one is
easy: Select DATEVALUE from the formula list. Click the Date_Text field
in the dialog box, click the corresponding cell on the spreadsheet, then
click OK, and copy down. The results are Excel serial numbers, so you
must choose Format>Format Cells>Number>Date, and then select a
format from the list.
Formula: =DATEVALUE(H33)
Three more formula tips
As you work with formulas more, keep these bonus tips in mind to avoid confusion:
Tip 1: You don’t need another
formula to convert formulas to text or numbers. Just copy the range of
formulas and then paste as Special>Values. Why bother to convert the
formulas to values? Because you can’t move or manipulate the data until
it’s converted. Those cells may look like phone numbers, but they’re
actually formulas, which cannot be edited as numbers or text.
Tip 2: If you use Copy and
Paste>Special>Values for dates, the result will be text and cannot
be converted to a real date. Dates require the DATEVALUE formula to
function as actual dates.
Tip 3: Formulas are always
displayed in uppercase; however, if you type them in lowercase, Excel
converts them to uppercase. Also notice there are no spaces in formulas.
If your formula fails, check for spaces and remove them.
Excel formulas
