Table of Contents
These procedures and tips are not meant to be an Excel Manual. These are a collection of tips and information collected over the years. Enjoy!
When you audit a worksheet, you can trace the precedents (the cells that provide data to a specific cell) or you can trace the dependents (the cells that depend on the value in a specific cell.)
To quickly complete cells without typing, especially numbers
§ Copy one cell: Select one cell to copy. Place your cursor in the bottom right corner until your cursor turns into a black plus sign (+). This is called the Fill Handle. Hold down the left mouse and drag. When you release, you will have copied the contents of that cell.
a. There are sequences such as days of the week, months of the year, quarters, etc. that will appear when you select just one cell, such as January.
b. To create your own series: Tools, Options, and Custom Lists. Press Add and type each entry separated by a comma and space.
§ Copy a sequence: Select two cells to fill a sequence. Type 1 in the first cell and 2 in the second cell. Highlight both cells. Grab fill handle and drag.
§ Copy a formula for a numbered sequence: In the first cell of your sequence, type =ROWS(A$2:A2) using the cell location as your formula. The above formula shows that you are in cell A2. Grab fill handle and drag. When you use this sequence, as you add and delete rows, the numbers will adjust.
Depending on the task you want to perform in Excel, you can use either relative cell references, which are references to cells relative to the position of the formula, or absolute references, which are cell references that always refer to cells in a specific location. If a dollar sign precedes the letter and/or number, such as $A$1, the column and/or row reference is absolute. Relative references automatically adjust when you copy them, and absolute references don't.
To combine cells, such as Beech Acres account numbers or first and last names, create a formula in the blank cell where you want the full information to appear. The keys to this formula are 1) use the & sign and 2) use “-“or “(space) “ as your separators. Examples:
A B C D
1 Program Service Type Expense Acct Full Acct #
2 972 8290 6770 972-8290-6770
A B C
1 First Name Last Name Full Name
2 Mary Smith Mary Smith
To separate data in cells, i.e., to convert names that are in one cell, such as Smith, John or John Smith, into two separate cells for easy sorting:
To edit while in the cell:
· Arrow keys
To edit after pressing Enter:
· Ctrl + Z
· Type new text and Enter
· Edit cell by double-clicking, pressing F2 or clicking in Formula bar and make your corrections.
· To enter a value in Fraction format, type 0, press the spacebar, and type the fraction (including the slash). For example, typing 0, pressing the spacebar, and typing ½ enters one-half in Fraction format.
· To enter a value in Currency format, type a dollar sign [$] before the value. For example, type $10.00 to enter 10 dollars in Currency format. (Don't confuse the Currency format with the format generated by the Currency button on the Formatting toolbar, which formats the cells in Accounting format with a dollar sign.)
· To enter a value in Percentage format, type a percent sign [%] after the value, as in 70%.
· You can also format cells as text as you type. For example, if you enter the ZIP code value 08053, Excel formats it as the number 8053 by default. To configure Excel to format it as text, enter an apostrophe
· Type ['] before the value. In this case, typing '08053 formats the cell as the text value 08053.
To quickly access the Format Cells dialog box to change things like type style, alignment, or borders, select the cell or cells you want to format and press CTRL+1.
To use ChartWizard:
1. Select the data in the worksheet.
2. Click on the ChartWizard. A series of ChartWizard dialog boxes displays.
· ChartWizard 1 of 4 prompts for a chart type selection. Selecting Finish on the first step of the ChartWizard creates a chart with the default chart format.
· ChartWizard 2 of 4 confirms that the data in the range of selected cells is to be used in the chart.
· ChartWizard 3 of 4 allows you to format further the chart type you selected in the previous dialog box.
· ChartWizard 4 of 4 defines the location of the chart, as well as whether the chart is a new sheet or an embedded object.
NOTE: You can create a chart automatically by selecting the worksheet data and pressing F11.
Insert chart and chart axes titles (attached to an object on the chart i.e. title):
1. Click the Right mouse button on the chart.
2. Select Chart Options from the menu.
3. The Format Chart dialog box displays.
4. In the Titles tab, enter text for chart title, Value (y) and or Category (x) titles.
5. Click on OK to close the dialog box.
Adding data: After entering the new data and values in the spreadsheet, highlight the new range (include the old range). Move your mouse to the left corner of the range, and drag to the chart.
Deleting the data and values in the spreadsheet will remove it from the chart.
1. View, Comments
2. Type your changes
3. Click somewhere in the spreadsheet to close the comment.
1. Select cell that contains comment
2. View, Clear, Comments
1. File, Page Setup, Sheet.
2. Use the Comments drop-down menu to select where you want your comments printed.
To change where your cursor moves to after you press Enter, go to Tools, Options, Edit tab. At ‘Move Selection After Enter,’ use the drop-down arrow and select the direction you want.
To view a subset of data that you choose:
1. Data, Sort
2. Check My list has Header row
3. Use the drop-down arrow to sort by the column heading you want. You can sort on up to three choices.
Use this to copy information into a document or PowerPoint presentation.
Use this to link information into a document or PowerPoint presentation that will be updated whenever you make changes to your Excel spreadsheet.
Linked Data (DDE)
Use this to embed information into a document or PowerPoint presentation that will allow you to edit the worksheet without switching back to Excel or changing the Excel spreadsheet.
To quickly insert the current date in an Excel spreadsheet cell, press the Ctrl key and the Semi-Colon (;).
To point to a specific folder when opening a file or saving: Tools, Options, and General Tab. Type in file location.
In Excel’s Page Setup, you can insert the filename (spreadsheet name) in the header or footer but this did not include the full path (folders and subfolders). Here is a way to insert the path and filename into the worksheet:
In the cell where you want the full path to appear, you can type =cell (“filename”). Use no spaces and include the equal sign, parentheses and quotes. You may need to widen your column or your row to see the full name.
To close all open files (but not the program), hold down the Shift key and select File, Close All.
Click in the font size window and type a size. You can type any size you want, for instance, 4.5. You can type in sizes up to 409 in Excel.
To easily copy text formatting to other text, click inside the text formatting you want to copy from and press the Format Painter icon, then click and drag over the text you want to look like the original. Double-click the Format Painter to copy the formatting more than once.
A formula is a sequence of values, cell references, and operators that produces a new value from existing values. Start with an equal (=) sign to identify the cell as a formula.
1. Format the column that contains the date of birth entries: Format, Cells, Date, and choose 3/14/98 or 03/14/98.
2. Format the column that contains the age entries: Format, Cells, Number, and choose either 0, 1, or 2 decimal places.
3. After entering the first date of birth, go to the cell where you want the age to appear. For this example, the date of birth entry is in cell A2. When you write your formula, type in the real cell number of the date of birth. The formula should read =(NOW()-A2)/365.
a. =(NOW() represents today’s date
b. –A2 represents the date of birth
c. /365 represents the number of days divided by 365
4. Once you create the first formula, you can copy that formula to the rest of the cells.
5. You can double-click on the table below to activate the Excel Worksheet.
Excel's Protect Sheet capability can prevent users from changing cells, and it can hide formulas from view. For example, say you want to give users the ability to enter data in a worksheet for analysis, but you want to keep the proprietary formulas used in the analysis from being visible.
By default, the Protect Sheet feature locks all cells from data entry. To unlock cells for data input, follow these steps:
To hide formulas from view, follow these steps:
To print the actual formulas in Excel: Tools, Options, View tab. Under Window options, check Formulas.
To ‘freeze’ rows and columns so they stay visible as you scroll down and to the right, put your cursor in a cell below the rows and to the right of the columns you want to stay visible. For instance, if you wanted Row 1 (with all your headings) plus Column A visible, place your cursor in cell B2. Window, Freeze. Panes will freeze above & to the left of the active cell. To unfreeze, Window, Unfreeze.
While Excel's AVERAGE function ignores blank cells, it doesn't ignore cells that contain 0. This can result in inaccurate analysis of the data.
For example, let's say you want to calculate the average score for all students who took the final exam. Using the AVERAGE function, you might enter: =AVERAGE(B3:B23).
But if two students were absent and received a grade of 0, the result of the formula wouldn't give a true picture of the average grade. However, you can calculate the average and exclude the absentee students' grades.
Follow these steps:
1. Enter the following formula: =AVERAGE(IF(B3:B23<>0,B3:B23))
2. Press [Ctrl][Shift][Enter].
The entered formula first creates an array that includes only nonzero values in the range. The AVERAGE function then uses this array as its argument.
To enter a “hard” carriage return in a cell, press ALT+ENTER
Excel 2002 lets you add a picture to a worksheet’s header or footer. For example, suppose you want to create a header that features your company name and logo. Follow these steps:
1. Open the worksheet, and go to File | Page Setup.
2. On the Header/Footer tab, click the Custom Header button.
3. Put your cursor in the section you want the company logo to appear, and click the Insert Picture button.
4. Navigate to the picture you want to insert, select it, and click Insert.
5. To make any desired changes, click the Format Picture button.
6. Enter your company’s name in the section where you want it to appear.
7. To make any desired changes, click the Font button.
8. Click OK twice.
Row or column: Select the row(s) or column(s), right-mouse click on selected rows or columns, Hide.
Worksheet: Format, Sheet, and Hide. To unhide: Format, Sheet, Unhide and select sheet.
Workbook: Window and Hide. To unhide: Window, Unhide and select workbook.
Flag urgent items in a spreadsheet by using graphical elements.
Click on a hyperlink to go to another cell, sheet, chart, workbook, Web page, or other file.
1. Select the text or drawing object you want to display as the hyperlink, and then click Insert Hyperlink.
2. To link to an existing file or Web page, click Existing File or Web Page under Link to.
a. Browse to find File or Web Page and select the file or web page you want to link to
b. Click OK twice.
3. To link to a cell or sheet, select Place in this Document.
a. Type in the cell address and sheet. Example: If you want to link to cell B3 on the Sheet Tab named Budget 2005 in your Workbook, select it from Cell Reference and type in the cell number B35 in the Cell Reference window.
b. Click OK.
4. To remove hyperlinks: Right-mouse click, Hyperlink, Remove hyperlink.
One of the first functions beginning Excel users learn is the IF function, which takes the form =IF(condition,true_result,false_result). When you use this function, you typically test only one condition at a time. For example, in the formula =IF(A1>10,"Great","Average"), the only condition evaluated is whether the value in cell A1 is greater than 10.
However, there may be times you want to display a result depending on whether any one of multiple conditions is true. Suppose you need a formula that says: If A1 contains 10, or if A15 is greater than 20, or if A25 is less than 100, display "Great." But if none of these conditions is true, display "Average."
You can create such a formula by combining the IF and the OR functions. The OR function takes the form OR (condition1, condition2, condition3) and evaluates to a logical true value if any of the conditions is true. If all of the conditions are false, the OR function evaluates to a logical false. In our example, the formula would take the following form: =IF(OR(A1=10,A15>20,A25<100),"Great","Average")
If any of the three conditions is true, the OR function evaluates to true, and the formula returns "Great." If all three conditions evaluated by the OR function are false, the OR function returns a value of false, and the IF test returns "Average."
Place cursor where you want to insert your row or column. Insert, Row (or Column).
If you want to insert a range of copied cells between other rows or columns—instead of pasting over them—there’s an easy way to do it:
1. Select the cells you want to copy.
2. On the Edit menu, click Copy.
3. Select the area on the worksheet where you want to place the copied cells.
4. Press Ctrl + SHIFT + Plus Sign (+).
5. In the Insert dialog box, click the direction you want to shift the surrounding cells, and press OK.
The copied cells are inserted right where you want them, and none of your existing information is lost.
A list box makes it easier for Excel users to avoid errors. For example, a State field requires users to know the two-letter abbreviation for each state. To ensure accuracy, let users select from a list.
To create a list for the State field cells, follow these steps:
When users move to a cell in the State column, Excel displays a drop-down arrow. Clicking the arrow reveals a list of allowable states users can select for that cell. If users attempt to type an entry that's not on the list, Excel displays an error message.
To clear: Data Validation, Settings Tab, Any Value or Clear All.
Pressing the down-arrow while holding down the Alt key will produce a drop-down list of previously typed entries to select from.
Record a macro:
1. On the Tools menu, point to Macro, and then click Record New Macro.
2. In the Macro name box, enter a name for the macro.
3. The first character of the macro name must be a letter. Other characters can be letters, numbers, or underscore characters. Spaces are not allowed in a macro name; an underscore character works well as a word separator.
4. To run the macro by pressing a keyboard shortcut key, enter a letter in the Shortcut key box. You can use CTRL+ letter (for lowercase letters) or CTRL+SHIFT+ letter (for uppercase letters), where letter is any letter key on the keyboard. The shortcut key letter you use cannot be a number or special character such as @ or #. The shortcut key will override any default Microsoft Excel shortcut keys while the workbook that contains the macro is open.
5. In the Store macro in box, click the location where you want to store the macro.
6. If you want a macro to be available whenever you use Excel, store the macro in the Personal Macro Workbook in the Excel Startup folder.
7. To include a description of the macro, type the description in the Description box.
8. Click OK.
9. If you select cells while running a macro, the macro will select the same cells regardless of which cell is first selected because it records absolute cell references. If you want a macro to select cells regardless of the position of the active cell when you run the macro, set the macro recorder to record relative cell references. On the Stop Recording toolbar, click Relative Reference. Excel will continue to record macros with relative references until you quit Excel or until you click Relative Reference again.
10. Carry out the actions you want to record.
11. On the Stop Recording toolbar, click Stop Recording.
Tip If you want a macro to select a specific cell, perform an action, and then select another cell relative to the active cell, you can mix the use of relative and absolute references when you record the macro. To record a macro by using relative references, make sure that Relative Reference is pressed in. To record with absolute references, make sure Relative Reference is not pressed in.
Assign the macro to a toolbar:
1. Click on the Tools menu.
2. Select the Customize command.
3. Select the Commands tab in the Customize dialog box.
4. Click on the Macros option in the Categories list box.
5. Click on the Custom Tool Button option in the Commands list box and drag it to a new location on any toolbar.
6. Click on the Modify Selection button in the dialog box.
7. Click on the Name text field.
8. Type the macro name in the text field and press Enter.
9. Click on the Close button.
10. Click on the Custom Tool button just named.
11. The dialog box appears. Click on the macro you want to assign to the button.
12. Click on the OK button.
· Scroll bar
· Ctrl + G (type in Cell address) & Enter
· Ctrl + Home and Ctrl + End (last cell)
· Alt + PgDn=1 screen è
a. Alt +PgUp=1 screen ç
To select more than one row and/or column, after making first selection, hold down Ctrl key and make next selection. This is useful for formatting but cannot be used for copying.
1. Creating: Highlight cells. Press down arrow in Name box. Type name of range. Or: Highlight cells, Insert, Name, Define (type name of range), and Add.
2. Using: Ctrl+G (Go to), select Name range & press Enter.
Or: Formula = the Insert, Name, Paste and select
The order of precedence is a mathematical rule that dictates how formulas are calculated. A formula is calculated from left to right in a specific order: items in parentheses, exponents, multiplication and division, then addition and subtraction.
Use this feature to format and enhance your spreadsheet. You can center your spreadsheet, set margins, page orientation, fit to page for printing, add headers and footers, set rows to repeat and add gridlines.
1. File, Page Setup:
2. Page: Change Orientation to landscape, change Scaling to Fit to 1 wide, (no value) tall (click inside and delete the number)
3. Margins: Center horizontally & vertically
4. Header/Footer: Create Custom Header and type the title of your spreadsheet; Create Custom Footer and insert Date on left and page number on right.
5. Sheet: Rows to Repeat at Top, select rows that contain your headings that you want reprinted on each page, and press Enter. Check gridlines.
1. On the File menu, click Save As.
2. On the Tools menu, click General Options.
3. In the Password to open box, type a password and then click OK. Passwords are case sensitive. Type the password exactly as you want users to enter it, including uppercase and lowercase letters.
4. In the Reenter password to proceed box, type the password again, and then click OK.
5. Click Save.
6. If prompted, click Yes to replace the existing workbook with the open workbook.
When you create a password, write it down and keep it in a secure place. If you lose the password, you cannot open or gain access to the data in a password-protected workbook.
To remove the password:
1. On the File menu, click Save As.
2. On the Tools menu, click General Options.
3. Delete both passwords.
4. Click Save.
The PivotTable layout is the underlying structure of a PivotTable, which determines how fields are calculated. The four areas of a PivotTable layout are the DATA area, the ROW area, the COLUMN area, and the PAGE area.
· To print entire Worksheet: Print icon or File, Print
· To print a selected range: Select range to print. File, Print, Selection
· To print specific pages: File, Print, and type in first and last pages to print.
· To print entire workbook: File, Print, and select Entire Workbook.
1. Highlight data you DON’T want protected.
2. Format, Cells, Protection tab. Take üoff of Locked.
3. Tools, Protection, Protect Sheet, type in password and Reenter it to confirm.
4. Use will get error message if they try to enter data in locked cells.
5. To unlock, Tools, Protection, Unprotect sheet, and type in password.
To select more than one row or column, after making your first selection by clicking on a row number or column letter, hold down the Ctrl key and make your next selections. This is useful for formatting but cannot be used for copying.
Tools, Spelling or ABC icon
As with Word, you can create a template in Excel for frequently used worksheets. For example, suppose that you create a new worksheet each month to record the total number of volunteer hours worked in your organization. Rather than re-create the worksheet, you can delete the text and numbers entered for the month and save the format and formulas to a template.
Follow these steps:
1. Go to File, Save As.
2. In the Save As Type text box, select Template from the drop-down list.
3. Enter your template's name in the File Name box, and click Save.
Excel saves the template to the Templates folder with an .xlt file extension. To use the template, go to File, New and double-click the template's icon.
To make changes to your template, go to File | Open and select the Templates folder from the Look In drop-down list. Select and open your template, make your changes, and click Save. The next time you access your template from File | New, the document will include the updates you made to the template.
Besides aligning your text using left, center, right or justify, you can ‘wrap’ your text, make it fit into a cell, merge it, change the orientation, and even enter a hard return.
1. Use Left, Center, Right, Justify icons
2. Format, Cells, Alignment
® Wrap text=more than one line
® Shrink to fit=reduce the data to fit
® Merge cells=combine 2 or more adjacent cells
® Set text orientation by degrees
3. To enter a "hard" carriage return in a cell, press ALT+ENTER.
To center a title across more than one column, type your text in the first cell. Highlight the number of columns you want your text to cover, including the first cell with text. Format, Cell, Alignment and place a checkmark by Merge cells and select your text horizontal alignment, which is usually centered. Or press the Merge and Center icon.
View, Customize, Commands. Find the icon you want added to your toolbar and drag the icon to the toolbar.
1. Highlight range of cells you want to validate.
2. Data, Validation.
3. Setting Tab:
a. Allow: Select what criteria you will allow in the cells.
b. Complete the rest or the information depending on what criteria you selected.
4. Input Message: Type in message you want others to see when they select the cell. Excel will display a message box with the input message you entered.
NOTE: Click on that box and position it where you want it to appear.
5. Error Alert:
a. Select Stop, Warning, or Information.
b. Type in error message user will see if they enter data that does not meet the criteria.
If you have several single-sheet workbooks that are related and usually opened at the same time, it makes a lot of sense to have them in a single workbook. Excel makes it easy to combine several small workbooks into a single workbook with many worksheets.
1. In Excel, open all the workbooks that you want to combine into a single workbook.
2. Create a new, empty workbook.
3. Select Window | Arrange.
4. Choose Tiled, and click OK to show all the opened workbooks at the same time.
5. To move a sheet to the new workbook, click the sheet's tab and then drag the tab to the new workbook's window.
6. If necessary, change the sheet names in the new workbook by double-clicking the tabs and entering the new sheet names.
7. Save the new workbook.
8. Close all open workbooks. Don't save changes if you want to keep the data in the original workbooks.
1. Open the workbook into which you want to paste the copied sheet.
2. Switch to the workbook that contains the sheet you want to copy.
3. Right-click the Sheet tab of the sheet you want to copy, and then click Move or Copy on the shortcut menu.
4. From the To book drop-down menu, select the workbook that will receive the sheet. (To copy the selected sheet to a new workbook, click New book on the drop-down menu.)
5. Select the Create a copy check box. (If you don't select this check box, the sheet will be moved instead of copied.)
6. Click OK.
Three ways to insert worksheets:
To increase the number of worksheets to all new documents: Tools, Options, General tab, and set number in Sheets in new Workbook.
Left mouse click on sheet name tab and drag to new location.
Right mouse click on sheet name tab, type new name and press Enter.
When you have a great number of sheets and you want to move from one to the other, right click on the set of arrows in the lower left corner of the screen and the list of your worksheets will be offered to you. Just click on the name of the one you are look for. Arrange your sheets in alphabetical order to make the task even easier.
If you frequently work with the same group of files at the same time, you don’t need to spend extra time opening each file individually. Instead, you can create a workspace file that contains pointers to the files so you can open them all at once. For example, let’s say you normally work with three worksheet files at a time. Follow these steps:
When you’re ready to work with the files, double-click the icon to open all three files at once. When you close the .xlw file, all three files will close at once unless you’ve made any changes. If so, Excel will ask if you want to save your changes.
Tools, Options, Edit, Allow Auto Complete