Using multiple sheets
Contents
Why use multiple sheets?
Chapter 1 introduced the concept of multiple sheets in a spreadsheet. Multiple sheets help keep information organized; once you link those sheets together, you unleash the full power of Calc. Consider this case.
John is having trouble keeping track of his personal finances. He has several bank accounts and the information is scattered and disorganized. He can’t get a good grasp on his finances until he can see everything at once.
To resolve this, John decided to track his finances in OOo Calc. John knows Calc can do simple mathematical computations to help him keep a running tab of his accounts, and he wants to set up a summary sheet so that he can see all of his account balances at once.
This can be accomplished easily.
 |
For users with experience using Microsoft Excel, a Calc sheet is called either a sheet or worksheet in Excel. What Excel calls a workbook, Calc calls a spreadsheet (the whole document). |
Enabling Sheet tabs view
By default, OpenOffice has sheets enabled. However it is possible that an existing spreadsheet has tabs disabled, in which case opening that spreadsheet will disable sheet tabs. To re-enable it, go to Tools -> Options -> OpenOffice.org Calc (in the tree view) -> View, and ensure that 'Sheet tabs' checkbox is ticked in the Window subgroup.
Setting up multiple sheets
Chapter 1 gives a detailed explanation of how to set up multiple sheets in a spreadsheet. Here is a quick review.
Identifying sheets
When you open a new spreadsheet it has, by default, three sheets named Sheet1, Sheet2, and Sheet3. Sheets in Calc are managed using tabs at the bottom of the spreadsheet, as shown below.
Inserting new sheets
There are several ways to insert a new sheet. The first step, in all cases, is to select the sheet that will be next to the new sheet. Then do any of the following:
- Select Insert > Sheet from the menu bar, or
- Right-click on the tab and select Insert Sheet, or
- Click in an empty space at the end of the line of sheet tabs.
Each method opens the Insert Sheet dialog. Here you can choose to put the new sheet before or after the selected sheet and how many sheets to insert.
We need 6 sheets, one for each of the 5 accounts and one as a summary sheet so we will add 3 more. We also want to name each of these sheets for the account they represent: Summary, Checking Account, Savings Account, Credit Card 1, Credit Card 2, and Car Loan.
We have two choices: insert 3 new sheets and rename all 6 sheets afterwards; or rename the existing sheets, then insert the three new sheets 1 at a time, renaming each new sheet during the insert step.
To insert sheets and rename afterwards:
- In the Insert Sheet dialog, choose the position for the new sheets (in this example, we use After current sheet).
- Choose New sheet and 3 as the No. of sheets. (Three sheets are already provided by default.) Because you are inserting more than one sheet, the Name box is not available.
- Click OK to insert the sheets.
- For the next steps, go to “Renaming sheets” below.
To insert sheets and name them at the same time:
- Rename the existing sheets Summary, Checking Account, and Savings Account, as described in “Renaming sheets” below.
- In the Insert Sheet dialog, choose the position for the first new sheet.
- Choose New sheet and 1 as the No. of sheets. The Name box is now available.
- In the Name box, type a name for this new sheet, for example Credit Card 1.
- Click OK to insert the sheet.
- Repeat steps 1–4 for each new sheet, giving them the names Credit Card 2 and Car Loan.
On the Insert Sheet dialog, you can also add a sheet from a different spreadsheet file (for example, another Calc or Excel spreadsheet), by choosing the From file option. Click Browse and select the file; a list of the available sheets appears in the box. Select the sheet to import. If, after you select the file, no sheets appear you probably selected an invalid file type (not a spreadsheet, for example).
 |
For a shortcut to inserting a sheet from another file, choose Insert > Sheet from file from the menu bar. The Insert Sheet dialog opens with the From file option preselected, and then the Insert dialog opens on top of it. |
Inserting sheets from a different spreadsheet
If you prefer, select the Link option to insert the external sheet as a link instead as a copy. This is one of several ways to include “live” data from another spreadsheet. (See also Linking to external data.) The links can be updated manually to show the current contents of the external file; or, depending on the options you have selected in Tools > Options > OpenOffice.org Calc > General > Updating, whenever the file is opened.
Renaming sheets
Sheets can be renamed at any time. To give a sheet a more meaningful name:
- Enter the name in the name box when you create the sheet, or
- Double-click on the sheet tab, or
- Right-click on a sheet tab, select Rename Sheet from the pop-up menu and replace the existing name.
|
If you want to save the spreadsheet to Microsoft Excel format, the following characters are not allowed in sheet names: \ / ? * [ ] : and ' as the first or last character of the name. |
Your sheet tab area should now look like this.
Now we will set up the account ledgers. This is just a simple summary that includes the previous balance plus the amount of the current transaction. For withdrawals, we enter the current transaction as a negative number so the balance gets smaller. A basic ledger is shown below.
This ledger is set up in the sheet named Checking Account. The total balance is summed in cell F3. You can see the equation for it in the formula bar. It is the summary of the opening balance, cell C3, and all of the subsequent transactions.
| Content on this page is licensed under the Creative Common Attribution 3.0 license (CC-BY). |
