Application Topic
   >  Introduction to Spreadsheets
   >  Creating a Simple Spreadsheet
   >  Updating a Spreadsheet
   >  Displaying Data as Graphs

 

Creating Spreadsheets with OpenOffice

Creating a Spreadsheet using OpenOffice

If you need to create a spreadsheet, the application you need to use is the OpenOffice Spreadsheet module. On Ubuntu 10.x and below, this sits under the menu:

Applications → Office → OpenOffice.org Spreadsheet:

Open Spreadsheet

For Ubuntu Unity users, just click the LibreOffice Calc icon in the Launcher:

Unity Launcher LibreOffice Calc Icon

For Fedora users, search for "libre" in the Applications area:

Unity Launcher LibreOffice Calc Icon

Note: Puppy Linux ships with a very similar word spreadsheet called gnumeric, which is very similar in operation to LibreOffice

A new window will open up, in a grid pattern:

New Spreadsheet Screen

Cells in a spreadsheet are labelled by column (e.g. A) and row (e.g. 1). Click in the first cell (A1) and type “Expenditure”:

Add Text to Cell

Select the text you typed in by double clicking the text (or click-dragging the mouse over it). From the font size drop down list, pick “14” to enlarge the font on the heading text:

Format Text

You should then see the text size change; press CTRL+B to change it to bold text:

Bolden Text

Now, type in the following:

  • Hit the down arrow twice
  • Type “Electric” then hit the Tab key
  • Type “12.34” and hit ENTER
  • Type “Gas” then hit the Tab key
  • Type “30.12” and hit ENTER
  • Type “Water” then hit the Tab key
  • Type “10.00” and hit ENTER
  • Type “Telephone & Internet” then hit the Tab key
  • Type “20.00” and hit ENTER
  • Hit the down arrow
  • Type “Total”
  • Select the “Total” text and hit CTRL+B to change it to bold text

You should now have something that looks like the screenshot below:

Spreadsheet Content After Initial Setup

Click in cell B8 (-on the same line as the text “Total”), then click in the function f(x) box and type in the formula:

=sum(b3:b6)

Formulas always begin with the equals (=) sign. After the equals, we have the function name -in this case it's “sum”, which adds together all the cells specified within the parentheses that follow. Finally, we specify the range (:) of cells that we want to sum: here, it is all the values in the range B3 to B6 (-i.e. a subset of the column B):

Spreadsheet Formula Entry

So, what this is specifying is that in column B8, we want to place the sum of all values in the “B” column from row 3 to row 6. If you get this right, you should see the following in cell B8:

Spreadsheet Formula Value

Now, you may think that it takes longer to create the formula, than to manually add up the total, but the clever thing about using formulae is that if you change any of the dependant fields, the sum will be recalculated.

To test this, click in cell B3 and change the value to 14.34, then press ENTER; you should see that the total value has now changed from 52.58 to 54.58 :

Spreadsheet Formula Value Changed

The values given are all monetary, so let's make that explicit by changing their format. First select all the figures (-including the function value in B8) by click-dragging from B3 to B8. Release the mouse button and you should see the selected columns subtly shaded:

Spreadsheet Columns Selected

Right click in the shaded area: a dialogue box will open – choose “Format Cells” from this:

Spreadsheet Format Cells

Another dialogue will appear – choose “Currency” and the format desired, then click “OK”:

Spreadsheet Format Currency

If all went well, the spreadsheet should now look as follows:

Spreadsheet Formatted Currency

Let's say now, that we realised that we'd forgotten to add in our Television Licence (-in the UK all homes that own a Tv must have one); to add this, click on the row ID for row 6 (-shown as orange below):

Spreadsheet Select Row

With the row selected, type CTRL+ (plus sign) to insert a new row:

Spreadsheet Insert Row

Now type in the following:

  • Type “Tv Licence” followed by the Tab key

  • Type “11.71” followed by the ENTER key

If all went well, the screen should look as follows:

Spreadsheet New Row Text

Notice that the total has automatically adjusted itself.

Save your changes using the option:

File → Save As

SpreadsheetSave As

A dialogue will appear: enter the filename “expenses” and click “Save”:

Spreadsheet Specify Filename

Notice that the status bar has changed to indicate the new filename:

Spreadsheet Specify Filename

Now, let's close the file, by selecting:

File → Close

Spreadsheet Close


HomeSite IndexDesktop GuideServer GuideHints and TipsHardware CornerVideo SectionContact Us

 sitelock verified Firefox Download Button