Import, export and sharing issues in spreadsheets
Form
fields
Combo
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.
Option
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.
Scroll
bars and spinners are now implemented in Calc, but are not imported
yet.
Array
constants
Array
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.
Optional
parameters in formulas
Some
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.
Formulas
with optional parameters are not correctly imported into Calc.
Functions
Statistical,
engineering and financial functions
In
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.
Analysis
ToolPak Functions
The
function EFFECT provided by the Analysis ToolPak for Excel is
converted to EFFECT_ADD in Calc and returns the same results.
Other
functions not implemented / imported
Only
three other functions are not imported: INFO, GETPIVOTDATA, and
HYPERLINK. Hyperlinks are supported but not as a function; use the
Hyperlink button on
the Function
bar.
DataPilot—Pivot
Table
DataPilots
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
limitations are:
There
is no PivotChart facility, but a DataPilot can be used as the data
source for a chart.
The
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.
There
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().
You
can not define different formats for different fields.
Calc
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).
The
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.
AutoFilter
AutoFilter
imports and exports correctly with the required rows being hidden.
However, there are a number of differences between Excel and Calc:
There
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.
The
remaining visible row numbers do not change color to warn that there
is a filter in place.
Error
values do not show as an option in the drop down lists when cells in
the column have errors.
In
the drop down lists, the equivalent to Excel’s “Custom” is
called “Standard”.
Charts
Chart
types that Excel provides but Calc does not:
Bar
of pie converts to a 2D – columns – normal.
Pie
of pie converts to a 2D – columns – normal.
Radar
filled converts to 3D – columns deep.
Radar
with markers – converts to 2D – net – normal but
counterclockwise rather than Excel’s clockwise.
Other charting
issues include:
There
are fewer axis options, such as no option for having an inverted y
axis (negative at top towards positive at bottom).
The
data must be contiguous, with the labels in the first row or column.
Number
formats
There
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.
There
is no predefined option for bracketing negative numbers. Use a custom
format such as “$#,##0.00_);[RED]($#,##0.00)”.
Grid
lines
In Excel grid lines
are a property of a worksheet; in Calc it is an application-wide
option:
Tools
> Options > OpenOffice.org Calc > View
