#1: Joining Text Together

You can concatenate or adjoin text in multiple columns by using the & operator or the
CONCATENATE function. For Example, assume that you typed the following data
in cells A1:C2:

A1: B1: Middle C1: Last

First

A2: B2: C2:

Tom Edward Smith
To place the full name (first, middle, and last names) in cell D2, type the following
formula:
$D$2: =CONCATENATE(A2," ",B2," ",C2)
-or-
$D$2: =A1&" "&B2&" "&C2
Note A space (" ") between the cells is used to insert a space between the
displayed text.

#2: Setting the Print Area with One Click

Since Microsoft Excel version 4.0, a Set Print Area toolbar button has been
available in the File category. When you click Set Print Area, you can set the print
area to the current selection. After you add this button to an existing toolbar, you
can click it to easily set a print area to the currently selected range.

Adding the Set Print Area Button in Microsoft Excel

To add the button in Microsoft Excel 97, follow these steps:

1. Point to Toolbars on the View menu, and then click Customize.
2. Click the Commands tab. Click File under Categories and scroll down the
list of commands until you see Set Print Area.
3. Click to select Set Print Area, and then drag the command to an existing
toolbar.

To add the button in Microsoft Excel 5.0 and 7.0, follow these steps:
1. Click Toolbars on the View menu.
2. Click Customize.
3. Click File in the Categories list if it is not already selected.
4. Click the Set Print Area button.

This is the last button on the first row.

5. Drag the button to an existing toolbar.

#3: Excluding Duplicate Items in a List

If you create a list of items that contains duplicate items and you want to derive a
unique list, use the Advanced Filter command in Microsoft Excel to derive a
unique list.

Example

I
To see an example, follow these steps:
1. Type the following data in cells A1:A10 in a new workbook:

A1: Fruits
A2: Apple
A3: Cherry
A4: Pear
A5: Cherry
A6: Plum
A7: Apple
A8: Apple
A9: Pear
A10: Apple

2. Point to Filter on the Data menu and click Advanced Filter.

3. Under Action, click Copy to another Location. In the List Range box, type
$A$1:$A$10. Select unique records only, type $B$1 in the Copy to box,
and then click OK.

The following unique list appears in column B:

B1: Fruits
B2: Apple
B3: Cherry
B4: Pear
B5: Plum

Note that this method also works for multiple columns and that you can hide rows
when you use the Advanced Filter command.

#4: Changing Text to Numbers (Method 1)

Sometimes when you import files from other sources, numeric values may appear
to be numbers but act like text values. To resolve this behavior, convert these
values into numbers. One method for doing this is to multiply these text values by
1.
To convert the text values, follow these steps:
1. Click a blank cell in the worksheet and make sure that the cell is not
formatted as text. Then type 1 in the cell.
2. With that cell selected, click Copy on the Edit menu.
3. Select the range that contains values you want to convert to numbers.
4. Click Paste Special on the Edit menu.
5. Under Operation, click Multiply, and then click OK.

This method converts the text to numbers. You can tell whether you successfully
converted the text values by viewing the alignment of the number. If you use the
General format and the values are aligned to the right, the values are numbers;
text values are aligned to the left.

#5: Changing Text to Numbers (Method 2)

You can also use the Text Import Wizard to convert text to numbers.
To convert the text values by using the wizard, follow these steps:
1. Select the range that contains values you want to convert to numbers.

II
 2. On the Data menu, click Text to Columns.
3. Click Next twice to proceed to step 3 of the wizard.
4. In Column Data Format, click General, and then click Finish.

This method converts the text to numbers. You can tell whether you successfully
converted the text values by viewing the alignment of the number. If you use the
General format and the values are aligned to the right, the values are numbers;
text values are aligned to the left.

#6: Sorting Decimal Numbers in an Outline

Assume that you create the following outline numbers in cells A1:A6:

A1: 1.1.0
A2: 1.10.0
A3: 1.2.0
A4: 1.20.0
A5: 1.21.1
A6: 1.3.0
After you sort the outline numbers, they are displayed in the same order (the
order in which you typed them). However, if you want the numbers between each
decimal to be sorted, use the Text Import Wizard.
For example, to sort the sample numbers, follow these steps:
1. Select cells A1:A6
2. Click Text to Columns on the Data menu.
3. In step 1, click Delimited, and then click Next.
4. In Delimiters, clear every option except Other. In the Other box, type a
period. Click Next.
5. In step 2, type $B$1 in the Destination box so that the original outline is
not overwritten. Click Finish.

The numbers appear in columns B, C, and D.

6. Select cells A1:D6.

7. On the Data menu, click Sort.
8. In the Sort by list, click column B.
9. In the Then by box, click column C.
10. In the Then by list, click column D. Click OK.

The sorted list appears in column A.

#8: Entering the Current Date or Time

Do you want to quickly enter the current date in a cell? To do this, press CTRL+;
and press ENTER. To quickly enter the current time in a cell, press CTRL+: and
press ENTER.

#9: Viewing the Arguments of Functions

To see arguments in a formula, press CTRL+SHIFT+A. For example, if you type

III
 =RATE
and press CTRL+SHIFT+A, you can see all the arguments for that function (for
example, =RATE(nper,pmt,pv,fv,type,guess)). If you want more details, type the
following
=RATE
and press CTRL+A to display the Function Wizard.

#10: Quickly Filling a Range with the Same Data

Do you want to quickly enter the same text or the same formula in a range of
cells? To do this, follow these steps:
1. Select the range of cells that you want to fill.
2. Type the text or formula but don't press ENTER. Instead, press
CTRL+ENTER.

The data appears in the selected range.

#11: Linking a Text Box to Data in a Cell

You can link a text box so that it displays the contents of a cell. To do this, follow
these steps:
1. Click Text Box on the Drawing toolbar, click the worksheet and drag the
pointer to create the text box.
2. To edit in the formula bar, click in it or press F2.
3. Type the link formula (for example, type =A1), and then press ENTER

The text you type in the linked cell (for example, A1) appears in the text box. You
can move the text box to any worksheet in the workbook you want.

#12: Linking a Picture to a Cell Range

You can copy a range of cells and paste a resulting snapshot picture on a
worksheet. This is a neat way of easily seeing cell contents anywhere on the
worksheet. You can use this method to print nonadjacent cells on one page. The
great part is that the picture is linked and updated with both content changes and
formatting changes. To make a linked picture, follow these steps:
1. Select the cell range.
2. Click Copy on the Edit menu.
3. Select the cell in which you want the picture to appear.
4. While holding down SHIFT, click Paste Picture Link on the Edit menu.

The result is a snapshot that is updated as the source cells are changed or
formatted.

#13: Troubleshooting a Long Formula

If you create a long worksheet formula that is not returning the expected result,
you can drag the pointer to select part of the formula in the formula bar, and then
press F9. When you do this, only the selected portion of the formula is evaluated.
Important If you press ENTER, that part of your formula is lost, so be sure to press
ESC instead. However, if you mistakenly press ENTER, try pressing CTRL+Z to
undo the change.

IV
 #14: Viewing a Graphical Map of a Defined Name
(Excel 97 Only)
When you set the Zoom box for a worksheet to a setting that is 39 percent or less,
a defined name that consists of a cell range of two or more adjacent cells appears
in a rectangle on the screen. When you click Zoom on the Standard toolbar and
type a value of 40 percent or greater, rectangles that identify named ranges
automatically disappear. Note that this feature is not available in earlier versions
of Microsoft Excel.

#15: Filling Blank Cells in a Column with Contents

from a Preceding Cell
Assume that you type the following names in column A:

To correctly sort the names, you must fill the names in the blank cells. To do this
in the example, follow these steps:
1. Select cells A1:A10.
2. On the Edit menu, click Goto.
3. Click Special, click Blanks and click OK.
4. Type =a1 and press CTRL+ENTER.

This step enters the names in the blank cells that you selected.
5. Select cells A1:A10.
6. On the Edit menu, click Copy.
7. On the Edit menu, click Paste Special.
8. Under the Paste group, click Values and click OK.

The names are filled down the cells for you.

#16: Shortcut for Switching from a Relative

Reference to an Absolute Reference
You can press F4 to toggle the relative and absolute cell address for a formula.
When you type a formula in the formula bar, use a cell reference in relative
address form (for example, use A1). After you type the reference, press F4 and
the cell reference is automatically changed to an absolute cell reference (for
example, $A$1). You can also continue to press F4 to display mixed absolute and
relative reference forms.
For more information about cell referencing, click the Find tab in Microsoft Excel
Help, type the following text
absolute and relative

V
and then double-click the "The difference between relative and absolute
references" topic.

#17: Using OFFSET to Manipulate Data in Cells That

Are Inserted
Assume that you are using the following data in cells A1:A7 and that you want to
subtract the last row from the first row in the range:

A1: 1
A2: 2
A3: 3
A4: 4
A5: 5
A6:
A7: =A5-A1
Assume that you want to use a formula that will always be two rows below the
last cell (with a blank cell between the formula and the last cell that contains
data). Assume that when you insert a new row at the blank cell (row 6 in the
following example), you want the formula to subtract the data in cell A6 (instead
of the data in cell A5) from the data in cell A1.
Note that in this example, the formula (=A5-A1) fails to subtract the data in row
A6 when you insert a row with data in A6.
To do this, use the OFFSET function. This function returns a reference to a range
that is a specified number of rows and columns from a cell or range of cells. In the
example, use the following formula:
=OFFSET(A6,-1,0)-A1
This OFFSET formula is not fixed on the row above A6 and changes as you insert
new rows.

#18: Using Advanced Filter

If you create a list of data in Microsoft Excel and you want to select certain items
and copy them to another sheet, use the Advanced Filter command in Excel. To
start using this command, point to Filter on the Data menu, click Advanced Filter,
and then follow the instructions. If you are not sure what information Excel is
prompting you for, see Microsoft Excel Help.

#19: Using Conditional Sums to Total Data (Sum+If)

Assume that you create a list of data in cells A1:A10 and that you want to sum all
the values that are greater than 50 and less than 200. To do this, use the
following array formula:
=SUM(IF(A1:A10>=50,IF(A1:A10<=200,A1:A10,0),0))
Note Be sure to enter the formula as an array by pressing CTRL+SHIFT+ENTER.
After you do this, you see curly braces {} surrounding the formula. Do not try to
enter the braces manually.
The formula uses nested IF functions for each cell in the range and adds the cell
data only when both test criteria are met.

#20: Using Conditional Sums to Count Data (Sum+If)

VI
Assume that you create a list of data in cells A1:A10 and that you want to count
all the values that are greater than 50 and less than 200. To do this, use the
following array formula:
=SUM(IF(A1:A10>=50,IF(A1:A10<=200,1,0),0))
Note Be sure to enter the formula as an array by pressing CTRL+SHIFT+ENTER.
After you do this, you see curly braces {} surrounding the formula. Do not try to
enter the braces manually.
The formula uses nested IF functions for each cell in the range and adds one to
the total only when both criteria tests are met.

#21: Using INDEX and MATCH to Look up Data

Assume that you create the following table of information in cells A1:C5 and that
this table contains age information in cells C1:C5:

Assume that you want to look up the age of a person by using the person's name.
To do this, use a combination of the INDEX and MATCH functions as in the
following sample formula:
=INDEX($A$1:$C$5, MATCH("Mary",$A$1:$A$5,),3)
This sample formula uses cells A1:C5 as the table and looks up Mary's age in the
third column. The formula returns 22.

#22: Dragging the Fill Handle to Create a Number

Series
By dragging the fill handle of a cell, you can copy the contents of that cell to other
cells in the same row or column. If the cell contains a number, date, or time
period that Microsoft Excel can project in a series, the values are increment ed
instead of copied. For example, if the cell contains "January," you can quickly fill in
other cells in a row or column with "February," "March," and so on. You can also
create a custom fill series for frequently used text entries, such as your
company's sales regions.

#23: Double-click the Fill Handle to Automatically Fill

Data
You can double-click the fill handle of a selected cell to fill the contents of the cell
down a column for the same number of rows as the adjacent column. For
example, if you type data in cells A1:A20, type a formula or text in cell B1, press
ENTER, and then double-click the fill handle, Microsoft Excel fills the data down
the column from cell B1 to cell B20.

#24: Using the VLOOKUP Function with Unsorted

Data
In versions of Microsoft Excel earlier than version 5.0, you must sort data in
ascending order for the VLOOKUP function to work correctly. In Excel 5.0 and

VII
later, VLOOKUP does work when you use it with unsorted data. However, you
must add an additional argument to the formula. This argument, which is the
fourth argument (Range_Lookup), is assumed to be TRUE if you do not specify a
value. This behavior makes the function compatible with earlier versions of Excel.
To make VLOOKUP work correctly with unsorted data, change the Range_Lookup
argument to FALSE. The following is a sample function that looks up the age of
Stan in the data table you created for #21:
=VLOOKUP("Stan",$A$2:$C$5,3,FALSE)

#25: Returning Every Nth Number

Assume that you create the following data table in cells A1:A12 and that you want
to obtain every third number in a column and place the numbers in an adjacent
column:

To do this, use the ROW function with the OFFSET function, for example, use the
following sample formula:
=OFFSET($A$1,ROW()*3-1,0)
This formula is dependent on the row of the cell in which it is entered. In the
formula, the ROW function returns the row number of the cell in which the formula
is entered. This number is multiplied by 3. The OFFSET function moves the active
cell down from cell A1 the specified number of rows and returns every third
number.

#26: Round to the Nearest Penny

Assume that you enter the following formulas in cells A1:A3 in a worksheet:

A1: =1.23/2
A2: =1.21/2
A3: =SUM(A1:A2)
Assume that you are working with money and that the results of the calculations
are formatted for currency. The values that are returned are the following:
A1: $0.62
A2: $0.61
A3: $1.22
As you can see, the total in cell A3 is incorrect. The problem is that even though
the number format (money) rounds the displayed values, the underlying values
were not rounded to the nearest penny. We can resolve this behavior by using the
ROUND function. For example, change the formulas to the following:

A1: =ROUND(1.23/2,2)
A2: =ROUND(1.21/2,2)
A3: =ROUND(SUM(A1:A2),2)

VIII
The second argument of the ROUND function tells Microsoft Excel which digit to
round. In this case 2 tells Microsoft Excel to round to the nearest hundredth.

#27: Installing and Using Microsoft Excel Help

Microsoft Excel Help allows you to search for information about a specific usage
topic, browse through a list of topics, or search for specific words and phrases
instead of topics. You can also use context-sensitive Help (press F1) to view
information that pertains to the task at hand.
The Help files must be installed in order for you to access them. If Help is not
installed, run the Setup program again and click Add/Remove to install the files.

#28: Avoiding Opening and Saving Directly from

Floppy Disk
When you open a workbook, Microsoft Excel creates temporary files in the folder
in which you save the file and in the folder from which you opened the workbook.
These temporary files are deleted when you close the file. Also, Microsoft Excel
creates a copy of the file on the media when you save the file. This behavior may
be problematic if you open a workbook from a floppy disk or if the floppy disk
does not have enough free space to accommodate the file.
For these reasons, it is a good idea to copy the file to your hard disk before you
work with it. After you make modifications, save the file to the hard disk, and then
copy it back to the floppy disk.

#29: Using One Keystroke to Create a new Chart or

Worksheet
To quickly create a chart, select the chart data, and then press F11. To create a
new worksheet, press SHIFT+F11.

#30: Setting up Multiple Print Areas on the Same

Worksheet
You can set up multiple print areas on the same worksheet without using a macro.
To do this, use the Custom Views and Print Report commands. Essentially, you
define views of the worksheet, and then define a report with the views of your
choice. For more information, see Microsoft Excel Help or see the following
Knowledge Base article:

IX