Lesson 4: Introduction to the Calc Spreadsheet


Download Lesson 4: Introduction to the Calc Spreadsheet


Preview text

ESSENTIAL LibreOffice: Tutorials for Teachers Copyright © Bernard John Poole, 2014. All rights reserved

4

INTRODUCTION TO THE Preparing a Grade book

CALC

SPREADSHEET

LEARNING OUTCOMES
This tutorial will help you understand what a spreadsheet is and where a spreadsheet might come in useful for classroom management. Specifically you will learn about the following topics.
• Helpful hints for understanding the basic concepts of a spreadsheet, including: • cells, rows and columns • cell coordinates • entering data into a spreadsheet cell
• Setting up labels, including: • setting column widths • aligning data in cells • entering column and row labels
• Creating and copying formulas • Making changes in a spreadsheet
• inserting rows and columns • deleting rows and columns • Producing a printed copy of the contents of a spreadsheet document • Saving a backup copy of your work • Appreciating the power of spreadsheet templates
108

Lesson 4: Introduction to the Calc Spreadsheet
A caveat before you begin: You'll find it easiest to use the tutorial if you follow the directions carefully. On computers there are always other ways of doing things, but if you wander off on your own be sure you know your way back!
4.1 GETTING STARTED
Before we begin For this lesson it will be good to have a separate folder inside your Data Files folder for the spreadsheets you’re going to create.
Insert your USB drive (Removable disk) containing the Work Files for LibreOffice in a USB port on your computer Navigate to your USB drive > Work Files for LibreOffice folder > Data Files folder, and create a new folder called Spreadsheet Documents Some background about spreadsheets Fig. 4.1 illustrates a typical spreadsheet for keeping track of student grades.
Fig. 4.1 Typical spreadsheet (Grade book) for a 4th Grade class As illustrated in Fig. 4.1, a spreadsheet is a grid divided into rows that run across the spreadsheet from left to right, and columns that run from top to bottom. The leftmost column (Column A in Fig. 4.1) is often used for descriptive labels that identify the name of the information that is stored in each of the cells in any particular row in the spreadsheet. The rightmost columns of data (Column J and M in Fig. 4.1), and the lower rows of a set of figures (Rows 25 thru 27 in Fig. 4.1) are often set aside for row and column totals and summaries respectively.
109

ESSENTIAL LibreOffice: Tutorials for Teachers Copyright © Bernard John Poole, 2014. All rights reserved
The Calc screen is thus a window onto a large grid of rows and columns (Fig. 4.1) into each cell of which data are entered, usually from the keyboard. You can build formulas into selected cells which automatically carry out calculations on designated sets of data. You'll learn how to do this in this lesson and the next.
Historically, spreadsheets were maintained in large format books called accounting journals or ledgers. They date back to the 14th and 15th centuries and were famously first documented by a Franciscan monk named Brother Luca Pagioli (Fig. 4.2), who published, in 1494, the first known book about accounting practices.
Fig. 4.2 Fra Luca Pagioli Nearly 500 years later, in 1978, Dan Bricklin, a young graduate student at the Harvard School of Business, came up with the idea for an interactive spreadsheet and, together with Robert Frankston, developed a program for an early PC—the Apple II.
Fig. 4.3 Dan Bricklin and Robert Frankston, c. 1980 110

Lesson 4: Introduction to the Calc Spreadsheet
They called their program Visicalc. It simulated a traditional accounting worksheet, though they saw its potential for use outside the accounting field. It was, in fact, the very first electronic spreadsheet, a prototype of the many varieties of spreadsheets available today. The Calc spreadsheet which you are about to learn to use is considerably more powerful than Visicalc, if only because the computers of today are many times faster than anything available in 1978. Calc is able to handle much larger sets of numerical data at much greater speeds and has a richer set of functions and general calculating and data visualization tools. Like Visicalc, however, it goes beyond the hand-written worksheet used in traditional accounting in that it is programmable—you can program it to do the math for you! And this was Dan Bricklin’s stroke of genius.
We have a multitude of challenges in our everyday lives, so why do math if we can design a machine to do it for us? This doesn’t mean we shouldn’t teach math in schools, of course. What it means is that we should teach math in schools so that our students can understand what the computers can do for them, as well as know how to program the computer do it.
You can build instructions into an electronic spreadsheet to do relatively complex mathematical calculation and analysis. You also can build instructions into the spreadsheet to carry out humdrum, repetitive calculations—the kind of calculations which could take hours, even days, to complete manually but which, when done electronically, take a matter of seconds.
The spreadsheet is most used for business accounting and data analysis. In K-12 schools, however, the spreadsheet comes in handy as a tool for keeping grades. It also is useful for creating charts and graphs of all kinds, as well as for data analysis related to class projects where numbers need to be organized, managed, and analyzed. It also is an excellent tool for helping students learn math concepts and has many other applications with students across the curriculum. The Skills Consolidation section at the end of the chapter will give you the opportunity to brainstorm with your classmates in order to come up with a list of such applications.
But first you need to learn more about spreadsheets and how they work.
Practice makes perfect As with the word processor, the best way to learn about the spreadsheet is to build a spreadsheet document and work with it. So here goes.
Open the LibreOffice program, then open a new Calc spreadsheet
You should now see on your screen a new Calc spreadsheet document titled Untitled 1.
Templates A template is an outline or form which can be used over and over when carrying out other projects that require the same basic document format. Here you are going to build a spreadsheet template to simulate an empty Grade book. Once you have created the template, you will keep it as on your disk for future use. You will be able to use this template from semester to semester to build the electronic Grade books for all your future classes.
The default LibreOffice name for documents (such as Untitled 1) is always nondescript. So it always is a good idea to immediately name any new document with a recognizable name of your choice. After all, you want to be able to tell what each document contains when you look at a list of the many document names on your disk. Shakespeare asked: “What’s in a name?” Well, when it comes to file names on a computer disk, names matter a great deal! You are about to create a template for a Grade book, so let’s call a spade a spade: a good name for the document might be something like “Gradebook Template.”
111

ESSENTIAL LibreOffice: Tutorials for Teachers Copyright © Bernard John Poole, 2014. All rights reserved
From the File menu select Save As and navigate to your USB Drive > Work Files for LibreOffice > Data Files) to your Spreadsheet Documents folder Type Gradebook Template as the document name for the new spreadsheet and click on the Save button You should now be looking at a screen with the name of your document (Gradebook Template) at the top of a blank worksheet ready for you to insert your data.
4.2 HELPFUL HINTS WHILE USING THE SPREADSHEET
Let’s start by examining some of the nuts and bolts of spreadsheets. A spreadsheet is a grid divided into rows and columns The intersection of a column and row is referred to as a cell (Fig. 4.4).
Cell A1 is in Column A, Row 1
Fig. 4.4 Think of the spreadsheet as a grid divided into rows and columns Right now, cell A1 is selected in the top left-hand corner of the spreadsheet.
With cell A1 selected, type the number 2014 in cell A1 and hit Enter so you have at least one item of data in the spreadsheet for what follows A cell is where information, in the form of either a label, or a number, or a formula for calculation, will be entered.  Labels are where you use text to describe the data in the columns and rows.  Numbers are just that—numbers, as in mathematics. Numbers are what spreadsheets are
all about.  Formulas (and Functions) are mathematical expressions built into certain cells that instruct
the spreadsheet to carry out calculations on specified sets of numbers in the rows and columns. As you go on with the tutorial, these concepts will become clearer to you.
112

Lesson 4: Introduction to the Calc Spreadsheet

Moving around in the spreadsheet There are tens of thousands of rows and columns in the Calc spreadsheet! That should be enough for any spreadsheet applications you might have in mind.
Usually you will use the mouse to select the cell you want to work in by clicking on the cell. Once you have entered data into a particular cell, you can use the commands in Table 4.1 to proceed to other related cells.

Key Pressed Tab Shift-Tab Arrow keys Enter Shift-Enter Scroll bars
Accept button Cancel button

Effect Moves selection to the right, to the next cell in the same row Moves selection to the left, to the previous cell in the same row Move selection one cell in any direction Moves selection down to the next cell in the same column Moves selection up to the previous cell in the same column Scroll vertically and horizontally through the spreadsheet
Accepts data in a cell but does not move to another cell Cancels data in a cell but does not move to another cell

Table 4.1 Cell selection commands
Try out each option now before proceeding—this will help you follow later directions.
Practice by moving around the spreadsheet—after you have visited several cells, end up by clicking on cell A1 to make it the current or active cell.

Identifying the active cell's coordinates Look in the top left hand corner of the Calc screen (Fig. 4.4 on the previous page) to see which cell (the active cell) you are in at any point in time.
Click on any cell now and look at the cell's coordinates in the top left corner of the spreadsheet window
Each cell (a location in the spreadsheet) has an address which begins with a letter to indicate the column, followed by a number to indicate the row. For example, G6 identifies the cell at the intersection of column G, row 6.
Click on cell G6 now and look at the cell's coordinates in the top left corner of the spreadsheet window

Selecting a range (group) of cells Table 4.2 lists the methods for selecting a group or range of cells.

Task

Method

To select a block of cells

Drag from the first cell to the last cell in the block

To select an entire row of cells Click on the number (the row label) on the left edge of

(horizontally from left to right)

the spreadsheet

To select an entire column of cells Click in the letter(s) of the alphabet (the column label) at

(vertically, from top to bottom) the top of the column

To select several rows

Drag the row numbers on the left edge of the spreadsheet

To select several columns

Drag across the column labels at the top of the columns

Table 4.2 Selecting a group (range) of cells

113

ESSENTIAL LibreOffice: Tutorials for Teachers Copyright © Bernard John Poole, 2014. All rights reserved
Once again, take a moment now to try each of these methods for selecting groups of cells
Location of the active (selected) cell after entering data into a cell When you type the data for a cell, the data appear both in the cell you have selected AND in the Entry bar at the top of the spreadsheet (Fig. 4.5).

Cancel the data

Accept the data

Data Entry bar
As you type data into a cell, the data appear both in the cell and in the Entry bar

Fig. 4.5 The spreadsheet Data Entry bar See how this works for yourself now.
Click on cell B1 and type the number 2014 but don’t hit Enter, then look at the Entry bar above the spreadsheet cells The data are again in both places at once, but they are not yet permanently accepted into the cell. The number 2014 awaits your acceptance of it into the spreadsheet. The data are displayed in the cell to give you the opportunity to: 1. check that what you typed is correct before accepting the data into the active (selected)
cell; 2. make up your mind whether the data should be entered into the spreadsheet at all; 3. decide which cell you would like to be the active cell next after the data have been copied
to the currently active cell. You accept the data by either:
 moving to another cell in the spreadsheet (by clicking on the Enter key or Tab key on the keyboard or by using the mouse to click on some other cell);
or,  clicking on the Accept ( ) icon to the left of the Entry bar.
You may decide not to enter the data into the spreadsheet. In this case you either hit the Del(ete) key in the Entry bar (see Fig. 4.5 above) and start over. If, on the other hand, the data are correct, you would hit the Enter key (which moves the active cell to the next cell down in the column, or click on the Accept button ( ), which keeps the cell you are working in as the active cell.
Click the Accept button ( ) now
114

Lesson 4: Introduction to the Calc Spreadsheet

Notice that the number 2014 in cell B1, as with the number 2014 in cell A1, is immediately right justified, aligned on the right edge of the cell, which is the correct justification for numbers in mathematics.
Alternatively, you may want to proceed to the cell immediately to the right of the active cell into which the number you just typed will go. Or you may want to proceed to the cell just below the active cell, or the cell just above the active cell, and so on.
A short while back you practiced moving around the spreadsheet using the commands listed in Table 4.1 on page 113. This table also lists the key(s) to press to tell Calc which cell to go to after you accept the data you have typed into the Entry bar. It might be a good idea, if you’re new to spreadsheets, to take a few minutes to check out that table again.
The cell in which you just typed 2014 (Cell B1) is still the active cell.

Press each of the keys or key combinations in Table 4.1 (on page 113) and check out the result of the action in the spreadsheet each time

Blanking out a cell or cells in the spreadsheet The quickest way to blank out a selected cell or cells is to hit the Del(ete) key on the keyboard. Let’s try this now.

Click on the first cell holding the data 2014 (cell A1) and hit the Del(ete) key

Now cell A1 is empty. The Delete key saves you having to use the mouse and menus.

Undo what you just did by pressing Ctrl+z (to put back 2014 in cell A1)

To delete the data in a group of cells you would drag across the cells to select them—they will become highlighted. Then you’d hit the Del(ete) key as before.

Practice this now by dragging across Cells A1 and B1 and hitting the Del(ete) key

Bingo. Remember that you can undo the Delete operation (or any other Edit operation) by immediately pressing Ctrl+z on the keyboard.

Editing the data in the Entry bar While you are typing in data, and before you hit Enter or click on the Accept button ( edit the data as if you are using a word processor.

), you can

Editing data after they have been entered into a cell If you are past the cell where you have an error and want to make a correction or change, move back to the cell in question by selecting (clicking on) it. The data in that cell will be displayed in the Entry bar at the top of the spreadsheet. Click in the Entry bar (the cursor will show where you clicked on the text) and then just go ahead and make any changes you want. Replace the old entry by clicking the Accept button ( ) or by selecting another cell in the spreadsheet.
At this point your Gradebook Template spreadsheet should be empty of data since you just deleted the entries in the first two cells (A1 and B1).

4.3 SETTING UP LABELS FOR YOUR GRADE BOOK TEMPLATE
When you have completed this section and the following section (Sections 4.3 and 4.4) your Grade book should look like Fig. 4.6 on the next page. The steps that follow will help you achieve this goal. Follow them carefully because when you are finished creating the Gradebook Template you

115

ESSENTIAL LibreOffice: Tutorials for Teachers Copyright © Bernard John Poole, 2014. All rights reserved
will have a working template to use as the basis for any gradebook you might like to use with your present or future classes.
Fig. 4.6 Gradebook Template Let’s start in Cell A2.
Select cell A2 and type GRADE REPORT Check the data in the Entry bar to make sure you typed the label correctly; correct any errors Click the Accept button ( ) when you are sure all is well (or press one of the other keys—arrow keys, Enter/Enter key, Tab key—which accept data into the spreadsheet) The data in Cell A2 is an example of a label—which is any text you use to describe the data that are in a spreadsheet. All the column and row headings are labels also. Let’s put some labels in a couple of other cells in Column A Select cell A4 and type the label Class: (note the colon), then press Enter to move the cell pointer to cell A5 Type the label Semester: and press Enter to move the cell pointer to cell A6 Next type the label Year: and click on the Accept button ( ) in the Entry bar At this point you should be thinking about saving the work you have completed to this point! Since you have already named the document (Gradebook Template), you can use a quick keyboard shortcut. Press Ctrl+s to save your work to this point Aligning the labels in spreadsheet cells The grade book will look best if the three labels you just entered into cells A4, A5, and A6 of the spreadsheet are right aligned in their respective cells. Right aligned means that the label is aligned to the right side of the cell. Unless you tell Calc otherwise, the system will left align any text
116

Lesson 4: Introduction to the Calc Spreadsheet
(letters of the alphabet, for example). Likewise, the system will right align any data that are made up of numbers. This makes sense if you look at Fig. 4.7.
Fig. 4.7 Text is left aligned; numbers are right aligned However, text sometimes looks best when it is right aligned in the cell. This is the case with the labels you just entered into cells A4, A5, and A6. Here is how you right align the text in these cells.
Position the spreadsheet cursor on cell A4 Hold down the mouse button and drag down to cell A6, so that all three cells are selected (cell A4 will still be selected even though it is not highlighted—it has the heavier border which also indicates selection in the spreadsheet) In the Formatting toolbar click on the Right Alignment button This will right align the cells that you have selected (A4 through A6). Check this on the screen before you go on. Press Ctrl+s to save your work so far (this is a good habit to get into!) Entering the column and row labels Now you will enter the labels for each of the columns that eventually will contain the scores for the assignments you might give your students during the course of a school session. Select cell D8 and enter the grade label A1 (short for Assignment 1) Check that you have typed the label correctly, then press the Tab key to move to the next cell across and enter the assignment label A2 Do the same thing three more times, using the labels A3, A4, and A5 (your last entry should be in cell H8) Click the Accept button ( ) next to the entry bar at the top of the screen These labels might represent homework or quizzes for a unit or for a whole semester, depending on how many assignments you might assess for grading purposes. When you use this Gradebook
117

Preparing to load PDF file. please wait...

0 of 0
100%
Lesson 4: Introduction to the Calc Spreadsheet