4

I have a table in Google Sheets that lists products in rows and dependencies in columns. Most product depend on only a few things so there are a lot of columns showing extraneous information which makes it look at. I can hide all the columns not relevant to a given product, as well as all the rows not associated with the product (there are multiple rows for a product representing different pipeline stages).

However, if I want to switch products, I have to manually unhide everything and then manually rehide stuff for the new product. I thought I could use a filter view to save the current view, but when I switch to a different filter it doesn't remember what was hidden for that view.

How can I have different preset settings which each remember which rows and columns were hidden for that setting?

Improve this question

3 Answers 3

Reset to default
2

You could copy and transpose all the data, so that each product has a column, and all the dependencies are in rows below it.

This isn't generally good practice to organize data like this, but it would allow you to easily filter the dependencies, and then you could use filter views.

If you wanted to go a step further, you could save a filter view for each product.

Note: It is possible to set up a dynamic transposition from one range to another, in which case you could have two matching tabs with all the same data; one where you edit it and view it as desired with the filter views I described above, and another that's referencing that range and transposing it dynamically to the "best practice" way to organize data (Records in rows, and Dependencies / fields in columns).

Improve this answer
1

Try a pivot table. Select Data > Pivot Table. Pivot Tables allow you to select only what you want to see in each report.

Improve this answer
1
  • Pivot tables are very handy but could be tricky to replicate what is done by using filters and manually hiding rows/columns.
    – Rubén - Volunteer Moderator - ♦
    Commented Dec 11, 2019 at 18:25
0

You could use the macro recorder to record the column / row hiding. Maybe also to record the filter setup.

Then use another macro to remove the filter and un-hide all the columns and rows.

NOTES:

  1. Instead of filters you could try using filter views. They allow to save the filtering settings but not the column / row hiding made manually.
  2. The Google Sheets macro recorder could introduce redundant or unnecessary commands and might not record all the steps. If you know a bit of programming and JavaScript you might improve the recorded macro.

Other alternatives are

  • functions like QUERY FILTER, SORT and probably other
  • arrays formulas
  • pivot tables

References

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.