You are currently offline, waiting for your internet to reconnect

This article describes some of the most powerful and useful features and functions in Microsoft Excel that remain undiscovered by users. For example, you may create a new macro to perform a calculation when an existing formula or function can perform the task. Or, you may create a new macro to perform a task when you can use an existing feature that performs the task.

- INTRODUCTION
- MORE INFORMATION
- Join text in multiple columns
- Set the print area
- Exclude duplicate items in a list
- Multiply text values by 1 to change text to numbers
- Use the Text Import Wizard to change text to numbers
- Sort decimal numbers in an outline
- Use a data form to add records to a list
- Enter the current date or time
- View the arguments in a formula
- Enter the same text or formula in a range of cells
- Link a text box to data in a cell
- Link a picture to a cell range
- Troubleshoot a long formula
- View a graphical map of a defined name
- Fill blank cells in a column with contents from a previous cell
- Switch from a relative reference to an absolute reference
- Use the OFFSET function to modify data in cells that are inserted
- Use the Advanced Filter command
- Use conditional sums to total data
- Use conditional sums to count data
- Use the INDEX function and the MATCH function to look up data
- Drag the fill handle to create a number series
- Automatically fill data
- Use the VLOOKUP function with unsorted data
- Return every third number
- Round to the nearest penny
- Install and use Microsoft Excel Help
- Do not open and save directly from a floppy disk
- Use one keystroke to create a new chart or worksheet
- Set up multiple print areas on the same worksheet

A1: First | B1: Middle | C1: Last |

A2: Tom | B2: Edward | C2: Smith |

To put the full name, in cell D2, type one of the following formulas:

$D$2: =CONCATENATE(A2," ",B2," ",C2)

$D$2: =A1&" "&B2&" "&C2

Note A space (" ") between the cells is used to insert a space between the displayed text.back to the top

To add the

- On the
**View**menu, point to**Toolbars**, and then click**Customize**. - Click the
**Commands**tab. - Under
**Categories**, click**File**, and then scroll down the list of commands until you see the**Set Print Area**toolbar button. - Click
**Set Print Area**, and then drag the command to an existing toolbar.

To do this, follow these steps:

- 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 - On the
**Data**menu, point to**Filter**, and then click**Advanced Filter**. - Under
**Action**, click**Copy to**. - In the
**List Range**box, type $A$1:$A$10. - Click
**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

back to the top

To convert the text values, follow these steps:

- Click a blank cell in the worksheet, make sure that the cell is not formatted as text, and then type 1 in the cell.
- With the blank cell selected, click
**Copy**on the**Edit**menu. - Select the range that contains the values that you want to convert to numbers.
- On the
**Edit**menu, click**Paste Special**. - Under
**Operation**, click**Multiply**, and then click**OK**.

back to the top

- Select the range that contains the values that you want to convert to numbers.
- On the
**Data**menu, click**Text to Columns**. - Click
**Next**two times to go to step 3 of the wizard. - In the
**Column Data Format**GroupBox, click**General**, and then click**Finish**.

back to the top

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 appear in the same order. The outline numbers appear in the order that you typed them. However, if you want to sort the numbers between each decimal, use the Text Import Wizard. To do this, follow these steps:

- Select cells A1:A6.
- On the
**Data**menu, click**Text to Columns**. - In step 1 in the Text Import Wizard, click
**Delimited**, and then click**Next**. - In the
**Delimiters**GroupBox, click to clear every check box except the**Other**check box. In the**Other**check box, type a period, and then click**Next**. - In step 2, type $B$1 in the
**Destination**box so that the original outline is not overwritten, and then click**Finish**.

The numbers appear in columns B, C, and D. - Select cells A1:D6.
- On the
**Data**menu, click**Sort**. - In the
**Sort by**list, click column**B**. - In the
**Then by**box, click column**C**. - In the
**Then by**list, click column**D**, and then click**OK**.

back to the top

back to the top

back to the top

back to the top

- Select the range of cells that you want to fill.
- Type the text or formula but do not press ENTER. Instead, press CTRL+ENTER.

back to the top

- On the
**Drawing**toolbar, click**Text Box**, click the worksheet, and then drag the pointer to create the text box. - To make changes in the formula bar, click in the formula bar or press F2.
- Type the link formula--for example, type =A1, and then press ENTER.

back to the top

- Select the cell range.
- On the
**Edit**menu, click**Copy**. - Select the cell where you want the picture to appear.
- On the
**Edit**menu, click**Paste Picture Link**while you hold down the SHIFT key.

back to the top

Important If you press ENTER, that part of your formula is lost. Therefore, make sure that you press ESC instead. However, if you mistakenly press ENTER, press CTRL+Z to undo the change.

back to the top

When you set the

back to the top

- Select cells A1:A10.
- On the
**Edit**menu, click**Go to**. - Click
**Special**, click**Blanks**, and then click**OK**. - Type =a1, and then press CTRL+ENTER.

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

back to the top

For more information about cell referencing, click the

back to the top

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 under the last cell with a blank cell between the formula and the last cell that contains data. Assume that if you insert a new row at the blank cell (row 6 in the following example), you want the formula to subtract the data that is in cell A1 from the data that is in cell A6 instead of from the data that is in cell A5.

Note that in this example, the formula =A5-A1 does not subtract the data in row A6 when you insert a row with data in A6.

To do this, use the OFFSET function. The OFFSET function returns a reference to a range that is a specified number of rows and columns from a cell or from a range of cells. In this example, use the following formula:

=OFFSET(A6,-1,0)-A1

The OFFSET formula is not fixed on the row above A6 and changes as you insert new rows.back to the top

back to the top

=SUM(IF(A1:A10>=50,IF(A1:A10<=200,A1:A10,0),0))

Note Make sure that you 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.

back to the top

=SUM(IF(A1:A10>=50,IF(A1:A10<=200,1,0),0))

Note Make sure that you 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.

back to the top

=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.back to the top

back to the top

back to the top

To make the VLOOKUP function 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 that you created earlier in the "Use the INDEX function and the MATCH function to look up data" section:

=VLOOKUP("Stan",$A$2:$C$5,3,FALSE)

back to the top=OFFSET($A$1,ROW()*3-1,0)

This formula depends on the row of the cell where it is entered. In the formula, the ROW function returns the row number of the cell where 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.back to the top

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, 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) |

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

back to the top

The Help files must be installed for you to access them. If Help is not installed, run the Setup program again, and then click

back to the top

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.

back to the top

back to the top

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

142529 XL: How to create multiple views and create and print a report

back to the top Properties

Article ID: 843504 - Last Review: 12/19/2006 08:04:51 - Revision: 1.2

- Microsoft Excel 2000 Standard Edition
- Microsoft Excel 2002 Standard Edition
- Microsoft Excel 97 Standard Edition
- Microsoft Office Excel 2003

- kbfunctions kbhowto kbinfo KB843504