Ask Your Question

How to autofilter data range only in Calc? [closed]

asked 2015-11-25 07:03:35 +0100

printRick gravatar image

updated 2020-07-20 10:11:07 +0100

Alex Kemp gravatar image

I'm trying to sort a part of a table using auto filters but I can't figure out how to do it. I start with a table like this: image description

If I define a data range and then select and sort it by PRICE ascending, for example, it works fine: image description

(The data range used to sort the table is highlighted)

But if I enable autofilters on the very same data range and then click on the arrow at PRICE and choose "sort ascending" look at what happens:

image description

Not only did it sort rows not included in the data range (these got messed up because they are sums of the cells above and after the sorting they didn't have the same number of rows above), but it added them to data range as can bee seen in the highlighted part and if I were to sort them again without autofilters I'd have to modify/redefine the datarange.

My question is how to use Auto filters on a data range without having these kind of problems. E.g. it should sort the selected rows and not modify the range to include everything below the headers before sorting the data.


Version: - Build ID: 420m0(Build:2) - OS: Ubuntu 14.04.3 LTS 64-bit

PD: Sorry if the links to the screenshots appear twice but at first it didn't look like the "image" format would work so I put the links in text to make sure

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2020-07-20 10:11:31.659697


This bug is still present as of version (Both the ”rows not included in the (selected) range are sorted“ issue and the ”the range is changed/expanded“ one.) Build ID: 1:5.2.7-1+deb9u4

@printRick did you report the issue?

user4657 gravatar imageuser4657 ( 2018-12-06 15:57:55 +0100 )edit

3 Answers

Sort by » oldest newest most voted

answered 2015-11-29 09:25:17 +0100

pierre-yves samyn gravatar image


I reproduce the problem (on windows 7/64 &

Sort by auto-filter should apply by default to the data range (if it is defined) and, at the very least, it should respect the selected range (instead of extending to the contiguous range).

May be you can fill a report Issue (help here)?

Meanwhile, the workaround is to use DataSortthat applies (default) to the data range defined by DataDefine Range


edit flag offensive delete link more

answered 2019-12-16 08:18:29 +0100

morphingstar gravatar image

Automatically including additional rows outside the already defined range has been a nasty bug of LO (and may be: OO?); been there for ages.

Solution 1: Add at least 3 empty rows after the last valid table row included in the table range. Color these. Insert "_" into cells of the lower row as a cursor stopper for when you hit Ctrl+Down. Enter your formulae after these rows.

Solution 2: Put all results MAX, MIN, SUM, WHATNOTS above the LABEL ROW. I habitually use row 100 for column labels. My table data starts on row 101. Any calc's and strings processes are in rows 11 to 89, etc. like Validity cell ranges and similar stuff. Where possible above the column of the source data. Results show in rows 90 to 98. Row 99 shows label explanations, multi-line.

I use C as the first data column, B is color marked to see data range, A is empty to allow the cursor run down to sheet's end, then right right > > > and up to find the lowest / last occupied cell. This is convenient for my stuff.

Auto-ranging a table is unfortunately not possible with LO OO, your need ATARI 1000 spread sheet software to create this convenience. About year 1985. The same is true for auto-column width creation - both without (non-working OO LO) hand knitted macros.

Auto-ranging means recording a macro which will find out the data range: A table or a horizontal list of column widths. Simply recording, no programming! Then type-adapt range data without macro / programming. Modern and complicated or impossible versus old, simple/ and no longer available.

edit flag offensive delete link more

answered 2018-12-06 16:34:09 +0100

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

Seems to be this bug:

Another workaround is to temporarily insert an empty line right below the last row of the range, that is:

  • insert the temporary row
  • perform your filter actions
  • remove the row
edit flag offensive delete link more

Question Tools



Asked: 2015-11-25 06:59:05 +0100

Seen: 1,583 times

Last updated: Dec 16 '19