Documentation/FAQ/Calc

From Apache OpenOffice Wiki
< Documentation‎ | FAQ
Revision as of 21:07, 7 June 2006 by Kirk (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

Contents

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

There are several possible paths to alter characters:

Select the individual character(s) to be made superscript/subscript. To do this, click on the cell where the text resides. Now at the input line above the spreadsheet, select the characters to be altered by clicking before the first one with the mouse, then drag the highlight box over the character(s) you want to change, and release the mouse button. The character(s) you wish to change should now be highlighted.

  • In the pull-down menus, go to Format > Character...,

or click with the right mouse button (right-click) and select Character... from the pop-up menu.

  • Click on the Font Position tab.
  • Click so that a dot appears near your choice of Superscript

or Subscript. There are other adjustments included there if you need them, but the defaults work ok too.

To access this feature via keyboard commands, select the text to be altered, then use the the following keystroke combinations:

  • for Superscript use: CTRL + SHIFT + P
  • for Subscript use: CTRL + SHIFT + B

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

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

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 versions of OpenOffice.org, will open these with the password.


How can I use decimal signs (dots) instead of commas to display rational figures?

In some localized Windows OSes such as the French and Italian ones, the rational figures and numbers are displayed, by system default, using a comma. It is possible to avoid this behavior:

  • from the Menu Bar.
  • Tab of the pop-up window that will appear, change the language option to English.

Another way to obtain the same result is to right click on the cell and choose the 'Format Cell' quick menu option, displaying the pop-up cell properties window. Afterwards, follow the procedure described above.

==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?== If you click in a cell, it is not selected, but focused. To select a cell:

  • Hold down the SHIFT key and then click on the cell.
  • 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.


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. Examples of this are the days of the week or the months of the year. Sort lists can be used to fill data into cells too. To automatically fill data into cells from sort lists:

  • Insert a day of the week, or other list item, into a cell.
  • Next, select this cell, then drag the black square at the lower right

corner across other cells in the same row or column.

  • The other days of the week, or other items in the list related to

the word chosen the first step will automatically fill into the selected cells.

To create your own sort list:

  • Using the drop-down menu, go to Tools > Options.
  • In the left column expand Spreadsheet and go to Sort Lists.
  • In the pop-up window that will appear, select New.
  • Under Entries, type your list, in the order you want, and

separate each word by a comma. Do not use spaces.

  • When your list is complete, click Add.
  • To learn more, click the Help button in the sort list options box.


==Is there any way to merge a text file with separated fields (*.txt,

  • .csv) into a Calc file?==

Text files cannot be inserted into a Calc spreadsheet, because a table or range must be specified in this OOo feature. For text files, use the Import feature instead. Reference <a href="001.html">How do I open a tab-delimited text file in a Calc spreadsheet?</a> for more information. External data in formats that include tables or ranges, such as web pages and spreadsheets, can be inserted into a Calc worksheet. It looks like a cool feature. I'm just learning about it too!


  • Open a new calc sheet, and choose Insert > External Data...

from the Menu Bar.

  • In the External Data pop-up window that appears, click

the [...] radio button. This pulls up yet another pop-up box, entitled Insert.

  • Browse to the file, and click on it so that its name appears in

the File name field.

  • In the File type field, select the file format that

matches that of the file with the table in it.

  • Click Insert. This brings back the External Data

pop-up box.


  • Now in the Available Tables/Ranges section of the External

Data pop-up box, a list of tables and ranges appears. Click on a range to select it.

  • Once a table or range is selected, you can click OK to

import the data to the Calc sheet.


There is an update control option available too. Click on the Help button to see a blurb about it, and play with it to learn more. If you can add more details to this FAQ about it, please do!

How can I wrap text within a cell?

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 > 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.

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

This is a known bug in Calc that is shown in Issue <a

href="http://www.openoffice.org/issues/show_bug.cgi?id=1171">#1171</a>.

This problem is fixed in the OOo1.9m26 developer snapshot version of OpenOffice.org and will be available in the stable version of OpenOffice.org 2.0. To obtain the latest versions of OOo, visit the <a

href="http://download.openoffice.org/index.html">Download Central</a>

page. Note there is a work-around:

 *Merge the cell with adjacent cells: Select the cells you wish to

merge, then select Format > Merge Cells... > Define from the drop-down menus.

 *Center the data in the merge cell: Click the Align Center

Horizontally button on the tool bar, or use the drop-down menus to select Format > Cell, click the Alignment tab, then choose Center from the Horizontal drop-down menu in the Text alignment section.


==In MS Excel I can center my text so that the text will flow both to the left and right if the cells are empty; how can I do this in Calc? == It is possible in Calc for the text to flow across empty cells on the right side of a cell. However text flow outside of the left edge of a cell is not yet supported in Calc.


How do I use styles and formatting?

The documentation project has provided a document for this entitled <a

href="http://documentation.openoffice.org/HOW_TO/spreadsheet/calc4_EN.html">How

to Format a Spreadsheet</a>.

Can I use a spreadsheet file with more than 32,000 rows?

The current stable releases of Calc allow a maximum of 65536 rows. However, with the 1.1.x release, this is well known, and it is a limitation in comparison to Microsoft Office 2000.

So, if you use 1.1.x, the 32000 limit may a real problem for your application. Nonetheless, there are a few options to consider. Frequently when you are hitting the row limit, it is a symptom of a spreadsheet design which could be improved:

 *Restructure the rows. Split the data into several files or

several spreadsheets within one file.

 *Use a real database. This is the most frequent thing people are

trying to accomplish by having such a large number of rows in a sheet.


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 refuse to open my text file?

A text file must be imported into Calc using .csv or .txt format. Otherwise, the file will automatically open in Writer.

 *Rename the file so that it uses the .csv file extension. 
 *In the File > Open dialog, set the File type

field to Text CSV. This is quite a way down in the list so it is easy to miss. It is grouped with the spreadsheet file types.

For more information:

 *In the pull-down menus, go to Help > Contents > Index

and type csv files;spreadsheets in the Search term input bar.

 *<a href="001.html">How

do I open a tab-delimited file in a Calc spreadsheet?</a>

How do I add trailing characters to a cell?

If what you have are index numbers like "DCP_5509" in column B and you want to add a common suffix like "myparty.jpg" to the end of this existing data, then enter the function =CONCATENATE(B1;"myparty.jpg") into a new cell that will show the index number from cell B1 with the new suffix attached. Next, apply this function to the rest of the cells in the new column. Copy the first cell in the new column, then paste it to the remaining cells in that column. Now the new column displays "DCP_5509myparty.jpg" and the like. Note: Alter the function to =CONCATENATE("myparty.jpg";B1) to add the additional characters as a prefix to the existing data from cell B1.


How are notes within cells displayed?

To create a Note for a cell, use Insert > Note. The Note can be set to show permanently: do a right-click in the cell, then select Show Note. The note can also be set to display when hovering over the cell, if the Tips selection is enabled: Click on Help > Tips. As long as the tick is displayed next to Tips, notes for cells will always be displayed when the mouse hovers over cells with notes.

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.

How can I start Calc instead of Writer?

By passing -calc as an argument on the command line when executing openoffice.org.

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. Go to File > Print > Options and then tick the box labeled Print only selected sheets for Document. To make the change permanent for all occurrences of OOo Calc, go to Tools > Options > Spreadsheet > Print and select the same option: Print only selected sheets for Document.

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

You can select the cell(s) you want to print by highlighting them with the mouse, then go to Format > Print Range > Define. Once Define is clicked, the cells you had highligted become the selected range for that sheet.

==How do I get Sheet1 to print as portrait and Sheet2 to print as landscape?== In your open document in OpenOffice.org:

 *Open the Stylist by using the drop-down menus, Format >

Stylist or Styles and Formatting, or by pressing the function key, [F11].

 *In the Stylist window, click on the Page Styles icon

(fourth icon from left).

 *The type Standard or Default will likely be

highlighted. Hold the right mouse button down to see the context menu and choose New...

 *In the dialog that appears, give the new page style a descriptive

name, such as Landscape Page.

 *Click on the Page tab and change the page orientation to Landscape

by clicking on the adjacent radio button. You may also make other changes you wish to assign to this page style, such as adjusting the margin sizes or enabling the header and footer.

 *Click OK. The new style will appear as one of your page

style choices.

 *Place the text cursor in your document at the location where you

want to insert the Landscape page.

 *Select Insert > Manual Break...
 *In the dialog that appears, select Page Break and in the Style

drop-down list, select your new page style, for example Landscape Page.

 *Click OK.

To end the Landscape area in your document:

 *Place the text cursor in your document at the location where you

want to stop the Landscape layout.

 *Select Insert > Manual Break...
 *In the dialog that appears, select Page Break and in the Style

list, select the previous page style, such as Standard or Default.

 *Click OK.


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

For Calc to remember the preferred number formats, add them to your default, or current document template.

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: Go to 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.


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.

Where can I find definitions for the error codes?

The error code definitions live in the Help files.

 *In the OpenOffice.org window, go to the Help drop-down

menu.

 *Select Contents.
 *Select the Index tab by clicking on it.
 *In the Search input box, type error codes. An

index list will pop up, with "error codes" in it.

 *Under the error codes section in the index list, double-click on OpenOffice.org

Calc. This brings up the page of calc error codes.


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

In OOo Calc all formulas must start with the equal sign (=).* If your formula does start with an = character, then check that the options are set to not view the formula. Go to Tools > Options; then, in Spreadsheet > View verify that there is no "tick mark" in the box beside the Formulas selection.

  • This is a difference between Calc and MS-Excel(R).

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

Yes. This requires a custom number format in the cells containing the times that you wish to add together.


 *With the cells selected, go to Format > Cells, then

select the Numbers tab.

 *Set the Category to User-defined; then, in the Format

Code input bar at the bottom, type [HH]:MM.

 *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. This new format will be located under the Time Category, at the bottom of the list of Formats, until you add more custom formats that fall into this category.

Where can I find more documentation for Calc?

Internal OpenOffice.org resources:

 *<a href="http://documentation.openoffice.org/HOW_TO/index.html">OOo

Documentation Project How-Tos</a>

 *<a href="http://documentation.openoffice.org/manuals/index.html">OOo

Documentation Project Manuals</a>

External resources:

 *<a
href="http://www.ooodocs.org/modules.php?name=Content&pa=showpage&pid=3">OOoDoc.Org

How-Tos</a>

 *<a
href="http://www.tutorialsforopenoffice.org/category_index/spreadsheet.html">Calc

Tutorials for OpenOffice</a>

 *<a
href="http://www.digitaldistribution.com/samples/calcfirststeps/">First

Steps with Calc</a> </ul> Internal OpenOffice.org mail lists where you can find information about Calc:

    *<a href="http://www.openoffice.org/servlets/SummarizeList?listName=users">OOo General Users List Archives</a> *<a href="http://sc.openoffice.org/servlets/SummarizeList?listName=users">OOo Spreadsheet Project User List Archives</a> External mail lists and forums where you can find information about Calc: *<a href="http://www.ooodocs.org/modules.php?name=Forums&file=viewforum&f=3&sid=c36b0ed8a9e6354fc7d3a326b0c06628">OOoDocs Calc General Forum</a> *<a href="http://www.oooforum.org/forum/viewforum.php?f=3">OOoForum::View Forum::OpenOffice.org Calc</a> Please write to let us know if there are other Calc related resources that should be shown in this list.

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


    The problem does not exist in 2.0,2.
    


    ==How do I extend the range of data shown in my chart after I add new data to my spreadsheet?== From OOo Help > Contents > Index, under charts; inserting (AutoFormat): To quickly change the data range used in your chart, select a new range, and then drag the selection onto the chart. You can also select the chart, right-click, and then choose Modify Data Range.

    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.

     *Open Calc. In the pull-down menus, go to File > Open.
     *Browse to find and select the ASCII file.
     *Select File type: Text CSV (.csv; .txt). Note: This choice is near the bottom in the spreadsheet file types section of the list.
     *Now click Open.
     *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. 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.
     *Click the Help button in the Text Import box accessed above in Step 5 to bring up more information about the parameters there, if further explanation is needed.
    


    ==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. You can:

     *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.


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

    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 > 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.

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

     *Open Calc.
     *Select the row(s) to repeat.
     *Select Format > Print Ranges > Edit from the
    

    pull-down menus.

     *Change Rows to repeat from - none - to - user
    

    defined - via the drop-down menu.

     *The row numbers can be set a couple of ways:
    
         **Enter the row number (e.g. $4 for row 4).
         **Click on the icon to the right of the input bar, then select
    

    a cell on the desired row from the spreadsheet. Multiple rows can be specified dragging your mouse across a range of cells.

         **Type the range into the Rows to repeat input bar.
    

    Place a colon between the starting cell and the ending cell to show a range.


     *Click OK.
    

    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 > Print Preview to preview pages before printing. For more information: Go to Help > Contents > Index and type print ranges. Double-click on list items there to bring up the corresponding help pages.



    ==How do I disable the capitalized letter at the beginning of cells in Calc?== Go to Tools > AutoCorrect/AutoFormat and select the Options tab; then uncheck the Capitalize the first letter of every sentence option.

    ==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.

     *Click on the cell just below and to the right of the row and
    

    column that will contain your caption.

     *From the drop-down menus, select Window > Freeze. 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.

     *Click on the cell just below and to the right of the row and
    

    column that you want to contain your caption.

     *From the drop-down menus, select Window > Split. 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.

    It is also possible to split a sheet window in another way.

     *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. When this icon shows, click and hold down the left mouse button to display a border line in the grid of cells.

     *With the mouse, drag the line on the grid to the row or column
    

    that will contain your caption.

     *Release the mouse button. The sheet will be split at this border.
    


    How do I print my spreadsheets?

    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.

    For more information:


     *<a
    href="http://documentation.openoffice.org/HOW_TO/spreadsheet/calc5_EN.html">How
    

    to Set the Printing Settings</a>


    ==How do I make a wide title cell extend across several columns in my spreadsheet?== Follow these instructions:


     *Select the multiple 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.

     *From the pull-down menu, select Format > Merge Cells >
    

    Define. The cells selected in the step above now appear as one.

    Many times, the title or merged-area created needs the text centered or aligned within it. To do this:

     *Click on the title cell to select it.
     *From the pull-down menu, select Format > Cells.
     *On the dialog that appears, select the Alignment tab.
     *Both the Horizontal and Vertical alignment of
    

    text can be set here, where other adjustments to cell text are allowed as well. The alignments can also be defined more simply by clicking on the alignment buttons on the toolbar above the spreadsheet.


    ==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 > Cells...
     *In the dialog that appears, click on the Alignment tab.
    

    In the Text Direction section of the Alignment box, try these options:

     *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.

     *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.


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

    Follow these steps:
    
     *From the pull down menus, select: Tools > Options.
     *In the left column of the dialog that appears, select: Spreadsheet
    

    > 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. See also: <a href="052.html">How do I extend the range of data shown in my chart after I add new data to my spreadsheet?</a>

    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 > 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 > 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 > Protect
    

    Document > Sheet or Tools > Protect Document > 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 > 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 > Select All in the pull-down menus, or use the Control-A keystroke combination.

     *To show all rows, select: Format > Row > Show.
     *Similarly, to show all columns, select: Format > Column
    

    > 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.

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

    <tbody> </tbody>
    Error Code Cleartext Explanation
    501 Invalid character The error is a character that is invalid in this context,

    e.g.

    =1Eq instead of =1E2.
    502 Invalid argument A function argument has an invalid value, e.g. a negative number for the root function.
    503 Invalid floating point operation Division by 0 or other calculation that would result in an overflow of the defined value range.
    504 Parmeter list error A function parameter is an invalid type, e.g. text instead of number, or domain reference instead of cell reference.
    505 Internal syntax error
    506 Invalid semicolon
    507 Error: Pair missing
    508 Error: Pair missing For example, closing parenthesis without opening parenthesis

    or missing closing parenthesis in the middle of the formula (missing

    closing parenthesis at the end of a formula is added automatically).
    509 Missing operator E.g. =2(3+4) * operator missing between the 2 and the (.
    510 Missing variable Two operators in a row, e.g. =1+*2.
    511 Missing variable The function requires more variables than are provided, e.g. AND() and OR() without parameter.
    512 Formula overflow Compiler: as the name indicates, the internal number of

    tokens that has nothing to do with the formula string length is indicated (max. 512) rather than the number of operators, variables, parentheses, etc. Interpreter: Formulas that create several matrices at once (max. 150), including basic functions that would receive too large an array

    as a parameter (max. 0xFFFE, i.e. 65534 bytes).
    513 String overflow Compiler: an identifier in the formula exceeds 255

    characters.

    Interpreter: a result of a string operation exceeds 255 characters.
    514 Internal overflow Sort operations with too much numerical data (max. 100000) or calculation stack overflow.
    515 Internal syntax error
    516 Internal syntax error A matrix is expected on the calculation stack but is not available.
    517 Internal syntax error Unknown OpCode, e.g. a document with a newer function is loaded in an older version that does not contain the function.
    518 Internal syntax error A variable was supposed to be popped from the calculation stack but is not available.
    519 No result (#VALUE is in the cell rather than Err:519!) A function could not deliver a value that corresponds to the

    definition or a cell referenced in the formula contains text rather

    than a number.
    520 Internal syntax error The compiler created an unknown compiler code.
    521 Internal syntax error No result on the calculation stack.
    522 Circular reference A formula refers directly or indirectly to itself and the

    iterations are not activated under Tools/Options/Table

    Document/Calculate.
    523 The calculation procedure does not converge Some financial statistics functions miss a targeted value or

    iterations of circular references do not reach the minimum change

    within the maximum steps set.
    524 Invalid reference (#REF is in the cell rather than Err:524) Compiler: a column/row description name could not be

    resolved. Interpreter: in the formula, a cell is referenced whose column, row or

    table was deleted.
    525 Invalid name (#NAME is in the cell rather than Err:525) Compiler; An identifier could not be resolved (no reference,

    no domain name, no column/row description, no macro, etc.) Interpreter:

    same as runtime, e.g. basic function or AddIn, not found
    526 Internal syntax error Obsolete, no longer used, but could come from old documents if the result was a formula from a domain.
    527 Internal overflow Interpreter: references too encapsulated (cell references cell, the cell references, the cell references,...)

    Note: a '###' in a cell is not an error, but an indication that the column is too narrow to display the data properly. For more information read <a href="002.html">I see '###' in a cell that formerly held data. How can I see my data again?</a> Hint: The error code definitions live in the Help files.

     *In an OpenOffice.org window, go to the Help drop-down
    

    menu, then select Contents.

     *Select the Index tab by clicking on it.
     *In the Search input box, type error codes. An
    

    index list will pop up, with "error codes" in it.

     *Under the error codes section in the index list, double-click on OpenOffice.org
    

    Calc. This brings up the page of calc error codes.



    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 > 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:
    =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 > Sheet > 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?

    Insert a function into a cell by following these instructions:

     *Select the cell that will hold the function.
     *From the pull-down menus, select Insert > Function.
     *Pick Date&Time as the Category.
     *Double-click theToday 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 > 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

     *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, follow these instructions:

     *From the pull-down menus, select Edit > Headers &
    

    Footers...

     *Select either the Header tab or the Footer tab.
     *Click in the area where the page number blurb will display (Left,
    

    Center, or Right) to place the cursor inside the box.

     *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 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 32,000 rows, maximum. Spreadsheets converted from other applications that contain more than 32,000 rows will be truncated. Hint: Split large spreadsheets from other applications into smaller worksheets, so that each has fewer than 32,000 rows prior to converting them. A range containing fewer than 32,000 rows will convert correctly. For more information:


     *<a href="023.html">What
    

    is the maximum number of cells in an OpenOffice.org spreadsheet?</a>


    ==What is the maximum number of cells in an OpenOffice.org spreadsheet?== The maximum number of rows per sheet is 32,000. The maximum number of columns per sheet is 256. The maximum number of cells per sheet is 8,192,000. And the maximum number of sheets per workbook is 256. Where this can be a hindrance:


     *<a href="022.html">A
    

    large OpenOffice.org spreadsheet was converted from another

    application. Some of my rows are missing! What happened?</a>
Personal tools