SHORTCUTS.

INFO FUNCTION.
INFO is an interesting command, you can find out how much memory is available if you click on an empty cell and enter =info("memavail") If you click an empty cell and enter =info("osversion") you'll get some information on the operating system in use. Here is a list of some other keywords to use with INFO:
"directory" - Displays the current path
"memavail" - Displays the available memory (in bytes)
"memused" - Displays the number of memory bytes being used for data
"numfile" - Displays the number of active worksheets
"osversion" - Displays the current operating system version
"recalc" - Displays the current recalculation mode (Automatic or Manual)

Let's suppose you have a completed pie chart and one of the slices is considerably smaller than the others. Click the chart to select it, then click the slice you'd like to move out. Use the mouse to drag the slice to a new location. Don't worry about messing up your chart, the chart will only allow you to drag the slice in a direction that will maintain the chart's perspective.If you decide to put the slice back into the pie, click the slice and drag it toward the center of the chart as far as it will go.

PERCENTAGES
When you enter percentages in Excel, be sure to enter the numbers as decimals. Excel multiplies all entries in a cell formatted as Percentage by 100. For example, 20 is 20%. To see how this works, enter .2 into cell A1. Now select the cell and choose Format|Cells. Next, click the Numbers tab, select Percentage and click OK. The number in A1 will become 20%.

SUMMATION?
If you want to total the numbers in cells A1 through A5 of an Excel worksheet, you may want to display the sum in cell A7. Select cell A7 and enter =Sum(A1:A5)
Alternatively, you can click on A7 and enter =Sum(  Then click on cell A1, hold down the Shift key and click cell A5. Press Enter and your sum will be displayed because Excel will make the cell entries for you.

PERCENTAGES?
When you use percentages in Excel, enter the numbers as decimals. That is, if you mean to enter 20 percent, enter 0.2. When you format a cell as Percentage, any numbers in the cell are multiplied by 100. Try this: Enter .2 in cell A1, then select that cell and choose Format|Cells. Now click the Number tab, then choose Percentage and click OK. The cell will now display 20.00%.

ERASE A CELL
If you're entering a number into an Excel cell, and you see that you've made an error, all you have to do is press Esc. Pressing Esc erases the cell and you can start over.

EDIT
There are two ways to view and edit Excel formulas. If you select a cell that contains a formula, the formula will appear in the formula box right above the worksheet. You can click in the formula there to edit it. You can also view and edit the formula right in the cell. Double-click the cell and the formula will appear, ready to be edited.

SETTING EXCEL'S PRINT AREA
When you need to print a small worksheet, the best approach is to print only the cells that you're actually using. To set the area you want to print, use the mouse to highlight the needed cells. Now choose File|Print Area|Set Print Area.
When you choose File|Print (or press Ctrl-P) you'll print only the selected area.

MOVING NUMBERS
You've generated a rather long numbered list, and now you see that you'd like to organize the list a bit differently. So you select the text in one of the entries and then use the mouse to drag it to a new location. It doesn't work. How come?
When you want to move selected text from a numbered list, you need to select the paragraph mark and drag it to the new location along with the text. If the paragraph marks don't show in your document, choose Tools|Options and click on View. Select the Paragraph mark check box and then click on OK

TEMPERATURE CONVERSION
Since Europe expresses its temperatures in centigrade (or Celsius) and the USA most often use Fahrenheit, having a way to quickly switch between the two can be helpful. Excel 97 comes to the rescue with a command named CONVERT. To see how it works, type 68 into cell A1. Now move to cell A3 and type in =CONVERT(A1, "F", "C") and press Enter. This formula converts Fahrenheit (F) to Centigrade (C) and the result should be 20.
If you need to convert from Centigrade to Fahrenheit, type in =CONVERT (A1, "C", "F") and press Enter. If you left 68 in A1, the new result should be 154.4.
Note: if the Convert function doesn't work for you, choose Tools|Add-Ins. Make sure the Analysis ToolPak option is turned on and click on OK.

PRINTING BLANK SHEETS IN EXCEL
If you need to print a blank sheet between two or more worksheets that contain your data, you'll find that Excel ordinarily declines to print a data-free worksheet. However, if you select a group of empty cells and then choose File Print Area|Set Print Area, Excel will print a blank page.
If you want to print a blank page showing the Excel gridlines that you can use as a form, choose File Page Setup and then click on the Sheet tab. Select the Gridlines check box and then click on OK.

SPELLCHECK
If you have data on more than one worksheet in a workbook, you can check the spelling on all of them at the same time. All you have to go is right-click one of the worksheet tabs. This opens a menu from which you choose Select All Sheets. Now, press F7 to begin the spell check.

SELECTING CELLS
You know how to select a single cell in Excel--just click on the cell. And you probably know how to select a range of cells by holding down the mouse button as you drag across the cells you want to select.
Don't ignore the keyboard though--it's frequently more efficient to click on the first cell and then hold down the Shift key while you use the arrow keys to mark your cell range selection.

Here's how to set it up. Open Windows Explorer and locate the PowerPoint.exe file (depends on where you put it during installation--the default is C:\Program File\MS Office 97). Now double-click on the Windows folder in Explorer's left pane and scroll down so you can see the Send To folder. Now use the right-mouse button to drag the PowerPoint.exe icon to the Send To folder. When the icon is over the folder, release the button and then choose Create Shortcut(s) Here.

NUMBER OF DAYS
If you'd like to know how many days left until Christmas, run Excel and type ="12/25/97"-"11/01/97" (or whatever the current date is) into a cell. Excel will return the number of days (54) between the two dates. The trick here is to remember the quotes. If you don't use quotes, you'll get some very strange results

EXCEL'S AUTOSAVE
By default, when you use Excel's Add-in AutoSave, the program will prompt you when it's time to save the document. If Excel is minimized when the time for a save comes, its taskbar icon will blink. So, if you see a blinking icon, don't worry--it's just Excel asking if you want to save the document. You can put an end to this prompt if you like. Choose Tools, AutoSave. When the AutoSave dialog box opens, deselect the Prompt Before Saving check box and click on OK. No more blinking icons for you.

SCROLLING ALONG IN EXCEL
There are two ways to scroll through an Excel worksheet without changing the cell selection. One way is to use the scroll bars. You can scroll anywhere you want by clicking on the vertical or horizontal scroll buttons. Another (and easier) way to scroll without changing the cell selection is to press the Scroll Lock key and then use the arrow keys to scroll through the worksheet. You suspected that Scroll Lock key was good for something, didn't you?

TRANSPOSING COLUMNS AND ROWS
During worksheet development, you might find that you wish you had put your data in rows rather than columns, or columns rather than rows. The problem with this scenario is that you don't want to make all these changes by hand. But what if Excel could do it for you? Excel can.
Select the data range you want to transpose and then press Ctrl-C to copy it. Now click where you want it to appear and then choose Edit, Paste Special. When the dialog box opens, select the Transpose check box and click on OK.

FILL OPTION
Excel's Fill option can save you lots of typing and headaches. Let's say you need to make a list of a series of dates. And what you really need is a list of workdays--Monday through Friday.
Select the first cell and enter the start date. Let's say you enter Monday November 3, 1997 (11/03/97). Now use the mouse to select the first cell and the other cells you want to fill with the dates. If you're going to enter dates for the entire month of November, you need to select about 22 cells. If you select more than you need, you can delete the extras later.
With all the cells selected, choose Edit, Fill, Series. Now select Date and Weekday and click on OK. The column will fill with the series of dates that represent weekdays in November.

MAKE IT PROPER
Here's an Excel function that you may not have seen. It's called Proper, and its job is to turn text into words with initial capital letters.
Why would you need Proper? If you purchase mailing lists, you know that many of them come in all caps. This is OK, except that you might like to convert them to a more standard (and more attractive) form before using them on a mailing label, or in a letter.
This is where Proper comes in. Of course, its most efficient use is in a macro, but you can check it out without writing a macro. Press the Caps Lock key and type something into cell A1. Perhaps THIS WILL DO FOR AN EXAMPLE.
Now move to cell A2 and type =Proper(A1) and you'll get This Will Do For An Example.

Proper doesn't deal only with all caps, any text you present to Proper (uppercase, lowercase, mixed) will come out with the initial character of each word capitalized

TEXT BOXES AND OTHER SHAPES
To place a text box in Excel, you need the Drawing toolbar. So first, choose View, Toolbars, Drawing. Now click on the Text Box button in the Drawing toolbar (its icon resembles a printed page). Use the mouse to draw the text box in your worksheet.
Once you've drawn the text box, you probably want to choose the font and font size for the text. Click on the text box and choose Format, Text Box. When the Format Text Box dialog box opens, choose your font, font size, and even color and click on OK. Want the text centered in the box? Click on the Center button in the Formatting toolbar. Now, type your text.
When you save the worksheet, the text box and all its parameters are saved with it. When you open the worksheet again, the text box will appear just as you left it.

You're not limited to placing text in a text box; you can also add text to a shape you create using AutoShapes. To do this, click on the AutoShapes button in the Drawing toolbar and then choose a shape from the expanded list. Use the mouse to draw the shape.

A SPECIAL DATE
You can do more with Excel's date formatting than you might think. Suppose you'd like a particular cell to show only the month and the day. Click on the cell (to select it) then choose Format, Cells and select Custom. Under Type enter mmmm dd and then click on OK. Let's say the date is 10/31/97. Type that in and the cell will display October 31.
Suppose now that you'd like to show the day of the week, the month, and the day (numerical). Choose Format, Cells and select Custom again. This time, type dddd, mmmm dd to produce a display of Friday, October 31. Click on OK to close the dialog box and record the changes. If you want to add the year, go back to the Format Cells dialog box and type dddd, mmmm dd, yyy Now click on OK.

IN SUMMARY
Want to quickly sum a row of numbers in Excel? Enter all the numbers and then click on the cell in which you want the sum to appear. Now click on the Sum button in the toolbar. Use the mouse to select the numbers you want to sum and then press Enter. The sum appears immediately and you didn't even have to type in a formula.

TRANSPOSING COLUMNS AND ROWS
During worksheet development, you might find that you wish you had put your data in rows rather than columns, or columns rather than rows. The problem with this scenario is that you don't want to make all these changes by hand. But what if Excel could do it for you? Excel can.
Select the data range you want to transpose and then press Ctrl-C to copy it. Now click where you want it to appear and then choose Edit, Paste Special. When the dialog box opens, select the Transpose check box and click on OK.

CONTIGUOUS?
Excel will let you select rows and columns of cells whether they be contiguous or noncontiguous. Let's look at how this works.
To check out Excel's selection options, first open a new sheet. Now start at cell A1 and type 1, 2, 3, 4, 5 moving downward (that is, enter numbers in cells A1, A2, A3, A4, and A5. Repeat those entries in cells D1 through D5 and cells E15 through E19.

If you select columns that have spaces (cells) between them, Excel considers them contiguous if they are adjacent. For example, cells A1 through A5 and F1 through F5 are adjacent. However, cells A1 through A5 and F2 through F6 are not adjacent. Also, cells A1 through A5 and A15 through A19 are adjacent. You can select them and then choose Edit, Copy. Then you can move to a new location (perhaps H1) and press Ctrl-V (or choose Edit, Paste). This will paste all the numbers into cells H1 through H10.

A WORKSHEET IN EXCEL, A TABLE IN WORD
There are several ways to get Excel worksheet data into a Word document. In all cases, you begin by selecting the cells you want to put into the Word document. So select the cells and then press Ctrl-C to copy your selection to the Clipboard.
Now let's look at our first method. Switch to your Word document and choose Edit, Paste (or press Ctrl-V). This will paste the worksheet selection into Word as a table. Note that this method does not provide a link to the Excel document. Changes made in Excel will not appear in Word.
To insert the worksheet selection as a linked file, copy the selection (Ctrl-C) and then switch to Word. Now choose Edit, Paste Special. When the Paste Special dialog box opens, select Formatted Text (RTF) and Paste as Link. Now click OK, and the worksheet selection will appear as a linked table in your Word document.
You can also insert a linked table by copying the worksheet data (Ctrl-C) and then choosing Edit, Paste Special. This time select Microsoft Excel Worksheet Object and Paste as Link, and the data will appear as just numbers--no table.
In both cases, the data is linked to the Excel worksheet, so any changes you make in Excel will also appear in the Word document.
If you use Edit, Paste Special and choose Formatted Text (RTF) and Paste as Link, the data will appear in table form. You can select the table and then choose Table, AutoFormat to format the table to suit you. This is also the case when you simply copy the worksheet data and then switch to Word and press Ctrl-V. You can format the table as you wish.
If you paste the data using Edit, Paste Special and choose Microsoft Excel Worksheet Object and Paste as Link, the data will not appear in table form. Therefore you can't apply table formatting.

MAKE A QUICK MOVE
In Excel, you can copy a group of cells by selecting them and then pressing Ctrl-C. This copies the cells to the Windows Clipboard. Now you can move to another cell range and press Ctrl-V to paste the cells to the new location.
If you prefer to move the group of cells, simply select them and press Ctrl-V at a new location.
In either case, you'll find that you can eliminate the Ctrl-V part of the paste operation by clicking into the first cell of your target range and pressing Enter.
This shortcut doesn't actually save any keystrokes, since you press Ctrl and V at the same time to make the paste. But simply pressing Enter has a certain elegance about it.

MAKE A GROUP OF DATES
There is an easy way to fill a series of Excel cells with a sequence of dates. All you have to do is type a date into a cell. Now grab the cell by its little handle (you'll see the handle if you look closely at the lower right corner of the cell when the cell is selected) and drag it through the cells you want to fill.

You can use the same technique to fill a series of horizontal cells with the sequential dates. Just enter the first date and then grab the cell and drag horizontally.

SEEKING YOUR GOAL
Let's say you have a worksheet designed to handle your widget sales. Here you are at the start of your fiscal year and you know you must realise at least £2,000,000 in widget sales during the coming year to meet your goals.
How many widgets do you need to sell to take in £2,000,000? This isn't so hard to calculate. You can divide £2,000,000 by the Widget price of £255.54 to get 7826.56335603 (round to 7827).

You can make the calculation very quickly in Excel if you use the Goal Seek function. And this way, you don't have to add anything to your worksheet or run a calculator program. Let's look at an example.

                Widget Price £255.54 (cell A1)
                Number of Widgets sold (cell A2)
                Total Sales (cell A3). The formula is =(A1*A2).

At this point, cell A2 is empty. Because it's the first of the fiscal year, you haven't sold any widgets yet.
Now click cell A3 (Total) to select it and then choose Tools, Goal Seek. When the dialog box opens, you'll see three entry boxes: Set Cell, To Value, and By Changing Cell. Set Cell will be set to A3, since you selected that cell before opening the dialog box. Into the entry box labelled To Value, type 2000000 (your goal). In the entry box labelled By Changing Cell, type A2
This is the value you want to determine. Click OK and cell A2 will display 7826.563. Round this number to 7827. You'll need to sell 7827 widgets to take in £2,000,000.
After you read and record the number displayed in cell A2, click Cancel and your worksheet reverts to its original entries (blank in this case).

MIGHTY MOUSE!
If you use the IntelliMouse, you can use the centre wheel to move up and down in an Excel worksheet. If you don't use IntelliMouse, you'll just have to be content with using the scroll bars. Here are some notes on scrolling in Excel:
When you drag the scroll box, a little yellow tool tip will appear to help guide you. If you're scrolling around and decide you want to see the active (selected) cell very quickly, press Ctrl-Backspace. Excel will immediately transport you to the page that shows the active cell.
You can also use the middle button of the Logitech (or Logitech-compatible) mouse to perform the scroll.
To scroll, just middle-click anywhere in the document window and drag away from the special symbol that appears. The farther you move from the symbol, the faster the scrolling. This appears to work in all the Office programs.

POWER OF NUMBERS
Excel offers two ways to calculate the power of a number. Let's look at both.
Click in cell A1 to select it. Enter =22^3 and press Enter. Excel will perform the calculation and report that 22 to the third power is 10648. Now go to cell A2 and type =POWER(22,3) then press Enter. Once again, Excel will report that 22 raised to the third power is 10648. Take your pick--either one works.

MULTIPLE SHEETS
When you need to work with multiple sheets in Excel, you may want to make the headers the same on all the sheets. Here's a little trick that works very well.
Hold down the Ctrl key and click the name tab of each sheet that you intend to use. Now type in your headers on one of the sheets. Hold down the Ctrl key again and deselect all the sheets except Sheet 1. Now click Sheet 2. The header will appear on Sheet 2 just as it did on Sheet 1, and just as it will on all the sheets you selected initially. You're not limited to entering headers--you may also want to enter formulas this way.
This trick is handy for entering headers. Just don't forget to turn it off before you start entering data. Otherwise, all the sheets will show the same data.

FIND AND REPLACE
You can use Excel's Find and Replace utilities in much the same way you would Word's. Let's say that you'd like to find the cell that contains the number 21,987.23. You know it's in there, but you just don't know where. Choose Edit, Find and, when the dialog box opens, type in the value and click Find Next.
Now let's suppose you'd like to find all occurrences of the value 25 and replace them with 35. Choose Edit, Replace. When the dialog box opens, enter the two values and click Find Next. When Excel finds the first occurrence, click Replace (if that's what you want). If you're very sure this is what you want, you can click Replace All and Excel will replace every instance of 25 with 35. Here's where you can have a problem. Let's say that you have a number of occurrences of 25 and you're sure you want to replace them all with 35. But, do you have some occurrences of 25,000? If you do and you're not careful, 25,000 will get replaced with 35,000.
To prevent this, select Find Entire Cells Only before you do any kind of search and replace. This tells Excel to find and replace only those occurrences of 25 (not 25,000).

INSERT A NEW SHEET
Let's say you're just working away on a multiple-sheet workbook. You suddenly realise that you really should have a sheet between Sheet 1 and Sheet 2. All is not lost. You don't have to start all over. All you have to do is click the Sheet 2 name tab to select it and then choose Insert, Worksheet. This will insert a new sheet before Sheet 2. Since Excel's default workbook contains 16 sheets, the new sheet will be named Sheet 17.
If a given workbook needs only one or two sheets, why keep all those other sheets around?
Let's say that you have a workbook that uses only one sheet. Go to the bottom of the worksheet and click Sheet 2. Now hold down the Shift key and use the Move to End button (it looks like a right-arrow running into a vertical line) to move to the end of the workbook. Click Sheet 16. Now choose Edit, Delete Sheets. You'll get a dialog box warning you that you are about to permanently delete the worksheets. Click OK.
If you should need another sheet, you can choose Insert, Worksheet. The new sheet will be placed before Sheet 1 and will be named Sheet 17.

FUNCTIONAL DOLLARS
Here's an Excel Dollar tip for you, you can have more than one Dollar format in an Excel worksheet. Try this: Type into cell A1 4,234.22
Now click cell A1 and choose Format, Cells. When the Format dialog box opens, click the Number tab (if necessary). Now click Currency and then click OK to accept the default and close the dialog box. The number will appear right-justified in the cell--just what you'd expect.
Now type into cell A2 =DOLLAR(4234.22) and press Enter. This time, the number will be in Dollar format, but it will be left justified in the cell. The reason for this apparent anomaly is that the DOLLAR Function converts the number to text format. This doesn't mean that the number formatted by the DOLLAR function won't work in calculations. To confirm its proper operation, click cell A3 and type =A1+A2 and press Enter. Cell A3 will display $8,468.44, which demonstrates that both numbers were included in the SUM.

CONCATENATION
When you develop those truly cool worksheets, you don't want to miss anything. So, let's see what concatenation can do to help you make that cool worksheet even more cool. If you have information in several cells that you want to pull together in one cell, try this.
Let's say that cell A1 contains the word Sally and cell A2 contains Sally's sales total for the month. You can go to cell C3 and type =CONCATENATE("Salesperson ",$A$1, " is this month's leader with £", $A$2, " in sales.")
When you press Enter, Excel will display the line Salesperson Sally is this month's leader with £23456.96 in sales.

PUT IT ALL IN
If you need to type a lot of text into a single cell, you can control the width of your text by typing Alt-Enter to insert a carriage return. When you need to use a Tab, press Ctrl-Alt-Tab. When you type into a cell, you can press Alt-Enter to add a Carriage Return.

COMMA COMA
When you enter commas into a cell's contents, make sure you get them in the right place. If you don't, you'll confuse Excel.
Let's say that you've just entered 4567.89 and you want to place a comma after the 4, to make the number read 4,567.98  If you should inadvertently enter the comma after the 5, you'll get 45,67.89 and Excel will assume you want it to be a text entry. If you enter a number with commas, and you don't see the number move over to the right side of the cell, check those commas.

GOT TIME FOR TIME?
If you want to insert the date and time into an Excel worksheet, you can click a cell and enter the date. To insert the current date into a cell, you can type =TODAY() and press Enter. If you're in too much of a hurry for all that typing, try pressing Ctrl-; (semicolon) to insert the time, and Ctrl-: (colon) to insert the date. The inserted information will display using the cell's current format.

SETTING FORMAT THE RIGHT WAY
If you want to quickly set the format of a single cell in Excel, select the cell and then right-click it. When the pop-up menu opens, select Format Cells. This opens the Format Cells dialog box. Once in the dialog box, you can click the Number tab to set up the cell's format. While you're at it, you can also set Alignment, Font, Border, Patterns, or Protection. Make your selections and click OK to record your choices and close the dialog box.

ENTERING DATES
You can enter a date into an Excel cell and then drag that cell to enter sequential dates. You can do the same for sequential weeks (or any time period). That is, if you'd like a list of Mondays in a month, you can enter the date for the first Monday in one cell, then the date of the second Monday in an adjacent cell. Then highlight both cells and drag. Excel will create a sequence of Mondays (or whatever day you want--use Friday if it will make you feel better). All the dates in the sequence will be seven days apart.
Here's an example:
Go to cell A1 and enter 6/4/98 Now go to cell A2 and enter 13/4/98 Highlight both cells, grab the little handle, and drag down three cells. Excel will now display 6/4/98, 13/4/98, 20/4/98, 27/4/98, 4/5/98 in row A.

PASTING CHARTS
To paste Excel charts into a PowerPoint slide without pasting the entire worksheet, do the following.
Right-click the chart and then choose Copy. Now move to the PowerPoint slide and press Ctrl-V (or choose Edit, Paste). This will get the chart into PowerPoint without dragging the whole worksheet along with it.

ABSOLUTELY
You can quickly turn a normal Excel cell reference into an absolute reference. (An absolute reference forces Excel to always refer to the cells you specify. Let's say you have entered =SUM(A1:A5) into cell A7. Double-click cell A7 and then use the mouse to select the reference A1:A5. Now press F4 and then press Enter. The cell contents will change to the absolute reference form of =SUM($A$1:$A$5)

MAKING THOSE CHARTS MOVE
In the previous tip, we pointed out that you can copy an Excel chart by right-clicking the chart and choosing Copy. Then you can move to PowerPoint and press Ctrl-V to paste the chart onto a PowerPoint slide.
Once the slide is in PowerPoint, you can animate those chart components if you wish. This is possible because you can Ungroup the chart. Try this:
Select the chart and choose Draw, Ungroup. Next, press Ctrl-A to select all the components. Now hold down the Shift key while you deselect all the chart components that you want to animate. Once you've deselected all the necessary components, release the Shift key and choose Draw, Group.
Now you can right-click the chart and choose Custom Animation. Click the Timing tab and select Animate. Select the components to animate and then click the Effects tab. Select the animation effect you want for each component. To see how your animation looks, click Preview. When you're finished, click OK to close the dialog box and save your changes.

DROP THAT CELL
Although everyone knows that you can select Word text and then use the mouse to drag the selected text to a new location, many users don't know that you can do the same thing in Excel.
To see how drag and drop works in Excel, open a new worksheet and type First Cell into cell A1. Now type Second Cell into cell A2. Select the two cells and move the mouse near the edge of the cells. When the pointer changes from a plus sign to a pointer, press and hold the mouse button. Use the mouse to drag the cells to a new location and release the mouse button.

The reason many people miss this Excel feature is that you need to make sure you don't grab the handle, or end up inside the cell. You have to grab just the edge and only after the cursor turns to a pointer.

EXCEL LAYOUT
Layout is important when you're designing a complex worksheet. But it's hard to see how the layout looks when you can view only a portion of the worksheet at once. To see how your entire worksheet looks, try this:
Choose View, Full Screen. Next, press Ctrl-End to move to the last cell used by your worksheet. Now press Ctrl-Shift-Home to select the entire worksheet you've worked on, from the last cell to cell A1. Choose View, Zoom and select Fit Selection. Click OK, and there's the entire worksheet. You won't be able to read anything, but you can see how the layout looks.

PASTING CHARTS
To paste Excel charts into a PowerPoint slide without getting the entire worksheet you right-click the chart and then choose Copy. Now move to the PowerPoint slide and press Ctrl-V (or choose Edit, Paste.) This will get the chart into PowerPoint without dragging the whole worksheet along with it.

EXCEL CELL DRAG AND DROP
In Word, you can select text and then use the mouse to drag the selected text to a new location; Excel users can do the same thing in Excel worksheets. To see how drag and drop works in Excel, open a new worksheet and type First Cell into cell A1. Now type Second Cell into cell A2. Select the two cells and move the mouse near the edge of the cells. When the pointer changes from a plus sign to a pointer, press and hold the mouse button. Use the mouse to drag the cells to a new location and release the mouse button.
The reason many people miss this Excel feature is that you need to make sure you don't grab the handle or end up inside the cell. You have to grab just the edge and only after the cursor turns into a pointer.

AUTOMATIC HEADER/FOOTER IN EXCEL
P.V. sends this macro written to put headers and footers into an Excel worksheet. This macro will insert your choice of headers and footers into the Excel worksheet and then open Print Preview so you can check them.
To enter the macro, run Excel and choose Window, Unhide. This will open a dialog box from which you should choose Personal.xls and click OK. Now, type in the macro shown below exactly as it appears.

Sub PrintPreView()
'PrintPreView Macro
' Macro by P. V.

Application.ScreenUpdating = False
Application.Calculation = xlManual
With ActiveSheet.PageSetup
' the header
.CenterHeader = "My Header" & "&""Arial,Regular""&8"
' Left Footer
.LeftFooter = "My Left Footer " & "&""Arial,Regular"" &8"
' Center Footer
.CenterFooter = "My Center Footer" & "&""Arial,Regular""&8"
' Right Footer
.RightFooter = "My Right Footer" & "&""Arial,Regular""&8"
' to center the print horizontally on the page
.CenterHorizontally = True
End With
Application.Calculation = xlAutomatic
ActiveSheet.PrintPreView
End Sub

EXCEL PAGE PREVIEW
Layout is important when you're designing a complex Excel worksheet. But it's hard to see how the layout looks when you can view only a portion of the worksheet at once. To see how your entire worksheet looks, try this:
Choose View, Full Screen. Now press Ctrl-End to move to the last cell used by your worksheet. Now press Ctrl-Shift-Home to select the worksheet from the last cell to cell A1. Choose View, Zoom and select Fit Selection. Click OK, and there's the entire worksheet. You won't be able to read anything, but you can see how the layout looks.

EXCEL ABSOLUTE REFERENCE
The standard form of reference is relative. By that we mean that when you enter a formula such as =sum (a1:a20) the references are relative. If you copy the formula to another column, the references will change. To see this, put some numbers in cells A1 through A5 and then enter =sum(a1:a5) in cell A7. Now put some number in cells C1 through C5. Click cell A7 (the formula) to select it. Now press Ctrl-C to copy the cell contents. Click cell C7 and press Ctrl-V to paste the formula. Look at the formula, and you'll see that it has changed to =sum(c1:c5) for its new location. An absolute reference takes the form =sum($a$1:$a$5)
Enter this formula into cell A7 and press Enter. The calculation will proceed as you'd expect. Now select cell A7 and press Ctrl-C to copy the contents. Move to cell C7 and press Ctrl-V to paste. If you look at the formula now, you'll see that it's exactly the same as the one in cell A7. That's absolute for you--it will only calculate the named cells.

EXCEL IF FUNCTION
There are times when you can make good use of Excel's IF function in your worksheets. Let's say you have a sales sheet that you use to determine who might qualify for a bonus. You'd have a list of names in the first column (say column B) and the monthly sales amounts for each one in column C.
Let's look at how to use IF. The basic form of the IF function is: IF (condition, true response, false response.)
So, in our proposed example, you can use the IF function in column D to indicate qualification. Let's assume that the minimum for bonus qualification is £10,000. If your names are in B2, B3, B4, and B5, and your sales totals in C2, C3, C4, and C5, type into cell D2 the following formula:
                                            =IF(C2>10000,"Qualified","Not Qualified")

LAST NAME FIRST
Let's pretend that you have an Excel Worksheet that contains a list of employees. They're all entered in the form first name, last name. The problem is that you need to extract the last names only. Let's take a two-day look at the elements needed to do this.
Before you can extract the last name, you need to know how long the string is and where the space is located. So, let's go to cell A1 and type a name, such as
Jean Shepherd
Now go to cell D1 and enter =len(a1)
Cell D1 should now display 13
Now what about that space between the names? In cell E1, enter =find(" ",a1)
Cell E1 will display 5 because Jean contains four letters and the space is the fifth character.
Now let's look at a complete formula that will extract the last name.
Go to cell A1 and start a list of names, with the first name in cell A1, second name in cell A2, like this:
Jean Shepherd
Mark Twain
Gracie Allen
George Burns

We know from the last tip that the total length of the name Jean Shepherd is 13 characters and that the space is character 5. This means that the length of the last name is eight characters (13-5). So we could use Excel's Right string command to return the last name. Go to cell D1 and enter =right(a1,8)
When you do this, cell D1 will display Shepherd But, we don't want to calculate each one by hand, so let's get Excel to do the entire job for us. Go to cell D1 and enter =right(a1,len(a1)-find(" ",a1))
Now grab the little tag at the lower-right corner of cell D1 and drag down through D4. Click somewhere away from column D. Cells D1 through D4 should now read:
Shepherd
Twain
Allen
Burns

NAME THAT RANGE
Here's a way to make those large worksheets easier to work with: Give important ranges a name. For example, if you have a worksheet that lists a number of costs, and you want to get all the costs for the month of May, you can give the range that includes all the numbers for May a name.
Let's say that there are costs for May 1995 through 1998 in cells A5, B5, C5, and D5. Use the mouse to highlight cells A5 through D5. Choose Insert, Name, Define. Type May and click OK. Now move to a blank cell, say cell F7 and enter =sum(May) to see the total of all the May figures.

EXCEL MEDIAN
The problem with Excel's Median function is that many users confuse it with Average. Although Median and Average can often produce the same result--they are not the same thing. The median value is the number in the middle of a group of numbers. That is, half the numbers have a value higher than the median, and half the numbers have a lower value. You'll often see a group of salaries reported as a median. For example, you may read that the median income in the great state of Confusion is £45,000.
If you open a blank Excel worksheet and type into cells A1 through A10 1 2 3 4 5 6 7 8 9 10 and then go to cell A12 and type =average(a1:a10) you'll get a value of 5.5.
Now move to cell A14 and enter =median(a1:a10) and you'll also get a value of 5.5. This is what confuses people--Average and Median often produce the same result. But if you type 10 2 7 8 3 9 12 14 4 1 in cells A1 through A10, cell A12 (Average) will display 7 and cell A14 (Median) will display 7.5

EXCEL AUTOSAVE
The feature exists, but you may have to do a bit of work to get to it. Excel's AutoSave is part of the Add-Ins--goodies that come along with Office but that aren't necessarily installed. To see if it's available, choose Tools, Add-Ins. If you see AutoSave in the list, select it by clicking the check box and then click OK.
If AutoSave isn't in the Add-Ins list, put the Office 95 CD into the CD-ROM drive and click Start, Settings, Control Panel. In Control Panel, double-click the Add/Remove Programs icon to open the utility. Locate Microsoft Office (this line will vary depending on your version) and select it. Now click Add/Remove. When Office Setup opens, select Excel and click Change Options. Now select Add-Ins and click OK. Follow through with the Setup wizard to install the Add-Ins. The next time you run Excel, AutoSave will be available.

EXCEL INTEREST RATE
Is the published interest rate what we're really paying? Let's use Excel to find out. Let's say you're about to make a purchase, and the interest rate as published is 12 percent per year. However, you read the fine print and find that the interest is compounded quarterly. You can use Excel's Effect function to determine what your actual rate is going to be.
In cell A1 enter =effect(12%,4) since the interest will be compounded four times a year. Excel reports that the actual interest rate is 12.55%. If the interest is compounded monthly, you'd enter =effect(12%,12) and Excel would return a rate of 12.68%.
The Effect function is part of Excel's Add-Ins. If you get no result when you use Effect, choose Tools, Add-Ins and select it from the list. If it isn't on the list, close Excel and run Office Setup to install the Add-Ins.

NUMBER FORMAT
Although Excel offers a wide selection of number formats, it isn't possible to anticipate all the formats that users might need. Let's say your company uses a format such as 1-234-5678-1998 for inventory:
You can select a cell (or column or row of cells) and choose Format, Cells. When the Format Cells dialog box opens, click the Number tab. Under Category, locate Custom and select it. Now, click one of the existing formats to select it. In the Type entry box, delete your selection and type #-###-####-####
Click OK. Now, navigate to one of the cells to which you applied the format and enter 123456781998 When you press Enter (or one of the arrow keys), Excel will display 1-234-5678-1998

WORKSHEETS
You can add worksheets without doing any harm to existing data--but there are a few peculiarities. Let's say that your current workbook has three sheets. These sheets are named Sheet1, Sheet2, and Sheet3. Now, let's say that you want to add a new sheet that is to be named Sheet4. Click the Sheet3 tab and choose Insert, Worksheet. You'll get a new sheet named Sheet4, but it will appear between Sheet2 and Sheet3. Excel inserts a new sheet before the selected sheet. But, you wanted it after Sheet3, right? So, use the mouse to grab the new Sheet4 tab and drag it to the right of the Sheet3 tab. When you release the mouse button, Sheet4 will appear after Sheet3.
You can also use drag and drop to move a worksheet to another workbook. Try this: Run Excel and then create a new workbook by clicking the New button (or choosing File, New). Next, choose Window, Arrange. When the Arrange Windows dialog box opens, select Horizontal (for our example) and click OK. Both worksheets will now appear in the window. Use the mouse to drag the Workbook 1 Sheet1 name tab up to the space between Sheet1 and Sheet2 of Workbook 2. The worksheets in Workbook 2 will now be Sheet1, Sheet1 (2), Sheet2, and Sheet3, etc.

INDENTATION
Once you've crunched the numbers in an Excel worksheet, you often go back through the sheet to make it look better. In some cases, you might like to have the entire worksheet start in the second column. The problem is that you've already used column A. You can easily insert a new column. All you have to do is click column A and choose Insert Columns. The whole worksheet moves over by one column.
But what if you don't want the indentation to be quite as large as the default column width? In the label area, move the mouse pointer over the line between A and B and drag the line to the left until the spacing is just right for your worksheet.

PRINTING FORMULAS
Before you can print the formulas, you have to get Excel to display them. Choose Tools, Options and, when the Options dialog box opens, click the View tab. Now, select the check box labelled Formulas and then click OK to close the dialog box and record the change.
You'll see that the worksheet now displays the formulas instead of the results of the calculations. So choose File, Print while the formulas are displayed, and the printout will also display the formulas.
Note that the viewing selection we described will remain in effect until you change it or load a new worksheet. A new worksheet will automatically default to displaying the cell values rather than the formulas.

ACCESSING YOUR EXCEL DATA
If you've developed a customer list in Excel that's beginning to get rather large and cumbersome to deal with, you may want to consider converting it to an Access list. To do this, click anywhere in the list and choose Data, Convert to Access. A wizard will open and guide you through the conversion. We can't describe the process in more detail because the choices you make in the wizard depend on your specific list. After the conversion is finished, you'll get a message informing you of the new file's name and location.

CELL ALIGNMENT
Let's say you want to make a drawing of a rectangle, and you want it to enclose a cell range of 3 by 3. First, you'll need the Drawing toolbar. If you don't see it, choose View, Toolbars and select Drawing.
Now, click the Rectangle button in the Drawing toolbar. Hold down the Alt key and draw the rectangle with the mouse. As you draw the rectangle, its sides will snap to the cell borders. If you need to move the rectangle to a new location, hold down the Alt key while you make the move and it will snap into position on the cell borders.

LEFT FUNCTION
If you go to cell A1 and enter 12345 and then enter =left(a1) into cell A3 and click Enter, you'll get the single digit 1. You can even pick out numbers using the MID function. For example, if you leave 12345 in cell A1 and then enter mid(a1,2,1) into cell A3, you'll get 2.
The same applies to the RIGHT function. Entering =right(a1) into cell A3 will cause cell A3 to display 5.
The problem is that the returned numbers are in text format and you can't use them for calculations. However, you can convert them, using the VALUE function. For example, to get the left digit of a number in A1 returned as a number, enter into cell A3 =value(left(a1)) and click Enter
These nested formulas get the left digit and then convert the result from text to a number.

HIDE IT FROM VIEW
You can hide the columns that make up worksheet data. Let's say you have a column of data, starting with Cell B1. You have the results in Cell C5 and you want to hide column B. Click in column B and choose Format, Column, Hide. Column B will disappear.
Since you'll want column B to appear again after your show-and-tell exercise, here's how to get it back. Select cells on each side of the hidden column (in this case, you could select cells A1 and C1). Then choose Format, Column, Unhide.
Note that you can use the procedures described here to hide and unhide rows rather than columns. Just select the row and choose Format, Row, Hide and Format, Row, Unhide.  

MAIL MERGE.
Let's say you develop a workbook that lists all your names, addresses, etc. When you make the list in Excel, use column headings. For example, your worksheet might contain the headings shown here.
Last Name, First Name, Address, City, County, Postcode
Fill in a few rows for test purposes. Choose File, Save As and name your new file. Find a folder in which to save it and click Save. You can close Excel now, if you wish.
Next, run Word and choose Tools, Mail Merge. When the Mail Merge Helper opens, click Create, Form Letters. Click New Main Document. Next, click Get Data and choose Open Data Source. When the Open dialog box appears, click the arrow at the right side of the list box labelled Files of Type and select MS Excel Worksheets (*.xls). Locate your new Excel file and select it. Click Open to open the file. When asked, confirm that you want to open the entire worksheet.
Next, you need to add at least one field to your main document. Type in Dear Mr. and then type a space and click Insert Merge Field. Select Last Name and then type a comma. To see how this works, click Merge to New Document (its icon shows a right arrow pointing to a sheet of paper). You should now see the inserted names in the new document.

INTERSECTING RANGES.
If you name two intersecting ranges, you can use both names to extract information. For example, suppose you have a worksheet that tracks your budget for a year. Say you'd like to extract your January house payment by simply typing in =January House Payment
You can do just that if you use intersecting range names. To see how this works, open a blank worksheet and enter January in cell B1. Now, in cells A2 through A5, enter Gas and Lights, Groceries, House Payment, Miscellaneous.
In cells B2 through B5, enter some values. We used £120, £212, £780, £200.
Select cells B1 through B5 and choose Insert, Name, Define. When the Define Name dialog box opens, January is the suggested name. Click Add and then click OK to close the dialog box and save your new name. Now, select cell A2 and choose Insert, Name, Define. When the dialog box opens with the name Gas and Lights, click Add and then click OK. Repeat this procedure for cells A3, A4, and A5.
Now that all ranges are named, move to cell D5 and enter =January House Payment . Cell D5 now displays £780--the amount of the January house payment.

SPECIAL SYMBOLS.
There are several ways to add special symbols in Excel cells. You can select a cell or range of cells and choose Format, Cells. When the Format Cells dialog box opens, click the Font tab. Now select the Symbol font and click OK. Select one of the formatted cells and hold down the Alt key while you enter 0214 from the keypad. This enters a check mark in the selected cell.
You can also use AutoCorrect. To do this, follow the procedure already described and then copy the check mark (press Ctrl-C). Next, choose Tools, AutoCorrect and type cm in the Replace entry box. Press Tab to get to the With entry box, and press Ctrl-V to paste the check mark you copied to the Clipboard. Click Add, then OK to close the dialog box and record your new entry. You can now type cm in cells to get a check mark. However, the check mark won't appear until you set the cell's font to Symbol.
Since AutoCorrect isn't a very satisfying way to address the problem, we wrote a short macro to do the job. To apply the macro to all worksheets, you need to put it into AutoStart.xls. Choose Window, Unhide. When the dialog box opens, click AutoStart.xls to select it, then click OK. If you don't have Unhide available, you need to create AutoStart.xls. Open a blank worksheet and choose File, Save As. Name the sheet AutoStart.xls and save it in the Xlstart folder in your Office folder.
With AutoStart.xls in view, press Alt-F11 to open the Visual Basic Editor and enter the following exactly as shown, except that in the line "ActiveCell.FormulaR1C1 = "O" 'enter Alt + 0214 here:" the capital "O" should have an umlaut (that's two little dots above the letter).

DELETE A MENU COMMAND.
You can delete menu commands and, it's a fairly safe operation, since you can go back to the original command set whenever you want. There's the further advantage that the way to edit menus isn't especially obvious, so most of your users probably won't stumble across it.
To make sure your modifications apply to all worksheets, you'll need to make the changes in a workbook that's available to all. In most cases, this is a hidden workbook named Personal.xls that resides in the XLStart folder. To see if this is available to you, choose Window, Unhide. When the Unhide dialog box opens, you should see Personal.xls in the list. Select it and click OK to close the dialog box and unhide Personal.xls.
Now, using Personal.xls, let's eliminate the Options command from the Tools menu. Choose Insert, Macro, Module. When the module opens, choose Tools, Menu Editor. When the Menu Editor opens, look under Menus and click Tools to select it. Now, under Menu Items, select Options and then click Delete. Finally, click OK to close the editor and save your menu selections. Save Personal.xls and then choose Window, Hide to hide it again. When you close Excel, you'll be asked if you want to save the changes in Personal.xls. Click Yes.
At this point, the Options command will no longer appear in the Tools menu. However, if you need to restore the command, click the Module tab and choose Tools, Menu Editor. When the Menu Editor opens, click Restore All and then click OK to close the editor and apply your changes.
If you don't have Personal.xls, you'll need to create it. Open a blank worksheet and choose Save As. When the Save As dialog box opens, name the worksheet Personal.xls and then locate the XLStart folder. Click Save to save the file in the XLStart folder. The next time you run Excel, Personal.xls will open. Choose Window, Hide to hide it and choose File, Exit to close Excel. Click Yes when asked if you want to save changes in Personal.xls.
The next time you run Excel, Personal.xls will open as a hidden worksheet. You will need to copy the modified Personal.xls to the XLStart folder on each of the computers.

OPEN WITH NO WORKBOOK.
To open Excel with no document, right-click the Excel shortcut and choose Properties. When the dialog box opens, click the Shortcut tab. Click in the Target text entry box and use the arrow keys to move to the end of the line. Make sure the line is not selected, press Space, and then add /e to the end of the existing line.
Make sure you click in the Target text entry box before you press Space. Clicking in the Target text entry box deselects the current entry. If you press Space with the current entry selected, you will delete the entire entry.

USING F8 TO SELECT RANGES
There are a number of ways to select cells in Excel. One quick way to select a large range of cells is to select an anchor cell and then press F8. Then, click any other cell, and Excel will create a range using the anchor cell and the newly selected cell as the opposite anchor cell. For instance, if you select cell C3, press F8, and then select cell H10, Excel will highlight the range C3..H10. If you then select cell A15, Excel will highlight the range C3..A15. Excel will continue highlighting ranges until you press F8 to turn off this feature.

TOOLS DO DOUBLE-DUTY
Excel's many toolbars make a number of features available with a quick click. But there's more here than meets the eye. If you hold down the Shift key, many tools will change their purpose. For instance, the Open tool will save the current worksheet if you hold down the Shift key before clicking that tool. Likewise, if you hold down Shift and click the Save tool, Excel will display the Open dialog box.
To learn if a tool has a hidden task, hold down the Shift key and click it but don't release the mouse button. Excel will change the button image to indicate its second task. If you don't want to issue the new command, simply move the mouse pointer away from the tool before releasing the mouse button.

NUMBER OF MONTHS BETWEEN TWO DATES.
Working with dates can be frustrating, unless you know the right functions to help you along. For instance, you can use the DAYS360() function to return the number of months between two dates. Are you wondering how a function that returns the number of days between two dates can return months? Simply divide the result of the DAYS() function by 30. Let's look at a quick example. First, let's enter the dates.1/1/99 and 12/31/99 in cells B1 and B2, respectively. Then, enter in cell B3 the formula =DAYS360(B1,B2)/30 which will return the value 12. The function itself returns the value 360. The second component then divides that result by the average number of days in a month (30). The result is the number of months between both dates.

UNDEFINED FUNCTION ERROR.
If you suddenly receive an error such as Undefined Function or Function Not Available, you've probably lost some of your library references. This can occur when you upgrade software--and not just your Excel software. You can upgrade a seemingly unrelated application and run into this problem. Fortunately, it's easy to repair.
Open the VB Editor by pressing Alt-F11. Then, inside the editor choose References from the Tool menu. Check the listing in the Available References control for selections tagged Missing. If you find one, deselect it. Next, from the Debug menu, choose Compile Project. This should resolve any missing references.

FONT SIZE.
The Font Size tool on the Formatting toolbar lists sizes from 8 to 72, but Excel isn't limited to those sizes. You can specify a Font Size property from 1 to 127 using VBA. For instance, to change a work font size, you'd use code similar to Set myDocument = Worksheets(1) myDocument.Shapes(1). TextEffect.FontSize = 24 Just because you specify a font size doesn't mean Excel or your printer can accommodate that size with the specified font. You must have the appropriate font installed and the font itself must be able to accommodate the size you choose. If it can't, Excel will substitute a similar font if possible.

CALCULATING BINARY.
Ever need the binary value for a decimal value? If so, use the Analysis ToolPak--an Excel add-in. Enter the value you want to convert to binary in a cell. Then, in another cell, enter the function =DEC2BIN(celladdress) For instance, if you enter the value 10 in cell A1, you'd enter the function =DEC2BIN(A1) in any other cell and press Enter. The function will return 1010--the binary counterpart of the value 10.

QUICKLY CLOSING WORKBOOKS.
If you're like many Excel users, you may have several workbooks open at one time. When you're done working and you're ready to close up shop, you probably find closing all those files a little tedious.
To quickly close all the open workbooks, hold down the Shift key before you open the File menu. When you do, the Close command will read Close All. Select this command, and Excel will close all the open workbooks at once.

HIDDEN RESOURCE.
One of Excel's best-kept secrets is WordArt, an Office graphics accessory. WordArt will help you twist and turn pieces of data into a colourful and eye-catching display. If you aren't familiar with WordArt, it's about time you two became acquainted.
First, position your cursor and choose Insert, Picture, then select WordArt. Once in the WordArt gallery, select one of the layouts. You can alter your choice later. For now, give priority to the color and shading. Once you've made your selection, click OK. Next, type the text you want to display graphically.
It's easy to modify the results. To resize the graphic, simply drag the sizing handles. If you want to maintain the graphics' proportions, hold down the Ctrl key while you're resizing. Perhaps the most common use for WordArt is to insert vertical text. Once you have your text graphic in your worksheet, select the ABC icon on the WordArt toolbar to vertically transpose the text. Rotating the text is just as easy. Click the Free Rotate icon on the WordArt toolbar. WordArt will replace the normal sizing handles with green dots at the four corners of your graphic. Grab one of those green dots and drag the graphic's corner.

WORKING WITH DATES ON PC's AND MACS.
The dates fail to agree because Excel for Windows starts its date calculations at January 1, 1900, and the Mac version dates start at January 2, 1904. You can correct the problem if you change the way Excel calculates its dates. To do this, choose Tools, Options. When the Options dialog box opens, click the Calculation tab. Select the check box labelled 1904 Date System. Click OK to save your selection and close the dialog box.
Note: This will cause some problems if you also share worksheets with other Windows users. If your office has a mixture of PCs and Macs, you should change the way PC Excel calculates the dates on all the systems.

MINIMIZING OLE REFERENCES.
VBA methods and properties use the OLE IDispatch interface--and that takes time. (Don't worry if you don't know what the OLE IDispatch interface is.) Consequently, minimising the number of methods or properties can speed things up. Here's the general rule of thumb: Every time you use the dot identifier (.), VBA hits the OLE IDispatch interface. For example, the following statement contains three dots:
Workbooks(1).Sheets(1).Range("A1").Value = "abc"
This statement will call on the interface three times, which of course takes more processing time than a statement with only one dot identifier. Although it isn't always practical, keeping the dot identifiers to a minimum is one good way to speed up your code.

CLICK TO OPEN PICTURES.
You can assign a macro to an inserted picture. Open a blank worksheet and choose Tools, Macro, Record New Macro. When the Record Macro dialog box opens, click OK to start recording. Now, choose Insert, Picture, ClipArt. When the Clip Gallery opens, move it out of the way and click the Stop button on the Record toolbar.
Next, choose Insert, Picture, ClipArt and double-click a picture to insert it. Right-click the picture and choose Assign Macro. When the Assign Macro dialog box opens, click Macro1 (assuming this is the first macro in this workbook), then click OK to close the dialog box and assign your macro. Now all you have to do to open the Clip Galley is click the picture.

SHOW ME ALL THE FORMULAS IN A WORKSHEET.
When you're dealing with a large worksheet, you may have trouble finding all the formulas. All you see in the worksheet are the results. You can show the formulas in a worksheet by holding down Ctrl and pressing the Tilde key (~). This is the key to the left of the 1 key.
When you press Ctrl-Tilde, Excel will display all the formulas in the worksheet. To get back to normal view, press Ctrl-Tilde again.

CIRCLE THIS.
Circling something can bring attention to it, and you can do this easily in Excel. First, display the Drawing toolbar by right-clicking any toolbar and choosing Drawing from the context menu. Then, click the Circle tool on the Drawing toolbar and drag the mouse pointer across the cell(s) that contain the data you want to encircle. The circle will most likely be solid and cover the data, so right-click the circle, choose Format AutoShape, and click the Colours And Lines tab. Next, choose No Fill from the Fill Color control and click OK. If a red circle would be even better, repeat the above process and choose red from the Line Color control.

QUICK FORMATS.
When you need to format a cell or range, don't reach for the Format menu or right-click the range. Instead, press Ctrl-1 to display the Format Cells dialog box. If you're a keyboard lover, you'll find this a welcome relief to grabbing the mouse.

UNFORMATTING.
Although applying formats is an easy task, a complex sheet can require a bit more thought and time. However, unformatting a sheet is always a quick task--much quicker than you might think. It's not necessary to select each formatted cell or range and remove each format. You can quickly return a cell, a range, or even the entire sheet to the General format. To do so, select the cell or range you want to unformat, then press Ctrl-Shift-~ (tilde).
Excel will remove all formats and return the selected cell or range to the General format (the default format for all sheets).

SELECT AN ENTIRE ROW.
Yes, there is. In fact, there are several ways to select an entire row in an Excel worksheet. One way is to click in the row heading, where you'll find the row identifier. For example, the heading for row 5 is the 5 you see in the grey area at the left side of the Excel window. Click the 5 and Excel will highlight the entire row.
An easy method is to click in the row you want to select, then press Shift-Space.

NONCONTIGUOUS RANGES.
You probably know that you can select non-contiguous ranges by holding down the Ctrl key as you select additional cells. For example, select cells A1:A3. Then, hold down the Ctrl key and select cells C4:C6, and Excel will highlight both ranges.
One limitation with these multiple selections is you can't cut and paste data. If you try, Excel will display an error message that explains you can't use that command (whatever copy command you try) on multiple selections.

PRINT BUTTON PROBLEM.
Choosing File, Print displays the Print dialog box, where you can choose from a number of print options, including the range you want to print. When you click the Print button on the Standard toolbar, Excel prints the default print area. This may or may not be what you meant to do, especially if you don't even remember what the default print area is! If you find you often end up with unexpected results when using this button, just remove it from your toolbar and replace it with the Print... button. This button displays the Print dialog box before printing.
We've told you how to delete and add buttons, but just in case we'll repeat ourselves a bit. First, right-click any toolbar and choose Customize from the context menu. (Or select View, Toolbars, Customize.)
To delete a button, simply drag it off the toolbar. To add a button, click the Toolbars tab and identify the toolbar you're modifying. Then, select the Commands tab and select the appropriate item in the Categories control. Next, drag the button you're adding from the Commands control to the appropriate position on the toolbar. That's it. You're all set.

WORKING WITH FRACTIONS.
To use fractions in an Excel worksheet, click the cell into which you wish to enter a fractional number and type the fraction. For example: 3 7/8.
Press the down arrow and click the cell to select it. You'll see in the formula bar that the decimal value is 3.875.
If you want Excel to convert a column of decimal entries to fractions, select the column and then choose Format, Cells. When the dialog box opens, click the Number tab. Click Fraction to select it and then choose the type of fraction you want (one digit, two digits, etc.). After you make your selection, click OK to close the dialog box and save your changes.

SWITCHES.
When you launch Excel, it will open a new workbook by default. You can alter this behaviour using a command-line switch. Specifically, /e opens Excel without opening a new workbook. There are other switches:
/I starts Excel with a maximized window.
/p "folder" sets the active path to folder - ignoring the default folder.
/r "filename" opens filename in read-only mode.
/s starts Excel in safe mode.
To set a temporary command line, click the Start button on the Windows Taskbar and choose Run. Then, enter the complete path to Excel's application file, Excel.exe, including the switch.

PUBLISHING PROBLEMS.
Excel won't allow you to save a password-protected workbook or sheet in HTML format (as a Web page). You can save individual sheets when the entire workbook has been protected, as long as the sheet you're trying to publish hasn't been protected. All protected sheets are off limits.
The solution is simple - to temporarily remove the password, select Tools, Protection, then choose Unprotect Sheet and enter the password. Publish the sheet, then reapply the password.

FORMULA DISPLAY SHORTCUT.
There is a very easy way to switch between displaying data and formulas - all you have to do is press Ctrl-~ (tilde). This combination acts as a toggle. The first time you press Ctrl-~, Excel displays formulas rather than data. Press the combination again, and Excel switches back to data.

WORKSHEET REGION.
You can select an entire worksheet region by pressing Ctrl-Shift-*. By worksheet region, we mean any contiguous block of entries surrounded by blank cells. The previous keystroke will select all contiguous cells with entries. For instance, if you have entries in cells A1..C3 and the cursor is in cell B2, pressing Ctrl-Shift-* will select cells A1..C3. If you have entries in cells B3 and C4 and the cursor is in either of those cells, pressing Ctrl-Shift-* will select cells B3..C4.
In addition, the cursor doesn't have to be in one of the cells in the contiguous region. The cursor can be in any bordering empty cell. However, the selection might be different because the selection will include any entries. That means if you have two contiguous blocks of data--one in cells A1..B3 and cells D1..E3 and the cursor is in cell C2, pressing Ctrl-Shift-* will select cells A1..E3, not just one contiguous block or the other.

A FORMATTING SHORTCUT.
Do you find yourself repeating formatting tasks from one worksheet to another in the same workbook? If so, it isn't necessary. You can apply formats to one, several, or all the worksheets in your workbook at the same time.
To apply the same format to every worksheet in the workbook, right-click any sheet tab and choose Select All Sheets. Then, go about your formatting tasks. Whatever formatting you apply with all the sheets selected, Excel will apply to all the sheets - not just the current sheet.
If you only want to include some worksheets in the format group, hold down the Ctrl key as you click the appropriate sheet tabs to create a group of sheets. Then, go about your formatting tasks.
To ungroup either group (all or some), right-click any tab in the group and choose Ungroup Sheets.

SHARING AN EXCEL WORKBOOK.
Did you know that two people can work on an Excel worksheet at the same time? Suppose you're on a Local Area Network and you could save some time if you and a co-worker could access the same worksheet simultaneously. Here's what you do. Open the worksheet and choose Tools>>Share Workbook. When the Share Workbook dialog box opens, select the check box labelled "Allow changes by more than one user at the same time. This also allows workbook merging." Click OK to close the dialog box. Now, ask someone on your network to open the same workbook. You'll find that sharing a worksheet is easy.

Back to Tips Page