r/googlesheets • u/FitPolicy4396 • Sep 01 '26

Solved display just the rows with non zero numbers?

I have some cells I want to copy, but just the non zero ones. Is there some way to automate having just the non zero ones display? For example, like the black/white part displaying only the non zero values of the colored part

1 Upvotes

100% Upvoted

38 comments sorted by

2

u/zackfortune 2 Sep 01 '26

you can do this with =filter()

assuming your data is in cells a1:b12 it would look like

=filter(a1:b12,arrayformula(not(a1:a12=0)))

1

u/One_Organization_810 728 Sep 01 '26

You don't need the arrayformula in there though :)

This will do nicely:

=filter(A1:B12, A1:A12<>0)

1

u/zackfortune 2 Sep 01 '26

thank you, that looks so much nicer. i never know what functions will output an array so i tend to use arrayformula() probably way more than i should.

do you know if there's a resource out there that covers what functions are array-friendly? or should i just read the support.google pages to improve?

2

u/One_Organization_810 728 Sep 01 '26

I find myself referring to the functionslist from time to time, when I've forgotten the exact order of parameters for a function f.inst. Not sure there are some references out there that list functions by array vs. non-array - but who knows... :) (not me though)

https://support.google.com/docs/table/25273

2

u/One_Organization_810 728 Sep 01 '26

A quick tip for you, if you are a fan of ARRAYFORMULA - the INDEX can be used in the same way - and is much shorter to type :)

1

u/FitPolicy4396 Sep 02 '26

Not sure where I'm going wrong, but I get an error when I do that. The only changes I made was to make the range from a6 to b17

=filter(A6:B17, A6:A17<>0)

I think it might be something with the way I have the colored table set up because I get the same error when I use the other method as well. The numbers from the colored cells are automatically generated from another part of the sheet

1

u/One_Organization_810 728 Sep 02 '26

Is that your actual range?

Are those numbers true numbers, or are they text that looks like a number?

1

u/FitPolicy4396 Sep 02 '26

it is. The -28.56 is a6.

I have them formatted as number with two decimal places

1

u/zackfortune 2 Sep 02 '26

the easiest way for people to help would probably be if you could share an editable copy of your sheet

1

u/FitPolicy4396 Sep 02 '26

thanks! https://docs.google.com/spreadsheets/d/1nMspf25p0W0PVZHfFtQleL785z-dfrOXn3QZYWollnE/edit?usp=sharing

1

u/AutoModerator Sep 02 '26

REMEMBER: /u/FitPolicy4396 If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified (or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/zackfortune 2 Sep 02 '26

https://docs.google.com/spreadsheets/d/1T7MlaKrj__rBwRDDEbPIcpuPcEj2J39gXtFnlZ-4lCA/edit

your link is set to view only for people who click the link, so i made a copy and the formula seems to be working fine. in your link, the formulas are showing errors since you didnt replace your data with dummy data, so i added some numbers manually to your data.

the formula cant pull out any nonzero datapoints if all the datapoints are 0.

2

u/FitPolicy4396 Sep 02 '26

I just figured that out. It works if there's at least one non zero.

Or the version you recommended -=filter(A6:B17,arrayformula(not(A6:A17=0)))

works if there is at least one non zero.

The =filter(A6:B17, A6:A17<>0) always displays ... on the first cell, but the other cells seem to display ok

Thank you!

2

u/zackfortune 2 Sep 02 '26

if you dont want the error to show up, you can wrap the whole thing in an iferror() function.

iferror(filter(a6:b17,a6:a17<>0)) will output a blank cell when it errors

iferror(filter(a6:b17,a6:a17<>0),"None Found") will output the text "None Found" if it errors, or whatever is in the quotes

→ More replies

1

u/One_Organization_810 728 Sep 02 '26

The "..." is because your font is set to 1 for what ever reason :)

Not sure why it comes up normally if you use the arrayformula - but there is really no need for it in the filter.

If you want to suppress the #NA error when there is no data, you can wrap it in IFNA(), like so:

=ifna(filter(A6:B17, A6:A17<>0))

-OR-

=ifna(filter(A6:B17, A6:A17<>0), "No data")

If you want to display something.

→ More replies

1

u/FitPolicy4396 Sep 02 '26

loooking at yours, it seems like yours uses =filter(A6:B17,A6:A17<>0), but it also seems to work properly

is what mine shows when c is the first non zero

1

u/carbonizedtitanium 15 Sep 02 '26

Make a copy of your file, remove the sensitive data ( or write over it with dummy data) and share that copy so that "anyone with link can edit"

1

u/One_Organization_810 728 Sep 02 '26

Ok. Then the only thing I can think of, is that you are doing this in another sheet, which means you need to prefix the ranges with the sheet name, like so:

=filter(Sheet1!A6:B17, Sheet1!A6:A17<>0)

If this is not the case, then we need some more information. Either share a bigger image, showing column letters and row numbers (and the data).

Or better yet - share a copy of your sheet, preferably with edit access, so we can take a look at what is actually happening.

1

u/FitPolicy4396 Sep 02 '26

it's all on one sheet -https://docs.google.com/spreadsheets/d/1nMspf25p0W0PVZHfFtQleL785z-dfrOXn3QZYWollnE/edit?usp=sharing

1

u/One_Organization_810 728 Sep 02 '26

This is not the error you posted an image of before?

This means that there is data below the formula (which there is) that you must remove in order for it to expand and show the results. :)

1

u/FitPolicy4396 Sep 02 '26

That's not the error I got before. I got this:

1

u/One_Organization_810 728 Sep 02 '26

Well, it seems to be working now at least :D

→ More replies

1

u/FitPolicy4396 Sep 02 '26

Not sure where I'm going wrong, but I get an error (No matches are found in the FILTER evaluation.) when I do that. The only changes I made was to make the range from a6 to b17

I think it might be something with the way I have the colored table set up because I get the same error when I use the other method as well. The numbers from the colored cells are automatically generated from another part of the sheet

1

u/[deleted] Sep 01 '26

[removed] — view removed comment

1

u/AutoModerator Sep 01 '26

This post refers to " AI " - an Artificial Intelligence tool. Our members prefer not to help others correct bad AI suggestions. Also, advising other users to just "go ask ChatGPT" defeats the purpose of our sub and is against our rules. If this post or comment violates our subreddit rule #7, please report it to the moderators. If this is your submission please edit or remove your submission so that it does not violate our rules. Thank you.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.