Tuesday, July 23, 2013

Filter Data With Formatting and Without Formula


( by Dave Viola )

I think the answer to my question is no, but I'll take a shot anyways. I'd like to filter a data range, returning only those rows that contain at least one value. However, I'd like the result to maintain the display formatting (specifically the fill color) of the original data. Here is an example:

The first column only contains labels, so I am filtering on the data in columns C:H. If I create a column I with row sums, either of these functions will return the correct values (but without formatting):

= FILTER( B2:H6I2:I6 > 0 )
= QUERY( B2:I6, "select B,C,D,E,F,G,H where I > 0" )

So, is it possible to maintain formatting using the filter() or query() functions, or via some other approach?



Have a look at the following animated screenshot to filter data within any range, that is by turning on filter ( and not with filter fomula ):

Select the range (above screenshot) and then turn on filter (you can do that by clicking on that Filter icon in Tool Bar menu) and then select the criteria that you need.

And now after filtering data, you can copy it and paste it either on the same sheet or on any other sheet, have a look at the following animated screenshot:

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:
If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 


No comments:

Post a Comment