Article ID: 843504  View products that this article applies to. On This PageSUMMARYThis 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. INTRODUCTIONMORE INFORMATIONJoin text in multiple columnsYou can concatenate or adjoin text in multiple columns by using the & operator or the CONCATENATE functionfor example, if you type the following data in cells A1:C2:Collapse this table
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 areaSince 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:
Exclude duplicate items in a listIf 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:
Multiply text values by 1 to change text to numbersSometimes 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:
Use the Text Import Wizard to change text to numbersTo do this, follow these steps:
Sort decimal numbers in an outlineAssume that you create the following outline numbers in cells A1:A6:Collapse this table
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:
Use a data form to add records to a listIf 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.Collapse this image Enter the current date or timeIf 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 formulaWhile 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 functionfor 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 cellsIf you want to quickly enter the same text or the same formula in a range of cells, follow these steps:
Link a text box to data in a cellTo do this, follow these steps:
Link a picture to a cell rangeYou 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:
Troubleshoot a long formulaIf 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 nameNote 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 cellAssume that you type the following names in column A:Collapse this image
Switch from a relative reference to an absolute referenceYou 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 formfor example, use A1. After you type the reference, press F4 and the cell reference is automatically changed to an absolute cell referencefor 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 doubleclick the The difference between relative and absolute references topic. Use the OFFSET function to modify data in cells that are insertedAssume 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:Collapse this table
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 =A5A1 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 commandIf 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 dataAssume 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 dataAssume 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 dataAssume that you create the following table of information in cells A1:C5 and that this table contains age information in cells C1:C5:Collapse this image =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 seriesBy 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 dataYou can doubleclick 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 doubleclick the fill handle, Excel fills the data down the column from cell B1 to cell B20.Use the VLOOKUP function with unsorted dataIn 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 numberAssume 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:Collapse this image =OFFSET($A$1,ROW()*31,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 pennyAssume that you enter the following formulas in cells A1:A3 in a worksheet:Collapse this table
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: Collapse this table
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: Collapse this table
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 HelpMicrosoft 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 contextsensitive 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 diskWhen 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 worksheetTo 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 worksheetYou 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
(http://support.microsoft.com/kb/142529/
)
XL:
How to create multiple views and create and print a report
Properties 
