Excel Tips

Excel 2007
Cells and Ranges
AutoFill	If you highlight a single isolated cell and drag the AutoFill handle (small box at the lower right of the active cell) with the LEFT mouse button, Excel copies the highlighted cell into the new range of cells. A small menu pops up that you can access to change what you had just copied to be a fill series instead. If you highlight the last cell in a series and drag the AutoFill handle (small box at the lower right of the active cell) with the LEFT mouse button, Excel copies the highlighted cell into the new range of cells. A small menu pops up that you can access to change what you had just copied to be a fill series instead. If you highlight an entire series and drag the AutoFill handle (small box at the lower right of the active cells) with the LEFT mouse button, Excel continues the series into the new range of cells. A small menu pops up that you can access to change what you had just extended to be a copy of the series instead. If the range of cells to be copied into is insufficient to contain the entire series, the series is truncated. If the range of cells to be copied into is larger than the series copied, the copying repeats until all the cells are filled. If you perform any of the above operations but drag the AutoFill handle (small box at the lower right of the active cell) with the RIGHT mouse button, Excel pops up small menu asking you what you want to put in the new range of cells - copy or fill series - before putting anything into the cells.
Clipboard	The Office Clipboard can contain up to 24 copied items. To see the list use Home/Clipboard and click on the expansion arrow in the lower right corner of the tab.
Copy cells	Highlight the cell or adjacent cells you want to copy, use Ctrl/C to copy, move the cursor to where you want the copy to be placed, and use Ctrl/V. If copying a range of cells, place the cursor in the cell that will be the upper left corner of the range when pasted. Or highlight the cell you want to copy, move the cursor to the border of the cell until the thick cross becomes a cross composed of little arrows. Press the Ctrl key and left click. The cursor changes into an arrow with a + to the upper right. Move the cursor to where you want the copy to be placed. Release the mouse button to place the copy in the cell. Highlighting more than one cell allows you to copy a range of cells. Also see AutoFill.
Edit the contents of a cell	Put the cursor on the cell you want to edit. a) Type the modifications directly into the formula bar. The previous contents will be overwritten. b) Double click on the cell and type the modifications into the entry in the cell directly on into the formula bar. c) Hit F2 and type the modifications into the entry in the cell directly or into the formula bar.
Merge cells and center text button	Highlight the cells you want to merge. Use the Home/Alignment tab.Click on the merge and center button (a little square with an "a" and two little arrows going in opposite directions). The cells will be merged and any number or text in any one of the cells will be centered. If there is more than one cell with characters in it, only the left most one will be saved and centered. Excel warns you if it finds more than one cell about to be merged with text or numbers in them.
Move a cell's contents	Highlight the cell you want to move, move the cursor to the border of the cell until the thick cross becomes a cross composed of little arrows. Left click and move the cursor to where you want the cell contents to be placed. Release the mouse button to place the contents in the new cell location. Highlighting more than one cell allows you to move a range of cells.
Formatting
Format with the cell painter button	With the cursor on the cell whose formatting you want to copy, click on the little paint brush on the standard toolbar. Then click on the cell you want to format similarly. If you want to format more than one other cell, double click on the little paint brush. Esc (or typing something else) cancels the cell painter function.
Formatting numbers, especially $'s	Select the cell(s) to be formatted and choose Home/Number. You can click on the expansion arrow in the lower right hand corner to bring up the entire formatting menu or you can use the shortcuts on the tab. The number format window has a drop down menu for just formatting the numbers. In finance you will be interested in putting dollar signs on numbers: Selecting Currency puts the $ right before the first number in the value Selecting Accounting puts the dollar sign on the far left edge of the cell. Or you can simply type a $ in the cell to the left of the cell containing the number needing a $. Or just type in the number with a dollar sign first. Excel automatically assumes currency format. The Number tab also allows you to add, with a click, an accounting $, comma separators for easy reading, percent format, and increasing or decreasing the number of decimal places.
Single and double underline numbers	Single Underline. Select the number(s) to be underlined. Use Home/Font and choose Underline (U). Ctrl/U also underlines numbers. Double Underline. Select the number(s) to be double underlined. Use Home/Font and choose Double Underline from the drop down menu next to U. To use an accounting double underline, click on the expansion arrow in the lower right hand corner of the Font tab and use the Underline drop-down menu. Accounting single and double underline is easier to read than ordinary single and double underline. The small drop-down menu with the B I U box changes the underline selection to the last one selected, assuming that's the one you would want to use the most frequently.

Formulas and Functions
Absolute and relative cell references	Use dollar signs in front of the letter and/or number in a cell reference if you wish copies of the formula to always refer back to the same cell. For example: If the cell formula in D13 is =C13+$B$6 and it is copied down the D column, the $B$6 will stay the same as the 13 changes to 14, 15, 16, etc. The C will not change because the copies are in the same column as the original formula. The C13 is a relative cell reference; the $B$6 is an absolute cell reference. If the formula is copied to E13, the formula will read =D13+$B$6. If the formula is copied to E14, the formula will read =D14+$B$6. Pressing the F4 key after you have typed a cell reference (but before typing a blank or the next character) will cycle through all the possible combinations of relative and absolute designations using $'s.
Display formulas	Use Ctrl-Tilde (~) for a fast way to toggle between displaying values or formulas. Another method is selecting Formulas/Show Formulas. Selecting it again toggles back to the values display. Note that displaying formulas messes up the column widths and the display of text. You may have to expand the column widths so that the entire formula is visible. Usually ignore what has happened to the text and formatting. Print the spreadsheet in that mode. Then revert back to the original "value" display. If you changed the columns widths to display the entire formulas, you will have to resize the column back to the original width.
Edit the contents of a cell	Put the cursor on the cell you want to edit. a) Type the modifications directly into the formula bar. The previous contents will be overwritten. b) Double click on the cell and type the modifications into the entry in the cell directly on into the formula bar. c) Hit F2 and type the modifications into the entry in the cell directly or into the formula bar.
Importing External Data


Pasting Excel into Word
As a Word table	It doesn't matter whether Page Layout/Sheet Options/Gridlines and Headings View and Print are toggled or not. In Excel highlight the area to be copied. Click Ctrl/C. In Word place the cursor where you want the table to appear. Click Ctrl/V. Gridlines and headings do not show.
As an embedded spreadsheet without gridlines or headings	Toggle Page Layout/Sheet Options/Gridlines and Headings View and Print all off. In Excel highlight the area to be copied. Click Ctrl/C. In Word place the cursor where you want the spreadsheet embedded. Use Home/Paste/Paste Special/Microsoft Office Excel 2003 Worksheet object to paste the spreadsheet into Word. Neither gridlines nor border row and column headings will show. Printing the page prints the spreadsheet without gridlines or headings. Double left click on the image to activate the Excel editor. Gridlines and headings do not appear. But the spreadsheet can be edited. Printing the page with the Excel editor activated does not cause gridlines or headings to appear.
As an embedded spreadsheet with gridlines	Toggle Page Layout/Sheet Options/Gridlines View on. In Excel highlight the area to be copied. Click Ctrl/C. In Word place the cursor where you want the spreadsheet embedded. Use Home/Paste/Paste Special/Microsoft Office Excel 2003 Worksheet object to paste the spreadsheet into Word. Gridlines appear but not border row and column headings. Printing the page prints the spreadsheet still without gridlines. Double left click on the image to activate the Excel editor. Gridlines appear but not headings. The spreadsheet can be edited. Printing the page with the Excel editor activated does not cause gridlines to print. If you want the gridlines to print, toggle both Page Layout/Sheet Options/Gridlines View and Print on before pasting into Word. The gridlines will print whether the Excel editor is activated or not.
As an embedded spreadsheet with headings	Toggle Page Layout/Sheet Options/Headings View on. In Excel highlight the area to be copied. Click Ctrl/C. In Word place the cursor where you want the spreadsheet embedded. Use Home/Paste/Paste Special/Microsoft Office Excel 2003 Worksheet object to paste the spreadsheet into Word. Headings do not appear but double click on the image to activate the Excel editor and the headings will appear. Printing the page will not print the headings whether the Excel editor is activated or not. If you want the headings to print, toggle both Page Layout/Sheet Options/Headings View and Print on before pasting into Word. The headings will print whether the Excel editor is activated or not.
As an embedded spreadsheet with both gridlines and headings	Combine the above two procedures. The gridlines and headings settings are independent of each other.
As an image without gridlines or headings	Toggle Page Layout/Sheet Options/Gridlines View off. In Excel highlight the area to be copied. Click Ctrl/C. In Word place the cursor where you want the image placed. Use Home/Paste/Paste Special/Picture (Windows Metafile) or Picture (Enhanced Metafile) to paste the image into Word. Neither gridlines nor border row and column headings will appear. Printing the page will not produce gridlines in the image.
As an image with gridlines but no headings	Toggle Page Layout/Sheet Options/Gridlines View on. In Excel highlight the area to be copied. Click Ctrl/C. In Word place the cursor where you want the image placed. Use Home/Paste/Paste Special/Picture (Windows Metafile) or Picture (Enhanced Metafile) to paste the image into Word. The image will include gridlines. The image will print with gridlines visible.
As an image with headings	Toggle Page Layout/Sheet Options/Headings Print on. In Excel highlight the area to be copied. Click Ctrl/C. In Excel choose Home/Paste/As Picture/Copy as Picture/As shown when printed. In Word place the cursor where you want the image placed. Click Ctrl/V. Image will be pasted with border and row and column headings. The page will print with the border headings.
As an image with gridlines and headings	Toggle Page Layout/Sheet Options/Gridlines View on. Toggle Page Layout/Sheet Options/Headings Print on. In Excel highlight the area to be copied. Click Ctrl/C. In Excel choose Home/Paste/As Picture/Copy as Picture/As shown when printed. In Word place the cursor where you want the image placed. Click Ctrl/V. Image will be pasted with gridlines and border and row and column headings The page will print with both gridlines and border headings visible.
Printing
Printing selections	Highlight the selection to be printed. Click on the Office Button. Select Print. On the next menu select Print to choose a printer and modify its properties. Or select Quick Print to print the spreadsheet without any changes. Or select Print Preview. Print Preview can also be selected from the properties of the printer. From Print Preview you can select Page Setup and change orientation (portrait or landscape), scaling (percent of normal or fit to one page), margins, headers and footers, and sheet options. Click on Print under the Office Button or from the Print Preview box.
Print cell formulas	Use Ctrl-Tilde (~) for a fast way to toggle between displaying values or formulas. Another method is selecting Formulas/Show Formulas. Selecting it again toggles back to the values display. Note that displaying formulas messes up the column widths and the display of text. You may have to expand the column widths so that the entire formula is visible. Usually ignore what has happened to the text and formatting. Print the spreadsheet in that mode. Then revert back to the original "value" display. If you changed the columns widths to display the entire formulas, you will have to resize the column back to the original width. Print the selection with the formulas displayed.
Worksheet Operations
Change column widths	Right click on the letter in the column heading. Choose Column Width from the menu that appears. Type in the width desired. Choose OK. Or place the cursor on the dividing line between two columns, left click, and drag the dividing line to where you want it. A little box appears telling you what the column width is at any time. Highlighting more than one column heading allows you to change the width of more than one column at a time by using the menu.
Change row height	Right click on the number in the row heading. Choose Row height from the menu that appears. Type in the height desired. Choose OK. Or place the cursor on the dividing line between two rows, left click, and drag the dividing line to where you want it. A little box appears telling you what the row height is at any time. Highlighting more than one row heading allows you to change the height of more than one row at a time by using the menu.
Copy a worksheet	With the Ctrl key pressed left click on the tab at the bottom of a worksheet and move the cursor to where you want the copy to be in the order of the worksheets. While you are copying the worksheet the cursor shows a little page with a + in it. The copied worksheet has the same name as the original but with a (2) after it. If you keep copying the same sheet, the number in parentheses keeps ratcheting up.
Indent lines	Financial statements must look presentable. They don't look presentable if the indented columns don't line up (as happens if you just use the space bar to put in some indentation). One way to get them to line up is to stick in another narrow column in front of the A column. Then type major headings in the new A column, and subheadings or numbers in the B column. Print the sheet without gridlines and the columns will look neatly indented.
Insert cell(s)	Highlight where you want the new cell(s) to be and right click on it. A dialogue box appears that lets you choose whether to simply move the existing cells right or down or to insert entire rows or columns where you want the new cell(s) to be. You can also use Home/Cells/Insert Cells, which brings up the same dialogue box.
Insert rows and columns	For rows: Right click on the row number on the far left. Select Insert on the pop-up menu. A new row is inserted above the row you clicked on. It duplicates the row height of the row above the one you clicked on. If you want to insert more than one row, highlight as many rows as you wish to insert. You can also insert rows by using Home/Cells/Insert/Insert Sheet Rows. For columns: Do the same as above but right click on the column letters. The new column(s) is (are) inserted to the left of the column selected.
Move a worksheet	You can rearrange the order of the worksheets by left clicking on the tab at the bottom of a worksheet and dragging it to the new place you want it to be.
Navigate on a worksheet	Use the arrows to move up or down, right or left, one cell at a time. Home: Moves to the beginning of the row. PageUp: Moves up one screen. Page Down: Moves down one screen. Alt/PageUp: moves one screen to the left Alt/PageDown: moves one screen to the right Ctrl/Home: moves to cell A1 Ctrl/End: moves to last nonempty cell of the worksheet End then Home: moves to last nonempty cell of the worksheet With the Scroll Lock on: Home: Moves to the upper-left cell displayed in the window. End: Moves to the lower-right cell displayed in the window. Arrow keys: Scrolls left, right, up, or down one cell.
Rename a worksheet	Right click on the tab at the bottom of the worksheet and choose Rename from the menu. Type in the new name and then Enter.
AA


AA

Excel 2003
Formatting
Conditional formatting	Choose Format/Conditional Formatting from the menu bar. A dialogue box appears that allows you to change the format of the cell depending on the contents of the cell. For example, you could have a number displayed in bold green characters if the number exceeded 100.
Formulas and Functions and the Formula Bar
AutoSum button	AutoSum menu: sum, average, count, max, min After typing in a series of numbers either in a column or in a row place the cursor in the next cell. Click on the AutoSum button (a capital sigma) on the standard toolbar and then Enter. That cell will then contain the sum of the numbers in the series. Clicking on the little down arrow next to the sigma will bring up the menu which will allow you to choose the other functions in the menu. Enter inserts the function and calculates the answer.
Cancel button	When you start typing into a cell a red X appears to the left of the formula bar. Clicking on the red X cancels what you have typed so far and exits the editing mode.
Enter button	When you start typing in a cell a green check mark appears to the left of the formula bar. Clicking on this green check mark finishes your entry and accepts what you have typed into the cell.
Fill in a series of numbers	Method 1: Select the cell where you want to start the series. The cell must contain the first value in the series. On the Edit menu, point to Fill, and then click Series. To fill the series down the page, click Columns. To fill the series across the page, click Rows. In the Step value box, enter the value that you want to increase the series by. In the Stop value box, enter the value you want to stop the series at. Method 2: Select the first cell in the range you want to fill, and then enter the starting value for the series. To increment the series by a specified amount, select the next cell in the range and enter the next item in the series. The difference between the two starting items determines the amount by which the series is incremented. Select the cell or cells that contain the starting values. Drag the AutoFill handle over the range you want to fill. The fill handle is the little black square in the lower right hand corner of the border highlighting a cell or cells. To fill in increasing order, drag down (for a column of numbers) or to the right (for a row of numbers). To fill in decreasing order, drag up or to the left. If you do not enter two numbers in two adjoining cells to start, dragging the fill handle will merely copy the starting value. The fill handle is them simply a copy handle. But an AutoFill Options menu will appear that will allow you to choose to fill a series rather than to simply copy cells.
Formulas	Excel formulas start with an equal sign, though Excel will usually put that in automatically. Examples of formulas: To add the numbers in two cells: =+C14+C15 (but they don't have to be adjacent: +C14+F128) To subtract two numbers: =+C14-C15 To divide two numbers: =+C14/C15 To multiply two numbers: =+C14*C15 To add a range of numbers: =sum(C14.C43) To raise an expression to a power: =(1+c13)^2 To put an expression in an exponent: =+c14^(b4-e10)
Functions inserted	a) Click on the on the Insert Function button (fx) just to the left of the formula bar. That brings up a dialogue box that will let you choose the function you want to insert. The box describes the form of the formula to use and what the function does. b) On the menu bar choose Insert/Function.
If/then formulas	If/then statements: =if(condition, then do this, else do this) The formula checks to see if some condition is true or not, such as the size of the value in a cell, or the relative sizes of two cells. If the condition is true, Excel then performs the task listed next. If the condition is not true, Excel performs the task that is listed last. For example, assume that in cell F9 you have =if(C9<100, D9+1,E9+2) Excel first checks to see if the condition =if(C9<100 is true. So, =if(C9<100, D9+1, means that if the value in C9 is less than 100 then the value in D9 will be increased by one and the result displayed in F9. =if(C9<100, D9+1,E9+2) means that if the value in c9 is not less than 100 then the value in E9 will be increased by one and the result displayed in F9.
If/then formulas: multiple conditions	If more than one condition must be met, use the AND() function in the statement. For example, in D4: =if(AND(a4>7,b4>10,c4>20),"Yes","No") This will print a Yes in D4 if all three conditions are true. Otherwise it will print a No. If at least one of multiple conditions must be met, use the OR() function. For example, in D4: =if(OR(a4>7,b4>10,c4>20),"Yes","No") This will print a Yes in D4 if any of the three conditions are true. Otherwise it will print a No.
#NAME	If you get a #NAME error message, it means the function you are trying to use is not available. Use Add-Ins from the Tools Menu to add the Analysis Toolpak. That will add many financial functions not part of the default installation.
Refer to another sheet	To display on a worksheet/page a number from another worksheet/page, use the following format. =Sheet1!A15. goes into a cell on Sheet2 and displays whatever number is in cell A15 of Sheet1. (Don't forget the exclamation mark.) To do a calculation using numbers from another sheet, each cell in the other sheet must be designated even if all the numbers are coming from the same sheet. For example, to add two numbers from Sheet3 and display the result on Sheet4 you would use a format like: =Sheet3!B15 +Sheet3!B16 This formula would go into whatever cell on Sheet4 that you want the sum of cells B15 and B16 from Sheet3 to display.
Refer to another file	You can even use numbers from another file. For example: putting the formula =[data.xls]Sheet1!A1 into a cell on any sheet/page of a file called results.xls would retrieve the number from cell A1 of Sheet1 of the file called data.xls. This makes it handy to create a data file separate from the files in which you do your data analysis. The data file can be created only once or modified only once. Every analysis file that references that data file can be updated automatically when it is opened. (Excel will prompt you to ask if you want the results file updated.)
Reference style	The default in Excel is to label the columns A, B, C, etc. But they can be designated 1, 2, 3, etc, just like the rows. This is called "R1C1 reference style". To enable this style use Tools/Options/General/Settings and toggle the "R1C1 reference style" box. To return to the default simply untoggle the box. Cell C5 in the default style would be cell R5C3 in the alternate style.
Relative and absolute cell references	Use dollar signs in front of the letter and/or number in a cell reference if you wish copies of the formula to always refer back to the same cell. For example: If the cell formula in D13 is =+C13+$B$6 and it is copied down the D column, the $B$6 will stay the same as the 13 changes to 14, 15, 16, etc. The C will not change because the copies are in the same column as the original formula. The C13 is a relative cell reference; the $B$6 is an absolute cell reference. If the formula is copied to E13, the formula will read =+D13+$B$6. If the formula is copied to E14, the formula will read =+D14+$B$6. Pressing the F4 key after you have typed a cell reference (but before typing a blank or the next character) will cycle through all the possible combinations of relative and absolute designations using $'s.
Importing External Data
Ctrl C / Ctrl V, i.e, copy and paste	Many times text and graphics can be imported from Web sites or Word documents by highlighting the desired material, copying, and pasting. Highlight the desired material. Hit Ctrl/C. Switch to Excel and place the cursor where you want the material that has been added to the clipboard to be pasted. Hit Ctrl/V. The material should be pasted into the Excel worksheet. It may require extensive reformatting. The best results occur when you are copying material from a table. Ctrl/C can be replaced by Edit/Copy from the menu bar. Ctrl/V can be replaced by Edit/Paste from the menu bar.
Download .csv files (text files)	The extension ".csv" means "comma separated values". The data is in text format with each piece of data separated by a comma. This is referred to as a delimited text file; the commas are the delimiters. When you open a .csv file, Excel usually recognizes this format automatically and arranges the data in columns without the commas. If Excel doesn't recognize the file format, it will place all the text in the first cell of each row. Use Data/Text to Column with the cursor highlighting all the first cells in the rows. Choose Delimited. Select Comma in the next screen; the default may be Tab or Space. The next screen will allow formatting of each column if desired. Click on Finish. The text should have been arranged into columns without the commas.
Download .dat files (text files)	The text in .dat files may be delimited by commas, tabs, spaces or may be arranged in columns already. When you open the file in Excel, all of the text is placed in the first cells of each row. Use Data/Text to Column with the cursor highlighting all the first cells in the rows. Choose Delimited. Select Comma in the next screen; the default may be Tab or Space. The next screen will allow formatting of each column if desired. Click on Finish. The text should have been arranged into columns without the commas. If the data is arranged in columns, choose Fixed instead of Delimited. The next screen will allow you to manipulate where Excel will split the columns.
Import External Data/New Web Query	See Web Query lower down.
Insert a stock quote: Research	Choose Tools/Research. Enter the stock ticker symbol where it says "Search for". Open the drop down menu "All Reference Books". Choose MSN Money Stock Quotes. A detailed stock quote appears in the window below. Click on "Insert Price". Excel inserts text like "US:HSY 51.80". Use Data/Text to Columns to decompose the text into columns. You can then do calculations on the stock price. The stock price is not refreshable.
Insert a stock quote: MSN Money	Choose Data/Import External Data/Import Data. Select MSN Money Central Investor Stock Quotes.iqy from the list. Click Open and then Ok. Enter the stock ticker symbol and toggle "Use this value/reference for future refreshes". Click on Ok. Excel posts an extensive stock quote into the worksheet. This data is refreshable. If you right click on any cell containing material just imported, a "! Refresh data" item appears at the bottom of the list. If you click on this, the material will be imported all over again. And wipe out any formatting you may have done after the first import.
Open a Web page directly	In Excel choose File/Open. Enter the url of the Web page you want to open in the File name box. Click on Open. Excel opens the Web page in a new workbook. When you save this workbook, be sure to choose the Excel format in the Save As dialogue box. Excel defaults to the .htm format because that was the format of the last file you opened. When you save the spreadsheet in .htm format, the spreadsheet files are saved in a separate folder. If you open the .htm file on a different computer, nothing will show up.
Web Query	Choose Data/Import External Data/New Web Query. Enter the url of the Web page from which you want to import data. The Web page will open in a smaller window, and small yellow boxes with an arrow in each will appear at the upper left corner of each block of data or graphic that can be imported. Click on the yellow boxes corresponding the material you want to import. The yellow boxes will turn green and the arrows will turn to checkmarks. Click on Import. Click on Ok on the Import Data dialogue box that appears. The data will be imported into Excel. The format will probably invite reformatting. However, this data is refreshable. If you right click on any cell containing material just imported, a "! Refresh data" item appears at the bottom of the list. If you click on this, the material will be imported all over again. And wipe out any formatting you may have done. If you don't want the material to be refreshable, you can un-toggle the appropriate box in Properties on the Import Data dialogue box.