Difference between revisions of "Documentation/FAQ/Calc"

From Apache OpenOffice Wiki
Jump to: navigation, search
(→‎How do I make a wide title cell extend across several columns in my spreadsheet?)
(categorized and cleaned the Calc FAQ)
Line 1: Line 1:
 
<div style="padding: 5px; background-color: #EFEFEF; border: 1px solid #999; margin-bottom: 10px;">
 
<div style="padding: 5px; background-color: #EFEFEF; border: 1px solid #999; margin-bottom: 10px;">
−
Apr, 16 2007: Currently being edited by [mailto:fpe@sun.com Frank], please refrain from changing.
+
Note: Answers apply to all versions of OpenOffice.org unless stated otherwise.
 
</div>
 
</div>
 +
  
 
<!-- MARK -->
 
<!-- MARK -->
Line 8: Line 9:
  
 
===How do I insert superscript or subscript text in my spreadsheet?===
 
===How do I insert superscript or subscript text in my spreadsheet?===
−
{{WWAll}}
 
  
 
'''Using the Menu'''
 
'''Using the Menu'''
Line 29: Line 29:
 
* Cell height may require an adjustment to accommodate the new character.
 
* Cell height may require an adjustment to accommodate the new character.
 
* These commands also work for the word processor.
 
* These commands also work for the word processor.
−
 
−
 
−
 
−
 
−
===How can I wrap text within a cell?===
 
−
{{WWAll}}
 
−
 
−
To wrap text within a cell, or merged set of cells:
 
−
 
−
*Select a cell or group of cells.
 
−
*Right-click the selected area and go to '''Format cells...''', or in the pull-down menus, go to '''Format &gt; Cells...'''
 
−
*Click on the '''Alignment''' tab.
 
−
*Under the Properties section, tick '''Automatic line break'''.
 
−
*Click '''OK'''.
 
−
 
−
Now the contents of the cell will be wrapped to fit the cell.
 
  
  
Line 50: Line 34:
  
 
===How can I use a dot (.) as decimal sign instead of a comma?===
 
===How can I use a dot (.) as decimal sign instead of a comma?===
−
{{WWAll}}
 
  
 
Some languages, like French, Italian, or German, use a comma as  
 
Some languages, like French, Italian, or German, use a comma as  
Line 77: Line 60:
  
 
===How do I use styles and formatting?===
 
===How do I use styles and formatting?===
−
{{WWAll}}
 
  
 
See [http://documentation.openoffice.org/HOW_TO/spreadsheet/calc4_EN.html How to Format a Spreadsheet].
 
See [http://documentation.openoffice.org/HOW_TO/spreadsheet/calc4_EN.html How to Format a Spreadsheet].
Line 85: Line 67:
  
 
===How can I create my own sort lists?===
 
===How can I create my own sort lists?===
−
{{WWAll}}
 
  
 
Sometimes it is useful to use sort lists to control the order of
 
Sometimes it is useful to use sort lists to control the order of
Line 112: Line 93:
  
 
===How do I add additional strings or characters to cell contents?===
 
===How do I add additional strings or characters to cell contents?===
−
{{WWAll}}
 
  
 
Use the '''CONCATENATE''' function to add strings or characters to cell contents:
 
Use the '''CONCATENATE''' function to add strings or characters to cell contents:
Line 131: Line 111:
  
 
===How are notes within cells displayed?===
 
===How are notes within cells displayed?===
−
{{WWAll}}
 
  
 
'''To create a Note for a cell'''
 
'''To create a Note for a cell'''
Line 193: Line 172:
  
 
===How do I wrap text within a cell in my spreadsheet?===
 
===How do I wrap text within a cell in my spreadsheet?===
−
{{WWAll}}
 
  
 
To wrap text within a cell, or merged set of cells:  
 
To wrap text within a cell, or merged set of cells:  
Line 209: Line 187:
  
 
===How do I disable the capitalized letter at the beginning of cells in Calc?===
 
===How do I disable the capitalized letter at the beginning of cells in Calc?===
−
{{WWAll}}
 
  
 
# Select '''Tools - AutoCorrect''' from the main menu
 
# Select '''Tools - AutoCorrect''' from the main menu
Line 219: Line 196:
  
 
===How do I make a wide title cell extend across several columns in my spreadsheet?===
 
===How do I make a wide title cell extend across several columns in my spreadsheet?===
−
{{WWAll}}
 
  
  
 
# Select the cells in which the title is to appear.<br>Do this by clicking in the first cell, then hold down the mouse button and drag the selection box across the last cell of your chosen area.
 
# Select the cells in which the title is to appear.<br>Do this by clicking in the first cell, then hold down the mouse button and drag the selection box across the last cell of your chosen area.
 
# Select '''Format - Merge Cells - Merge Cells''' (OpenOffice.org 1.1.x: Format - Merge Cells - Define) or '''Format - Merge Cells - Merge and Center Cells'''
 
# Select '''Format - Merge Cells - Merge Cells''' (OpenOffice.org 1.1.x: Format - Merge Cells - Define) or '''Format - Merge Cells - Merge and Center Cells'''
 +
 +
 +
 +
 +
===How do I rotate a column title so that it fits above my very narrow column?===
 +
There are a few options. Follow these instructions:
 +
 +
# Select the cells where you wish to rotate the text.
 +
# Select '''Format - Cells...''' from the main menu
 +
# Click on the '''Alignment''' tab
 +
# In the '''Text Orientation''' section (OpenOffice.org 1.1.x: Text Direction) drag the dot on the
 +
circle to the bottom of the circle (or enter '''270''' in the Degrees box).
 +
This will rotate the text in the selected cell by 270 degrees.
 +
# Click '''OK''' to see the effect. Experiment with placement of the dot to learn more.
 +
 +
 +
 +
 +
===Is there a way to add times that total greater than 24 hrs?===
 +
 +
Yes. Select a format code with the hour symbols in square brackets, like '''[HH]:MM'''.
 +
 +
# Select the cell you want to apply the format to
 +
# Select '''Format - Cells''' from the main menu
 +
# Select the '''Numbers''' tab
 +
# Set the Category to '''Time'''
 +
# Select one of the formats with hours > 24, for example '''[HH]:MM:SS'''<br>you can also select the '''Format Code''' line and enter the format yourself
 +
# Click '''OK'''.
 +
 +
Now when you add the times from cells with this format together, you will get the true sum
 +
of hours and minutes. Otherwise, the sum will reset to zero each time 24 hours is reached.
 +
 +
 +
 +
 +
===Some of the rows or columns in my spreadsheet are hidden. How do I see all rows or columns?===
 +
 +
# Select the area of the spreadsheet where rows or columns are hidden. To select the entire spreadsheet,
 +
select '''Edit - Select All''' from the main menu or press '''CTRL'''+'''A'''
 +
# To show all rows, select '''Format - Row - Show''' from the main menu<br>
 +
To show all columns, select '''Format - Column - Show''' from the main menu
 +
 +
 +
 +
 +
===How do I format cells in Calc so that the rows number automatically?===
 +
 +
# Enter the formula '''=row()''' into cell where the row numbering will start
 +
# Extend the cell by dragging the bottom right corner all the way down to the cell you want the numbering to end
 +
 +
'''Note:'''
 +
* If you insert new rows, you will have to copy the formula to the new cells.
 +
 +
 +
 +
 +
===How are thick lines or borders created around my cells?===
 +
 +
# Select the cells where you wish to apply a border.
 +
# Select '''Format - Cells...''' from the main menu
 +
# Click on the '''Borders''' tab
 +
# In the '''Line Arrangement''' section, under '''Default''', click on the icon that
 +
best shows the style of border you wish to use, or<br> Under '''User Defined''',
 +
select custom border styles by clicking on the individual lines shown bordering four 'dummy' cells.
 +
 +
To change the width and color of a border:
 +
 +
* In the '''Line''' section, under '''Style''', click on the desired line for your border, and
 +
* Under '''Color''', choose the desired color for your border.
 +
 +
 +
 +
 +
===How do I insert a page number in the form of 'Page 1 of N' on each page of a spreadsheet?===
 +
 +
Page numbers can be inserted into the header or footer sections of a spreadsheet.
 +
These will be visible only in the Page Preview mode and on the print. The page numbers
 +
will not be visible on, nor are they related to, the calc sheets themselves.
 +
The page numbers reflect the defined print areas of the workbook.
 +
 +
To insert page numbers:
 +
 +
# Select '''Edit - Headers &amp; Footers...''' from the main menu
 +
# Depending on where you want the page number to appear, select either the '''Header''' or the '''Footer''' tab
 +
# Click in the area where you want the page number to be displayed (Left, Center, or Right) to place the cursor inside the box.
 +
# Type "Page ", then add one space character.<br>
 +
# The available data fields are represented by a row of icons below the input areas,<br>
 +
Click on the document icon with ONE number sign [ # ] to insert the page number placeholder.
 +
# Type " of "
 +
# Click on the document icon with TWO number signs [ ## ] to insert the page total placeholder
 +
# Click '''OK'''
 +
 +
'''Note:'''
 +
* The data fields available in the header and footer settings are the only data fields and formats available in spreadsheets.
  
  
Line 263: Line 333:
  
 
===How do I open a tab-delimited file in OpenOffice.org Spreadsheet? What if I have a different type of delimiter?===
 
===How do I open a tab-delimited file in OpenOffice.org Spreadsheet? What if I have a different type of delimiter?===
−
{{WWAll}}
 
  
 
First, if your ASCII file is not already named with a '''.txt''' extension, rename it that way.
 
First, if your ASCII file is not already named with a '''.txt''' extension, rename it that way.
Line 308: Line 377:
 
{{SeeAlso}}
 
{{SeeAlso}}
 
* [[#How_do_I_open_a_tab-delimited_file_in_OpenOffice.org_Spreadsheet._What_if_I_have_a_different_type_of_delimiter.]]
 
* [[#How_do_I_open_a_tab-delimited_file_in_OpenOffice.org_Spreadsheet._What_if_I_have_a_different_type_of_delimiter.]]
 +
 +
 +
 +
===A large Calc spreadsheet was converted from another application. Some of my rows are missing! What happened?===
 +
 +
OpenOffice.org spreadsheets supports a maximum of 65,536 rows (32,000 in releases 1.1.x).
 +
Spreadsheets converted from other applications that contain more than 65,536 rows will be truncated.
 +
 +
'''Notes:'''
 +
* Split large spreadsheets from other applications into smaller worksheets, so that each has fewer
 +
than 65,536 rows prior to converting them. A range containing fewer than 65,536 rows will convert
 +
correctly in OpenOffice.org 2.x
  
  
Line 344: Line 425:
 
<!-- MARK -->
 
<!-- MARK -->
 
==Saving and Exporting Files==
 
==Saving and Exporting Files==
 +
 +
===How do I output my spreadsheet data as an ASCII, delimited text file?===
 +
 +
# Select: '''File - Save As...''' from the main menu
 +
# In the '''Save as''' dialog that appears, select File type '''Text CSV (.csv; .txt)''' from the list of
 +
spreadsheet file types.<br>Press '''T''' several times in the list to quickly jump to that entry.
 +
# Click to enable the box next to '''Edit filter settings'''.
 +
# Click '''Save'''.
 +
# In the '''Export of text files''' dialog box that pops up, enter the field and text delimiters of your choice.<br>
 +
The '''Text delimiter''' is the character that will surround any text entries in your spreadsheet, to keep
 +
each phrase together as an entity, when the file is exported into ASCII format.
 +
 +
 +
  
 
<!-- MARK -->
 
<!-- MARK -->
Line 349: Line 444:
  
 
===Why does Calc print out all the sheets in the file?===
 
===Why does Calc print out all the sheets in the file?===
−
{{WWAll}}
 
  
  
Line 368: Line 462:
  
 
===How can I print some, but not all, of the cells on a sheet?===
 
===How can I print some, but not all, of the cells on a sheet?===
−
{{WWAll}}
 
  
 
# Select the cell(s) you want to print  
 
# Select the cell(s) you want to print  
Line 382: Line 475:
  
 
===How do I get Sheet1 to print as portrait and Sheet2 to print as landscape?===
 
===How do I get Sheet1 to print as portrait and Sheet2 to print as landscape?===
−
{{WWAll}}
 
  
 
You can assign a different page style to each sheet. To print out different page orientations,
 
You can assign a different page style to each sheet. To print out different page orientations,
Line 401: Line 493:
  
 
===How I do select a row (or rows) in my spreadsheet to repeat on every page when printed?===
 
===How I do select a row (or rows) in my spreadsheet to repeat on every page when printed?===
−
{{WWAll}}
 
  
  
Line 436: Line 527:
 
*Make adjustments to the print settings, then preview again. Repeat until the print displays in the desired format.
 
*Make adjustments to the print settings, then preview again. Repeat until the print displays in the desired format.
  
−
'''''Note:''''' This command will allow you to see the current page number assignments for the data that you want to print. This is especially useful when only a portion of the spreadsheet is to be printed, or when several print ranges are associated with one Calc file.
+
'''''Note:''' This command will allow you to see the current page number assignments for the data that you want to print. This is especially useful when only a portion of the spreadsheet is to be printed, or when several print ranges are associated with one Calc file.
 
'''Formatting the page:'''
 
'''Formatting the page:'''
  
Line 454: Line 545:
 
*To toggle this view off, select '''View &gt; Page Break Preview'''.
 
*To toggle this view off, select '''View &gt; Page Break Preview'''.
  
−
'''''Note:''''' If everything appears gray, this means that no print ranges are defined. If a print range is defined, the printing area will show up with a white background with a blue outline at the page boundary. A gray 'Page N', where 'N' is the page sequence number ''for the sheet'', will appear in the middle of the print area. If no range is defined for printing, follow the instructions
+
'''''Note:''' If everything appears gray, this means that no print ranges are defined. If a print range is defined, the printing area will show up with a white background with a blue outline at the page boundary. A gray 'Page N', where 'N' is the page sequence number ''for the sheet'', will appear in the middle of the print area. If no range is defined for printing, follow the instructions
 
below.
 
below.
  
Line 487: Line 578:
 
*Click '''OK'''.
 
*Click '''OK'''.
  
−
'''''Note:''''' Printing a selected range of data is also controlled by print scaling. If the data selected does not fit on the desired number of pages, adjust the Scale using either of the scaling procedures above (scale by percentage or by number of pages allowed).
+
'''''Note:''' Printing a selected range of data is also controlled by print scaling. If the data selected does not fit on the desired number of pages, adjust the Scale using either of the scaling procedures above (scale by percentage or by number of pages allowed).
  
 
'''Editing the print range:'''
 
'''Editing the print range:'''
Line 513: Line 604:
  
 
===How do I extend the range of data shown in my chart after I add new data to my spreadsheet?===
 
===How do I extend the range of data shown in my chart after I add new data to my spreadsheet?===
−
{{WWAll}}
 
  
 
* Select a new range and drag the selection onto the chart.  
 
* Select a new range and drag the selection onto the chart.  
  
 
* You can also select the chart, right-click, and then choose '''Modify Data Range'''.
 
* You can also select the chart, right-click, and then choose '''Modify Data Range'''.
 +
 +
 +
 +
===How do I create a chart in a spreadsheet that is automatically updated when the data is changed?===
 +
 +
When you create a chart based on data that is present in the same spreadsheet, the chart will
 +
automatically be updated whenever the data in the spreadsheet changes.
  
  
Line 526: Line 623:
  
 
===Why does deleting filtered rows remove other rows too?===
 
===Why does deleting filtered rows remove other rows too?===
−
{{WWAll}}
 
  
 
When using Calc filters, selections that span a range of cells
 
When using Calc filters, selections that span a range of cells
Line 540: Line 636:
  
 
===What do each of the error codes (Err:NNN) in OpenOffice.org spreadsheets mean?===
 
===What do each of the error codes (Err:NNN) in OpenOffice.org spreadsheets mean?===
−
{{WWAll}}
 
  
 
See [[FAQ:Calc_Error_Codes|here]] for a list of error codes
 
See [[FAQ:Calc_Error_Codes|here]] for a list of error codes
Line 555: Line 650:
  
 
===Why do I see the formula text and not the result of the formula?===
 
===Why do I see the formula text and not the result of the formula?===
−
{{WWAll}}
 
  
 
* Make sure that all formulas will start with the equal sign (=)
 
* Make sure that all formulas will start with the equal sign (=)
Line 576: Line 670:
  
 
===I see '###' in a cell that formerly held data. How can I see my data again?===
 
===I see '###' in a cell that formerly held data. How can I see my data again?===
−
{{WWAll}}
 
  
 
When the content of a cell is too long to be displayed in a single cell, it will  
 
When the content of a cell is too long to be displayed in a single cell, it will  
Line 593: Line 686:
 
==Miscellaneous==
 
==Miscellaneous==
  
−
===What's the maximum number of rows for a spreadsheet file?===
+
===What's the maximum number of rows and cells for a spreadsheet file?===
 
{{WW11x}}
 
{{WW11x}}
 
The OpenOffice.org 1.1.x versions were able to handle a maximum of 32,000 rows.
 
The OpenOffice.org 1.1.x versions were able to handle a maximum of 32,000 rows.
Line 600: Line 693:
 
{{WW2x}}
 
{{WW2x}}
 
The limitations of the OpenOffice.org 2.x Calc versions are
 
The limitations of the OpenOffice.org 2.x Calc versions are
−
* maximum number of rows: 65,535
+
* maximum number of rows: 65,536
 
* maximum number of columns: 256
 
* maximum number of columns: 256
 +
* maximum number of cells per sheet: 16,777,216
 
* maximum number of sheets: 256
 
* maximum number of sheets: 256
 +
* maximum number of cells per file: 4,294,967,296
  
 
'''Notes:'''
 
'''Notes:'''
−
* When referencing to external documents in formulas or when creating sceanrios, hidden helper sheets are created that reduce the available number of sheets.
+
* When referencing to external documents in formulas or when creating sceanrios,  
 +
hidden helper sheets are created that reduce the available number of sheets and consequently cells.
  
  
Line 611: Line 707:
  
 
===How can I start OpenOffice.org Calc instead of Writer?===
 
===How can I start OpenOffice.org Calc instead of Writer?===
−
{{WWAll}}
 
  
 
Pass '''-calc''' as an argument on the command line when executing openoffice.org to start OpenOffice.org Calc:
 
Pass '''-calc''' as an argument on the command line when executing openoffice.org to start OpenOffice.org Calc:
 
  ooffice -calc
 
  ooffice -calc
  
−
 
−
 
−
 
−
===Is there a way to add times that total greater than 24 hrs?===
 
−
{{WWAll}}
 
−
 
−
Yes. Select a format code with the hour symbols in square brackets, like '''[HH]:MM'''.
 
−
 
−
# Select the cell you want to apply the format to
 
−
# Select '''Format - Cells''' from the main menu
 
−
# Select the '''Numbers''' tab
 
−
# Set the Category to '''Time'''
 
−
# Select one of the formats with hours > 24, for example '''[HH]:MM:SS'''<br>you can also select the '''Format Code''' line and enter the format yourself
 
−
# Click '''OK'''.
 
−
 
−
Now when you add the times from cells with this format together, you will get the true sum
 
−
of hours and minutes. Otherwise, the sum will reset to zero each time 24 hours is reached.
 
  
  
Line 638: Line 716:
  
 
===How can I see the row and column captions as I scroll through a sheet that is larger than my display?===
 
===How can I see the row and column captions as I scroll through a sheet that is larger than my display?===
−
{{WWAll}}
 
  
 
There are two ways to obtain this result:
 
There are two ways to obtain this result:
Line 675: Line 752:
  
  
−
===Where can I find more documentation for Calc?===
+
===How do I protect cells in my spreadsheet?===
  
−
Internal OpenOffice.org resources:
+
Cell protection is active for all cells by default. If only certain cells are to be protected, this setting must be turned off.
−
* [http://documentation.openoffice.org/HOW_TO/index.html OOo Documentation Project How-Tos]
+
−
* [http://documentation.openoffice.org/manuals/index.html OOo Documentation Project Manuals]
+
  
−
External resources:
+
To '''exclude''' cells from the protection:
−
* [http://www.ooodocs.org/modules.php?name=Content&amp;pa=showpage&amp;pid=3  OOoDoc.Org How-Tos]
+
# Select the cells to be excluded from protection<br>Hold down the '''Ctrl''' key while
−
* [http://www.tutorialsforopenoffice.org/category_index/spreadsheet.html Calc Tutorials for OpenOffice]
+
clicking on non-adjacent cells to highlight the ones that are to be protected.<br>If you only want to
−
* [http://www.digitaldistribution.com/samples/calcfirststeps/ First Steps with Calc]
+
protect a small number of cells, it may be easier to clear the protection for all
 +
cells and the re-activate protection for the appropriate cells. For this, select the entire
 +
spreadsheet: press '''CTRL'''+'''A''' or click on the little gray box above row 1 and to
 +
the left of column A.
 +
# Select '''Format - Cells''' from the main menu
 +
# Click on the '''Cell Protection''' tab
 +
# Clear the check mark for the '''Protected''' option
 +
# Click '''OK'''
  
−
Internal OpenOffice.org mail lists where you can find information about Calc:
 
−
* [http://www.openoffice.org/servlets/SummarizeList?listName=users OOo General Users List Archives]
 
−
* [http://sc.openoffice.org/servlets/SummarizeList?listName=users OOo Spreadsheet Project User List Archives]
 
  
−
External mail lists and forums where you can find information about Calc:
+
Initially however, the protection is not '''activated'''. To activate the protection:
−
* [http://www.ooodocs.org/modules.php?name=Forums&amp;file=viewforum&amp;f=3&amp;sid=c36b0ed8a9e6354fc7d3a326b0c06628 OOoDocs Calc General Forum]
+
* Select '''Tools - Protect Document - Sheet''' to protect the current sheet only
−
* [http://www.oooforum.org/forum/viewforum.php?f=3 OOoForum::View Forum::OpenOffice.org Calc]
+
* Select '''Tools - Protect Document - Document''' to protect all sheets in the current document
  
−
Please write to let us know if there are other Calc related resources that should be shown in this list.
 
  
−
<!--
 
−
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
 
−
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
 
−
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
 
−
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
 
−
-->
 
  
 +
===How can I use cells from different Calc files?===
  
 +
# Open the source file
 +
# Select the cells and press '''CTRL'''+'''C''' to copy them to the clipboard
 +
# Open the target file
 +
# Select a target cell and select '''Edit - Paste Special''' from the main menu
 +
# Check the '''Link''' box in the '''Options''' section
  
 +
The inserted data are now linked to the original document. Whenever the
 +
data changes in the source document this will be reflected in the
 +
target document.
  
  
  
  
 +
===How I change the order of sheets in my spreadsheet?===
  
 +
You can move a sheet to a different position, click and hold the sheet tab at the bottom
 +
of the screen with the mouse and drag it to its new position.
  
 +
You can also move sheets (even across different documents) using the menu:
 +
# Right-click the sheet you want to move and select '''Move/Copy Sheet...''' from the pop-up menu, or<br>
 +
select '''Edit - Sheet - Move/Copy''' from the main menu
 +
# Specify the new position of the sheet in the dialog.<br>You can even move the sheet to a different document that is opened in Calc
 +
# Click '''OK'''
  
 +
'''Note:'''
 +
* If you check the '''Copy''' option in the dialog, the sheet will copied, not moved.
  
  
−
===How do I rotate a column title so that it fits above my very narrow column?===
 
−
There are a few options. Follow these instructions:
 
  
−
*Select the cells where you wish to rotate the text.
 
−
*From the pull down menu, go to '''Format &gt; Cells...'''
 
−
*In the dialog that appears, click on the '''Alignment''' tab.
 
  
−
In the Text Direction section of the '''Alignment''' box, try these options:
+
===How can I rename a sheet?===
  
−
*Click on the button labeled '''ABCD'''. (Letters on the button are arranged from top to bottom.) This box will arrange your title with the first character at the top of the cell, the second character underneath it, as shown in the label on the button. Click '''OK''' to see the effect.
+
* Right-click the sheet you wish to rename and select '''Rename Sheet''' from the popop menu, or
−
*Alternatively, drag the little dot on the circle to the bottom of the circle (or enter '''270''' in the Degrees box). This will rotate the text in the selected cell by 270 degrees. Click '''OK''' to see the effect. Experiment with placement of the dot to learn more.
+
* Select '''Format - Sheet - Rename''' from the main menu
  
−
===How do I create a chart in a spreadsheet that is automatically updated when the data is changed?===
+
'''Note:'''
−
Follow these steps:
+
* You can only use letters, numbers, spaces, and the underline character in the sheet name.
  
−
*From the pull down menus, select: '''Tools &gt; Options'''.
 
−
*In the left column of the dialog that appears, select: '''Spreadsheet &gt; General'''.
 
−
*On the page that appears, in the Updating section under '''Update links when opening''', click the checkbox next to '''Always'''.
 
  
−
'''Note:''' This is a global setting and will affect all spreadsheet charts and linked information from spreadsheets. If this is not desirable, click the checkbox next to '''On request'''. With this
 
−
selected, a dialog box will pop up when each file with links to a spreadsheet is opened, where you can choose whether or not to update the item with newer information from the linked spreadsheet.
 
  
−
{{SeeAlso}}
 
−
[http://052.html How do I extend the range of data shown in my chart after I add new data to my spreadsheet?]
 
−
 
−
===How do I protect cells in my spreadsheet?===
 
−
Cell protection is active for all cells by default. If only certain cells are to be protected, this setting must be turned off.
 
−
 
−
*Select the entire spreadsheet. (A quick way is to click the little gray box above row 1 and to the left of column A.)
 
−
*From the pull-down menus, select '''Format &gt; Cells'''.
 
−
*In the dialog that appears, select the '''Cell Protection''' tab.
 
−
*Uncheck the box next to '''Protected''' to remove the protection on all cells.
 
−
 
−
To select a range of cells to protect, or to select non-adjacent cells to protect:
 
−
 
−
*Hold down the '''Ctrl''' key while clicking on the non-adjacent cells to highlight the ones that are to be protected.
 
−
*When all of the desired cells are selected, in the pull-down menus, go to '''Format &gt; Cells...'''
 
−
*In the dialog that appears, select the '''Cell Protection''' tab.
 
−
*Click on the box next to '''Protect'''.
 
−
*Click '''OK'''.
 
−
 
−
Once the cells are marked for protection, the protection option must be enabled at the sheet or spreadsheet level:
 
−
 
−
*From the pull-down menus, select '''Tools &gt; Protect Document &gt; Sheet''' or '''Tools &gt; Protect Document &gt; Document'''.
 
−
*In the dialog that appears, provide a password , then confirm it by typing it again.
 
−
*Click '''OK'''.
 
−
*The protected cells will now be uneditable.
 
−
 
−
===How do I output my spreadsheet data as an ASCII, delimited text file?===
 
−
Follow these steps:
 
−
 
−
*From the pull down menus, select: '''File &gt; Save As...'''
 
−
*In the '''Save as''' dialog that appears, select File type '''Text CSV (.csv; .txt)''' from the list of spreadsheet file types.
 
−
*Click to enable the box next to '''Edit filter settings'''.
 
−
*Click '''Save'''.
 
−
*In the '''Export of text files''' dialog box that pops up, enter the field and text delimiters of your choice. The default '''Field delimiter''' is '''[Tab]'''. The '''Text delimiter''' is the character that will surround any text entries in your spreadsheet, to keep each phrase together as an entity, when the file is exported into ASCII format.
 
−
 
−
===Some of the rows in my spreadsheet are hidden. How do I see all the rows?===
 
−
The '''Show''' command will cause all rows or columns in a selected area to be visible on the display.
 
−
 
−
*Select the area of the spreadsheet where rows are hidden. To select the entire spreadsheet, go to '''Edit &gt; Select All''' in the pull-down menus, or use the '''Control-A''' keystroke
 
−
combination.
 
−
*To show all rows, select: '''Format &gt; Row &gt; Show'''.
 
−
*Similarly, to show all columns, select: '''Format &gt; Column &gt; Show'''.
 
−
*After the area is selected, '''Show''' also appears in the right-click menu, if only columns or rows are hidden.
 
−
 
−
===How do I format cells in OpenOffice.org Spreadsheet so that the rows number automatically?===
 
−
One method (this example assumes that you wish to put your row numbers in column A):
 
−
 
−
*Enter the formula '''=row(a1)''' in cell A1.
 
−
*Extend the cell by dragging the bottom right corner all the way down column A.
 
−
 
−
'''Note:''' If you add or insert new rows, you will have to copy the formula to the new cells.
 
−
 
−
 
−
===How can I use cells from different Calc files?===
 
−
You can open the source file, select and copy onto the clipboard
 
−
(CTRL + C) the desired cells and then in the target file you should
 
−
select a target cell and choose '''Edit &gt; Paste Special''' from
 
−
the Menu Bar and enable the link option in the choice shown in the
 
−
dialog box that opens.
 
−
A more direct way to do this is to write a formula of the following
 
−
kind in the formula bar or in a target cell: <br>
 
−
'''=DDE(soffice; YourFilePath; SourceSheetName.SourceCellName)'''
 
−
''' Example:''' =DDE(soffice;c:\myfile.sxi;MySheet.A1)
 
−
 
−
===How I change the order of sheets in my spreadsheet?===
 
−
The simplest method to move a sheet is to grab its tab with the
 
−
mouse, click on the tab with the mouse and hold the mouse button down,
 
−
then drag the tab to its new position amongst the other tabs in the
 
−
workbook. There are other methods to move (or copy) sheets too:
 
−
 
−
*Select the sheet you want to move.
 
−
*While still positioned over the sheet tab, click the right mouse
 
−
button to access the context menu, then choose '''Move/Copy Sheet...''',
 
−
or in the pull-down menus go to '''Edit &gt; Sheet &gt; Move/Copy...'''
 
−
*In the Move/Copy dialog that appears, be sure that the check box
 
−
labeled '''Copy''' is not set unintentionally. Place a tick at '''Copy'''
 
−
only if you wish to copy the sheet instead of move it.
 
−
*Specify where to move the sheet in the Move/Copy pop-up box.
 
−
Select an open document from the drop down menu labeled '''To document'''.
 
−
Choose the position of the sheet in that document from the drop-down
 
−
menu labeled '''Insert before'''.
 
−
*Click '''OK'''.
 
−
 
−
===How can I rename a sheet?===
 
−
 
−
*Select the sheet to be renamed.
 
−
*While still positioned over the sheet tab, click the right mouse button to access the context menu, then choose '''Rename Sheet...'''.
 
−
*Enter the new name.
 
−
*Click '''OK'''.
 
  
 
===How is a variable date inserted into a spreadsheet cell?===
 
===How is a variable date inserted into a spreadsheet cell?===
−
Insert a function into a cell by following these instructions:
 
  
−
*Select the cell that will hold the function.
+
# Select the cell that will hold the function.
−
*From the pull-down menus, select '''Insert &gt; Function'''.
+
# Enter '''=TODAY()''' for the current date or '''=NOW()''' for the current date and time.<br>
−
*Pick '''Date&amp;Time''' as the '''Category'''.
+
The values will be dynamically updated when the file is reloaded.  
−
*Double-click the'''Today''' function for the current date, or double-click the '''Now''' function for the current date and time.
+
−
*Click '''OK'''. The cell should now contain the selected function.
+
  
−
===How are thick lines or borders created around my cells?===
 
−
Creating a border:
 
  
−
*Select the cells where you wish to apply a border.
 
−
*From the pull-down menus, select '''Format &gt; Cells...''' then click on the '''Borders''' tab.
 
−
*In the '''Line Arrangement''' section, under '''Default''', click on the icon that best shows the style of border you wish to use, or
 
−
*Under '''User Defined''', select custom border styles by clicking on the individual lines shown bordering four 'dummy' cells.
 
  
−
Changing the width of a border:
 
  
−
*In the '''Line''' section, under '''Style''', click on the desired line for your border, and
+
===Where can I find more documentation for Calc?===
−
*Under '''Color''', choose the desired color for your border.
+
  
 +
Internal OpenOffice.org resources:
 +
* [http://documentation.openoffice.org/HOW_TO/index.html OOo Documentation Project How-Tos]
 +
* [http://documentation.openoffice.org/manuals/index.html OOo Documentation Project Manuals]
  
−
===How do I insert a page number in the form of 'Page 1 of N' on each page of a spreadsheet?===
+
External resources:
 +
* [http://www.ooodocs.org/modules.php?name=Content&amp;pa=showpage&amp;pid=3  OOoDoc.Org How-Tos]
 +
* [http://www.tutorialsforopenoffice.org/category_index/spreadsheet.html Calc Tutorials for OpenOffice]
 +
* [http://www.digitaldistribution.com/samples/calcfirststeps/ First Steps with Calc]
  
−
Page numbers can be inserted into the header or footer sections of a
+
Internal OpenOffice.org mail lists where you can find information about Calc:
−
spreadsheet. These will be visible only in the Page Preview mode and on
+
* [http://www.openoffice.org/servlets/SummarizeList?listName=users OOo General Users List Archives]
−
the print. The page numbers will not be visible on, nor are they
+
* [http://sc.openoffice.org/servlets/SummarizeList?listName=users OOo Spreadsheet Project User List Archives]
−
related to, the calc sheets themselves. The page numbers reflect the
+
−
defined print areas of the workbook. To insert page numbers, follow
+
−
these instructions:
+
  
−
*From the pull-down menus, select '''Edit &gt; Headers &amp; Footers...'''
+
External mail lists and forums where you can find information about Calc:
−
*Select either the Header tab or the Footer tab.
+
* [http://www.ooodocs.org/modules.php?name=Forums&amp;file=viewforum&amp;f=3&amp;sid=c36b0ed8a9e6354fc7d3a326b0c06628 OOoDocs Calc General Forum]
−
*Click in the area where the page number blurb will display (Left, Center, or Right) to place the cursor inside the box.
+
* [http://www.oooforum.org/forum/viewforum.php?f=3 OOoForum::View Forum::OpenOffice.org Calc]
−
*Type '''Page''', then add one space character.
+
−
*The available data fields are represented by a row of icons below the left, center, and right input areas. Click on the document icon with ONE number sign [ # ] to insert the page number field.
+
−
*Type a space, '''of''', and another space.
+
−
*Click on the document icon with TWO number signs [ ## ] to insert the page total field.
+
−
*Click '''OK'''.
+
  
−
'''Note:''' The data fields available int the header and footer
+
Please write to let us know if there are other Calc related resources that should be shown in this list.
−
settings are the only data fields and formats available in spreadsheets.
+
−
 
+
−
===A large OpenOffice.org spreadsheet was converted from another application. Some of my rows are missing! What happened?===
+
−
 
+
−
OpenOffice.org spreadsheets supports 65536 rows (32000 in releases 1.x), maximum.
+
−
Spreadsheets converted from other applications that contain more than
+
−
65536 rows will be truncated.
+
−
 
+
−
'''Hint:''' Split large spreadsheets from other applications into
+
−
smaller worksheets, so that each has fewer than 65536 rows prior to
+
−
converting them. A range containing fewer than 65536 rows will convert
+
−
correctly.
+
−
 
+
−
{{SeeAlso}}
+
−
*[[FAQ:Calc#What_is_the_maximum_number_of_cells_in_an_OpenOffice.org_spreadsheet.3F| What is the maximum number of cells in an OpenOffice.org spreadsheet?]]
+
−
 
+
−
===What is the maximum number of cells in an OpenOffice.org spreadsheet?===
+
−
The maximum number of rows per sheet is 65536 (32000 in releases 1.x). The maximum number
+
−
of columns per sheet is 256. The maximum number of cells per sheet is
+
−
16,777,216. The maximum number of sheets per workbook is 256, which altogether makes it 4,294,967,296 cells per workbook, theoretically. Most certainly the machine doesn't have enough addressable memory available to put content in all cells.
+
−
 
+
−
'''Where this can be a hindrance:'''
+
−
 
+
−
*[http://022.html A large OpenOffice.org spreadsheet was converted from another application. Some of my rows are missing! What happened?]
+
  
 
[[Category:User_FAQ]] [[Category:Calc]]
 
[[Category:User_FAQ]] [[Category:Calc]]

Revision as of 11:28, 19 April 2007

Note: Answers apply to all versions of OpenOffice.org unless stated otherwise.


Contents

Formatting

How do I insert superscript or subscript text in my spreadsheet?

Using the Menu

  1. Select the individual character(s) to be made superscript/subscript:
    • Click on the cell with the text
    • At the input line above the spreadsheet, select the characters to be altered
  2. Select Format - Character from the menu
  3. Click on Font Position
  4. Click Superscript or Subscript. You can optionally change the character reduction ratio but usually the default value will work fine.


Using the Keyboard

  1. Select the individual character(s) to be made superscript/subscript:
    • Click on the cell with the text
    • At the input line above the spreadsheet, select the characters to be altered
  2. For Superscript, press CTRL+SHIFT+P
    For Subscript, press CTRL+SHIFT+B


Notes:

  • Cell height may require an adjustment to accommodate the new character.
  • These commands also work for the word processor.



How can I use a dot (.) as decimal sign instead of a comma?

Some languages, like French, Italian, or German, use a comma as decimal separator. The appearance of numbers in Calc depends on the language settings. If you want to use the dot or decimal point as separator you need to switch the language for the corresponding cells to English:

  1. Select the corresponding cells (press CTRL+A to select all cells of a sheet)
  2. Select Format - Cells from the menu
  3. Select the Numbers tab and select one of the English variants from the Language list.

Notes:

  • If you use a dot in a language that uses a comma as decimal separator, Calc will not recognize the input as number.
  • The decimal sign used will change with the language. If you switch back to a language that uses the comma as separator, the displayed decimal separator will change accordingly.


If a cell is center-justified, and the text is wider than the cell, it automatically left-justifies. Can I fix this?

1.1.x

This is a known bug in OpenOffice.org 1.1.x that is fixed in the current version.



How do I use styles and formatting?

See How to Format a Spreadsheet.



How can I create my own sort lists?

Sometimes it is useful to use sort lists to control the order of your data, especially if the data is better sorted in an order that is not alphabetical or numerical.

Sort lists can be used to fill data into cells by "guessing" following values based on the value of a first cell. The most widespread example is the days of the week or names of the months:

Example:

  1. Insert "Jan" into a cell.
  2. Select this cell, then drag the black square at the lower right corner across other cells in the same row or column.
  3. The other names of the months will automatically fill into the selected cells.

To create your own sort list:

  1. Select Tools - Options from the menu.
  2. Click OpenOffice.org Calc and Sort Lists in the left column
    In OpenOffice.org 1.1.x, the option is called Spreadsheet instead of OpenOffice.org Calc.
  3. Click New on the right side of the window.
  4. Type your list in Entries box separating each word by a comma or a line break. Do not use spaces.
  5. Click Add after your list is complete to save your new sort list.



How do I add additional strings or characters to cell contents?

Use the CONCATENATE function to add strings or characters to cell contents:

  1. Go to an empty cell
  2. Enter =CONCATENATE("prefix";A1;"suffix") as the cell contents

"prefix" being any text you would like to add in front of the existing cell contents
A1 being the original cell
"suffix" being any text you would like to add after the existing cell contents

Example:

  • Original Cell "C5" with content "filename"
  • In a new cell, enter =CONCATENATE("my_";A1;".ods")
  • The new cell value will be "my_filename.ods"



How are notes within cells displayed?

To create a Note for a cell

  • Select Insert - Note from the menu

To show a note permanently

  1. Right-click in the cell with the note
  2. Select Show Note



Why are my notes not showing for cells?

1.1.x

  • Make sure that Help - Tips from the main menu is marked


2.x

  1. Select Tools - Options from the main menu
  2. Select OpenOffice.org - General
  3. Make sure that Help - Tips is marked



I have a custom number format that I use, but Calc forgets it.

2.x

For Calc to remember the preferred number formats, add them to a template:

  1. Create a new empty spreadsheet document or open an existing template by selecting File - Templates - Edit from the main menu
  2. Add the number formats as required
  3. Select File - Templates - Save from the main menu
  4. Insert a name for the template and select a template category (e.g., My Templates)
  5. Click OK to save the template

To have them available with every newly created spreadsheet you need to make this template your default template:

  1. Select File - Templates - Organize from the main menu
  2. Select the template from the list of templates (double click the template categories to show all containing template files)
  3. Right-click the template file name and select Set as default template
  4. Click Close


How can I use conditional formatting?

Formats in each cell can be based upon defined conditions. This function can be accessed through the drop-down menus:

  • Select Format - Conditional Formatting...

In the dialog box that appears, enter the conditions that determine the formats and the desired formats for each condition. If more help is needed to understand the settings, click the Help button in the dialog box, and a new help window opens with a description of all the fields shown in the dialog box. The help file also shows the path to a sample file that uses conditional formatting for reference.


See Also




How do I wrap text within a cell in my spreadsheet?

To wrap text within a cell, or merged set of cells:

  1. Select a cell or group of cells.
  2. Right-click the selected area and go to Format cells..., or select Format - Cells from the main menu
  3. Click on the Alignment tab.
  4. Check the Wrap text automatically (in OpenOffice.org 1.1.x: Automatic line break)
  5. Click OK.

Now the contents of the cell will be wrapped to fit the cell.



How do I disable the capitalized letter at the beginning of cells in Calc?

  1. Select Tools - AutoCorrect from the main menu
  2. Select the Options tab
  3. Clear any automatic option you want to disable (in this case Capitalize the first letter of every sentence)



How do I make a wide title cell extend across several columns in my spreadsheet?

  1. Select the cells in which the title is to appear.
    Do this by clicking in the first cell, then hold down the mouse button and drag the selection box across the last cell of your chosen area.
  2. Select Format - Merge Cells - Merge Cells (OpenOffice.org 1.1.x: Format - Merge Cells - Define) or Format - Merge Cells - Merge and Center Cells



How do I rotate a column title so that it fits above my very narrow column?

There are a few options. Follow these instructions:

  1. Select the cells where you wish to rotate the text.
  2. Select Format - Cells... from the main menu
  3. Click on the Alignment tab
  4. In the Text Orientation section (OpenOffice.org 1.1.x: Text Direction) drag the dot on the

circle to the bottom of the circle (or enter 270 in the Degrees box). This will rotate the text in the selected cell by 270 degrees.

  1. Click OK to see the effect. Experiment with placement of the dot to learn more.



Is there a way to add times that total greater than 24 hrs?

Yes. Select a format code with the hour symbols in square brackets, like [HH]:MM.

  1. Select the cell you want to apply the format to
  2. Select Format - Cells from the main menu
  3. Select the Numbers tab
  4. Set the Category to Time
  5. Select one of the formats with hours > 24, for example [HH]:MM:SS
    you can also select the Format Code line and enter the format yourself
  6. Click OK.

Now when you add the times from cells with this format together, you will get the true sum of hours and minutes. Otherwise, the sum will reset to zero each time 24 hours is reached.



Some of the rows or columns in my spreadsheet are hidden. How do I see all rows or columns?

  1. Select the area of the spreadsheet where rows or columns are hidden. To select the entire spreadsheet,

select Edit - Select All from the main menu or press CTRL+A

  1. To show all rows, select Format - Row - Show from the main menu

To show all columns, select Format - Column - Show from the main menu



How do I format cells in Calc so that the rows number automatically?

  1. Enter the formula =row() into cell where the row numbering will start
  2. Extend the cell by dragging the bottom right corner all the way down to the cell you want the numbering to end

Note:

  • If you insert new rows, you will have to copy the formula to the new cells.



How are thick lines or borders created around my cells?

  1. Select the cells where you wish to apply a border.
  2. Select Format - Cells... from the main menu
  3. Click on the Borders tab
  4. In the Line Arrangement section, under Default, click on the icon that

best shows the style of border you wish to use, or
Under User Defined, select custom border styles by clicking on the individual lines shown bordering four 'dummy' cells.

To change the width and color of a border:

  • In the Line section, under Style, click on the desired line for your border, and
  • Under Color, choose the desired color for your border.



How do I insert a page number in the form of 'Page 1 of N' on each page of a spreadsheet?

Page numbers can be inserted into the header or footer sections of a spreadsheet. These will be visible only in the Page Preview mode and on the print. The page numbers will not be visible on, nor are they related to, the calc sheets themselves. The page numbers reflect the defined print areas of the workbook.

To insert page numbers:

  1. Select Edit - Headers & Footers... from the main menu
  2. Depending on where you want the page number to appear, select either the Header or the Footer tab
  3. Click in the area where you want the page number to be displayed (Left, Center, or Right) to place the cursor inside the box.
  4. Type "Page ", then add one space character.
  5. The available data fields are represented by a row of icons below the input areas,

Click on the document icon with ONE number sign [ # ] to insert the page number placeholder.

  1. Type " of "
  2. Click on the document icon with TWO number signs [ ## ] to insert the page total placeholder
  3. Click OK

Note:

  • The data fields available in the header and footer settings are the only data fields and formats available in spreadsheets.



Opening and Importing Files, Microsoft Interoperability

Is it possible to open a Microsoft Excel file that is protected by a password in Calc?

1.1.x

With 1.1.x releases of OpenOffice.org, it is not possible to do this directly. Such a file can be opened from Calc only if you remove the password using the original application.


2.x

2.x versions of OpenOffice.org, will open the password protected file when the correct password was entered.



How do I insert external data into an existing Calc spreadsheet file?

2.x

You can insert tabular data from an external files into an existing Calc spreadsheet file as follows. A new sheet will be created for the inserted data.

  • Select Insert - Sheet From File and select the file with the data
  • If you have selected a text file with values separated by delimiters you will see an import dialog to set the corresponding options


See Also




How do I open a tab-delimited file in OpenOffice.org Spreadsheet? What if I have a different type of delimiter?

First, if your ASCII file is not already named with a .txt extension, rename it that way.

  1. Select File - Open from the main menu and browse to find and select the file.
  2. Select File type: Text CSV (.csv; .txt)

Note: This choice is near the bottom in the spreadsheet file types section of the list.

  1. Click Open.
  2. In the dialog that appears next, select the Separator options.

These are the characters or methods used in the file to separate the fields of data. The same methods must be specified in this box as those used in the file to import the data into a spreadsheet. After selecting the separator type, a preview of the data will be displayed in the Fields section. If the data visually lines up in columns, then the correct separator has been selected. If not, a different separator type may be used in the file. The goal is to match the correct character used as a separator in the file, so that the data will line up nicely in the visible cells.

  1. When the data lines up, click OK.

Hints:

  • The characters used as a separators and delimiters will be visible, if you open the .txt

file in Writer and enable the hidden characters (View - Nonprinting Characters).



My formula from an Excel worksheet doesn't work!

This can be caused by many reasons, with the most common reason being that OpenOffice.org uses semi-colons (;) between arguments, instead of commas (,) like in Excel.



Why does Calc refuses to open my .txt file?

A text file with tabular values must be imported into Calc using .csv format. You cannot open it by double-clicking on the file name, unless it has the .csv extension, since a text file will automatically open in Writer.

If you want to open a .txt file with tabular data using Calc:

  • Rename the file so that it uses the .csv file extension.

or

  • Select File - Open from the menu and select Text CSV from the File type list.

This is quite a way down in the list so it is easy to miss. An easy way of navigating there is to press T several times when inside the list field until the filter name comes up.


See Also



A large Calc spreadsheet was converted from another application. Some of my rows are missing! What happened?

OpenOffice.org spreadsheets supports a maximum of 65,536 rows (32,000 in releases 1.1.x). Spreadsheets converted from other applications that contain more than 65,536 rows will be truncated.

Notes:

  • Split large spreadsheets from other applications into smaller worksheets, so that each has fewer

than 65,536 rows prior to converting them. A range containing fewer than 65,536 rows will convert correctly in OpenOffice.org 2.x



Selecting, Copying, and Pasting

I want to select two cells that are not adjacent, but holding down the CTRL key does not seem to work. How can I perform this action?

1.1.x

In OpenOffice.org 1.1.x, if you click in a cell, it is not selected, but focused. To select a cell, you need to

  1. Click on a cell to focus it
  2. Hold down the SHIFT key and then click on the cell again to select it

After having done so, you'll be able to select multiple non-adjacent cells, by holding down the CTRL key as you select new cells.


2.x

In all 2.x versions, you can use the CTRL key to mark cells that are not adjacent.


What is the fastest way to copy a calculation to all rows?

To quote from the Shortcut Keys for Spreadsheets page in Calc Help: Help > Contents > shortcut keys; in spreadsheets: To fill a selected cell range with the formula that you entered on the Input line, press Alt+Enter. Hold down Alt+Enter+Shift to apply the cell format of the input cell to the entire cell range.


Saving and Exporting Files

How do I output my spreadsheet data as an ASCII, delimited text file?

  1. Select: File - Save As... from the main menu
  2. In the Save as dialog that appears, select File type Text CSV (.csv; .txt) from the list of

spreadsheet file types.
Press T several times in the list to quickly jump to that entry.

  1. Click to enable the box next to Edit filter settings.
  2. Click Save.
  3. In the Export of text files dialog box that pops up, enter the field and text delimiters of your choice.

The Text delimiter is the character that will surround any text entries in your spreadsheet, to keep each phrase together as an entity, when the file is exported into ASCII format.



Printing

Why does Calc print out all the sheets in the file?

By default, Calc is configured to do just that. But the setting can be changed so that only the current sheet is printed too:

  1. Select File - Print from the main menu
  2. Click Options
  3. Check the box Print only selected sheets for Document

To make the change permanent for all occurrences of OpenOffice.org Calc:

  1. Select Tools - Options from the main menu
  2. Select OpenOffice.org Calc - Print and check the box Print only selected sheets for Document

In OpenOffice.org 1.1.x, the entry is called Spreadsheets instead of OpenOffice.org Calc.



How can I print some, but not all, of the cells on a sheet?

  1. Select the cell(s) you want to print
  2. Select Format - Print Ranges - Define from the main menu

This sets the print range for that sheet to the set of selected cells.

Notes:

  • You can have different print ranges on each sheet.



How do I get Sheet1 to print as portrait and Sheet2 to print as landscape?

You can assign a different page style to each sheet. To print out different page orientations, you first create a new page style with the desired format options and then apply it to the corresponding sheet(s).

  1. Select Format - Styles and Formatting (in OpenOffice.org 1.1.x: Format - Stylist) or press F11
  2. Click the Page Styles icon (2nd from the left) in the Styles and Formatting window (in OpenOffice.org 1.1.x: Stylist)
  3. Right-click in the page style list and select New...
  4. In the Page Style window, give the new page style a descriptive name, such as Landscape Page
  5. Click on the Page tab and change the Orientation to Landscape
  6. Click OK to close the window
  7. Select the sheet that you want to print in landscape orientation
  8. Double-click the newly created page style (e.g. Landscape Page) to assign that style to the sheet.



How I do select a row (or rows) in my spreadsheet to repeat on every page when printed?

  1. Select Format - Print Ranges - Edit from the main menu
  2. Place the cursor in the input field on the Rows to repeat line
    • Enter the row number manually (e.g. $4 for row 4, $4:$6 for rows 4 to 6), or
    • Click on the icon to the right of the input field, then select a cell on the desired row from the spreadsheet.

Multiple rows can be specified dragging your mouse across a range of cells.

  1. Click OK.

Notes:

  • The row(s) specified here will be repeated on each print.
  • You will not see a visual change in the spreadsheet, only in the printed copies.
  • Use File - Page Preview to preview pages before printing.
  • You an only select multiple rows id they are consecutive.



How do I print my spreadsheets?

<this should really rather be in a Howto>

In general, the best way to print large spreadsheets is to first preview the print output, then adjust the print settings to arrive at the desired effect. The following mini-procedures explain the main options for printing spreadsheets.

Previewing the print area:

  • With the spreadsheet open, go to File > Page Preview in the drop-down menus.
  • In the window that appears, use the navigation buttons on the tool bar to view the print. If the spreadsheet is too large for one page, some columns may appear on additional pages.
  • To close the preview window, click the Close Preview button to the right of the navigation buttons on the toolbar.
  • Make adjustments to the print settings, then preview again. Repeat until the print displays in the desired format.

Note: This command will allow you to see the current page number assignments for the data that you want to print. This is especially useful when only a portion of the spreadsheet is to be printed, or when several print ranges are associated with one Calc file. Formatting the page:

  • With the spreadsheet open, go to Format > Page... in the drop-down menus.
  • In the dialog that appears, select the Page tab.
  • Set the paper size.
  • Specify the Orientation of the page: click on the checkbox next to Portrait or Landscape.
  • Adjust the margins, if needed.
  • Set the table alignment to align the table to the page.
  • Click OK, or click other tabs in this box to acess more formatting options.


Adjusting the page breaks:

  • With the spreadsheet open, select View > Page Break Preview.
  • Place the cursor exactly on top of one of the (blue) page edge lines, then drag each edge to adjust the page boundaries as desired.
  • To toggle this view off, select View > Page Break Preview.

Note: If everything appears gray, this means that no print ranges are defined. If a print range is defined, the printing area will show up with a white background with a blue outline at the page boundary. A gray 'Page N', where 'N' is the page sequence number for the sheet, will appear in the middle of the print area. If no range is defined for printing, follow the instructions below.

Setting the area to be printed:

  • Go to the desired sheet.
  • Click and drag to select (highlight) the area of the sheet to be printed.
  • In the drop-down menus, go to Format > Print Ranges > Add.
  • Repeat the above steps for each sheet of the file to be printed.

Adjusting the printout to fit onto one page:

  • With the spreadsheet open, select Format > Page...
  • In the dialog that appears, select the Sheet tab.
  • In the lower right corner of the dialog, there is a Reduce/enlarge printout scale control. Enter a value that you think will make the data fit on the page.
  • Click OK.
  • Use Page Preview (instructions above) to see a preview of what will print. Readjust if necessary.

Adjusting the maximum number of pages on which the print should fit:

  • With the spreadsheet open, select Format > Page...
  • In the dialog that appears, select the Sheet tab.
  • At bottom of the dialog there is a scale control labeled, Fit printout on number of pages. Click the checkbox next to it; then enter the desired number of pages for the printout.
  • Click OK.
  • Use Page Preview (instructions above) to see a preview of what will print. Readjust if necessary.

Printing only a portion of the spreadsheet (a range of rows or columns):

  • In the spreadsheet where the data lives, click and drag the mouse over the cells to select (highlight) the rows or columns to be printed.
  • Select Format > Print Ranges.
  • In the print dialog that appears, click the checkbox next to Edit... at lower left.
  • Click OK.

Note: Printing a selected range of data is also controlled by print scaling. If the data selected does not fit on the desired number of pages, adjust the Scale using either of the scaling procedures above (scale by percentage or by number of pages allowed).

Editing the print range:

  • With the spreadsheet open, select Format > Print Ranges > Edit.
  • In the print dialog that appears, look at the Print range setting. It is probably set to user defined and shows a range of cells.
  • Adjust the cell definition in the range of cells, or select None to clear the print range.
  • Click OK.
  • If there are no settings, or they are cleared, use one of the other methods shown to set your print ranges.

Printing a spreadsheet in the middle of the paper:

  • With the spreadsheet open, select Format > Page...
  • In the dialog that appears, select the Page tab.
  • In the Table alignment section, select (or deselect as desired) the boxes next to Horizontal and Vertical. A visual of the table alignment settings will show in the small graphic on the same page as these parameters.


See Also




Charts

How do I extend the range of data shown in my chart after I add new data to my spreadsheet?

  • Select a new range and drag the selection onto the chart.
  • You can also select the chart, right-click, and then choose Modify Data Range.


How do I create a chart in a spreadsheet that is automatically updated when the data is changed?

When you create a chart based on data that is present in the same spreadsheet, the chart will automatically be updated whenever the data in the spreadsheet changes.



Error Handling and Troubleshooting

Why does deleting filtered rows remove other rows too?

When using Calc filters, selections that span a range of cells include filtered and hidden rows. Deleting a range of rows with a filter enabled will delete all rows in that range, whether they are visible or hidden, not just the visible rows.

To select multiple rows individually, select the first row, then use CTRL+Click to select each additional row.



What do each of the error codes (Err:NNN) in OpenOffice.org spreadsheets mean?

See here for a list of error codes

Note:

  • a '###' in a cell is not an error, but an indication that the column is too narrow to display the data properly.


See Also




Why do I see the formula text and not the result of the formula?

  • Make sure that all formulas will start with the equal sign (=)

If your formula starts with the = character, check that the options are set to not view the formula:

  1. Select Tools - Options from the main menu
  2. Select OpenOffice.org Calc - View (in OpenOffice.org 1.1.x: Spreadsheet - View)
  3. Make sure that the Formulas Option under Display is not checked



How to get proper Calc Help files for OpenOffic.org 2.0.1 for Windows?

2.x

Update to the latest release. The problem is fixed.



I see '###' in a cell that formerly held data. How can I see my data again?

When the content of a cell is too long to be displayed in a single cell, it will be clipped at the standard width and height of the cell itself. The rest of the content is simply hidden.

  • Go to the heading row and drag one of the column edges until it is wide enough

to display your data properly if the cell content is a number; or

  • Double-click on the cell if the cell content is text and a little red triangle

appears on the right side of the cell to signal that this is an overflowing one.



Miscellaneous

What's the maximum number of rows and cells for a spreadsheet file?

1.1.x

The OpenOffice.org 1.1.x versions were able to handle a maximum of 32,000 rows. Update to the current version to solve this issue.

2.x

The limitations of the OpenOffice.org 2.x Calc versions are

  • maximum number of rows: 65,536
  • maximum number of columns: 256
  • maximum number of cells per sheet: 16,777,216
  • maximum number of sheets: 256
  • maximum number of cells per file: 4,294,967,296

Notes:

  • When referencing to external documents in formulas or when creating sceanrios,

hidden helper sheets are created that reduce the available number of sheets and consequently cells.



How can I start OpenOffice.org Calc instead of Writer?

Pass -calc as an argument on the command line when executing openoffice.org to start OpenOffice.org Calc: 

ooffice -calc



How can I see the row and column captions as I scroll through a sheet that is larger than my display?

There are two ways to obtain this result:

Mode 1 (with a single row or column caption): Freeze the caption.

  1. Click on the cell just below and to the right of the row and column that will contain your caption.
  2. Select Window - Freeze from the main menu.

If you have either row captions or column labels, you can create a cross freezing by clicking on the uppermost left side cell that does not contain a caption. For example, in a sheet with single row and column captions, choose the B2 cell. The position of all cells above and to the left of the cell where the Freeze was activated will be frozen.

Mode 2 (with multiple row or column captions): Split the sheet.

  1. Click on the cell just below and to the right of the row and column that you want to contain your caption.
  2. Select Window - Split from the main menu.

The sheet window will be split. The result is different from Mode 1 because you can scroll all sections of the split window, showing the caption according to your needs.

There is another (almost hidden) way to split a sheet window:

At the top of the right scroll bar and at the right of the bottom one, you can see little black lines. When the mouse is over one of these zones, the mouse pointer changes into a dragging icon.

  1. When this icon shows, click and hold down the left mouse button to display a border line in the grid of cells.
  2. With the mouse, drag the line on the grid to the row or column that will contain your caption.
  3. Release the mouse button. The sheet will be split at this border.



How do I protect cells in my spreadsheet?

Cell protection is active for all cells by default. If only certain cells are to be protected, this setting must be turned off.

To exclude cells from the protection:

  1. Select the cells to be excluded from protection
    Hold down the Ctrl key while

clicking on non-adjacent cells to highlight the ones that are to be protected.
If you only want to protect a small number of cells, it may be easier to clear the protection for all cells and the re-activate protection for the appropriate cells. For this, select the entire spreadsheet: press CTRL+A or click on the little gray box above row 1 and to the left of column A.

  1. Select Format - Cells from the main menu
  2. Click on the Cell Protection tab
  3. Clear the check mark for the Protected option
  4. Click OK


Initially however, the protection is not activated. To activate the protection:

  • Select Tools - Protect Document - Sheet to protect the current sheet only
  • Select Tools - Protect Document - Document to protect all sheets in the current document


How can I use cells from different Calc files?

  1. Open the source file
  2. Select the cells and press CTRL+C to copy them to the clipboard
  3. Open the target file
  4. Select a target cell and select Edit - Paste Special from the main menu
  5. Check the Link box in the Options section

The inserted data are now linked to the original document. Whenever the data changes in the source document this will be reflected in the target document.



How I change the order of sheets in my spreadsheet?

You can move a sheet to a different position, click and hold the sheet tab at the bottom of the screen with the mouse and drag it to its new position.

You can also move sheets (even across different documents) using the menu:

  1. Right-click the sheet you want to move and select Move/Copy Sheet... from the pop-up menu, or

select Edit - Sheet - Move/Copy from the main menu

  1. Specify the new position of the sheet in the dialog.
    You can even move the sheet to a different document that is opened in Calc
  2. Click OK

Note:

  • If you check the Copy option in the dialog, the sheet will copied, not moved.



How can I rename a sheet?

  • Right-click the sheet you wish to rename and select Rename Sheet from the popop menu, or
  • Select Format - Sheet - Rename from the main menu

Note:

  • You can only use letters, numbers, spaces, and the underline character in the sheet name.



How is a variable date inserted into a spreadsheet cell?

  1. Select the cell that will hold the function.
  2. Enter =TODAY() for the current date or =NOW() for the current date and time.

The values will be dynamically updated when the file is reloaded.



Where can I find more documentation for Calc?

Internal OpenOffice.org resources:

External resources:

Internal OpenOffice.org mail lists where you can find information about Calc:

External mail lists and forums where you can find information about Calc:

Please write to let us know if there are other Calc related resources that should be shown in this list.

Retrieved from "https://wiki.openoffice.org/w/index.php?title=Documentation/FAQ/Calc&oldid=30475"
Personal tools