Smart Computing-Editorial

Guide Series

Buy This Issue
Table of Contents For This Issue


MS Works
September 2001• Vol.9 Issue 9 Page(s) 72-76 in print issue
Spreadsheet Count On It To Make Working With Numbers Easy

Jump to first occurrence of: [MICROSOFT] [WORKS]

As MS Works Word Processor is to text, MS Works Spreadsheet is to numbers. With Spreadsheet, you can perform a variety of tasks that would be difficult or impossible with other components of the suite. Spreadsheet lets you easily calculate, compare, and evaluate numbers. You can chart data to get a better idea of its physical layout or just use the program to create simple lists or checklists. Spreadsheet also offers a number of specialized features such as Easy Calc, which can guide you through some of the more difficult aspects of creating formulas that can do anything from perform simple arithmetic equations to crank out some serious statistics. The bottom line is that if you have to work with numbers, you’ll want to spend a little time getting comfortable with the features and capabilities of MS Works Spreadsheet.

The two primary sections of Spreadsheet, the spreadsheet section itself and the charting section, share many options under their respective menus, but there are differences between the two. This isn’t too surprising; while both work with the same set of data, spreadsheets and charts each offer special configuration options and have specialized features that are reflected in their menus. Where appropriate, we’ll cover any differences you’ll find in the charting menus. Note that readers Using Windows NT/2000 may see slightly different menu items than readers using Windows 95/98.

File Menu. The File menu offers a number of options for opening new and existing spreadsheets, saving spreadsheets and creating templates, and setting up a spreadsheet for printing. If you’d like to send a spreadsheet by e-mail, you’ll also find an option here to easily do so.

Open new and existing spreadsheets. To open a new spreadsheet, click New from the File menu. A list of available tasks will appear, letting you start a blank spreadsheet or open one of the existing templates, such as the car loan worksheet or fitness tracking. Alternately, you can click the New button on the toolbar to just open a new blank spreadsheet.

You can open existing spreadsheets by selecting Open from the File menu (or clicking the Open button on the toolbar). Browse to the file’s location and select it to open it. You can find files you’ve recently worked on listed at the bottom of the File menu, or you can click the History link in the Works Task Launcher (open the Start Menu and select Programs, Microsoft Works or select New from the File menu to launch it) to find a comprehensive list of past files you’ve worked on.

Choosing Page Setup from the File menu lets you set many printing options, such as margins and paper orientation.

You can also open and work with an Excel file by selecting Open from the File menu and then browsing to it. Set the Files Of Type box to Excel SS [*.xl*] to more easily find all Excel files. While this version of Works gets along much better with Excel than past versions, you’ll still find a lot of features that won’t transfer between the two, such as cell comments, hyperlinks, images and pictures, charts, and more.

Create templates. If you want to save a file and then use it again for different tasks, consider saving it as a template. Click Save As and then click Template. Enter a name for the template and then click the check box if you want to use that template as the template for every new spreadsheet you open. (To stop using that template as the default, choose Save As again and click the Template button. Click the Default button, then the Reset button.) Click OK, and Works will save the template to the Template folder. To open the template, click New from the File menu and select Tasks, then Personal Templates for a list of all your templates.

Page Setup. At some point, you’ll want to print your spreadsheet, and when you do, Page Setup will be a necessary first step. Page Setup’s three tabs let you set a number of options for printing, but they won’t affect the way the spreadsheet will appear on the screen. All three tabs feature a Preview box on the right so you can see what your changes will make your document look like and a Reset button on the bottom, so you can quickly override any changes you’ve made and drop back to the default settings.

With the Margins tab, you can set margins for the top, bottom, and sides of the spreadsheet, and you’ll also find options for setting how far from the edge headings and footers will appear. The Source, Size & Orientation tab deals primarily with the paper you’ll be using and how the spreadsheet will appear on it. You can determine page orientation here, as well as select the paper source and size. The final tab, Other Options, gives you an easy way to set the starting page number, as well as set details such as whether gridlines or row and column headers will be printed.

Print a spreadsheet. When you set out to print a spreadsheet, you have a few different routes you can take to get there. After setting up the page(s) with Page Setup, click Print Preview to see what the spreadsheet will actually look like when it prints. While in Print Preview, you can scroll through pages and zoom in or out to check individual sections.

To print select areas in a spreadsheet, first choose the cells you want to print, then select Print Area from the File menu and choose the Set Print Area option. The next time you go to print, only the cells you selected will be printed. To print the entire spreadsheet, you’ll have to go into the Print Area section again and choose Clear Print Area. When you’re ready to print the spreadsheet, you can select the Print button from the toolbar to automatically send it to the printer or choose the Print option under the File menu to more finely tune a print task by selecting the number of copies, special pages to print, and more.

When the spell checker finds a word it thinks is misspelled, it will give you several suggestions for the proper spelling if the word is in its dictionary.

Send a spreadsheet via e-mail. If you prefer to e-mail your spreadsheet to someone rather than print it out, select Send from the File menu. Works will bundle the document up and attach it to a blank e-mail using your default e-mail client.

Edit Menu. At the top of the Edit menu is a feature that may very well be the best friend a Spreadsheet user ever had: the Undo option. Other options here include the ability to copy, paste, or cut data; select various components; find and replace data; and fill cells or series of cells. The charting Edit menu also features options for editing titles, legends, and more.

Find and Replace. To find specific information in a spreadsheet, select a range of cells you want to search and choose Find from the Edit menu. In the Find And Replace dialog box, you can type exact words, numbers, or phrases to find, or you can use wildcards, which give you greater leverage in searching. One example of a wildcard you can use is the question mark (?); place it within a word or number to substitute a single character (sep?rate; for example). The asterisk (*) is another example of a wildcard; typing d*d will return any word beginning and ending in the letter D. Find lets you search by rows or columns, and you have the choice of searching text that appears in cells or in the formula bar. Clicking Find Next lets you easily jump from one instance of a word/number in a spreadsheet to the next.

If you’re looking to find and then replace text or numbers in your spreadsheet, select Replace from the Edit menu. (Selecting Find opens the same dialog box, but with the Replace tab highlighted.) This works much like the Find option: Select whether to search by rows or columns and then fill in the Find What text box with what you’re trying to locate and the Replace With text box with what you want to replace it with. Click the Find Next button to find the next entry and the Replace or Replace All buttons to replace single incidents or all incidences of the term, respectively.

Fill. The fill options on the Edit menu make it easy to repeat data from one cell to another. To do this, select the cell containing the information you want to repeat and the cell or cells you want to fill. Then choose Fill Down or Fill Right from the Edit menu. You can also create a series of numbers in cells by selecting the beginning of the series and the cells to fill and choosing Fill Series from the Edit menu.

View Menu. With the View menu, you can alter a number of the ways that the spreadsheet appears. Most of the options in this menu are toggles, which means you either turn them off or turn them on by selecting them. You can toggle the appearance of gridlines, the status bar, and formulas (in cells). You can also set up headers and footers here and choose whether to split the spreadsheet to display different parts of it at once.

If you have any charts associated with your spreadsheet, you can toggle between the chart and spreadsheet modes from the View menu. The chart version of the View menu also gives you options for showing data labels in the chart and displaying what the chart will look like when printed.

Among the number of cell formatting options available from the Format menu is the ability to use color or pattern shading.

Headers and footers. You can set up headers or footers for a document by selecting Header And Footer from the View menu. Drop-down menus let you easily add information (such as date, page numbers, and document title) in various alignments along the top (header) or bottom (footer) of the page. You can choose not to have either headers or footers show up on the front page of a spreadsheet by clicking the appropriate boxes. If you’d like to make up your own header or footer, type right in one of the boxes and check the Preview box to see how it will look. Note that headers and footers won’t show up on your document until you print it.

Split. While it may take a little getting used to for some users, the Split option lets you view different parts of a document at once, which is a great tool if you have a large spreadsheet. Click Split to generate a set of thick gridlines with an Adjust cursor at their center. Simply move the cursor to where you wish to split the screen and click the mouse button. This will create four different panes, each with its own scrollbar. When you get sick of this view, you can get rid of it by double-clicking it. Note that the Split option just reflects the way you view data; it does not affect the format or contents of the document at all.

Insert Menu. It should come as no surprise that the Insert menu deals with things you can insert into your spreadsheet: rows, columns, page breaks, functions, and more. There is no Insert menu while in chart mode.

Insert and delete rows and columns. To add a column, click the column header to the right of where you want the column to go and choose Insert Column from the Insert menu. To delete it, click the header to select it and choose Delete Column from the Insert menu. For rows, select the row heading below where you want the new row to go and choose Insert Row from the Insert menu or select a row and choose Delete Row from the Insert menu to get rid of it.

Insert formulas and functions. While Spreadsheet is a great place to store data, the program’s ability to calculate values based on that data is what really makes it shine. Not only can you use Spreadsheet to perform simple arithmetic equations and more complex computations, but all formulas are also dynamic, meaning that their value can fluctuate with the changing data in a spreadsheet. One thing to note when creating formulas: Spreadsheet uses standard algebraic rules regarding formulas, so it will figure the material in parentheses first, followed by exponents, multiplication, division, addition and subtraction. If you’re working with formulas containing multiple operators, it’s good to keep this in mind.

The Insert Functions dialog box offers a wide range of premade functions to choose from in categories such as Financial and Logical.

There are several ways to use formulas in Spreadsheet. The simplest is to just type a formula in a cell and press ENTER. All formulas start with an equal (=) sign, so to add cells A1 and A2, select the cell where you want the sum to go and enter =A1+A2 (or = SUM(A1+A2)), then press ENTER. The sum of A1 and A2 will be displayed in the cell where you entered the formula, and if you select the cell, you’ll see that the formula itself has moved to the formula bar. Alternately, to add a range of cells such as A1 to A3, type =SUM(A1:A3) and press ENTER.

Spreadsheet can quickly add whole rows and columns when you select an empty cell at the end of the row or column and click the AutoSum icon on the toolbar. Spreadsheet will display the range of cells in the empty cell and run the formula when you press ENTER.

If you don’t want to build your own formulas, you can use functions. Functions are ready-made calculations that can work with a wide range of data. They range from the simple (get the sum of a range of cells) to much more complex trigonometry functions (give the arctangent of an angle).

Name ranges. Naming ranges of cells is a good way to keep track of them, find them, and work with them in formulas and functions. To name a range of cells, first select the cells you want to use in the range, then choose Range Name from the Insert menu. This will pull up a dialog box where you can name the range and get a list of named ranges that already exist in the spreadsheet. An easy way to get a complete list of named ranges and the cells they represent is to click the List button in the Range Name dialog box. This will generate a two-column list in the spreadsheet where your cursor is.

Format Menu. Using the Format menu, you can alter many characteristics of cells, rows, and columns and the data that appears within them. This can help to make data stand out as unique, or it can help make the spreadsheet easier to read and understand. The first five options on the Format menu (Number, Alignment, Font, Border, and Shading) all bring up the Format Cells dialog box. This dialog box is split into five different tabs, each corresponding to one of the above menu options. Among these tabs are options that let you set the font size or color, align text in cells (and wrap it), set cell borders and shading, and more. If you like the way a given cell or range of cells has come out, you can easily use that same formatting on additional cells by selecting them and choosing Repeat Formatting from the Format menu.

If you’d rather try out a preset formatting “theme” on a range of cells, select AutoFormat to get a list of various pre-set Works formats. Selections here include formatting solutions for a number of different kinds of spreadsheets, including financial, creative, and professional.

When creating a new chart, you can choose from 12 different types, so finding one that does the best job of displaying your information should be no problem.

Row Height and Column Width. You can change the width of columns and the height of rows by first selecting them and then choosing Column Width or Row Height from the Format menu. For each, specify the size you want the row or column to be by choosing a point size or by setting the row or column to fit the tallest character. You can also set the width or height of a column or row by positioning your cursor near the header separators and dragging the edge when the Adjust cursor appears.

Format menu/chart mode. The Format menu in chart mode offers a number of specialized chart formatting options. You can add a second vertical axis (to add a second scale or make the chart easier to read) by selecting Two Vertical Axes. Add vertical or horizontal gridlines by selecting Vertical (Y) Axis or Horizontal (X) Axis and making sure the appropriate checkboxes are selected. You can change shading, color, and patterns in a chart by clicking Shading and Color; set font size, color, and type for labels and legends by selecting the Font option.

Tools Menu. From creating a chart and getting a hand with formulas to checking spelling and sorting, the Tools menu offers a selection of powerful features that will help you create and extend your spreadsheet.

Set up your spreadsheet to print with headers and footers in the Header And Footer dialog box.

Spell checker. The spelling feature is an invaluable way to make sure your spreadsheet is error-free and as professional looking as possible. To start the spell checker, click Spelling from the Tools menu. If Works finds a word that it thinks is misspelled, it will give you a variety of suggestions for replacements. Click the correct spelling and then click Change to replace that instance of the word or Change All to replace each instance of the word throughout the document. If your spelling is correct, you can click Ignore to go on to the next word or Ignore All if there are many occurrences of that word throughout the document. You can add a word to your personal dictionary by clicking the Add button, but you should be aware that this will place the word in the dictionary that all the Works components use.

Users can also change dictionaries, which can be valuable for many reasons, including if you work in more than one language. To change dictionaries, open the Tools menu and select Options, then the General tab. Select the dictionary you want from the drop-down menu.

If you are in a jam, consult the Works Help pane. It gives you several easy ways to find help within Spreadsheet.

Sorting. With the Sort option, you can do such things as sort selected cells and sort the selected cells plus all cells to the left and right. You can sort rows and columns numerically or alphabetically (in ascending or descending order), and you can select options in the dialog box to disregard headers and sort using additional columns.

Easy Calc. Let’s face it: One of the more difficult aspects to master in Works Spreadsheet probably lies with using formulas and functions. You can drop simple functions into a spreadsheet easily enough using the Function option from the Insert menu, but many of the more difficult functions may leave you scratching your head. Enter Easy Calc. This feature offers a lot of hand-holding in the form of well-documented dialog boxes that will walk you through each step of a function. If you’re unsure how a function works (or just want an in-depth look at it from the Works perspective), try Easy Calc.

Note that when you first open Easy Calc, it will offer a variety of simple functions such as Add and Average. Select one of these or click the Other button to get at the Easy Calc version of the Insert Function dialog box.

Using the Tile feature, you can place a spreadsheet and chart side by side and drag data from one to the other.

Create a chart. Charting lets you display your data so you can easily see trends; MS Works Spreadsheet gives you 12 basic chart types you can use and customize. To create a chart, select the data entries you want to use and choose Create New Chart from the Tools menu. On the Basic Options tab you can select a chart type (the Preview window makes it easy to click among chart types to see what your data will look like), give it a name, and select whether you want to show a border or gridlines. Clicking over to the Advanced Options tab offers additional options in chart organization and look, depending on your chart type. Click OK, and Works creates your chart.

Once you actually have a chart associated with a spreadsheet, you’ll see a number of additional options in the spreadsheet Tools menu, such as Rename Chart, Delete Chart, and Duplicate Chart. These are also available in the Tools menu while in chart mode.

Window Menu. Available only when you have both a spreadsheet and a chart (or charts) open at the same time, the Window menu lets you decide how Works displays windows (Cascade, Tile) and lets you arrange all icons in rows. This menu also gives you a list of windows that are currently open within Works Spreadsheet.

Help Menu. This brings us to the Help menu (which, by the way, is exactly the same in both the spreadsheet and chart modes). Spreadsheet offers you several options for getting help within the application, all of which are available in the Help pane that appears along the left side of the workspace. To open this Help pane, select Works Help from the Help menu, press the F1 key, or click the question mark (?) icon on the toolbar. Once the Help pane is open, you can click icons along its small toolbar to find help in several ways:

Answer Wizard. Type a question in the pane and click the Search button. If none of the answers help, try using different words and searching again. (You can return to the Answer Wizard screen by clicking the magnifying glass icon.)

Search for help. Click the binoculars icon to search the help topics by specific keywords.

Contents. You can look through Help contents to get overviews of topics by clicking the piece of paper icon.

Print it out. You can easily print a Help topic by clicking the printer icon.

Words in blue within Help sections are linked to definitions; click the blue words to see the definitions. If you want to resize or close the Help pane, you can do this with the controls in the upper right-hand corner of the pane (or just position the cursor at the edge of the pane and drag it larger or smaller).

MS Works also provides a series of introductory tours in areas such as Using Formulas and Functions, and other Help menu options provide access to Microsoft help online, assistance in using Works for people with disabilities, and more.

A Strong Solution For Many Users. While not as full-featured as Microsoft Excel, MS Works Spreadsheet does offer enough functionality, features, and assorted templates to make it a powerful spreadsheet solution for many home and small-business users.

by Rich Gray

Advanced Tips

•Charts, such as line or radar, are made up of markers to make the chart easier to read. You can change these markers to different shapes and colors from the Shading and Color section of the Format menu.

•If you go to paste data into a cell, and the cell only shows a series of number signs (####), it means that the column needs to be wider to accommodate the data. Click and drag the edges of the cell to make it wider so the information displays correctly or change the width by choosing Column Width from the Format menu.

•When creating most charts, Works will automatically create a legend for you that explains what each color and section of a chart represent. To make sure that Works doesn’t use generic labels such as Series 1 and Series 2, include any row or column titles when you select data to create a chart. To change legend labels, double-click a legend and enter or change labels for each series.

•You can freeze columns and rows so they stay in view as you scroll through a spreadsheet. Freezing columns or rows will also force Spreadsheet to print them at the left (columns) or top (rows) of the page. To freeze a column, select the column to the right of it and choose Freeze Titles from the Format menu. To freeze a row, select the row below it and make the same Freeze Titles selection. (When a selected row or column is frozen, a check mark will appear next to Freeze Titles in the Format menu.) To unfreeze rows or columns, select them and choose Freeze Titles again.

Want more information about a topic you found of interest while reading this article? Type a word or phrase that identifies the topic and click "Search" to find relevant articles from within our editorial database.

Copyright & Legal Information Privacy Policy