HOME PC Alamode About Us HELP
Reviews Columns Features Archives Other  


 Windows Tips & Tricks

Excel
March, 2003

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.  It consists of worksheets, workbooks, and formulas.  The basic tool is the formula.  It’s a special type of cell entry that returns a result.  When you enter a formula into a cell, the cell displays the result of the formula. Upon activation of the cell, the formula itself appears in the formula bar.  The formula bar is just below the toolbars at the top of the Excel window.  A formula begins with an equal sign (=) and can consist of any of the following elements:  operators such as + (for addition) and * (for multiplication),  cell references, including addresses like B4 or C12 as well as named cells and ranges, values and text, and worksheet functions, such as SUM.  You can enter a formula into a cell in three ways: manually, by pointing to cell references, or use the assistance of the formula palette.  The following tips apply to most versions of the Excel software programs.

Audible Proofreading
Excel 2002 comes with a new Text-to-Speech feature that you can use as an aid in proofreading your spreadsheet data. This tool is useful for the sight impaired user.  It’s also helpful if you're entering a lot of data manually, for example, from a printed sheet. While entering the data into the range, you can look at the printed sheet while Excel audibly confirms each entry.  If the data has already been entered into the spreadsheet, you can also check the printed sheet while Excel speaks out each entry.  To use the Text-to-Speech feature, you need to display the Text to Speech Toolbar.  To display the Toolbar and use its option, choose the Tools, Speech, Show Text To Speech Toolbar menu command.  You can also choose the View, Toolbars, Text To Speech menu command.  Excel then displays the Text To Speech Toolbar.  If you want Excel to speak a range, select the range and click the By Rows or By Columns button to make Excel speak the cells across rows or down columns.  Click the Speak Cells button for Excel to start speaking the cells.  Click the Stop Speaking button for Excel to stop speaking the cells.  If you want Excel to speak the cell every time that you enter data into a cell, click the Speak On Enter button.  Click the button again to cancel the feature.  Remember your computer must have the appropriate equipment for the text-to-speech feature to work. At minimum, you must have a sound card with speakers or a pair of headphones.  The “Speak On Enter” tool persists across Excel sessions.  If you enable this feature during one session and shut down Excel, it's still enabled the next time that you start the program.

AutoFilter
Excel's AutoFilter feature makes it a breeze to hide everything in a database except the records you want to see. All you have to do to filter a database is position the cell pointer somewhere in the database before  choosing Data, Filter, AutoFilter on the menu bar.  To filter the database to just those records that contain a particular value, you click the appropriate field's drop-down list button to open a list box containing all the entries made in that field, and select one to use as a filter.  Excel then displays only those records that contain the value you selected in that field.

AutoShapes
Working with AutoShapes in Excel is different than in PowerPoint or Word.  One big difference is that you can't simply select a group of objects with the mouse and then move, size, or delete them.  In Excel, you need to click the Selection Objects tool in the Drawing Toolbar.  Once you select the tool, Excel turns off the standard worksheet pointer and allows you to choose objects.  When you're finished with the Select Objects tools, press Esc to return to the normal worksheet functions.

AutoSum
The AutoSum tool on the Standard Toolbar not only enters the SUM function but also selects the most likely range of cells in the current column or row that you want totaled.  It then automatically enters them as the function's argument.  In most cases Excel selects the correct cell range to be totaled by highlighting it.  If no selection, you can manually correct the range by simply dragging the cell pointer through the block of cells that need to be summed.  Position the cell pointer in the cell where the sum is to appear, and click the AutoSum tool.  Excel inserts the SUM function onto the Formula bar.  It also places a marquee, the moving dotted line, around the cells it thinks you want to add and uses that cell range as the argument of the SUM function.

Background Colors and Patterns
To change the background color or pattern used in cells, select the cell or range that you want to format.  Choose Format, Cells from the menu bar or press Ctrl+L.  Click the Patterns tab in the Format Cells dialog box that appears.  Click a color on the Color grid.  To add a pattern, click the Pattern drop-down list box      and select one from the list.  You can choose a second color for the pattern.  Click OK to apply the color and/or pattern.  A faster way to change the background color, but not a pattern, is to select the cells and then choose a color from the Fill Color tool on the Formatting Toolbar.  In some cases you may want to use a graphics file as a background for a worksheet that is similar to the wallpaper displayed on your Windows desktop.  However, the graphics background is only for the screen display.  It doesn't show up on the page when you print the worksheet.  To add a background to a worksheet, activate the worksheet and choose the Format, Sheet, Background command.  Excel displays a dialog box that lets you choose a graphics file.  Locate the desired graphics file. You may have to change to a different folder.  Finally click Insert and Excel tiles your worksheet with the selected graphic.

Borders
To emphasize sections of your worksheet or parts of a particular table, you can add borderlines or shading to certain cells.  But don't confuse the borderlines that you add to accent a particular cell in the worksheet because borders added are printed whether or not you opt to print the worksheet gridlines.  To add borders to a cell selection, choose Format, Cells.  Click the Border tab from the Format Cells dialog box that opens.
Select the type of line you want to use in the Style area of the dialog box, such as thick, thin, bold, or hash marks.  Next choose the edge(s) to be applied to this line from the Border section of the dialog box.  When selecting where you want the borderlines drawn, keep these points in mind:  1. To have Excel draw borders around only the outside edges of the entire selection, click the Outline button in the Presets section of the Border tab.  2. If you want borderlines to appear around all four edges of each cell in the selection, like a paned window, select the Inside button in the Presets section instead.  To get rid of borders, you must select the cell(s) that presently contain them, open the Format Cells dialog box, and click the None button in the Presets section.  You can also do the same thing by clicking the first button in the Borders pop-up menu.

Calculation Bugs
You may have heard about calculation bugs that affect Excel 97.  Microsoft has acknowledged the existence of these bugs.  They involve a failure to recalculate some formulas under certain conditions. However, the bugs do not affect most Excel computations.  If you use Excel 97, make sure that you install SR-2, the most recent service release, and check Microsoft's Web site for the latest information on calculation bugs.

Cell Comments
Excel’s cell comment feature enables you to attach a comment to a cell.  It’s useful for documenting a particular value or helping to remember the functions of a particular formula.  When you move the mouse pointer over a cell that has a comment, it pops up in a small box.  To add a comment to a cell, select the cell and choose the Insert, Comment command or press Shift+F2.  Excel displays a text box that points to the cell.  Enter the text for the comment into the text box and click any cell when you're finished.  The cell displays a small red triangle to indicate that the cell contains a comment.

Cell Deletion
To delete a cell in an Excel worksheet, you press Delete or Backspace.  However, this doesn't delete the cell, it only clears the contents.  If you really want to delete cells, select the doomed cells and then press Ctrl + - (minus sign).  Now the selected cells will disappear and the adjacent cells will shift to fill the empty space.

Cell Names
If you've forgotten the range name needed in a formula or don't want to type it, press the F3 function key to display all range names.  Now you can double-click your desired name.

Centering Printed Output
Normally Excel prints a page at the top and left margins.  If you want the output to be centered vertically or horizontally on the page, choose the File, Page Setup command.  Click the Margins tab of the Page Setup dialog box that appears on your screen.  Now select the appropriate check boxes, horizontally or vertically, in the “Center on Page” area.  Lastly click OK to close the Page Setup dialog box.

Chart Titles and Legends
To move a chart title, click the title and drag one of the edges of the rectangular block that surrounds the title.  Even though there are handles on this boundary, they serve no purpose.  You cannot resize a chart title area with these handles.  If necessary, change the font size or alter the text and the size of the box.  To move a legend, click and drag it.  To resize, click it and drag one of the eight handles in any direction.

Chart Types
The type of chart you choose depends on your data.  Each chart type presents data in a specific way.  For example, area, column, and line charts show changes to values over time while pie charts show percentages.  After creating a chart, you can select a different type of chart that will better suit your data.  To change a chart on a worksheet, click a blank area in the chart you want to change.  Handles will appear around the chart.  To change a chart on a chart sheet, click both the tab for the chart sheet and Chart, Chart Type to see the Chart Type dialog box.  Next click the chart type you want to use from the list of options in the Chart Type box.  Click the chart design you want to use from the Chart Sub-Type box. The available chart designs depend on the chart type you selected in the previous step.  Click OK to confirm your changes and the chart displays the selected chart type.

Excel is a powerful tool.  Using its worksheets, workbooks, and formulas’ you can calculate and display complex information in an easy format which can be used to make difficult decisions a much easier task.


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