How To Clear Excel Formatting 
If you have modified the cell formats in a range of cells, and now need to get back to the original formatting without losing data, select the cells you want to clear and choose Edit/Clear/Formats. Doing so will take you back to the default format and will leave your data intact.

Go to Top

The easiest and quickest way to create a chart in Excel is to highlight the cell range you want to include in the chart and press F11. This creates the default column chart. Excel will create the chart on a separate Chart sheet.
To test this, type data into cells A1 through C3 and then select the range and press F11.

Entering Excel Formulas 
Excel has no special requirements when it comes to entering formulas. The one always necessary item is the equal sign (=). All formulas must begin with an equal sign. 

Although Excel will display all formulas in uppercase in the Formula Bar, Excel doesn't care about the case at all. When you enter formulas, you can save yourself some trouble by just entering them in lowercase.

All formulas (sum, average, etc.) place data cells in parentheses. For example
=sum (a1:a5)    works, but
=sum a1:a5      won't work.

Auto Fill In Excel 
Excel's Auto Fill works fine, but it does have a few quirks. For example, if you want to enter a series beginning with 1, you'll end up with nothing but ones, unless you know the trick. Here's the trick: type a 1 into cell A1. Hold down the Ctrl key and grab the cell highlighted by the little handle at the lower right corner. Drag down and you'll get 1, 2, 3, 4, etc.

If you drag down without using the Ctrl key, you'll get 1, 1, 1, 1 …
Auto increase year

1. Type 1991 on cell A
2. Type 1992 on cell B
3. Highlight both cells
4. Copy it down

Go to Top

=a1&d1
then press Enter. What you'll get in cell A4 is "Today's Date is 37250."

To concatenate a text cell and a date cell, you first have to convert the date to text. So go to cell A4 and type
=a1&text(d1,"mm/dd/yy")
then press Enter. Now cell A4 will display "Today's Date is 12/31/01."

Go to Top

Since Excel is math oriented, it's up to us to get usable print-outs. One way to make sure you'll get a good print-out is to always assign a print area to your worksheet. Suppose your data is contained in the area A1 through K25. Use the mouse to select A1 through K25 and choose File|Print Area|Set Print Area. Now print your worksheet.

Go to Top

You can often make your worksheet print-out look better if you print it in landscape mode (wide sheet). To do this, choose File/Page Setup and select the "Landscape radio button" and click OK.

Go to Top

This happens when you use a worksheet cell and then later the data. To check this out, run Excel and type data into cell H99. Now, delete the data in cell H99 and type data into cells A1 through C5. If you press Ctrl + End, Excel will take you to cell H99 even though it's blank, rather than to cell C5, which is probably where you want to go.

Add Borders To Excel Cells 
You can add a border around any cell or range of cells in Excel. This is a nice feature to use when you need to make a range of data stand out. To do this, all you have to do is select the cells to border and choose Format/Cells. When the Format Cells dialog box opens, click the Border tab. Next click the Outline Preset and then click OK to apply the border and close the dialog box.

Go to Top

Moving A Worksheet In Excel 
Let's suppose you've created a workbook of three worksheets. Now that everything is in place, you see that Sheet 2 really should be the first sheet and Sheet 1 the second sheet.

To move Sheet 2 so that it comes before Sheet 1, locate the sheet name tabs at the bottom of the workbook. Grab the Sheet 2 tab with the mouse and drag it to the left. When a small down arrow appears at the left side of the Sheet 1 tab, release the mouse button. Sheet 2 is now first and Sheet 1 is second.
Move Cells

1. Highlight Cells
2. When the cursor changes to pointing arrow, move to the place. It will change the formula function also.

To move to the specific Cell, Use the 'F5' key.
It opens GoTo Windows, Type Reference area, cell name to go

Go to Top

Sorting Excel Data 
When you have columns of related data that you want in alphabetical order, you have to make sure that you keep the columns together. For example, you have this list that you want to sort, keeping the fruits (column A) and their prices (column C) together:
 

In this case, you would select A1 through C5 and then choose Data/Sort. When the Sort dialog box opens, make sure Column A and 'Ascending' are selected and click OK.

If you fail to select Column C along with Column A, the prices will no longer represent the correct fruit.

Go to Top

Navigating To A Cell In Excel 
When you need to move to a specific cell in an Excel worksheet, you can scroll to the area that contains the cell and then click it. However, if the cell you want to select is buried somewhere in a rather large worksheet, you might find it easier and quicker to use Excel's Go To command.

Let's say you want to move to cell K324 in your worksheet. That's a lot of worksheet scrolling no matter how you do it. But you can press Ctrl + G, then type in K324 and press Enter and you're there.

Go to Top

Go to Top

The only way you can modify a protected worksheet is to remove the protection. To remove protection, choose Tools/Protection/Unprotect Sheet and type in the password.

Do not lose the password.. We don't know of any way to recover a worksheet without the password.

Merging Cells In Excel 
You want an attractive header in an Excel worksheet. The only problem you have is that the header spans several columns and it's difficult to center. What you need to do is use Merge and Center in Excel's toolbar:

Type in your header, then select all of the cells that your header spans. Click the Merge and Center button (to the right of the Align Right button near default).

Go to Top

When the Assign Macro dialog box opens, double-click the macro name that you want to assign to the button. And in the Customize dialog box, click Close to dismiss the dialog box and record your assignment.

Go to Top

Now, go through all the mouse clicks and key strokes that you need for the selected operation. When you finish recording your new macro, click the Stop button.

To use your new macro, press Alt + F8 and double-click the macro name.

Printing A Selection In Excel 
If you have a worksheet that you only need to print a portion of, you can tell Excel what to print. All you have to do is select the portion of the worksheet that you want to print and choose File/Print Area/Set Print Area. To print your selection, choose File/Print and click OK when the Print dialog box opens. To remove a print area, choose File/Print Area/Clear Print Area.

Go to Top

Go to Top

Did you know that you can zoom in on a range and have Excel choose just the right amount of zoom for your selection? Try this: enter some numbers into cells D5 through D9. Now, select the cells and choose View/Zoom. When the Zoom dialog box opens, select the 'Fit selection' radio button and click OK.

After you finish with the zoomed in view, you can simply press Ctrl + Z to undo the zoom. However, this won't work if you made any changes while in the zoomed view--the changes will get undone. In this case, choose View/Zoom again. When the Zoom dialog box opens, select the "100%" radio button and click OK.

Importing A Picture Into Excel 
There are two ways to get a picture into an Excel worksheet--you can copy it from another program and then paste it into Excel, or you can import it as a file. To import a picture, click where you want the upper left hand corner of the picture to appear and then choose Insert/Picture/From File. When the Insert Picture dialog appears, locate the picture file you want to insert and double-click it.

Go to Top

To do this, run Excel and choose File/Open. In the Open dialog box, click one of the files you want to print. Next, press and hold down the Ctrl key while you select the other files you want to print. Now, click the Tools button in the upper-right corner of the Open dialog box and then choose Print from the menu. Excel will then open, print, and close each of the selected workbooks.

Linking Excel Worksheets 
There are times when we can store some of our data on one worksheet and then link that data to another worksheet in the same workbook. As an example of how to do this, open a blank workbook and select Sheet3. Into cell A1, type 27 and press Enter. Now, click the Sheet1 tab and then click in cell A1. Type

=sheet3!a1
and press Enter. Cell A1 on Sheet1 will now display 27 (the contents of cell A1 on Sheet3).

Go to Top

Go to Top

Open a blank worksheet and enter

1
2
3
4

into cells A1 through A5. Now, choose File/Save As. Give your workbook a name and click Save. Choose Tools/Track Changes/Highlight Changes. Next, click in cell A2 and change the data to 9, then click somewhere away from cell A2. You will see that cell A2 is now outlined and contains a small triangle in the upper left corner of the cell. Move the mouse pointer over cell A2, and a comment will appear informing you that cell A2 was changed from 2 to 9.

Go to Top

You will notice that there is now a small red triangle in the upper right corner of the cell. This indicates that a note is present for that cell. If you move your mouse over the cell, Excel will display the message. To delete a comment, right-click the cell and choose Delete Comment.
Print comment

Comment has to be showing on the screen (show comment open)

Select File/Page setup/ sheet

Select Comments from the box and print report.

Go to Top

Adding A Map Button To The Excel Toolbar 
If you use Excel's Map very often, you might find it convenient to place a Map button into the toolbar. To do this, choose Tools/Customize. When the Customize dialog box opens, click the Commands tab. Now, under 'Categories,' click on Insert to select it. Under 'Commands,' locate the Map icon and use the mouse to drag it to the toolbar. Click Close to dismiss the dialog box.

Go to Top

Go to Top

Open a blank worksheet and enter some data. Perhaps you could enter
A
B
C
D
into cells A1 through A4, and then type
1
2
3
4
into cells B1 through B4. Now, select cells A1 through B4 and then choose Insert/Chart. When the Chart Wizard opens, accept the default settings and click Finish. Excel will place the chart on your current worksheet.
Go to Top
Inserting A Column In Excel 
Suppose you have a worksheet that already contains a bunch of data. Now you discover that you really need to add a new column C, moving the current column C (and all columns after C) to the right. To do this, click in column C and press Ctrl + Shift + Plus Sign (+). When the Insert dialog opens, select the 'Entire Column' radio button and click OK.  or

 Right click on column heading or row heading & choose Insert for a single col/row

 Highlighted multiple column or rows and right click & choose Insert or

 Right click on that highlighted area and add column or row which will give blank col/row on the top of the existing data

Go to Top

To do this, write your macro then choose File/Save As. Click the arrow at the right side of the 'Save as type' list box and select Microsoft Excel Add-In (xla). Name your new Add-In and click Save to save it and close the dialog box. Excel will automatically save the Add-In in the correct folder. To use the Add-In, one must choose Tools/Add-In and select the new name.

Naming Excel Ranges And How To Follow The Naming Rules 
When working with a large worksheet, it's a good idea to name the ranges that you will use for your calculation. To see how this works, enter some numbers into a few cells and then choose Insert/Name/Define and type in a name (test is OK for this example). To prove that your named range is working, click somewhere away from the range and press Ctrl + G to open the Go To dialog box. Double-click Test, and Excel will go to the named range.

There are some naming rules that you need to follow:

• A name can contain up to 255 characters.
• You must use a letter or an underscore for the first character.
• After the first character, you can use letters, numbers, periods, or the underscore character.

Go to Top

Go to Top

To make this change, choose Tools/Options. When the Options dialog box opens, click the Edit tab. Select (or leave selected) the check box labeled 'Move selection after Enter.' Now, click the arrow at the right side of the 'Direction' list box and select Right. Click OK to close the dialog box and record your selection. To test the change, type a number into cell A1 and press Enter. Excel should move to cell B1.

Setting Excel's File Options 
By default, Excel saves its documents to My Documents. However, if you would like to have Excel save all your workbooks to some other folder, you can permanently change Excel's default folder. To do this, choose Tools/Options. When the Options dialog box opens, click the General tab. Now, click in the entry box labeled "Default file location" and enter your new folder's path. Click OK to save your changes and close the dialog box.
Sort (Multiple sort)
Header and Data column has to have a space in order to use Sort function

1. Click inside of database
2. Select Data/Sort 

1. Highlight the range first
2. Data, Sort

Go to Top

To do this, enter a macro in a blank workbook. Choose File/Save As and name the workbook 'personal.xls.' Now, still in the Save As dialog box, navigate to C:\Program Files\Microsoft Office\Office\XLStart and click Save.

Next, choose Window/Hide and then choose File/Exit. When asked if you want to save changes, click Yes.

Your macro will now be available to all workbooks. Since personal.xls is hidden, it won't appear unless you choose Window/Unhide, which is what you'll need to do to add or modify any macros.

Go to Top

1
into cell A1 and press Enter. Next, move to cell C7, type
2
and press Enter. Now click cell B12, type in
=a1+c7
and press Enter. The result should be 3. Click cell B12 to select it and then choose Tools/Auditing/Trace Precedents. Excel will now draw an arrow from cell A1 to B12 and from cell C7 to B12 to display the precedents for the formula you entered in cell B12.

Go to Top

Run Excel and open a blank worksheet. Enter the names in cells A1 through whatever it takes. As an example, we entered names in cells A1 through A10 as shown here.

Amity
Beelzy
Cthulu
Damian
Eboz

Once you enter all the names, use the mouse to select all the cells. Now choose Tools/Options. When the Options dialog box opens, click the Custom Lists tab. Click Import and Excel will create a custom list from your column of names. Click OK to close the dialog box.
You can now delete the names in column A if you wish. 

Moving Folder Trees 
How do you represent the shift from one file tree structure to another? This may sound like a bunch of babble, but it happens all of the time, especially in the business world. Fortunately, you can easily move an entire group of folders to another folder in about 10 seconds.

To do this, open Explorer, find the highest level on your tree that represents your project, say: "Business Account." Now, find where you want to represent this "limb" of the tree as it will fall under the new file folder: "Vacation Account." Right-click on the "Business Account" file folder that you want to Move and drag it to the left side of the Explorer screen under the Vacation Account folder. Let go of the mouse and choose Move and all of the folders and files will move to the new location. Rename and/or delete folders as appropriate. You can also leave a folder under the name Business_Account_See_Vacation_Account so that you have a reference.

Go to Top

Printing Color Excel Worksheets on a Laser Printer
Suppose that you'd like to use color in your Excel worksheets, but the only printer you have is black and white. You can still use color in the worksheets and you can print them in black and white. Choose File/Setup. Then click the Sheet tab. Under Print, select Black and White and click OK.

Note that some colors won't print as you might expect. Light colors often print as just white and some darker colors print as black. The only solution to this problem is to experiment with some colors and make test prints. Then try to stick with those colors in your worksheets.

Hiding Data In Excel Worksheets 
Suppose you have data in cell A1 that you would like to hide from the rest of the world. To hide that data, click cell A1 and then choose Format/Cells. When the Format Cells dialog box opens, click the Numbers tab and then select Custom from the "Category" list. Now, double-click in the "Type" entry box and type in three semicolons (;;;). Click OK to close the dialog box and continue. The data in cell A1 is still there, and will work in calculations, but it isn't visible.

Go to Top

Excel Guidelines In Color 
In the last tip, we discussed how to get Excel to print the guidelines. This time let's look at how to view those guidelines in some color other than the default black. To do this, choose Tools/Options. When the Options dialog box opens, click the View tab. Now click the arrow at the right side of the "Color" list box (lower left-hand corner) to open the color palette and select a color. Click OK to close the dialog box and save your color selection.

If you print this on a color printer with gridline printing activated, the worksheet border will print in your selected color. However, the guidelines will still print in black.

Go to Top

There's a way to scroll an Excel worksheet without losing the headers. You can freeze the rows that contain the headers. To do this, click in Cell A3 and choose Windows/Freeze Panes. Rows 1 and 2 will now remain in view as you scroll down through your worksheet. To unfreeze the rows, choose Window/Unfreeze Panes.

Go to Top

Copying Cells In Excel 2000 
When you want to copy a range of cells to a new location, you don't have to paste the cells into an empty range. Instead, try it this way. Select the range of cells you want to copy and press Ctrl + C. Now, move to the location where you want the data to appear and press Ctrl + SHIFT + Plus Sign (+). When the Insert dialog box opens, click the direction you want the surround cells to move in and click OK. This is the approach you should use any time you want to paste data in between two existing columns of data.

Go to Top

Run Excel and enter some numbers into cells B1 through B5. Now, click cell B2 and press Ctrl + C to copy the cell's value to the Clipboard. Now, move to cell B4 and press Ctrl + C again. The Clipboard should now appear. If it doesn't, choose View/Toolbars/Clipboard. When the Clipboard appears it will show two Excel icons. Click in cell D1 and click the first icon on the left side of the Clipboard. This will paste the number from cell B2 into cell D1. Next, click cell E1 and then click the second Excel icon in the Clipboard to paste the contents of cell B4 into cell E1. You can click the X in the upper right corner of the Clipboard to close it.

Go to Top

Let's suppose that you have some nonprinting characters in cell A1. You want to get rid of the nonprinting characters, but you need to keep the data. Try this: go to cell B1, type

clean (a1)
and press Enter. Cell B1 will now display the text or data that you need, but not the nonprinting character.

Selecting A Data Range In Excel 
Suppose you have data in cells A1 through D6. If you click in cell A1 and hold down Ctrl + Shift, you can then press the right arrow key to select the row A1 through D1. To select a column, click in cell A1 (as an example) and hold down Ctrl + Shift while you click the down arrow. This will select the column from A1 through A6.

You don't have to stick to the beginning and ending cells, though. For example, you can click in cell B2 and hold down Ctrl + Shift and press the down arrow key to select cells B2 through B6.

Go to Top

Go to Top

Then select cell B1 and use the small handle at the bottom of the cell selection (the handle) and drag down to cell B5. Cells B1 through B5 will now display:

12
23
34

Using left and right functions
Example: Get just first name from full-name 

For example: Jean Brown -  =left(a1,4)
                  Last name only  -  =right(a1,5)
To get a position number for the blank in between firstname and lastname - =find(“ “, a1)
To get the length of name -  = len(a1)
To get only middle initial - =left(right(a1,10),2)

Go to Top

To make sure Excel sees your entry as a fraction, you can enter fractions such as 3/64 as

? 3/64

which Excel will interpret as the fraction 3/64 (0.184615384615385) rather than March 1964.

Go to Top

=randbetween(0,9)

and press Enter. Excel will immediately generate a number between 0 and 9. To generate a new number press F9 (recalculate).

If the function doesn't work, then you probably need to load the Analysis ToolPak Add-In. Choose Tools/Add-Ins. When the dialog box appears, select the check box labeled "Analysis ToolPak" and then click OK.

Go to Top

Creating An Excel Word Art Button 
Although you can use Word Art in Excel by choosing Insert/Picture/WordArt, Excel has no Word Art button as does Word. But, you can add a Word Art button to the Excel toolbar. To do this, choose View/Toolbars/Customize. When the Customize dialog box opens, click the Commands tab. In the "Categories" list, select AutoShapes and then drag the Word Art icon from the dialog box's right pane to the location you want on your toolbar.

Go to Top

Click in cell A5 now, and enter something. Click in cell A1 again to select it and then click the Format Painter button (its icon is a paintbrush). Your pointer will turn into a plus sign and a paintbrush. Click cell A5 and it will assume the formatting, color and all, of Cell A1.

Excel Is Ready For The Europe 
This is no great problem for you if you use Excel, though. All you have to do to activate the Europe currency tools is run Excel and choose Tools/Add-Ins. When the Add-Ins dialog opens, locate "Europe Currency Tools" and select its check box then click OK. Now, if you choose Format/Cells, then click the Number tab and select Currency, you'll find the Europe in the list.

Go to Top

To eliminate the double-click editing feature, run Excel and choose Tools/Options. When the Options dialog box opens, click the Edit tab. Deselect the check box labeled "Edit directly in cell" and click OK to close the dialog box and save your selection.

1  Double click on the cell and retype or
2. Use the formula bar, 'F2' key to retype
3. Click X from the formula bar to cancel
   ** Double click a cell shows formula

Go to Top

This is certainly simple enough, but if you'd rather not have to remember to type in that equal sign first, you can get Excel to drop the requirement. Just open a blank worksheet and choose Tools/Options. When the Options dialog box opens click the Transition tab. Select the check box labeled "Transition formula entry" and click OK.

Now, you can simply enter
96 + 23
to get the result of 119.

Note that you will now have to enter an apostrophe before a number that you want to enter as text.

Making Room For Headers In Excel
When you need to use headers that turn out wider than the cells, you can increase the cell width, or you can set the header text on an angle and leave the cell width as it is. To do this, type in a header and then choose Format/Cells. When the Format Cells dialog box opens, click the Alignment tab. Now you can use the Spin box labeled 'Degrees' to set your text angle.
After you set the angle, click OK to save your setting and close the dialog box. 

Go to Top

To place this toolbar in your Excel window, choose View|Toolbars|Reviewing. This will open Reviewing as a floating toolbar. However, if you use comments frequently, you can anchor the Reviewing toolbar at the top or bottom of the Excel window.

Email An Excel Worksheet 
An easy way to get a worksheet to someone outside your building, is to email it. All you have to do is save your worksheet and then choose File|Send To|Mail Recipient (as Attachment). When your email program opens, select the recipient, type in a note, and click Send.

Go to Top

• Clipboard can hold up to 12 files.
• Cells are looking up first then left when double click for ranges.
• To select non-contiguous cells, select first column and Ctrl & click next columns.
• To select contiguous cells, select first column and Shift & click next columns.
• Ctrl & Down arrow  - Bring to the last row
• Ctrl & Home  -  First Cell
• Ctrl & Right Arrow  - Last Cell
• Text Column  - automatic align left
• Number Column   - automatic align righ
• Double click on the name tab for rename sheet
• Press Enter key will bring the cursor down to next row but  will stay in the same Cell

Go to Top

1. Highlight all Column
2. Select Format/Column/AutFit Selection

 Added $ from formatting toolbar on the screen but gives an extra space
1. Use the Format/Cells/Numbers/ Currency
2. Highlited the range and right click & select Format/Cells/Choose currency
** Formatted number cell added an extra space

1. Highlighted the beginning cell to ending cell
2. Click on center format icon
3. It need to do each line

Go to Top

1. Bring the cursor down to the first cell that has numbers
2. Right click, Insert, Entire Column

1. Select a cell to make outstanding
2. Format/Cells/Alignment/Degrees
3. Change the Angle using degrees

1. Select all worksheet
2. Format, Autoformat and select the style

Go to Top

1. Highlight row headings for the rows you want to change heights
2. Drag mouse to the height you want to change  on the row heading area and let go, it will show vertical  double arrows when it's ready to drag. (same as Format, Row, Height)

1. Click on select heading areas that you want to set Auto filter on.
2. Data/Filter/Auto filter which will show down arrow box for each column.
3. Select a group

Select the area

When cursor turn to an arrow, Ctrl & drag will copy

Drag will move the area in the same sheet

Alt & drag will move to another sheet (to the sheet tabs)

Go to Top

• Select the sheet tab to copy from
• Press Ctrl & click and drag to next blank area and let go the mouse first before let go the Ctrl key
• Double click on the sheet tab to change the name

• Copy the area in to the clipboard
• Edit/Paste Special/ formats, value, formula etc..
• Use Add to accumulate values on operation in paste special

• Can have maximum 8 outlines
• Data/Group and Outline/Group or Auto outline
• “+”  -- there is more to see
• “ –“ -- that’s all
• If it has problem in ungroup/unhide, highlighted rows that are missing (one before and after), Format/Rows/Unhide

Go to Top

• Highlight range
• Type in name in the name box(first left box from the formatting toolbar and <Cr> (Name should be one word)

Or
• Insert/Name/Define, Create, Apply etc
• Select left column, OK 
• F3 key will bring paste name box to choose

• Select a cell to create
• Click on Data/Validation

Setting
•    Allow – Select ‘ list’
•    Source – highlight data range
•    OK

Go to Top

• Click the paste function(Fx) from the tool bar
• Choose the Lookup & Reference and VLOOKUP, OK
•   Lookup value - $C$4  (cell address)
•   Table array – table name
•   Col-Index-no – column number and OK

Go to Top

• Highlight only cells you want to get the result
• On the bottom of the status bar, sum= 1223 is showing, if select any range
• Right click in sum which will be open other popular functions
• Double click on a cell then it will give the selection box

• High light the cell and start typing numbers
• To add totals, click on the total column, click once &    or highlight beg cell to ending cell and click on 

To make an absolute value, press the 'F4' key. It will add $ sign where the cursor is. $E$10 etc..

1. Highlight the cell
2. Click on  Paste Function (Fx)
3. Select Statistical, Average or Max, Min etc.
4. Collapse the value 1 r  function box
5. Highlight the range, OK
** When highlight all grades on the bottom line, you can right click to see Sum, Average etc.

Go to Top

• Highlight the range of cells
• Data/Validation
• Setting  ---Allow – Decimal
• Data – Between
• Minimum – 0
• Maximum – 10
• Import message – Add message, title can be blank
• Error Alert
•  Style  - Stop
•  Error Message – Type your message

Protect workbook

• Highlighted areas
• Format/Cells/Protection, Turn off lockedb check mark. Only able to change those highlighted area but nothing else.
• If click on Hidden, no formula will be display on the window area
• Tools/Protection/Protect sheet or workbook

           (Don’t add a password)

Go to Top

• Highlight the area A
• Format/Cell, blank out the Locked column from the Protection tab
• Tools/Protection/Protect Sheet, then OK

Hide/Unhide column

• Select columns to hide

Format/Column/Hide

• Highlight areas
• Format/Cell/Protection, click on Hidden, OK
• Tools/Protection/Protection sheet, OK  (don’t need to add a password)

Go to Top

• Open a file
• Click File from the menu bar, choose the Save As
• Choose the Options, password to open

• Open a file
• Click File /Save As/Options
• Press the delete key to remove the password, if the password is already highlighted

• Select the area
• Format/Conditional format (Up to 3 conditions can be setup)

• Tools/Option/Custom list
• Click on the New list which will show the cursor on the list box
• Type in list then click Add

Go to Top

• In order to insert an extra row to all sheets that link, select all sheets using select the first sheet and Shift click on last sheet tab 
• Insert a row or column in the first sheet then it will add to all sheets
• Right click on sheet tab, select ungroup sheets will release select

• Click first file 
• Ctrl & click on others to open

Go to Top

• Click File/Page Setup from the menu
• Select the Sheet tab
• Under the Print titles area click on rows to repeat at top field.
• Click on red square box to hide the screen and highlight the columns heading
• Click the red square box to open the screen and click OK

• Open all files that you want to include in a workspace
• Click File/Save Workspace & give name of the workspace. It will create .xlw file
• When you open that workspace, it will open all files in a workspace

Go to Top

• Open or create a Summary file and select 1st cell you want to start.
• Select Data/Consolidate
• Function – Sum
• Reference – Click shrink box icon, click open sheet1, highlights all area with column heading and totals, click expands box icon, click Add, which will add to the All Reference column.

Continue to add all other sheets same as above.
Make sure turn on all “use labels in” lines.
• Click OK.

Go to Top

Go to Top

• Select the header
• Ctrl &  drag the arrow will copy the header
• Drag the arrow will move the header

• Sort the column first
• Data/Subtotal

Go to Top

The ability to instantaneously answer what-if questions is the most powerful feature of the Excel .
When you setup a worksheet, you should use as many cell references in formulas as possible.
The cell references in a formula often are called Assumptions.
Assumptions are cells whose values you can change to determine new values for formulas.

Preparation Steps

1. Start the Excel program
2. Bold all cells in the worksheet using Select All button above row heading 1 and to the left of Column heading A.
3. Enter the worksheet titles, column titles, and row titles. Increase the column widths.
• Type January and then click enter( )
• Change font size and click on the borders button and click the heavy bottom border
• Right click on the cell and format cells, Alignment tab, click 45 degree, OK
• Change the column width to 12"
• Drag the fill handle to copy 6 months or use the format painter button, select a new cell and then press the Left arrow key, when that cell is selected click the Format painter button, move the mouse pointer over a new cell, click a new cell to assign.
• Use the column indent, highlight columns then click increase indent toolbar.
4. Save the workbook.
5. Enter the assumptions in the range, copy cell names from the above rows and insert row. Esc key will remove the Marquee box
6. Enter the projected net sales for each of six months in row 4.
7. Display the system date in cell H2 using Paste function/Date & Time, Now
8. Enter the formulas that determine the monthly projected expenses and totals in Column H.
• To enter and IF Function - =if(b4>=$b$19, $b$18, 0)  - if b4 value is greater or equal to b19 then value = b18 otherwise 0
9. Create the 3-D column chart using range month and net incomes only, using Ctrl highlight.
10. Freeze panes using Window/Split to freeze titles to manage a big worksheet
11. Analyze the data in the worksheet by changing assumptions in the range using tool/goal seeking
• Drag the vertical split box from the lower-right corner of the screen so that the vertical split bar is positioned to the right of column D
• Click in total cell to highlight
• Select Tools/Goal Seek
• Click the To value text box, type amount and click changing cell to see the change.
12. To see the formulas press Ctrl & ` keys
13. To select both sheet and chart, select sheet and hold down the Ctrl key and click chart tab

Go to Top

1. Open up Microsoft Word 97.
2. Find the graphic image you want to use and tap Print Screen on your keyboard.  This copies the entire screen, not just the image you want.
3. Open up Paint by going to Start | Programs | Accessories | Paint.
4. Paste the screen into paint by clicking on Edit | Paste.
5. Select and cut or copy just the image you want from Paint.
6. In Microsoft Word, click on Edit | Paste Special. 
7. Remove the black check mark from Float over Text, then click OK.
8. Select that image by clicking it.
9. Go to Tools | AutoCorrect and put in the abbreviation you want for the image. (Image will show up in the box already with formatted text is selected)
10. Click on Add, then OK.

Go to Top

1. Assume you have the following:

2. Click on cell F2.  Click on the Paste Function    in the Standard Toolbar.
3. Click on All and find LOOKUP.
4. Click on LOOKUP and click OK.
5. Use the "Select Lookup value, array", and click OK.  The below screen should appear.

6. Lookup_value is cell E2.  Either click on it with the mouse or type in E2.  Press the tab key on the keyboard.
7. Array is cells A7:B11.  Either select them with the mouse or type in A7:B11.  Press the F4 function key to make them absolute value.
8. Your screen should appear as below:

9. Click on OK and AutoFill down.

Note: you could put the information in cells A7:B11 on a separate worksheet. 

Go to Top

1. Go to Data/Text to Columns
2. Choose Fixed Length
3. Click Next 
4. Click on the desired position to cut and next. This will break into 2 columns. 
5. Delete first column

Go to Top

• Estimate nest egg - use the FV function (Future Value of Money). 
• Type in formula field:=FV(interest rate, number of payment, payment amount)
•  If 5% per year, type .05 to represent 5%.  If monthly payments, (.1/12)
•  To calculate monthly payments to a 15 year college fund, type 12*15, payment amount $100, formula is =FV(.05/12, 12*15, -100)

• Calculate monthly payment - PMT function (Payment)
• Formula:=PMT(interest rate, number of periods, principle) For $19,000 at 2% financing: =PMT(.02/12, 4*12, 19000) for monthly payment of $412.21

Using the Pivot table

• Data/Pivot table report
1. Select the range of data
2. Select Data/Pivot table
3. Drop categories into boxes

Go to Top

If you have trouble rembering the syntax of Excel function, try this:
Type the function's name and then press <Ctrl>+ <Shift>+ A, which will paste in "dummy" arguments. For example, if you type =pmt into a cell and then press <Ctrl>+<Shift> + A.
You will get the formula=PMT(rate, nper, pv, fv, type).
Replaceing the dummy arguments with the actual arguments will then generate the calculation you need.