Excel Procedures

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!

 

Audit Tracking. 3

Auto Fill 3

Auto Save. 4

Cell References. 4

Cells, Combine or Separate. 4

Cells, Edit. 5

Cells, Format As You Type. 5

Cells, Format Dialog Box.. 5

Chart Objects. 5

Comments. 6

Cursor Movement. 6

Data, Filter. 6

Data, Sort. 7

Data, Using Excel Data in other Applications. 7

Date. 7

File Locations, Change default. 8

File path. 8

Files, Close All 8

Font size. 8

Format, Copy. 8

Formulas. 8

Formulas, Calculate Age Based On The Date Of Birth. 8

Formula Cells, Hide From Users. 9

Formulas, Print. 9

Freeze Panes. 9

Function, Compute an average that excludes zero values. 10

Hard Returns. 10

Header or Footer, Add A Picture To A Worksheet’s. 10

Hide Columns, Rows, Worksheets and Workbooks. 10

Highlight Urgent Items. 10

Hyperlinks. 11

If Tests For Determining Multiple Conditions. 11

Insert, Rows & Columns. 11

Insert Copied Cells Between Existing Cells. 11

List Box.. 12

Lists, Drop-Down. 12

Macros. 12

Move Around the Worksheet. 13

Multiple selections. 14

Named Ranges. 14

Order of Precedence. 14

Page Setup. 14

Password protect a worksheet. 14

PivotTable Layout. 15

Print. 15

Protection. 15

Selections, Multiple. 15

Spelling. 15

Templates. 15

Text Alignment. 16

Text, Merge & Center. 16

Toolbar, Add Icons. 16

Validation. 16

Worksheets, Add.. 17

Worksheets, Combine. 17

Worksheets, Copy an entire sheet to another workbook.. 17

Worksheets, Insert. 18

Worksheets, Move. 18

Worksheets, Rename. 18

Worksheets, View All 18

Workspace Files. 18

 


Excel Procedures

Audit Tracking

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.)

  1. Display the Auditing toolbar by pointing to Auditing on the Tools menu and then clicking Show Auditing Toolbar. Before you use the Auditing toolbar, make sure Show all or Show placeholders is selected under Objects on the View tab in the Options dialog box (Tools menu).
  2. Select the cell that contains the formula for which you want to find precedent cells.
  3. To display a tracer arrow to each cell that directly provides data to the active cell, click Trace Precedents on the Auditing toolbar.
  4. To identify the next level of cells that provide data to the active cell, click Trace Precedents again.

Notes

  • Blue arrows show cells in the same worksheet that provide data to the formula in the selected cell. Red arrows show cells that cause errors. If the selected cell contains a reference to another worksheet or workbook, a black arrow points from a worksheet icon to the selected cell.
  • To select the cell at the other end of an arrow, double-click the arrow. To select a precedent cell in another worksheet or workbook, double-click the black arrow and then double-click the reference you want in the Go to list.
  • If the precedent cell is in another workbook, that workbook must be open before you can select the precedent cell.

Auto Fill

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.

Auto Save

  1. On the Tools menu, click AutoSave.
  2. Select the Automatic save every check box.
  3. In the Minutes box, enter how often you want Microsoft Excel to save workbooks.
  4. Select any other options you want.

Cell References

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.

Cells, Combine or Separate

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

                                                                                         =A2&”-“&B2&”-“&C2

 

     A                     B                        C

1   First Name    Last Name       Full Name

2   Mary               Smith                 Mary Smith

                                                        =A2&” “&B2

 

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:

  1. Highlight the cells you want to separate.
  2. Data
  3. Text to Columns
  4. Next
  5. Check whatever separates the words in the cell, such as Space or Comma
  6. Finish.

Cells, Edit

To edit while in the cell:

·         Escape

·         Backspace

·         Arrow keys

·         Delete

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.

Cells, Format As You Type

·            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.  

Cells, Format Dialog Box

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.

Chart Objects

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.

Edit Chart

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.

 

Delete data

Deleting the data and values in the spreadsheet will remove it from the chart.

Comments

Create:

  1. Click in the cell where you want to insert a comment.
  2. Insert, Comments
  3. Click somewhere in the spreadsheet to close the comment.

View:

  1. View, Comments to have them always showing.
  2. Move your mouse over the cell with the comment or select it to read it.

Edit:

1.        View, Comments

2.        Type your changes

3.        Click somewhere in the spreadsheet to close the comment.

Delete:

1.        Select cell that contains comment

2.        View, Clear, Comments

Print:

1.        File, Page Setup, Sheet. 

2.        Use the Comments drop-down menu to select where you want your comments printed.

Cursor Movement

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.

Data, Filter

To view a subset of data that you choose:

  1. Place your cursor in row 1.
  2. Data, Filter, Auto Filter.
  3. Click the auto filter arrow in the column you want to filter.
  4. Select the item name of what you want to view.
  5. To turn it off, Data, Filter, Auto Filter.

Data, Sort

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.

Data, Using Excel Data in other Applications

Copied Data

Use this to copy information into a document or PowerPoint presentation.

 

  1. Highlight the cells you want to copy
  2. Press the Copy icon on the toolbar.
  3. Switch to the other spreadsheet or document.
  4. Place your cursor where you want to insert the linked data.
  5. Edit, Paste.

Embedded Data

Use this to link information into a document or PowerPoint presentation that will be updated whenever you make changes to your Excel spreadsheet.

 

  1. Highlight the cells you want to copy
  2. Press the Copy icon on the toolbar.
  3. Switch to the other spreadsheet or document.
  4. Place your cursor where you want to insert the linked data.
  5. Edit, Paste Special.
  6. Choose Microsoft Excel Worksheet Object, OK.

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.

 

  1. Highlight the cells you want to copy
  2. Press the Copy icon on the toolbar.
  3. Switch to the other spreadsheet or document.
  4. Place your cursor where you want to insert the linked data.
  5. Edit, Paste Special.
  6. Choose Microsoft Excel Worksheet Object and Paste link, OK.
  7. Double-clicking the Excel data will take you back to the spreadsheet. All changes made to the spreadsheet will be reflected in the Word document.

Date

To quickly insert the current date in an Excel spreadsheet cell, press the Ctrl key and the Semi-Colon (;).

File Locations, Change default

To point to a specific folder when opening a file or saving: Tools, Options, and General Tab. Type in file location.

File path

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.

Files, Close All

To close all open files (but not the program), hold down the Shift key and select File, Close All.

Font size

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.

Format, Copy

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.  

Formulas

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.

+ Add   

-  Subtract

* Multiply            

/  Divide

Formulas, Calculate Age Based On The Date Of Birth

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.

 

Formula Cells, Hide From Users

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:  

 

  1. Select the cells for data input, and click Format | Cells.   

 

  1. On the Protection tab, deselect the Locked check box, and click OK. 

 

To hide formulas from view, follow these steps:  

 

  1. Select the formula cells you want to hide, and go to Format | Cells.

 

  1. On the Protection tab, select the Hidden check box, and click OK.  

 

  1. Go to Tools | Protection | Protect Sheet, and click OK. You can also set a password to prevent others from unprotecting the sheet.  

Formulas, Print

To print the actual formulas in Excel: Tools, Options, View tab. Under Window options, check Formulas.

Freeze Panes

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.

Function, Compute an average that excludes zero values

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.

Hard Returns

To enter a “hard” carriage return in a cell, press ALT+ENTER

Header or Footer, Add A Picture To A Worksheet’s

 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.

Hide Columns, Rows, Worksheets and Workbooks

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.

Highlight Urgent Items

Flag urgent items in a spreadsheet by using graphical elements.

  1. Insert one column to be used for this
  2. Place your cursor in this column next to an urgent item
  3. Type M in a cell.
  4. With the cell selected, go to Format, Cells, and Font.
  5. Choose Wingdings and select red from the color list.
  6. Choose a larger font size if desired and click OK.

Hyperlinks

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.

If Tests For Determining Multiple Conditions  

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."

Insert, Rows & Columns

Place cursor where you want to insert your row or column. Insert, Row (or Column).

Insert Copied Cells Between Existing Cells

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.

List Box

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:

 

  1. Select all the cells in the State column.

 

  1. Go to Data | Validation.

 

  1. On the Settings tab, select List from the Allow drop-down list.

 

  1. In the Source text box, enter the allowable entries separated by commas as follows: NJ, NY, PA, etc.

 

  1. Select the In-cell Dropdown check box.

 

  1. Click OK.

 

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.

Lists, Drop-Down

Pressing the down-arrow while holding down the Alt key will produce a drop-down list of previously typed entries to select from.

Macros

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. 

Move Around the Worksheet

·            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 ç

Multiple selections

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.

Named Ranges

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.

3.        Or: Formula = the Insert, Name, Paste and select Named Range

Order of Precedence

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.

Page Setup

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.

6.        Close.

Password protect a worksheet

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.

PivotTable Layout

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.

Print

·         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.

Protection

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.

Selections, Multiple

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.

Spelling

Tools, Spelling or ABC icon

Templates

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.

Text Alignment

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.

Text, Merge & Center

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.

Toolbar, Add Icons

View, Customize, Commands. Find the icon you want added to your toolbar and drag the icon to the toolbar.

Validation

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.

Worksheets, Add

To quickly add a worksheet, press Shift + F11. Excel adds a new blank worksheet before whichever sheet you were working on at the time.

Worksheets, Combine

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.

Worksheets, Copy an entire sheet to another workbook

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.

Worksheets, Insert

Three ways to insert worksheets:

 

  1. Insert, Worksheet
  2. Right-mouse click on a sheet tab, Insert, Worksheet, OK.
  3. Shift + F11

To increase the number of worksheets to all new documents: Tools, Options, General tab, and set number in Sheets in new Workbook.

Worksheets, Move

Left mouse click on sheet name tab and drag to new location.

Worksheets, Rename

Right mouse click on sheet name tab, type new name and press Enter.

Worksheets, View All

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.    

Workspace Files

 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:

  1. Open all three files.
  2. Go to File | Save Workspace.
  3. Browse to where you want to save the workspace, name the file, and click Save.  The best place to save this file is your desktop.

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