Sparks Fly
[Home] [PBS VideoDatabase] [Social Studies] [TSU] [UVA]

A large table, which contains individual cells that hold data.

Spreadsheets are commonly used to carry quantitative analysis:

apply specific formulas;

chart the data;

assist the decision-making process.

What are the desired outputs?

What are the required inputs?

How is the data positioned?

What mathematical expressions are used?

Who will be using the spreadsheet?

Type in a question into the Auto Wizard box

Search the Index for a help topic

Use the Tip Wizard

Worksheet vs. Workbook

Formula Bar

Cell Address and Cell Reference

Formula Wizard box

Spreadsheet navigation

Control + Home

Control + End

The term worksheet is used interchangeably with the term spreadsheet. A workbook consists of several worksheets. What you see on the screen is one of the up to 255 worksheets in your current workbook. A single worksheet contains16,384 rows and 256 columns. An Excel file contains one workbook.

To create a new workbook, simply click on the New Workbook Button in the upper left corner of the screen.

Every time you open Excel, a blank workbook will be opened by default.

To move along the screen use either:

the arrow keys or

the scrollbars, by dragging their small gray rectangles.

To move between worksheets, click on the respective tab on the bottom of the screen.

To select a cell, simply click on it.

A block is a set of adjacent cells. To select a block, click in one of its corners and drag with the mouse until the black rectangle covers your whole block. To select a set of nonadjacent blocks, select the blocks one by one, holding the Ctrl key all the time.

To do anything with a block or cell, you first need to select it.

Name the worksheet

Activate cell, type (shows in formula bar)

Use the Form on the Data Menu (Enter name, subject, school for practice--use form as well)

Double click to activate cell for entry of information directly (see what looks like in cell)

In Excel 97, there are two ways to insert data into a cell:

double-click the cell and start typing;

select the cell (by a single click), click in the Formula Bar and start typing.

In either case, press Enter when done.

The first method is faster but the arrow keys do not always work properly with it.

If you made an undesired change to a cell, press Esc instead of Enter. If you already pressed Enter, press Ctrl + Z to undo the change.

To delete a cell or a block, select the cell or the block and press the Delete key.

Click on the cell and edit in the formula bar

Double-click on the cell and edit within the cell

Deleting cell information (click on cell and choose Edit, Clear or click on cell and press the Delete key)

Start typing a repeat of a name to see AutoComplete work

Click on Tools, Options, Edit, Enable AutoComplete Tab.

Pick from list (Rt. Click on cell and choose option

Have to select cells if you want to make changes at all

Click many cells by first clicking on the first cell, hold down shift key and click at end of the list

To select a continuous range, hold down control key

Click on headings to select entire row/column

Use AutoFill to complete days of the week

Create formula--begin with = sign

Do not enter numbers, enter cell references

Appropriate symbols

+

-

*

/

A cell address is the row and column in which the cell is located. For example, cell A10 is the cell in column A and row 10. A block address is defined in a similar way. For example, A10:D12 refers to the block with upper left corner A10 and lower right corner D12.

Excel allows you to enter a formula such as A10 + A11*A12 into a cell. The content of this cell will be adjusted automatically as the value of A10, A11, or A12 changes.

There are several ways to enter a formula.

Type into the cell : =A10 + A11*A12 and press Enter. Instead of typing the address of a cell, you can simply click on the cell.

To sum up a row or a column of a table (not the whole row or column in the worksheet):

select the cell below the table column or the cell to the right of the table row;

press the Sum Button Þ press Enter.

You can accept the cells that Excel selects or select your own by clicking on the first cell and moving to the last cell.

Sometime you may need to fill in a sequence of cells in some systematic way, such us:

January, February, March, …

Part 1, Part 2, …

Bla-bla 1, Bla-bla 2, Bla-bla 3, ….

In this case, fill in the first cell Þ drag the lower right corner of the cell to fill the rest.

You can also speed fill a cell with a formula by dragging the lower right corner of the cell. The formula itself will be adjusted in the new cells.

To find a peace of data in your workbook, simply use Edit Þ Find.

To find and replace a piece of data, go to Edit Þ Replace.

Many ways to copy a cell’s contents

Edit Copy, Edit Paste

Catch box in lower right (+) get cross hair for mouse pointer

Drag mouse downward to copy formula or cell contents

Control + C (copy), Control + V (paste)

To copy a block, select it and press the Copy Button.

To paste a block, place the cursor in the upper left corner of the new location and press the Paste Button.

To move a block, select it and drag one of its edges (wait for the big arrow to appear).

When you speed fill a formula, you may want to fix some cells within the formula.

In both cases, you may use fixed addresses. The F4 key changes the address type.

To save your workbook, simply press the Save Button. If you have not assigned a name to your workbook yet, Excel will ask you to do so.

Later on, you can make a copy of your workbook and save it under a different name. To do that, go to File Þ Save As Þ Type the new name Þ OK.

To close a workbook, click the Close Button in the upper right corner. If there are unsaved changes, Excel will ask you if you want to save them.

To open an existing file, click on the Open Button Þ double-click the icon of the file.

To delete a file, click on the Open Button Þ Right-click the file Þ Delete Þ Close.

Worksheets are most often used to analyze numeric data. However, proper formatting can add professionalism and readability to your results.

Excel allows you to make your worksheet as fancy as your eyes can carry! Do not allow, however, formatting to become an addiction, consuming most of your time.

Since worksheet users most often work with tables, Excel offers a rich repertoire of automatic table formats. To apply such a format:

Select the table Þ Format Þ AutoFormat Þ Choose a format Þ OK.

To adjust the size of a column (or row), place the cursor on the border of the column (or row) heading.. Then drag the border.

Excel can adjust automatically the size of particular columns (or rows):

Select the columns (or rows) Þ Format Þ Column (or Row) Þ AutoFit Selection (or AutoFit).

Excel offers a generous list of formats which you can apply to a cell or a block.

Select the cell (or the block) and choose Format Þ Cell Þ A box with six sections appears.

From there, you can specify the number and date format, the alignment of the text, the font color and type, the border type, etc.

A style is a combination of cell formatting options (fonts, colors, borders, patterns, alignment, etc.).

To define a style of your own, use:

Format Þ Style Þ Type a name for the style Þ Modify Þ Specify the components of the style Þ OK Þ OK.

To apply a style to a block, select the block and use Format Þ Style.

In addition to formatting, Excel allows the users to add and create true art in their worksheets.

Although, worksheets are used mostly to analyze numeric data, graphics can be a well-deserved treat for everybody using the worksheet.

To get started, add the Drawing toolbar:

View Þ Toolbars Þ Mark Drawing (if not marked already) Þ A bunch of drawing buttons will appear on the screen.

You can use the rectangle, oval, line, and arrow buttons to create a simple picture. Click on the respective button and then click where the shape must appear.

Use these tips to edit any object in Excel:

drag its borders to adjust its size;

drag the whole object (wait until the cursor becomes a four-headed arrow);

use the buttons on the Drawing toolbar to specify a fill-in color; the color, type, and width of the border; the shadow and 3-D effects.

Few users have enough time to create their own art. Fortunately, Excel offer a large accessory of pre-made art:

WordArt - several very fancy title fonts. To apply, click on the WordArt button.

AutoShapes - more than 100 common shapes (banners, special arrows, stars, etc.). To apply use the AutoShapes button.

Clip Art - pre-made pictures, sound effects, photos, and videos. To apply, go to Insert Þ Picture Þ Clip Art. If you insert the Office 97 disk, more objects will appear (not all are installed from the disk to save memory).

If you have an image or sound saved in a file, you can easily insert them into the spreadsheet:

Insert Þ Picture Þ From File Þ Double-click the name of the file.

Organize your data into a table with meaningful row and column headings.

Select the whole table with the headings.

Click on the Chart Wizard Button.

Specify the chart type and press Finish.

Your chart is ready.

Instead of pressing Finish, you could keep on pressing Next to add some extra attributes to your chart:

In the second box, specify which data goes to which axis.

In the third box, specify the titles, the position of the legend, the thickness of the gridlines, etc.

The forth box asks you whether you want the chart placed on a separate worksheet.

By far the most important decision in creating a chart is to select the right chart type. Almost any table can be plotted by almost any chart type. However, not each chart type will give you the intended perspective.

Excel allows you to change your initial decisions about the chart at any time. Excel also offers many additional effects.

A chart consists of several main components: title, X-axis, Y-axis, legend, chart area, plot area, and data series (e.g., the lines or the rectangles in the chart).

To edit a component, click on it to select it and then right-click it.

In the small box that appears, the first option is Format… If you select it, you will be able to change any of the formatting options available for this component.

Printing is the most trivial task to most users - press the Print button and go get your printout. This will print your entire worksheet.

With worksheets, however, you may want to add a few quick steps to the printing process to make your printout look more professional.

Of course, printing your spreadsheet can be as easy as pressing the Print button. You can, however, gain more control if you go to File Þ Print.

From the box that appears, you can:

Change the printer.

Print multiple copies.

Print only a pre-selected block.

Many users dislike gridlines on a printout (those ugly cells around your table or chart).

To hide the gridlines in your worksheet, go to Tools Þ Options Þ View and unmark Gridlines.

To hide the gridlines only in your printout, go to File Þ Page Setup Þ Sheet and unmark Gridlines.

Make your life easier

Get started creating time saving spreadsheets!

Return to Spreadsheets Handout