Join text in multiple columns
You can concatenate or adjoin text in multiple columns by using
the
& operator or the
CONCATENATE function--for example, if you
type the following data in cells A1:C2:
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.
Set the print area
Since Microsoft Excel 97 for Windows, a
Set Print
Area toolbar button has been available on the
File menu. When you click the
Set Print Area toolbar button, you can set the print
area to the current selection. After you add the
Set Print Area toolbar button to an existing
toolbar, you can click
Set Print Area to easily set a print area to the currently selected
range.
To add the
Set Print Area toolbar button in Excel, follow these
steps:
- 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.
Exclude 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 Excel.
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 |
Note that this method also works for multiple columns. You can hide rows when you use the
Advanced Filter command.
Multiply text values by 1 to change text to numbers
Sometimes when you import files from other sources, numeric values
may appear to be numbers but behave like text values. To resolve this problem,
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:
- 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.
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 and the text values are aligned to the
left.
Use the Text Import Wizard to change text to numbers
To do this, follow these steps:
- 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.
This method converts 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 and the text values are aligned to the
left.
Sort 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 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.
The sorted list appears in column A.
Use a data form to add records to a list
If you are adding records to a list, use a
predefined data form. To start, click a cell in the list, and then click
Form on the
Data menu.
Enter the current date or time
If you want to quickly enter the current date in a cell, press
CTRL+; and then press
ENTER. To quickly enter the current time in a
cell, press
CTRL+: and then press
ENTER.
View the arguments in a formula
While you enter a formula in a cell, press
CTRL+SHIFT+A to see the arguments in a formula. If you
type
=RATE, and then 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
=RATE, and then press
CTRL+A to
display the Function Wizard.
Enter the same text or formula in a range of cells
If you want to quickly enter the same text or the same formula in
a range of cells, follow these steps:
- Select the range of cells that you want to fill.
- Type the text or formula but do not press ENTER. Instead,
press CTRL+ENTER.
The data appears in the range that you selected.
Link a text box to data in a cell
To do this, follow these steps:
- 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.
The text that you enter in the linked cell appears in the text box--for example,
A1. You can move the text box
to any worksheet in the workbook that you want.
Link a picture to a cell range
You can copy a range of cells and paste the result picture on a worksheet. When you do this, you can easily see cell contents
anywhere on the worksheet. You can use this method to print nonadjacent cells
on one page. The picture is linked and updated with both
content changes and formatting changes. To make a linked picture, follow these
steps:
- 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.
The result is a snapshot that is updated as the source cells are
changed or formatted.
Troubleshoot a long formula
If you create a long worksheet formula that is not returning the
expected result, drag the pointer to select part of the formula in the
formula bar, and then press F9. When you do this, only the selected part of
the formula is evaluated.
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.
View a graphical map of a defined name
Note This section applies to Excel 97 for Windows only.
When you set the
Zoom box for a worksheet to a
setting that is 39 percent or less, a defined name that is made up 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 more, rectangles that identify named ranges
automatically disappear. Note that this feature is not available in earlier
versions of Microsoft Excel.
Fill blank cells in a column with contents from a previous cell
Assume that you type the following names in column
A:
To correctly sort
the names, fill the names in the blank cells. To do this, follow these steps:
- 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.
The names are filled down the cells for you.
Switch 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
absolute and relative, and then double-click the
The
difference between relative and absolute references topic.
Use the OFFSET function to modify 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 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.
Use the Advanced Filter command
If you create a list of data in Excel, and you want to
select certain items and copy them to another sheet, use the
Advanced Filter
command in Excel. To use this command, point to
Filter
on the
Data menu, click
Advanced Filter, and
then follow the instructions that appear on the screen. If you are not sure what information Excel is
prompting you for, see Microsoft Excel Help.
Use conditional sums to total data
Assume that you create a list of data in cells A1:A10, and that you
want to sum all the values that are larger 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
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.
Use conditional sums to count data
Assume that you create a list of data in cells A1:A10 and that you
want to count all the values that are larger 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
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.
Use the INDEX function and the MATCH function 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 function and the
MATCH function 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.
Drag 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 Excel can project in a series, the
values are incremented 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.
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,
Excel fills the data down the column from cell B1 to cell
B20.
Use the VLOOKUP function with unsorted data
In
Excel 97 for Windows and later versions, the
VLOOKUP function works when you use it with unsorted data.
However, you must add an additional argument to the formula. The
Range_Lookup argument, is assumed to be
TRUE if you do
not specify a value. Note that the
Range_Lookup argument is the fourth argument. This behavior makes the function compatible with earlier
versions of Excel.
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)
Return every third 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 put 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 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.
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, 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.
Install and use Microsoft Excel Help
Microsoft Excel Help lets you 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.
The Help files must be installed for you to access
them. If Help is not installed, run the Setup program again, and then click
Add/Remove to install the files.
Do not open and save directly from a floppy disk
When you open a workbook, Excel creates temporary files
in the folder where you save the file and in the folder where you opened the workbook from. These temporary files are deleted when you close the file.
Also, 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 has insufficient free space to hold 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.
Use 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.
Set 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 command and the Print Report command.
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.
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