Deletion and Formatting of non-filtered Selections in Calc

OpenOffice.org 2.0

Document - ID	Specification Owner	Last Change	Status
	Falko Tesch	26.08.2004	Final
Conforms to	StarOffice Specification Workflow v0.1, Aug 2003
Applies to	Calc
Task ID(s)	i2977
Category	Feature

Abstract

Many users that use filtering in Calc want to modify or delete non-filtered content. In Calc 1.x also the content “between” the non-filtered selection was quietly affected by these actions.

In Calc 2.0 the behaviour will now change for filtered selections to ease deletion and formatting on non-filtered cells.

i-Team Members (The specification owner is part of the i-Team)

	Name	E-mail Address
User Experience	Falko Tesch	Falko@sun.com
Development	Niklas Nebel	Niklas.nebel@sun.com
Quality Assurance	Frank Stecher	Frank.stecher@sun.com
Documentation	Uwe Fischer	Uwe.fischer@sun.com

Approved for Implementation

	Approved by	Date
User Experience	Falko Tesch	26.04.2004
Development	Niklas Nebel	27.04.2004
Quality Assurance	Frank Stecher	27.08.2004
Documentation	Uwe Fischer	8/26/04
String Review	N/a	

Document Change History

Rev. Level	Change	Initials	Date
1.0	Initial specification	FT	26.08.2004
1.1	Review done by Documentation -> approval	UFI	26.08.2004
1.2	All approvals arrived -> Final	FT	27.08.2004

Contents

Glossary	1

1	Motivation	1

2	User Scenarios	1

3	Goals	1

4	Requirements and Dependencies	1

4.1	Requirements	1

4.2	Technical Dependencies	1

5	Competitive Analyses	1

6	Detailed Specification	1

6.1	G11n	2

6.2	Error Conditions	2

6.3	Migration	2

7	Future Tasks	2

8	Notes	2

9	References	3

Glossary

Term	Description
Non-filtered selection	A selection made on cells/rows that are not hidden by an applied filter

Motivation

Many users that use filtering in Calc want to modify or delete non-filtered content. In Calc 1.x often data loss was the consequence since content “between” the non-filtered selection was also quietly affected by these actions.

User Scenarios

A user uses the AutoFilter functionality of Calc to filter a long list of names. He chooses “Smith” to be filtered so that the list will only show all “Smith”s found.

Unfortunately the initial list is nor alphabetically sorted to all entries with “Smith” are scattered all of the list.

The user now selects the non-filtered selection of all occurrences of “Smith” and deletes all of these rows. What the user did not see is, that also all in-visible (filtered-out) content in between the first and the last “Smith” entry is also deleted. He only detects this long after doing a lot of other editing and finally disabling the AutoFilter.

But since a lot further editing has been done the undo action for undeleting the involuntarily deleted data is not available anymore and an irreversible data loss has occurred.

Goals

In Calc 2.0 the most required actions, deletion and formatting of cell, will be addressed by this specification.

Requirements and Dependencies

Requirements

None

Technical Dependencies

none

Competitive Analyses

MS Excel

MS Excel exhibits the following behaviour when editing in non-filtered selections:

Create a blank spreadsheet with row 1 containing 2 column headers, e.g. A1='Item' and B1='What'

Fill columns A2 to A16 with numbers 1 to 15

Add a text 'XX' to columns B5, B10 and B14 only

Turn on AutoFilter: -standard-what=-empty- This should hide rows 5, 10 and 14.

Select A1:B16, COPY and paste to a new worksheet.

Result: Only visible cells are copied (12 rows total=15 rows excluding B5,B10,B14)

Keep the above and hide row 7.

Select A1:B16, COPY and paste to a new worksheet.

Result: only visible cells are copied (11 rows total=15 rows excluding 5, 10, 14 and 7)

Keep above and use the PivotTable/Outline function to group row 12 and hide it.

Select A1:B16, COPY and paste to a new worksheet.

Result: only visible cells are copied (10 rows copied=15 rows excluding 5, 10, 14, 7 and 12)

Keep the above but remove the AutoFilter

Select A1:B16, COPY and paste to a new worksheet.

Result: all rows are copied.

Return to step 4 (unhide hidden rows and Outline Group, apply filter)

Select A1:B16, CUT and paste to a new worksheet.

Result: ALL rows are moved.

Keep above (undo CUT or move data back) and hide row 7.

Select A1:B16, CUT and paste to a new worksheet.

Result: ALL rows are moved.

Keep above (undo CUT or move data back) and use the PivotTable/Outline function to group row 12 and hide it.

Select A1:B16, CUT and paste to a new worksheet.

Result: ALL rows are moved.

Return to step 4. (unhide hidden rows and Outline Group, apply filter).

In empty cells in column B enter O (enter individually – don't use paste!)

Select A5:B8, DELETE, then remove filter to see effect of DELETE.

Result: Only selected visible cells (rows 5, 7 and 8) were DELETE'd

Return test data to state after 19.

Hide row 7

Select A5:B8, DELETE, then remove filter to see effect of DELETE.

Result: Only selected visible cells (row 5 and 8) were deleted.

Return test data to state after 19.

use the PivotTable/Outline function to group row 7 and hide it.

Select A5:B8, DELETE, then remove filter to see effect of DELETE.

Result: Only selected visible cells (row 5 and 8) were deleted.

Return test data to state after 19.

Remove AutoFilter

Hide row 7

Select A5:B8, DELETE, then remove filter to see effect of DELETE.

Result: Rows 5-8 deleted.

Detailed Specification

All of the below only applies to filtered ranges that are created by using “AutoFilter”, “Standard Filter” or “Advanced Filter”.

It does not apply to cell range with manually hidden cells.

Deletion of cells and rows

In case of deletion of non-filtered cells or rows all possible hidden cells/rows will not be affected by the deletion process.

Apply Formatting/Styles

In case of applying formatting or new Styles to non-filtered cells or rows all possible hidden cells/rows will not be affected by the formatting process

G11n

N/a

Error Conditions

N/a

Migration

N/a

Future Tasks

An deeper investigation on currently left-out option such as paste is to be considered in future releases of Calc.

Notes

References

Deletion and Formatting of non-filtered Selections in Calc

Page 3

Http://specs.openoffice.org/calc/ease-of-use/editing_of_non-filtered_selections.sxw

Deletion and Formatting of non-filtered Selections in Calc

Falko Tesch

2004-08-26T13:59:21

Matthias Müller-Prove