Jump to content

 

Need help with an excel spreadsheet


jvaferreira
 Share

Recommended Posts

My capabilities with Excel are limited and I just can't figure this out.

 

Right now I have something like this

 

A - 1

B - 2

C - 3

D - 4

E - 5

 

and I want to make it so that if one value is zero it skips to the next one, so intead of having this

 

A - 1

B - 0

C - 3

D - 4

E - 5

 

I would have this

 

A - 1

C -3

D - 4

E - 5

 

Is that possible with Excel. Right now I have it so that if it's zero both cells are blank but there is still a space between the values when I print the page. If it's too complicated I'd rather just keep it as it is :o

Link to comment
Share on other sites

My capabilities with Excel are limited and I just can't figure this out.

 

Right now I have something like this

 

A - 1

B - 2

C - 3

D - 4

E - 5

 

and I want to make it so that if one value is zero it skips to the next one, so intead of having this

 

A - 1

B - 0

C - 3

D - 4

E - 5

 

I would have this

 

A - 1

C -3

D - 4

E - 5

 

Is that possible with Excel. Right now I have it so that if it's zero both cells are blank but there is still a space between the values when I print the page. If it's too complicated I'd rather just keep it as it is :o

 

Yes it's possible. Almost anything is possible with excel. :)

I can't help right now, but I'd be able to look at it at work tomorrow if you get no other responses.

Link to comment
Share on other sites

*snip*

The best way would be to use filters. It's super easy as you don't have to use any formula and it's non-destructive.

 

You should have a Filter or Sort & Filter button in the menu bar. Just activate the default filters there and it should add drop down menus on your column titles, where can you check/uncheck which values you want to see. Simple uncheck '0' and those lines won't be displayed anymore (but they're still there, and if you check '0' back, they'll reappear).

 

I don't know which version of Excel you use (and I doubt screenshots in French would be of any use to you), so in case my directions aren't clear or don't apply, you should be able find tons of tutorials online for this. ;)

Edited by kinjall
Link to comment
Share on other sites

There aren't any conditional formatting options for hiding cells when they are equal to zero (to my knowledge). You can right-click on either a row and column and hide it, which makes it not show up when you print the sheet, but also keeps the data there. You just can't see it unless you right-click again and unhide the row/column.

 

The best way would be to use filters. It's super easy as you don't have to use any formula and it's non-destructive.

 

You should have a Filter or Sort & Filter button in the menu bar. Just activate the default filters there and it should add drop down menus on your column titles, where can you check/uncheck which values you want to see. Simple uncheck '0' and those lines won't be displayed anymore (but they're still there, and if you check '0' back, they'll reappear).

 

I don't know which version of Excel you use, so in case my directions aren't clear or don't apply, you should find tons of tutorials online for this. ;)

 

Ooo this is a good idea too!

Edited by AerisFlowerGirl
Link to comment
Share on other sites

Yes it's possible. Almost anything is possible with excel. :)

I can't help right now, but I'd be able to look at it at work tomorrow if you get no other responses.

 

You have my attention :D

 

The best way would be to use filters. It's super easy as you don't have to use any formula and it's non-destructive.

 

I don't think filters would be useful in this case because I have other stuff on the same column a bit further down that I don't want to hide/filter

Link to comment
Share on other sites

As kinjall said, if you're just trying to hide rows that have a 0 in a specific column, a filter is the simplest route.

 

The zero rows will remain hidden if you print the sheet with the filter active.

 

~~~~~

 

EDIT (had this thread open for far too long apparently)

 

So you have rows further down with zeroes in that same column that you don't want to hide?

Edited by Slamma
Link to comment
Share on other sites

There's a script (or Module I think they call it there) you can use for excel, but it can be a little hit or miss and can be a right pain to actually setup. Alternatively, have you considered using Google Sheets? You can set a rule for conditional formatting that will remove the rows automatically.

Link to comment
Share on other sites

As kinjall said, if you're just trying to hide rows that have a 0 in a specific column, a filter is the simplest route.

 

The zero rows will remain hidden if you print the sheet with the filter active.

 

~~~~~

 

EDIT (had this thread open for far too long apparently)

 

So you have rows further down with zeroes in that same column that you don't want to hide?

 

I t could work with a filter, can I filter by row instead of column?

 

There's a script (or Module I think they call it there) you can use for excel, but it can be a little hit or miss and can be a right pain to actually setup. Alternatively, have you considered using Google Sheets? You can set a rule for conditional formatting that will remove the rows automatically.

 

Jumping between Excel and Google Sheets really, I'll have a check.

Link to comment
Share on other sites

It could work with a filter, can I filter by row instead of column?

Based on the example you used, I figured the data was in columns.

ColA ColB ColC

A 1 a

B 2 b

C 0 c

D 4 d

 

You'd filter the "0" out of ColB and be left with

A 1 a

B 2 b

D 4 d

 

You cannot use filters on rows, the filters are only usable on columns. If you want to filter by rows, I'd suggest just transposing your table so that you can use the filters.

Link to comment
Share on other sites

I don't think filters would be useful in this case because I have other stuff on the same column a bit further down that I don't want to hide/filter

I didn't know that and apparently V and Slamz didn't either, but it seems you can apply filters to a specified area (i.e. not the whole column). Just select the data with the rows you want to mask before clicking on the Filters button. As long as there's a blank row between your filtered area and the rest it should work, I just tested it myself (again, it might depend on which version you use). ;)

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
  • Create New...