Spreadsheet Tutorial Logo

T3 Home>Foundations>Week 8>Spreadsheet Tutorial Home>Getting Started

 

Overview

 


 

Standards

 


 

Getting Started

 


 

Other Resources

Getting Started

This section introduces basic Spreadsheet skills. As you read, you will be invited to "Try Out" the skill introduced.Each time you see the Interactive Mouse Iconmouse, we encourage you to return to your own spreadsheet application to practice.

Note: If you are on a Mac, use the Finder to switch back and forth between the window for this tutorial and the window for your application. The Finder is in the upper right hand corner of your screen, and, by holding the mouse button down on the Finder, you will see a list of all applications open on your Mac. If you are on a PC, "Alt+Tab" will automatically switch between your open windows.

1. Introduction

2. File Management

 

3. Creating a Spreadsheet:

  1. Rows and columns.
  2. Cells.
  3. Formatting: text, numbers, alignment, borders.
  4. Calculations.
  5. Fill: across, down, & special.
  6. Page display.
  7. Sorting data.
  8. Protection
  9. Charts and graphs.
  10. Using draw tools.

4. Integration:

  1. Inserting images.
  2. Inserting spreadsheets into a word processing document.
 

1. INTRODUCTION


1.1 Introduction: Online Help

All software applications have an online Help resource. Most often it is one of the last menu items on the right, and, at minimum, it will usually include an index, a list of contents, a search feature, and balloon help. Remember that this resource is here for you as you meet new challenges with your software in the future.

Help Menu

 

assistant helpOn Microsoft Excel, you might find the "Assistant" to be helpful.

This feature can be turned on and off under the help menu.

 

mouseLook at your help on your application and find items mentioned above. Be sure you understand how to turn the balloon feature on and off. Try out your help's search feature by entering a keyword, such as "column". Also, notice what other items are listed under your help menu. Is there a tutorial listed? If so, you may want to take the time to do this tutorial at some point.

Back to Top


1.2 Introduction: Menu Items

Each application has menu items across the top of the screen which allow you to use the features of your software. Notice the similarities between these two applications' menu items.

Menu Similarities

Many of the actions that can be taken through the menu items can be done through clicking a shortcut icon. Notice the shortcut icons located just below the menu items. In AppleWorks, it is called a Button Bar.

excel shortcut icons

mouseBecome familiar with the menu items in your application. Slowly drag your mouse over each shortcut to familiarize yourself with your shortcut icons. You may want to use your Balloon Help feature under the Help menu.

Back to Top


2. FILE MANAGEMENT


2.1 File Management: New

To create a new spreadsheet, go to File>New.

new document

Usually, this will open a blank spreadsheet. In Excel it will be called a workbook. In some integrative applications, you must choose what type of file you would like to create. In AppleWorks, after going to File>New, the following dialog box appears.

choose new file

A new spreadsheet file should look something like this:

 

mouse See what your options are after going to File>New. Create a new file in your application so that you can begin creating a practice spreadsheet. Notice if your application has a Project Gallery, a Wizard, or an Assistant. These are features that may offer you pre-formatted, blank spreadsheet files to work from, for future reference.

Back to Top


2.2 File Management: Save, Save As, Open

To save a document, go to File>Save.

The first time you save, you can choose a destination and file name. After this point your computer will automatically save it to that destination. If you would like to specify a new destination or verify the original destination of an existing document, go to File>Save As.

Here are recommended steps to saving. (The steps are outlined for the Excel dialog box, however, the steps are universal.)

To open an existing file, go to File>Open. The dialog box will look similar to the dialog box for saving. Find the folder where your existing file is, highlight it, and click Open.

mouseReturn to the new file you created in 2.1 of this tutorial. Practice saving this spreadsheet. Browse the options in the save window. Follow the four steps to saving. As a test, close the window or quit the application and see if you can find the document you just saved by going to File>Open.

Back to Top


2.3 File Management: View, Print Preview, Print

View ~

As you work on your file, you may want to zoom in to get a closer look, or zoom out to get some perspective. The zoom and magnification features in MS Excel are located under the View>Zoom. The shortcut is located at the top of the screen and looks like this: magnification . The zoom and magnification features in AppleWorks are in the lower left-hand corner of the spreadsheet file window and looks like this: magnification.

Print Preview, Print ~

Before printing your document, it is a good practice to go to Print Preview. This allows you to see exactly what your pages will look like printed out. It is a good place to verify the total number of pages of your spreadsheet and the layout. Go to File>Print Preview. If there is not a Preview option, you may have to choose Print, then look for a preview option within the resulting dialog box. When you are satisfied with what you see in the Print Preview, you may be ready to print. The dialog box that appears when you go to File>Print will vary depending on the type of printer you have. Here is what the print dialog box looks like for an Epson Stylus Color printer, accessed through an AppleWorks spreadsheet file.

printing options

Another feature you may want to refer to when preparing to print is Page Setup. Usually, this is where you can designate your page layout as Portrait or Landscape. Sometimes you can also work with your margins through Page Setup.

mouseFind the zoom and magnification feature on your application. Zoom in and out on your document to see the capabilities of the feature. Prepare for a practice print. Use Print Preview. If it is not in your File menu, a preview option should appear in your print dialog box. Explore the features in your Page Setup on your application. Practice changing your document from Portrait to Landscape Layouts.

Back to Top


3. CREATING A SPREADSHEET


3.1 Creating a Spreadsheet: Rows and columns.

All spreadsheets are made up of rows and columns.

Row headings are the numbers that appear along the left-hand side of the file. Row 4 is highlighted below.

row 4

Column headings are the capital letters that appear along the tops of the columns. Column B is highlighted below.

column B

In order to highlight an entire row or column, drag the mouse over the appropriate heading and single click. When you drag the mouse over the line the separates each heading, you can click and drag the mouse to change the column width or row height. Columns and rows may be formatted by first highlighting, then going to the Format features below.

format columns and rows

mouseIn your practice spreadsheet file, drag the mouse over the headings for the columns and rows. Practice highlighting one column, then one row. Practice changing the column width and row height.

Back to Top


3.2 Creating a Spreadsheet: Cells.

Each individual box in the grid of a spreadsheet is called a cell. Cells are the foundation of any spreadsheet. All data, including text, numbers, and formulas, are entered into a spreadsheet through individual cells.

The column and row headings are used to give each cell a specific address. For example, cell B4 is highlighted below.

cell B4

Notice that the cell address "B4" is noted in the upper left hand corner of the Sample Spreadsheet above.

To enter text into a cell, click once on the cell and begin typing. Most spreadsheet applications will show your blinking cursor and the text being typed into a field at the top of the window. When you are finished typing, press enter or return, and your text will appear in the original cell that you chose.

mouseSelect a cell in your document by clicking once on a cell. Make sure you know the cell address. Notice where your application displays the cell address. Select a cell, and practice typing text into it. Note the importance of pressing enter or return after typing text.

Back to Top


3.3 Creating a Spreadsheet: Formatting: text, numbers, alignment, and borders.

Formatting cells can be done before or after text, numbers, or formulas are entered into cells. The advantage to formatting after entering data is that you can see how the changes in format affect your spreadsheet immediately. The following sample spreadsheet shows what data can look like without and then with formatting.

before and after formatting

Formatting Text ~

In a spreadsheet, you can format text in much the same way as in a word processing document. Font, size, style and color are the main options. Select the cell with the text to be formatted, then choose the menu item for formatting text. In MS Excel and AppleWorks, go to Format.

format text menus

In Excel, the Format>Cells dialog box will appear. Click the the Font tab.

excel font formatting

Formatting Numbers ~

In a spreadsheet, you can designate what type of numerical data you will be entering, including currency, percentage, date, time and others. First select the cells you want to give a numerical attribute. Then,go to the Format menu.

format number

In AppleWorks, the dialog box looks like this:

Format Numbers

In Excel, the Format>Cells dialog box will appear. Click the the Number tab.

Format Numbers

 

Formatting Alignment ~

Whether you have numbers or text in a cell, you can set the alignment. Some aspects of alignment vary quite a bit from application to application. The standard alignment features will include left, right, and justified, as well as a text wrap feature. To set the alignment for certain cells, first select them. Then, go to Format.

Format Alignment

In Excel, click the Alignment tab in the Format>Cells dialog box.

format alignment

Formatting Borders ~

When you have your data entered, you may want to apply borders around certain cells to emphasize headings or to better organize your information. The grid that you see while you work in a spreadsheet file can be printed, or you can choose not to print it. (This is an option that you should be able to choose in File>Print.) If you choose not to print the grid, then the only lines around your data that will be printed will be the borders that you format.

To add borders to your spreadsheet, first envision where you want to put lines and borders. Select specific cells that you want to work with, then go to the Format menu.

format borders

borders dialog boxes

mousePractice working with the formatting options in your application. You may want to enter more data into cells, first, so you can see the effects of your formatting. Remember to select the cells you want to change before spending time in various formatting dialog boxes. Make sure you are familiar with formatting text, numbers, alignment, and borders in your spreadsheet application.

Back to Top


3.4 Creating a Spreadsheet: Calculations.

Data in cells can be added, subtracted, multiplied, divided, averaged, and calculated in a variety of other ways. These calculations are performed by entering a formula into a cell or cells.

Data within cells of a spreadsheet can be one of three types: labels, constants, or formulas. The following diagram illustrates the different types of data in cells.

types of data

Formulas often include functions. Functions are predefined formulas that perform calculations by using specific values. The SUM function adds values or ranges of cells, and the PMT function calculates the loan payments based on an interest rate, the length of the loan, and the principal amount of the loan.

The most commonly used spreadsheet formulas begin with "=SUM". To calculate data in your spreadsheet, you must first know what cells will be involved and what mathematical calculation you want to perform. For instance, to add cells B1 and B2, the formula would look like this:

"=SUM(B1+B2)"

To add the first ten cells of column B, the formula would look like either of the following:

"=SUM(B1...B10)"

"=SUM(B1:B10)"

To create a formula, you must first select a blank cell. If you are adding a column of numbers, you may want to select a cell directly below the column of numbers. Begin typing your formula, and click return or enter when you're done. Your application will tell you immediately if your formula will not work.

Other common calculations and their operators are listed below.

Basic rules of mathematical equations apply to formulas in regards to parentheses. For instance, the formula for the Average Test Score example above is: "=SUM(B2:B5)/4".

Spreadsheet applications offer an AutoSum feature. The shortcut icon looks like this: . The way that it is used will vary. In AppleWorks, select the cells that have the constants you wish to work with, then click the AutoSum symbol on the button bar. Work with the formula as needed, press return, and AppleWorks will choose a cell to put the formula into below or to the right of your data. In MS Excel, it works differently. See below.

Excel AutoSum Feature

 

mouseCreate formulas in your document. You may have to fill some cells with Constants first. Type in formulas manually first. Look for the AutoSum feature in your application. Become familiar with the AutoSum feature, as it can save you the time of typing formulas. Make sure you can add a range of cells and average a range of cells.

Back to Top


3.5 Creating a Spreadsheet: Fill: across, down, & special.

The "Fill" feature in a spreadsheet application refers to filling a range of empty cells with the same basic formula that exists in one particular cell. For instance, if you have several columns of data to be added, all you have to do is set the formula for the cell at the bottom of the first column. Select this one cell along with the adjacent cells yet to be filled with the same formula, and use your Fill Across feature. The formula you created will be pasted correspondingly to each column.

Fill Down operates in the same way. See the steps below.

Fill Down Steps

Fill special is a feature that will automatically fill a range of cells successively. For instance, you may want to have a column filled with successive dates, times, or numbers. To perform a special fill, select the cell that will begin the series of information along with the blank cells you desire to be filled. Select the special Fill feature in your application, then work within the dialog box appropriately.

Fill Special Menus

Following is a diagram outlining the steps to fill months in a column in MS Excel.

Excel Fill Special Steps

Following is a diagram outlining the steps to fill times in a column in AppleWorks.

AppleWorks Fill Special

mousePractice using the Fill feature in your software application.

Back to Top


3.6 Creating a Spreadsheet: Page display.

In various circumstances, you might want to change the way your spreadsheet appears while you work on it. For instance, you may want to remove all of the gridlines, or you may want to see what all of your formulas look like rather that their resulting values.

In AppleWorks, go to Options>Display:

Page Display

In MS Excel, go to Edit>Preferences, and click the View tab:

Edit View Preferences

mousePractice changing the page display in your application. Be sure you are familiar with turning on and off the options for viewing gridlines, row/column headings, and formulas.

Back to Top


3.7 Creating a Spreadsheet: Sorting data.

Sorting data can be done in two general ways: ascending or descending. Numerically, this means from 1 up to 20 or from 20 down to 1, respectively. Alphabetically, this means from A to Z or from Z to A, respectively. If you have a range of cells with data that you would like to have sorted using a particular row or column as a guide, a spreadsheet will sort your data keeping all correlated information and formulas together. In order to do this, however, you must be very careful to highlight all of the data involved. The Undo feature is very useful here if you mistakenly sort only one column of data and it confuses all of the information.

To sort data, first have an idea of preference for which column or row you would like to sort all of the data by. You will be able to choose three or more levels of sort preferences depending on your spreadsheet application. For instance, you may want to sort data on students by test score, then by last name, then by first name. You may also choose to sort by one column. You will enter this information in the Sort dialog box of your application.

Carefully select all data involved in the reorganization of information. Go to your application's Sort feature.

 

mouseSort the data in your practice spreadsheet. Make sure you understand your "Order Keys" or your levels of sorting and how this can help you better organize your data.

Back to Top


3.8 Creating a Spreadsheet: Protection.

If you put time into formatting cells and creating formulas, you may want to protect the cells. Especially if you create a spreadsheet that will be used frequently or that will be used by others who you do not want to be able to change certain formats and formulas.

For example, imagine that the following spreadsheet will be used as a template, meaning after each test you use this particular spreadsheet to find the class's average test score. Perhaps you have an assistant that will be entering the test scores into this template after all of the tests are graded. Before protecting cells, look at your spreadsheet and analyze what you cells you want to remain the same or "protect" and what cells will need to be left editable.

sample template

When you're ready to protect certain cells, select them. You may need to select a few at a time. Find your Protection feature in your spreadsheet application. In AppleWorks, you simply lock or unlock cells. In MS Excel, there are a few more steps involved.

Protection Steps

 

mouseIn your practice document, practice locking certain cells in your spreadsheet. Note how to unlock or unprotect cells.

Back to Top


3.9 Creating a Spreadsheet: Charts and graphs.

Once you have data in a range of cells, you have the option to create a chart or graph based upon that data. Here's an example of a MS Excel Chart based on a spreadsheet:

Carnival Proceed Chart

To make a chart, select the cells that contain the relevant data. Go to your application's Chart feature and work your way through the dialog box or wizard that presents itself. In MS Excel, you will be guided through a step by step process. In AppleWorks, it is up to you to be sure to explore all of your options in the dialog box. In both applications, you can edit the chart or graph once it is created, however, sometimes it is just easier to start over from the beginning when you are just learning.

AppleWorks:

AppleWorks Chart Options

 

mouseCreate a chart in your practice spreadsheet. Become familiar with labeling the chart with a Title, an X and Y axis, and a Legend. It is very important not to have blank columns or rows within your selected cells.

Back to Top


3.10 Creating a Spreadsheet: Using draw tools.

In your spreadsheet file, you can use draw tools. You need to set your preferences so that the tool palette will show. To do this, go to the appropriate menu item for your application. It should look something like this.

Once you have your tools, you can create text boxes and draw graphics just as you might in a word processing program. You need to be careful that what you draw does not cover up your existing data. Following is an example of an AppleWorks spreadsheet with draw tools highlighted.

AW Draw in Spreadsheet

mouseSee if you can get to different parts of your practice document by doing a search for a particular word. Try out the Find and Replace feature.

Back to Top


4. INTEGRATION


4.1 Integration: Inserting images.

Inserting images can be one way to use integrative applications. If you create or find images through other applications and save them to a folder on your harddrive, you can insert them into your spreadsheet file. There should be a feature within your menus called Insert or Insert Image. Often times it will be located under File>Insert. A window will pop up that will allow you to find the folder with your collected images. You may have to browse through folders or directories until you find it. If you want to insert clip art, there might be a separate menu item Insert Clip Art which will automatically take you to the folder filled with the application's library of art.

Once you find it and choose it, it will appear in your spreadsheet as a selected image, ready to be placed into position. Be sure to follow copyright law and work within the Fair Use Guidelines if you use an image you did not create. Clip art libraries that come with your software package need no citation. Most images from the Internet, CDROM's, and directly from other artists will require some type of citation depending on how you plan to use it. Look for the copyright statements before using images.

mouseFind your Insert feature, and insert an image. You may want to practice moving your inserted image into position while it is selected.

Back to Top


4.2 Integration: Inserting a spreadsheet into a word processing document.

You may want to insert information from a spreadsheet or a spreadsheet in its entirety into a word processing document. This is a very useful feature, although how to do it varies from application to application. In most cases, formulas and some formatting can be lost in the transfer process.

Insert Options

Another option is to select cells within the spreadsheet application, go to Edit>Copy. Then open the word processing file where you'd like to see the spreadsheet inserted, place the cursor into the appropriate position, and go to Edit>Paste.

mouse Practice inserting a spreadsheet into a word processing document. Notice whether or not formulas get transferred, or if they are lost in the transfer.

Back to Top

small t3 logo
http://www.k12.hi.us/~tethree/01-02/

Last Updated on 10/17/01.
Diana_Papini@notes.k12.hi.us.