Import, export and sharing issues in spreadsheets
boxes, list boxes and checkboxes with links to spreadsheet cells all
import and export properly, although due to font handling the number
of rows displayed in list boxes may be slightly different. List boxes
in Calc can either have multi-selection or not, whereas Excel has two
multi-selection modes: multi and extend. In Excel a link from a
multi-selection listbox to a cell is ignored (at least in Excel 2000;
the author has not checked Excel XP). In Calc the link works, but if
more than one item is selected, the cell’s value is set to #N/A.
buttons import into Calc, but the link with cells works differently.
In Calc each option button has its own link to a cell, setting its
value to true or false depending on whether the option is selected.
In Excel the cell link returns the number of the selected option
button. On exporting option buttons to Excel, the option buttons lose
their radio operation; in other words, they are not grouped so that
selecting one “unselects” the others.
bars and spinners are now implemented in Calc, but are not imported
constants are not available in OpenOffice.org. The workaround is to
have the constant values in cells on a sheet and refer to them. Array
constants used as parameters to a function in Excel are omitted when
imported into Calc.
parameters in formulas
formulas have more than one optional parameter at the end of the
parameter list. In Excel it is possible to leave an optional
parameter blank and specify a later parameter, but this is not
possible in Calc.
with optional parameters are not correctly imported into Calc.
engineering and financial functions
Excel some of the financial functions accept string parameters for
dates; in OpenOffice.org they require a serial number, and thus they
are not correctly imported into Calc.
function EFFECT provided by the Analysis ToolPak for Excel is
converted to EFFECT_ADD in Calc and returns the same results.
functions not implemented / imported
three other functions are not imported: INFO, GETPIVOTDATA, and
HYPERLINK. Hyperlinks are supported but not as a function; use the
Hyperlink button on
are Calc’s equivalent to Excel’s Pivot Tables. However there are
a number of limitations. Pivot tables import from Excel into Calc
(although the cells do not have the PivotTable shading) but as soon
as they are worked on these limitations become apparent. The
is no PivotChart facility, but a DataPilot can be used as the data
source for a chart.
user interface allows a maximum of 8 fields in the data, row or
column areas. (An alternative interface which allows more is
available from: https://homepages.paradise.net.nz/hillview/OOo/
in a document called MyDataPilot.sxc.
is some ability to group data (for example, a date field grouping by
week or quarter but not by month). The workaround is to create a new
column with a formula for calculating the grouping, e.g. =Month().
can not define different formats for different fields.
can have formulas based on things like “Difference From” or “%
of”, but can not have own custom fields (work around create new
columns to calculate the desired values).
Application Program Interface specification is incomplete for
writing macros that work with DataPilots—for example, the ability
to control through another program, such as OpenOffice.org’s own
macro language, BASIC, whether the tables have grand totals, or
having access to DataPilots created from external data.
imports and exports correctly with the required rows being hidden.
However, there are a number of differences between Excel and Calc:
is only one AutoFilter active for a spreadsheet document at a time.
The rows remain hidden but the drop-downs in the first row disappear
on applying AutoFilter elsewhere.
remaining visible row numbers do not change color to warn that there
is a filter in place.
values do not show as an option in the drop down lists when cells in
the column have errors.
the drop down lists, the equivalent to Excel’s “Custom” is
types that Excel provides but Calc does not:
of pie converts to a 2D – columns – normal.
of pie converts to a 2D – columns – normal.
filled converts to 3D – columns deep.
with markers – converts to 2D – net – normal but
counterclockwise rather than Excel’s clockwise.
are fewer axis options, such as no option for having an inverted y
axis (negative at top towards positive at bottom).
data must be contiguous, with the labels in the first row or column.
is no “accounting” format (with the currency symbol at the left
edge of the cell). A workaround would be to have another column to
the left that contains the currency symbol.
is no predefined option for bracketing negative numbers. Use a custom
format such as “$#,##0.00_);[RED]($#,##0.00)”.
In Excel grid lines
are a property of a worksheet; in Calc it is an application-wide
> Options > OpenOffice.org Calc > View