Lesson Five - Creating a Basic Spreadsheet for Calculating Household Expenses Using Calc

1. How to create a monthly household expense spreadsheet using the OpenOffice.org Calc spreadsheet application.

2. How to insert text and numbers within spreadsheet cells.

3. How to use the SUM function to quickly add values contained within a specified range of cells.

4. Learn how to create charts using data contained within a Calc worksheet.

Overview

The OpenOffice.org office productivity suite contains an easy-to-use, full-featured spreadsheet application called Calc. Calc has all of the fundamental features found in other major spreadsheet applications, plus features unavailable in any other application similar to it. In the next three lessons, we will learn how to use Calc to create both basic and more complex spreadsheet documents.

In this lesson, you will become acquainted with using Calc by creating a basic spreadsheet document for calculating household expenses on a monthly basis. Upon completion of this lesson, you will have learned the basics of entering text and numbers within cells, how to add values contained within a worksheet quickly and easily, how to create a bar graph that visually represents the data present within the Calc spreadsheet document and more.

Getting Started

Before we do so, we need to open the Lesson Five file that is available for use with this book. To open the file, follow these steps:

1. If you have not already downloaded the lesson files for this course, click on the Lesson Files topic located in the course lessons area and select to download the .zip file to your computer's desktop.
   
   

2. When the file appears on your computer’s desktop, double-click the file icon to unzip its contents and access the files associated with each lesson. Double-click the Lesson Files folder icon that contains the available lesson files, locate the Lesson 05 folder and double-click on it to access the file for this lesson. When you have opened the folder, double-click the file named lesson_05_start.ods to open the file.

Renaming the Worksheet

The first thing we are going to do is rename the worksheet we are working in. By default, Calc automatically creates three (3) worksheets when you create a new spreadsheet document. However, you can delete worksheets or add additional worksheets within a Calc spreadsheet document, if desired. These default spreadsheets are automatically named Sheet1, Sheet2 and Sheet3. While this naming scheme may not be a problem when working with basic worksheets, it can cause confusion when working with larger, more complex spreadsheet documents.

To rename the worksheet we will be working with, follow these steps:

1. Select the worksheet that is to be renamed by clicking on the worksheet tab located just above the Status Bar.

2. Click the Format menu, select Sheet from the menu options that appear and select Rename from the submenu options that appear.

3. When the Rename Sheet window appears, type the name Budget. This will become the name of the worksheet.

4. Click the OK to complete the operation.

Entering Text and Numbers Within Cells

Spreadsheet document layouts are organized by columns (labeled alphabetically) and rows (labeled numerically). The intersection of a row and column within the spreadsheet creates a cell. Cells are identified by their column and row location within the spreadsheet. For example, cell A1 is located within the spreadsheet where column A intersects with row 1.

Before any formatting or calculations are performed within a spreadsheet, often you must first select the cells associated with the operation you are trying to perform. To select an individual cell, simply click on the cell location with the left mouse button. To select multiple cells, hold down the left mouse button while selecting the range of cells.

To enter text and numbers within spreadsheet cells, simply select a cell and begin typing. The text and numbers will appear within the Formula Bar located just above the spreadsheet layout. Press the TAB key on the keyboard to select the cell in the next column, or press the ENTER or RETURN key to select the cell in the next row.

For this lesson, we are creating a spreadsheet that will help keep track of monthly household expenses. Only a couple of expenses have been recorded so far, and only six months are currently entered into the spreadsheet. Therefore, we need to complete the spreadsheet by entering the remaining months and related household expenses. To do so, follow these steps:

1. Select cell H1 within the Budget worksheet. Once the cell has been selected, type July for the month that will be entered into the cell. Then press the TAB key on your keyboard.

2. When you pressed the TAB key, you noticed that July was entered into cell H1 and Calc automatically proceeded to select cell I1 in the next column. With cell I1 automatically selected for you, type August and press the TAB key. Repeat this process for each month of the year. When you have reached the month of December, it should be entered into cell M1. In cell N1, type TOTAL in capital letters. If any of the words appear to get chopped off within the cell, it will be okay, as we will format the text and cells later.

3. Next, select cell A5 within the Budget worksheet. Once the cell has been selected, type Insurance for the type of expense that will be entered into the cell. Then press the ENTER or RETURN key on your keyboard.

4. When you pressed the ENTER or RETURN key, you noticed that Insurance was entered into cell A5 and Calc automatically proceeded to select A6 in the next row. With A6 automatically selected for you, type Phone and press the ENTER or RETURN key. Then in cells A7, A8 and A9, type Electric, Cable and Food respectively. In cell A10, type TOTAL in capital letters. Again, if any of the words appear to get chopped off within the cell, it will be okay, as we will format the text and cells later.

5. Now that we have created the rows and columns, we now need to provide the worksheet some data to calculate. As you have noticed, the rent and car payment amounts have already been entered for months January through June. Since those amounts will not change for the remainder of the year, enter 650.00 and 456.25 for the remainder of the year. When you enter 650.00, the decimal place will be dropped because the cells are currently formatted that way. We will be changing this format later in the lesson. Be sure not to enter any data within the TOTAL column, as it will be used later to calculate the yearly total for each household expense.

6. Next, we need to enter payment amounts for the other expenses. Beginning with cell B5, enter 100.00 for cells B5, C5, D5, E5, F5, G5, H5, I5, J5, K5 and M5 to reflect the amounts paid each month for insurance.

7. For the phone service expenses, enter the following amounts within the respective cells associated with the months the expenses were incurred: 42.25 (cell B6), 48.46 (cell C6), 46.18 (cell D6), 52.75 (cell E6), 45.52 (cell F6), 47.10 (cell G6), 49.19 (cell H6), 56.98 (cell I6), 54.14 (cell J6), 55.10 (cell K6), 58.55 (cell L6) and 60.17 (cell M6).

8. For the electric service expenses, enter the following amounts within the respective cells associated with the months the expenses were incurred: 178.18 (cell B7), 188.72 (cell C7), 165.12 (cell D7), 117.32 (cell E7), 128.98 (cell F7), 145.22 (cell G7), 176.54 (cell H7), 195.47 (cell I7), 168.65 (cell J7), 125.05 (cell K7), 145.34 (cell L7) and 162.27 (cell M7).

9. Beginning in cell B8, enter 48.15 for cells B8, C8, D8, E8, F8, G8, H8, I8, J8, K8 and M8 to reflect the amounts paid each month for cable television service.

10. Lastly, for the food expenses, enter the following amounts within the respective cells associated with the months the expenses were incurred: 225.15 (cell B9), 200.25 (cell C9), 182.56 (cell D9), 176.98 (cell E9), 215.48 (cell F9), 202.33 (cell G9), 199.87 (cell H9), 201.47 (cell I9), 164.77 (cell J9), 214.22 (cell K9), 210.99 (cell L9) and 223.57 (cell M9).

Using SUM to Add Values Within a Worksheet

Now that we have completed entering the text and values needed for the spreadsheet, we are ready to begin totaling up our expenses for each month and expense category. To do this, we will use the SUM function button located in the Function Bar. The SUM function button is similar to the AUTOSUM function button in Microsoft Excel. Using the SUM function button in Calc, you can automatically add the numbers in the cell range you select. The SUM function cannot be used to subtract, multiply or divide values within a selected cell range.

To add the expenses to receive a total for each month and category, follow these steps:

1. Using your mouse, select the cell B10.

2. Click the SUM function button located in the Function Bar. When you click the button, Calc automatically suggests a cell range. In this step, Calc automatically selects cell range B3 through B9, which is the cell range we wish to add the expenses of. If Calc had incorrectly chose the cell range you wish to add, you could simply hold down your left mouse button and drag over the desired cell range to make the appropriate selection. Once the desired cell range has been selected, press the ENTER or RETURN key and Calc adds the values within the cell range selected. The results are then produced within the cell originally selected.

3. Repeat Step #1 by selecting, one at a time, each of the following cells: C10, D10, E10, F10, G10, H10, I10, J10, K10, L10, M10, N3, N4, N5, N6, N7, N8, N9 and N10. After selecting an individual cell, follow the instructions in Step #2 to obtain the results for the appropriate cell range. When you reach cell N3 and click the SUM function button, Calc should automatically select cell range B3 through M3 (represented in the formula as B3:M3). This is the correct cell range to add the total yearly expense for rent. When you have completed adding the cell ranges specified above, the spreadsheet should look like the illustration below.

Formatting Text and Numbers Within Cells

As we have been adding the expenses for each month and category, you have noticed that not all of the values are formatted the same. For instance, all of the figures represented in this household expense worksheet are dollar amounts. When we typically write a dollar amount on paper, we write it with a dollar sign in front of the value, and cents are represented two decimal places behind the dollar amount. Currently, however, the amounts represented for the rent do not contain the cents amount. Nor do any of the values within the spreadsheet have the dollar sign placed in front of them.

In the following steps, we will format the cells so that the values contained within are represented in U.S. dollar amounts. We will also adjust the width of the cells within the spreadsheet so that characters and values within the cells are not chopped off. To perform these formatting changes, follow these steps:

1. Beginning on cell B3, hold down your left mouse button and drag over all of the numeric values contained within the worksheet to select them for editing.

2. Click on the Format menu and select Cells from the menu options that appear.

3. When the Format Cells window appears, click on the Numbers tab located at the top of the window.

4. In the Category selection area, select Currency.

5. Once a category has been selected, select a format type located within the Format selection area. By default, Calc will choose -$1,234.00. The minus (-) sign represented in front of the dollar amount represents a negative amount, as is a standard format spreadsheet applications use if a dollar amounts happens to be negative. This format type is acceptable, and should be selected if it isn’t already.

6. Click the OK button to complete the operation.

7. If you look carefully at cell N10, you will notice that the dollar value has disappeared and has been replaced by pound signs (###). This is because the cell isn’t wide enough to display all of the characters of the value that is currently there. Therefore, we need to adjust the column width for the worksheet. To do so, go to the Edit menu and choose Select All from the menu options that appear. This will select all of the cells within the worksheet.

8. Click on the Format menu, select Columns from the menu options that appear and select Optimal Width from the submenu options that appear.

9. When the Optimal Column Width window appears, it automatically detects that an additional 0.1” needs to be added to the width of each column. Click the OK button to complete the operation. When the column width has been adjusted, all of the characters and values within the worksheet should appear correctly, including cell N10. Click on any cell within the spreadsheet to deselect the cells.

Adding a Bar Graph Within a Spreadsheet

To create a bar graph within a Calc spreadsheet document, follow these steps:

1. Using your mouse, select the column range N3 through N9. After selecting the column range, hold down the CONTROL (CTRL) key on your keyboard and select the column range A3 through A9. By selecting column range A3 through A9, the bar graph, when completed, will contain a legend that will properly label each bar with the associated expense amount.

2. Go to the Insert menu and select Chart from the menu options that appear.

3. When the AutoFormat Chart window appears, the selection made in Step #1 should appear in the range field. If the selection is correct, make sure the checkbox ‘First Column as label” is selected. Then click the NEXT button at the bottom of the window. If the selection is not correct, click the Shrink button located next to the range field to enter back into the worksheet and select the appropriate cells containing the data to appear within the chart.

4. In the next window to appear, select the appropriate Chart Type. Use the scroll bar located on the right side of the window to view all of the available selections, including the bar graph. Also select the Rows from the Data Series radio button located below the Chart Type selections, and click the checkbox Show Text Elements In Preview to view how the chart will be labeled. The click the NEXT button.

5. The next window will allow you to choose from a selection of variant chart formats for the bar graph. For this lesson, select the first variant displayed (if it isn’t already selected) and click the NEXT button.

6. In the Display selection area that appears in the next window, type Household Expenses as the chart title within the text field provided. Make sure the checkboxes are selected for both the Chart Title and Legend. Select the checkboxes for both the X Axis and Y Axis titles. In the text fields provided, type Expenses for the X Axis and Amount ($) for the Y Axis. Make sure the radio button for formatting the data series in rows is selected.

7. Click the CREATE button to complete the operation. The completed chart should appear within the worksheet. Once the chart appears within the spreadsheet, click any cell to deselect the cells that were used to create the chart. Then you can move the chart to another location within the worksheet by placing your pointer in the middle of the chart, hold down your left mouse button and drag it to the desired location. You may also enlarge or reduce the size of the chart by placing your pointer on top of one of the green boxes on the corners of the chart, hold down your mouse button and drag the corner of the box until the chart reaches the desired size.

Saving a Spreadsheet Document

Now that the spreadsheet has been completed, the document file needs to be saved like any other spreadsheet document. OpenOffice.org supports over 20 file formats for opening and saving spreadsheet documents, including Microsoft Excel. To save the document to your computer’s hard drive or removable disk, follow these steps:

1. Click on the File menu and choose Save As from the menu options that appear.
   
   

2. A window will appear and prompt you to choose a location to save your document. Choose the location you want to save a document to in the Save In popup field.

3. In the field File Name, type the name you would like to save the file as.
   
   

4. In the Save As Type popup menu, select the file format you wish to save the document as, including the OpenDocument Spreadsheet (.ods) or Microsoft Excel (.xls) file format.

5. Click the button SAVE to complete the operation.

(NOTE: If you are given a window prompt that warns you about saving the document as a Microsoft Excel file, click the YES button. The OpenOffice.org development team has gone to great lengths to help ensure that your document saved in the Excel format will open properly with Microsoft Excel. However, because the programming code utilized to create the Excel file format is proprietary and not available to the OpenOffice.org team to view for ensuring full compatibility with Microsoft Excel, not all of your document’s formatting may open up 100% correctly when it is opened using the Microsoft Excel application.)

Printing a Calc Spreadsheet Document

Printing a Calc spreadsheet document is very similar to printing a Writer word processing document. However, because spreadsheets can be very long in width, often you will need to customize the orientation and scaling print configuration options before sending the document to the printer. To print a spreadsheet document within OpenOffice.org Calc, follow these steps:

1. With the file open within OpenOffice.org, click on the File menu and select Print from the menu options that appear. You may also hold down the Control (CTRL) key and press P on the keyboard to prompt for the Print window.

2. If you have more than one printer that your computer can send print jobs to, select the printer you wish to send the document to in the Printer selection area.

3. In the Print Range selection area, use the radio buttons to select which pages you wish to print. If you choose the Pages option, enter the page range you wish to print (example: 1-5 will print pages one through 5; 1,2,5 will print pages one, two and five). If you choose the Selection option, OpenOffice.org will only print the text you have selected (highlighted) within your document.

4. In the Copies selection area, enter the number of copies you wish to print of the document.

5. If you wish to customize the print job, click on the OPTIONS button and select or deselect the print options you wish to choose. If you do not want to customize any print settings, skip to Step #6.

6. When printing spreadsheets, you will often need to change the page orientation to Landscape and scale the page to less that 100% so that all of the columns that contain data on a worksheet will be printed on the same sheet of paper. You may also want to consider printing the worksheets on 8.5” x 14” US Legal size paper if there are many columns containing data within the worksheet. To configure the options, click on the PROPERTIES button within the print window and configure the appropriate selections using the popup menus provided.

7. Once you have completed specifying your print settings, click the OK button to begin printing.

Additional Resources

OpenOffice.org: Getting Started with Calc

http://documentation.openoffice.org/manuals/oooauthors2/0107GS-GettingStartedWithCalc.pdf

OpenOffice.org: Entering, Editing and Formatting Data

http://documentation.openoffice.org/manuals/oooauthors2/0302CG-EnterEditFormatData.pdf

OpenOffice.org: Creating Charts and Graphs

http://documentation.openoffice.org/manuals/oooauthors2/0304CG-CreatingChartsAndGraphs.pdf

Janice Holm Lloyd – N.C. Cooperative Extension Service: Budgeting for Home Ownership

http://www.ces.ncsu.edu/depts/fcs/housing/pubs/fcs432.pdf

Review Questions

1. By default, how many worksheets does a Calc spreadsheet document contain when you create a new document?

2. Columns are labeled _________ while rows are labeled _________ within the layout of a spreadsheet document.

3. What do the pound signs (###) within a cell indicate?

4. (True or False) Because spreadsheets can be very long in width, often you will need to customize the orientation and scaling print configuration options before sending the document to the printer.

5. (True or False) The SUM function can be used to add, subtract, multiply and divide values within a selected cell range.

Back to OOo3 Moodle Pages