Issue 29738 - Print/display of zero values, tools/options and format/page
Summary: Print/display of zero values, tools/options and format/page
Alias: None
Product: Calc
Classification: Application
Component: ui (show other issues)
Version: OOo 1.1.2
Hardware: All All
: P3 Trivial with 2 votes (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
: 43629 (view as issue list)
Depends on:
Reported: 2004-06-01 08:45 UTC by rtrout
Modified: 2013-02-07 22:33 UTC (History)
4 users (show)

See Also:
Latest Confirmation in: ---
Developer Difficulty: ---


Note You need to log in before you can comment on or make changes to this issue.
Description rtrout 2004-06-01 08:45:01 UTC
There are 3 places in which the display and printing of zero values can be defined:

1. Tools/Options/Spreadsheet/Display-Zero Values
2. Format/Page/Sheet/Print-Zero Values
3. Format/Cells/Number/Format - a User-defined format allows up to three format
codes: positive values; negative values; zero. By default, a zero is shown, but
a null "" can also be used to force no display/printing.

The first method is obviously a user setting that applies to all documents, 2&3
apply only to the current document.

The logic in which these are applied is:
* on screen - if Tools/Options/Spreadsheet/Display-Zero Values is set, then look
at cell formatting, ie. it does not FORCE Zero Value display, rather the Cell
Formatting is used.
* printing - if Format/Page/Sheet/Print-Zero Values is set, then look at cell
formatting. Again this does not force printing, only allow the Cell Formatting
to be followed.

If either Tools/Options or Format/Page Zero Values settings are CLEARED, then
zeroes are not display/printed, regardless of cell formating.

By way of comparison, Excel 97 does not provide the Format/Page option, and
instead ALWAYS follows the cell formatting.

Issues 6933, 29422, 8201, 24051, 27738 and 11144 were all created over confusion
regarding the display and printing of zero values (there may be others - there
is little recording of duped issues on this topic) (I will leave it to others to
decide if these Issues should potentially be marked a dupe of this one, or vice

It is confusing to me the history of why there are 3 approaches to
display/printing of zeroes, in seemingly conflicting ways. The user-wide setting
is consistent with Excel and appears to be in common use. The cell formatting
approach is similarly available in Excel but is possibly less well-known or used. 

The OOo UI on first inspection would appear to FORCE zero display/print if the
Tools/Options or Format/Page settings are used, when in fact this is not the
case (as mentioned previously it sets whether Cell Formatting is followed).
While I accept that the UI terminology is simple (and should remain so) the user
is given no hint at the use of Cell Formatting to achieve a document-formatted

If Zero Value display/printing functionality was to be enhanced, options are:

1. Remove the Format/Page setting - the Format/Cells setting provides the same
functionality and is consistent with Excel.

2. Move the Format/Page setting to Tools/Options, so it is easily found.

3. Provide Zero Values Print and Display setting in Format/Page AND Tools/Options.

4. Change the UI so that the Format/Page and Tools/Options settings are 'Hide
Zero Values'. This inverts the current settings, but better implies the true
action, given that Cell Formatting is also at play.

Other possibilities exist.

For each of the above options there are three possible behaviours:
a) Force print/display of zero values OFF
b) Force print/display of zero values ON
c) Allow print/display of zero values based on Cell Formatting

In my opinion, the simplest solution is 4 above, with Help docs to better
highlight the inter-relationship between Format Cell, Format Page and Tools Options.

However any deviation from Excel's behaviour (inverting UI settings, OOo's
Format/Page feature) does place this obscure feature in no-mans land. As a
result my preference would be if OOo extends Excel's functionality then it
either extends it powerfully (options 3 AND 4, with a/b/c) or simply (options 4
Comment 1 frank 2004-06-01 13:52:32 UTC
Hi Bettina,

one for you.

Comment 2 frank 2005-02-28 18:06:38 UTC
*** Issue 43629 has been marked as a duplicate of this issue. ***
Comment 3 bettina.haberer 2010-05-21 14:50:24 UTC
To grep the issues easier via "requirements" I put the issues currently lying on
my owner to the owner "requirements".