If you like these tips, you'll love the free, opt-in, monthly publication, The
Office Newsletter. View a recent issue and subscribe
here.
Conditional Sum or Conditional Count
=SUMIF(A1:A25,"Smith",B1:B25)
Totals the data in the B range where the corresponding row in the A column
equals Smith.
=COUNTIF(A1:A25,"Smith")
Counts the entries in the range that equal Smith..
|
OK, but how do I specify conditions in
multiple columns and then sum?
The easiest way is to install an add-in that comes with Excel. To perform
the install, Tools>Add-ins. Choose Conditional Sum Wizard. Takes
seconds and then you'll have the command "Wizard" under the Tools menu.
It's cake from there. |
Another Nifty Add-in
Tools>Add-ins>Lookup Wizard
The Lookup Wizard will then be located at Tools>Wizard
Quickly finds the value at the intersection of a column and a row. |
Pick From List
In a list that has no blank rows, you can R-click in the next available cell
and choose Pick From List to get a list of all the unique entries in that
column. Just click on the one you want to enter in the cell.
|
Fill in a Series of Dates
Put a date in a cell and R-drag the Fill Handle (the square in the bottom right
corner of the cell) to any cell. You'll get a menu that will enable you to fill
in the cells with weekdays or months, in addition to other date choices.
|
Change Text Case
Excel provides three handy functions to change the case of text.
UPPER: converts all text to uppercase
LOWER: converts all text to lowercase
PROPER: converts text to The First Letter Of Each Word Is Capitalized
If cell A1 contains the text: john r. doe, the following formula will produce
John R. Doe.
=PROPER(A1)
You may want to then select the cell or the range, Copy and then Edit>Paste
Special (then choose Values) to change the contents of the cells from functions
into the values produced by the functions.
|
Move Comma Delimited Names Into Separate Cells
Highlight the names in column A. From the menu bar, select Data Text to
Columns. Make sure the delimited file type is selected in step 1 and click
Next. Select only the comma check box and click Next. In the destination text
box, change $A$1 to a cell reference that won't interfere with your data. Click
Finish.
|
Keep Leading Zeroes
Precede the number with an apostrophe. The apostrophe won't display, but
the leading zeroes will.
Or you can format the cells as Text or Special then Zip Codes.
|
Search for an Asterisk, Question Mark, or Tilde
Since these characters have special meanings in Excel, you will need to
precede them with a tilde to be able to search for them successfully. So,
to find an asterisk, Edit>Find, and then type ~*
in the Find dialog box; to find a tilde, type ~~;
a question mark, ~?.
|
Every Other Page Blank When You Print Your
Spreadsheet?
Try this: File>Page Setup, on the Page tab, under Scaling, choose "Fit to",
and type a 1 in the pages wide box. In the pages tall box, delete any entry and
leave it blank.
|
Copy Page Setup Options to Other Sheets
Set the desired options for one sheet. Select that sheet tab first, then
hold down [CTRL] and select the tabs for the sheets you want to copy the
options to. Choose File>Page Setup and OK.
|
Quickly Navigate Multi-Sheet Workbooks
Right-click the the arrow buttons at the bottom left-hand side of the screen
(the sheet tab navigation tool). You'll get a clickable menu of all the sheets
in the file.
|
What Day of the Week?
Enter a date into a cell and specify a custom format(Format>Cells... on the
Number tab, choose Custom). In the text box provided type
dddd
|
Format Comments
After you have entered a comment into a cell, Right-click on the cell and
choose Edit Comment. Now you can use the formatting toolbar on your
comment.
|
Calculate Age
A frequent use of the DATEDIF function is to compute someone's age based on
the current date and their birthday. The formula below will return someone's
age (in years) based on their birthday in cell A1:
=DATEDIF(A1,NOW(),"y")
|
Transform Minutes into Hours (or Seconds into
Minutes)
Use this simple formula (the format of the destination cell must be hh:mm):
=A1/1440
|