HOME PC Alamode About Us HELP
Reviews Columns Features Archives Other  


 Windows Tips & Tricks

Excel
March, 2002

Bill Beverley is a retired U.S. Army Colonel and intermediate computer enthusiast. Early in his military career he was on the ground floor in the development of the U.S. Army's Field Artillery Tactical Fire Direction System (TACFIRE), a forerunner of subsequent digital computers / communications within the army.


Excel is a spreadsheet program similar to Lotus 1-2-3 and Quattro Pro. A spreadsheet works much like an accountant’s ledger book. It contains a grid where one can enter numbers and make calculations. The actual spreadsheet in Excel is called a “worksheet.” A single Excel document can contain many separate worksheets that collectively are called a “workbook.” With Excel you can manipulate the look of a worksheet for a specific requirement. You can also create graphs, called “charts,” with Excel for presentation purposes.

Arithmetic
When adding, subtracting, or dividing a few simple numbers in Excel, you can just type in an equal sign followed by the numbers. For example, if you want to add 30 and 50, you'd type =30+50 and press Enter. If you'd rather not have to remember to type in that equal sign first, change the Excel 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 30 + 50 to get the result of 80. However, hereafter you will have to enter an apostrophe before all numbers that you want to enter as text.

Chart
A chart is frequently the most effective way to present data to an audience. To create one using Excel, open a blank worksheet and enter some data. 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. To create an Excel chart quickly, you can simply use the keyboard (shift + arrow keys) to select your data and then press F11.

AutoEntry Direction
To type a number into an Excel cell and then move to the next cell on the right instead of the next cell down, change the AutoEntry direction. 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.

Borders
You can put a border around your data or segments of data to produce a more attractive worksheet in Excel. As an example, open a blank worksheet and type some data into several contiguous cells. Now select the cells that contain data and choose Format, Cells. When the Format Cells dialog box opens, click the Border tab. Next click on the “Outline” preset and then click OK to close the dialog box and apply your new border. Using the Border page of the Format Cells dialog box, you can select the desired type and color of border.

Calculator
Since Excel requires a good deal of math, you might want to add a calculator to your toolbar to save yourself time accessing it from your listed programs. To put one there, you click Tools, Customize. Next click on the Command tab and then pick Tools in the Categories window. Now scroll down on the Commands window until you find the calculator icon with the word “custom” next to it. Finally, drag it up to your toolbar.

Cell Comments
When sending your worksheets to others via e-mail or on floppy disks, you may be able to help others by providing them with notes on cells that some might consider a problem. All you need to do is click the cell in which you wish to place a note and choose Insert, Comment. A little entry box will open. You type in your message and then click somewhere away from the entry box to close it. Now you will notice that there is a small red triangle in the upper right corner of the cell. This triangle 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, you right-click the cell and choose Delete Comment. If there’s a comment in one of the cells in your worksheet and you need to modify it to match some changes to the worksheet, right-click the cell that contains the comment and choose Edit Comment. This command opens the entry box with the current comment. You can now modify the text. When you finish editing, click outside the comment entry box to close it.

Cell Navigation
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, it is 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 for this task; however, you can press Ctrl + G, then type in K324 and press Enter to get there.

Cell Resize
To resize all of the cells in a spreadsheet, click a cell, press Ctrl-A, move your cursor to the edge of one of the lettered cells at the top of the document, and resize it. Now do the same thing with one of the numbered cells at the left side of the spreadsheet. All the cells will be resized to match the size of the cell you modified.

Clear an Excel Cell
Some Excel users get into the habit of pressing Space to clear a selected cell. However, this is a bad habit. Some macros might cause a problem if they encounter a space in a cell. The best way to clear a cell is to select it and choose Edit, Clear. When the “Clear” submenu opens, choose All, Formats, Contents, or Comments. If you need to clear only the contents, select the cell and press Delete. ;

Background
There's nothing that says an Excel worksheet has to have a plain white background. If there’s a photograph file on your hard disk that you'd like to use as a worksheet background, choose Format, Sheet, and Background. When the Sheet Background dialog box opens, double-click the picture file that you'd like to use and then its icon. If you want to use a picture as your worksheet background, make sure the picture is not too dark or busy otherwise it will obscure the worksheet data.

AutoComplete
You can use AutoComplete in Excel worksheets to ease the load with data entry. For example, if you type “Los Angeles” in one cell, then type “Los” in another cell, Excel completes the entry and places Los Angeles in the new cell. If this doesn’t work, you may have turned off AutoComplete. To turn on this feature, choose Tools, Options. When the Options dialog box opens, click the Edit tab. Now select the checkbox labeled “Enable AutoComplete for Cell Values” and click OK to close the dialog box and turn on AutoComplete.

Clean Function
When importing data into an Excel worksheet, you may sometimes encounter nonprinting characters. These characters will appear as small rectangles in your cells. To eliminate these useless characters, you can use Excel's “Clean” function. Let's suppose that you have some nonprinting characters in cell A. To get rid of them, 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.

Changes in Worksheets
When you send your Excel worksheet to others for checking, editing, or data addition, it's nice to immediately see what changes they made to it. To see how this works, open a blank worksheet and enter 1 through 6 into cells A1 through A5. Now, choose File, Save As, give your workbook a name, click Save, and then choose Tools, Track Changes, and Highlight Changes. Next, click in cell A2, 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.

Colored Worksheets
Suppose that you'd like to use color in your Excel worksheets, but the only printer you have is a black and white one. You can still use color in the worksheets by printing them in black and white. Choose File and Setup. Then click the Sheet tab. Under Print, select Black and White and click OK. Some colors won't print as you might want them. Light colors often print as just white and some darker colors print as black. The only solution to this problem is to experiment with the colors and make test prints. ;

Cleaner Worksheets
If you need a clean-looking worksheet to show others, there is an easy way to eliminate the row and column headings along with the horizontal and vertical scroll bars. To make these changes, open a worksheet and choose Tools, Options. When the Options dialog box opens, click the View tab. Deselect the Row & Column Headers, Horizontal Scroll Bar, and Vertical Scroll Bar check boxes. Click OK to close the dialog box and record your settings. The worksheet will now appear without the row and column headers, and without the vertical and horizontal scroll bars. You can still scroll through the worksheet using the arrow keys and Page Up, Page Down.

Collect and Paste
Office 2000 programs have a new “Collect and Paste” feature. For a demonstration, use an Excel worksheet, 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. Next, move to cell B4 and press Ctrl+C again. The Clipboard should now appear. If it doesn't, choose View, Toolbars, and 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. To continue, 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.

Column Insertion
If you have a completed worksheet and then discover that there’s a need to add a new column C, moving the current column C (and all columns after C) to the right, click in column C and press Ctrl+Shift+Plus Sign (+). When the Insert dialog box opens, select the Entire Column radio button and click OK.

Summary
Excel worksheets, workbooks, and graphs offer you many powerful tools. You can, for example, perform advanced calculations with your data. These calculations will be automatically updated whenever you edit the data. In addition to simple and complex calculations, Excel can also predict trends and graph statistics.


Copyright© 1996-2010
Alamo PC Organization, Inc.
San Antonio, TX USA