Madison, SD, USA
Dakota State University
Madison, SD, USA
Excel Tips
These tips refer to Excel 2003 and earlier. Tips for Excel 2007 will be added.
| Cells and Ranges | |
| AutoFill | If you drag the AutoFill handle (small box at the lower
right of the active cell) in a single cell with the LEFT mouse button, Excel
copies the highlighted cell into the new range of cells. If you drag the AutoFill handle in a single cell with the RIGHT mouse button, Excel pops up a menu asking you what you want to put in the new range of cells - copy or fill series. If you drag with the LEFT button the AutoFill handle in a single cell that is already the last cell in a series, Excel copies the cell but pops up a small menu you can access so you can change what you had just copied to be a fill series instead. If you drag with the RIGHT button the AutoFill handle in a single cell that is already the last cell in a series, Excel pops up an options menu before putting anything into the cells. |
| Clipboard | The Office Clipboard can contain up to 24 copied items. To see the list use Ctrl/C twice when copying something or use Edit/Office Clipboard from the menu bar at any time. |
| Copy cells | 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 and 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. |
| Copy cells with the copy handle | Select the cell containing the number or formula you want to copy. The copy handle (AutoFill handle) is the little black square in the lower right hand corner of the border of the highlighted cell. Drag the copy handle down or up or left or right to copy the contents of the selected cell into the range of cells desired. |
| Merge cells and center text button | Highlight the cells you want to merge. Click on the merge and center button (a little square with an "a" and two little arrows going in opposite directions) on the formatting toolbar. 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. |
| Move a cell | 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 | |
| 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. |
| Dollar signs on numbers | Select the cell and choose Format Cells. Choose the Number tab. Select
Currency and the $. (You can also apply other currency symbols from the menu.) The dollar
sign will be right before the first number in the value. You can select the number of
decimal places to show. Or you can select Accounting. That format puts a dollar sign on the value but the $ is aligned to the left. You can also select the number of decimal places. Or you can simply type a $ in the cell to the left of the cell containing the number needing a $. Or just type in $1,200.34. That doesn't work with formulas though. |
| Double underline numbers | Select the cell containing the number to be double underlined. Choose Format/Cells/Font. Go to the Underline menu. Choose Double Accounting, then OK. |
| 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. |
| Formulas and Functions and the Formula Bar | |
| 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. 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. |
| AutoFill | If you drag the AutoFill handle (small box at the lower
right of the active cell) in a single cell with the LEFT mouse button, Excel
copies the highlighted cell into the new range of cells. If you drag the AutoFill handle in a single cell with the RIGHT mouse button, Excel pops up a menu asking you what you want to put in the new range of cells - copy or fill series. If you drag with the LEFT button the AutoFill handle in a single cell that is already the last cell in a series, Excel copies the cell but pops up a small menu you can access so you can change what you had just copied to be a fill series instead. If you drag with the RIGHT button the AutoFill handle in a single cell that is already the last cell in a series, Excel pops up an options menu before putting anything into the cells. |
| 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. |
| Clipboard | The Office Clipboard can contain up to 24 copied items. To see the list use Ctrl/C twice when copying something or use Edit/Office Clipboard from the menu bar at any time. |
| Display formulas | Use Ctrl-Tilde (~) for a fast way to toggle between
displaying values or formulas.
Another method is: Tools/Options/View/Formulas |
| 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. b) Double click on the cell and type the modifications into the entry in the cell directly. c) Hit F2 and type the modifications into the entry in the cell directly. |
| 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:
Method 2:
|
| 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 |
| 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: |
| #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: |
| 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. 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. |
| 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. |
| Printing | |
| Border row and column headings | To print border row and column headings, go to the Print Preview. Choose Setup. Select the Sheet tab. Under the Print section, toggle "Row and column headings." (You can also select gridlines on this Setup page.) |
| Gridlines | To print gridlines go to the Print Preview. Choose Setup. Select the Sheet tab. Under the Print section, toggle "Gridlines." (You can also select Border row and column headings on this Setup page.) |
| Print cell formulas | Go to Tools/Options/WindowOptions and put a check in the Formulas box (just click on the box). The spreadsheet will then display formulas. You can also display formulas by using Ctrl-Tilde (~) (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.) Print the spreadsheet in that mode. Then remove the check to revert 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 wdith.) |
| Print to fit on one page | To print an entire selection on one page go to the Print Preview. Choose Setup. Select the Page tab, which is the default. Under Scaling, toggle the Fit to: dialogue boxes and set both to 1 (the default). |
| 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. |
| 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 cells | Highlight where you want the new cells to be and choose Insert/Cells from the menu bar. A dialogue box appears that lets you choose whether to simply move the existing cells left or right or to insert entire rows or columns where you want the new cells to be. |
| Insert rows and columns | For rows: Right click on the row number on the far left. Select Insert on
the pop-up menu. If you want to insert more than one row, highlight as many rows as you
wish to insert. (You can also use the Insert Menu at the top of the screen.) For columns: Do the same as above but right click on the column letters. |
| 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. Alt/PageUp: moves one page to the left Ctrl/Home: moves cursor to cell A1 |
| 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. |
| Easter Eggs | |
| Easter egg: Excel 97 | Excel 97 has a cool Fractal Landscape Easter egg in it: Open Excel 97. Press F5 [Go to]. Type in range X97:L97, then OK. [XL = Excel] Press the Tab key once to make M97 the active cell. Hold down the Ctrl and Shift keys and click on the Chart Wizard icon in the toolbar. You will then be flying over a weird fractal landscape. Use the mouse like a joystick. Also, left click to speed up, right click to slow down or reverse. Watch for a small shrine on a mesa top that displays a scrolling list of Excel programmers and developers. Use ESC to return to the spreadsheet. |
College of Business & Information Systems
Dakota State University
Madison, SD 57042
Site Manager: Jim Janke
Contact at: jim.janke@dsu.edu
http://www.courses.dsu.edu/finance/exceltip.htm
April 2, 2007