cut and paste filtered cells (invisible cells only) from one column to another

Hi Ingrid,

For my knowledge simple copy/paste doesn'work with filtered/hided rows, actually you try to copy/paste non-adjustment cells. That could be few workarounds

1) Sort your column in ascending or descending order, copy first part (below zero) in one column and second one in another. If you need to return to initial order and had no another sorting field, create at very beginning helper column with ID (e.g. just fill it by sequential numbers), after finish copying sort back by this column.

Perhaps good enough for more or less static data.

2) You may split your column on two - select first column, in Ribbon menu under the Data click Text to Column item; on first screen of opened window select Delimited; on next one select Other and minus ("-") symbol, click Finish then.

Result will be like this

Negative numbers are converted to positive, but the are in separate column and you may interpret them as negative.

3) I'd prefer to use formula. If you have initial firs column, second will be for negative values and third for positive, add to second column cells (with proper reference on first column)

=IF([first column cell]<0,[first column cell],0)

and into the third column the formula with opposite condition.

After that apply to the cells in second and third columns custom format like this (use Ctrl+1 on selected cells)

#,##0.00;-#,##0.00;;@

Above will show cells with zeroes as empty ones:

If your initial data is relatively dynamic new/updated cells will go to new columns practically automatically, no need in repeating manipulations on entire data set as for first two options