T3 Home>Foundations>Week 8>Spreadsheet Tutorial Home>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 Icon, 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.
2. File Management
3. Creating a Spreadsheet:
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.
On Microsoft Excel, you might find the "Assistant" to be helpful.
This feature can be turned on and off under the help menu.
Look 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.
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.
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.
Become 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.
2. FILE MANAGEMENT
To create a new spreadsheet, go to File>New.
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.
A new spreadsheet file should look something like this:
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.
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.
Return 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.
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: . The zoom and magnification features in AppleWorks are in the lower left-hand corner of the spreadsheet file window and looks like this: .
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.
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.
Find 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.
3. CREATING A SPREADSHEET
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.
Column headings are the capital letters that appear along the tops of the columns. Column B is highlighted below.
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.
In 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.
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.
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.
Select 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.
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.
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.
In Excel, the Format>Cells dialog box will appear. Click the the Font tab.
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.
In AppleWorks, the dialog box looks like this:
In Excel, the Format>Cells dialog box will appear. Click the the Number tab.
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.
In Excel, click the Alignment tab in the Format>Cells dialog box.
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.
Practice 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.
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.
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:
To add the first ten cells of column B, the formula would look like either of the following:
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.
Create 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.
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 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.
Following is a diagram outlining the steps to fill months in a column in MS Excel.
Following is a diagram outlining the steps to fill times in a column in AppleWorks.
Practice using the Fill feature in your software application.
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:
In MS Excel, go to Edit>Preferences, and click the View tab:
Practice 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.
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.
Sort 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.
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.
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.
In your practice document, practice locking certain cells in your spreadsheet. Note how to unlock or unprotect cells.
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:
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.
Create 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.
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.
See 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.
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.
Find your Insert feature, and insert an image. You may want to practice moving your inserted image into position while it is selected.
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.
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.
Last Updated on 10/17/01.