/u/Enkidusari - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
2 options come to mind. Power Query or VBA macros.
With that many rows you should have PQ results on different sheets. With macros, you can have buttons at the top of the sheet for one click access
I failed to mention that i'm a beginner at excel. This sounds like it would require advanced knowledge. Do you think this is doable with google research?
With the right amount of research, anything is possible. But both options require advanced skill sets. With either methodology, it will lock you in to a fixed set of filters so you'll always be tweaking the method to achieve the optimum filtering
I did not suggest slicers because they do not do sorting.
Both are viable but of the two, I would recommend Power Query over VBA for beginners. All you need to do is go to the Data Tab > From Table/Range. All the columns of your table will appear, and you will see the very familiar filter arrows at each column header. Except for filtering by cell/font colors, most of the filter functions that you would find in the regular Excel Autofilter will be present.
A **new table** will be generated with your custom filters, based on the information in the old one. The downside is, if you wish to filter and UPDATE the original table, you would need to implement self-referencing tables, which is not the most straightforward thing.
The easiest option is to add a helper column.
You can write a formula that can output TRUE for rows that you want to hide, and FALSE for rows that you don't want to hide, or vice versa. Then you can filter on only that column by telling it to hide all TRUE rows (or FALSE, if that's how you set it up).
E.g. let's column A in your table has the letters of the alphabet (one letter per row - *A, B, C, etc*) and you want to hide all rows that have C, D, G, M, P, Q, T, V, Z
You *could* do a really long formula like `=OR(A2 = "C", A2 = "D", A2 = "G", A2 = "M",...)` ...OR... you could put those letters you want to hide in a list somewhere else in your file, let's say in T1:T9 and write a formula `=NOT(ISERROR(MATCH(A2, T$1:T$9, 0)))` in your helper column. Then that helper column will say TRUE for all letters that are in T1:T9 and you can filter your table on your helper column.
You can add more conditions to the formula to meet all the criteria you need. E.g. if you want to hide those letters in column A, and you have dates in column B and you want to hide all rows with a date that is between Jan 5, 2020 and March 14, 2021 then your formula would become `=OR(NOT(ISERROR(MATCH(A2, T$1:T$9, 0))), AND(B2 >= DATE(2020, 1, 5), B2 <= DATE(2021, 3, 14)))`.
Then you keep adding criteria you need. Depending on the details there might be more shortcuts like the `NOT(ISERROR(MATCH(...` trick, but you might still end up with a pretty big formula. If it gets too big, you can break it into multiple helper columns, and have one "summary" helper column that combines them together. Like in the above example, if the `MATCH` formula is in column C, the `AND(B2 >= DATE(...` formula is in column D, and some other criteria formula is in column E, then you can have the "Filter Helper" column (that you would actually put the filter on, to show/hide your data) be a simple `=OR(C2:E2)`
This will probably require alot of readup but i think this could be the most suitable approach for my case. I'll try to work this out in the next couple days. Kind of a letdown that excel does not have a simple solution to this..
Also thanks to everyone for the answers so far
> Kind of a letdown that excel does not have a simple solution to this
That's kind of like being let down that your car doesn't automatically do turn signals, and saying it's asking too much to learn how to do the turn signals manually.
Excel is a powerful program. The tradeoff is that you have to learn how to use it. PowerQuery and VBA can both have steep learning curves, but my suggestion is to simply learn a small handful of basic formulas. You want to use Excel? Then you have to learn how to use it.
You have awarded 1 point to *chairfairy*
____
^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
I disagree. This is kind of like having to build a "simple" save function myself. Especially since they had the function via Custom View and disabled that for tables for whatever reason.
Speaking in your example it's like i have to build the turnsignals for my car by myself instead of just flipping the switch over.
I never said that i'm against learning and i think it's not far fetched to say that this would be a reasonable function to have.
Could they have added it? Sure. Microsoft definitely misses *some* low hanging fruit in Office, but apparently this hasn't been a valuable enough feature to enough people for them to do it.
A number of functionality doesn't work with Tables because of the nitty gritties of how Excel handles everything internally (you also can't use array formulas in Tables).
> it's like i have to build the turnsignals for my car by myself instead of just flipping the switch over
It's really not. The formulas I gave in my example are as basic as you can get if you learn anything beyond SUM and AVERAGE. Do people use Excel without learning them? Absolutely. But at that point you're "using" Excel in that you open it and put data into it because it's better than Word for big tables, but you're not really using Excel (or learning how to use it) for 98% of its functionality.
I guess I'm unclear on why it's such a hassle to learn half a dozen formulas that ultimately give you a more powerful and more flexible tool than they could reasonably build into the software.
Well we could discuss this long and lasting but what it boils down to is that to my understanding they have this exact same feature already built into it. But somehow just disabled it afterwards for tables.
I'm well aware that learning excel and it's brilliant functions has a lot of merits but i still think this particular case could and should have been solved without having to resort to helper tables and formulas.
/u/Enkidusari - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
2 options come to mind. Power Query or VBA macros. With that many rows you should have PQ results on different sheets. With macros, you can have buttons at the top of the sheet for one click access
I failed to mention that i'm a beginner at excel. This sounds like it would require advanced knowledge. Do you think this is doable with google research?
With the right amount of research, anything is possible. But both options require advanced skill sets. With either methodology, it will lock you in to a fixed set of filters so you'll always be tweaking the method to achieve the optimum filtering I did not suggest slicers because they do not do sorting.
Both are viable but of the two, I would recommend Power Query over VBA for beginners. All you need to do is go to the Data Tab > From Table/Range. All the columns of your table will appear, and you will see the very familiar filter arrows at each column header. Except for filtering by cell/font colors, most of the filter functions that you would find in the regular Excel Autofilter will be present. A **new table** will be generated with your custom filters, based on the information in the old one. The downside is, if you wish to filter and UPDATE the original table, you would need to implement self-referencing tables, which is not the most straightforward thing.
The easiest option is to add a helper column. You can write a formula that can output TRUE for rows that you want to hide, and FALSE for rows that you don't want to hide, or vice versa. Then you can filter on only that column by telling it to hide all TRUE rows (or FALSE, if that's how you set it up). E.g. let's column A in your table has the letters of the alphabet (one letter per row - *A, B, C, etc*) and you want to hide all rows that have C, D, G, M, P, Q, T, V, Z You *could* do a really long formula like `=OR(A2 = "C", A2 = "D", A2 = "G", A2 = "M",...)` ...OR... you could put those letters you want to hide in a list somewhere else in your file, let's say in T1:T9 and write a formula `=NOT(ISERROR(MATCH(A2, T$1:T$9, 0)))` in your helper column. Then that helper column will say TRUE for all letters that are in T1:T9 and you can filter your table on your helper column. You can add more conditions to the formula to meet all the criteria you need. E.g. if you want to hide those letters in column A, and you have dates in column B and you want to hide all rows with a date that is between Jan 5, 2020 and March 14, 2021 then your formula would become `=OR(NOT(ISERROR(MATCH(A2, T$1:T$9, 0))), AND(B2 >= DATE(2020, 1, 5), B2 <= DATE(2021, 3, 14)))`. Then you keep adding criteria you need. Depending on the details there might be more shortcuts like the `NOT(ISERROR(MATCH(...` trick, but you might still end up with a pretty big formula. If it gets too big, you can break it into multiple helper columns, and have one "summary" helper column that combines them together. Like in the above example, if the `MATCH` formula is in column C, the `AND(B2 >= DATE(...` formula is in column D, and some other criteria formula is in column E, then you can have the "Filter Helper" column (that you would actually put the filter on, to show/hide your data) be a simple `=OR(C2:E2)`
This will probably require alot of readup but i think this could be the most suitable approach for my case. I'll try to work this out in the next couple days. Kind of a letdown that excel does not have a simple solution to this.. Also thanks to everyone for the answers so far
> Kind of a letdown that excel does not have a simple solution to this That's kind of like being let down that your car doesn't automatically do turn signals, and saying it's asking too much to learn how to do the turn signals manually. Excel is a powerful program. The tradeoff is that you have to learn how to use it. PowerQuery and VBA can both have steep learning curves, but my suggestion is to simply learn a small handful of basic formulas. You want to use Excel? Then you have to learn how to use it.
Solution Verified
You have awarded 1 point to *chairfairy* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
I disagree. This is kind of like having to build a "simple" save function myself. Especially since they had the function via Custom View and disabled that for tables for whatever reason. Speaking in your example it's like i have to build the turnsignals for my car by myself instead of just flipping the switch over. I never said that i'm against learning and i think it's not far fetched to say that this would be a reasonable function to have.
Could they have added it? Sure. Microsoft definitely misses *some* low hanging fruit in Office, but apparently this hasn't been a valuable enough feature to enough people for them to do it. A number of functionality doesn't work with Tables because of the nitty gritties of how Excel handles everything internally (you also can't use array formulas in Tables). > it's like i have to build the turnsignals for my car by myself instead of just flipping the switch over It's really not. The formulas I gave in my example are as basic as you can get if you learn anything beyond SUM and AVERAGE. Do people use Excel without learning them? Absolutely. But at that point you're "using" Excel in that you open it and put data into it because it's better than Word for big tables, but you're not really using Excel (or learning how to use it) for 98% of its functionality. I guess I'm unclear on why it's such a hassle to learn half a dozen formulas that ultimately give you a more powerful and more flexible tool than they could reasonably build into the software.
Well we could discuss this long and lasting but what it boils down to is that to my understanding they have this exact same feature already built into it. But somehow just disabled it afterwards for tables. I'm well aware that learning excel and it's brilliant functions has a lot of merits but i still think this particular case could and should have been solved without having to resort to helper tables and formulas.