How do I Save a Specific Filter within a DataBase

Copper Contributor

I have a table of data with headers.  I can Sort this data, and I can filter this data.  What I would like to do is save a particular system of filtering this data, so that I can recall or re-use it later.  

 

In my research it appeared that this was the intent of "Custom Views."  I have encountered this instruction in various places including some How To video tutorials, Help topics, etc.  However, when I filter my data as I want and sort it, when I go to the View menu, "Custom Views" is grayed out and is not available.  I have not been able to find a reason why, and at least one of the suggestions truly seemed crazy - grayed out because there was data in a table ... of course there was data in the table.

 

I would like either help in finding out why Custom Views is grayed out and how to correct that, or how otherwise to save a particular filter/sort system for later use.

 

I am using Microsoft 365 Excel version 2109. Windows 10.

10 Replies

@KSNelson1 

Yes, Custom View doesn't work with structured tables, that's directly mentioned in support page Create, apply, or delete a custom view (microsoft.com)

 

Why so - philosophic question, you may check discussions like this Custom VIews--menu selection is grayed out- - Microsoft Tech Community

@Sergei Baklan 

If Custom Views no longer work with structured tables then is there a way to save specific sort/filter operations like Custom Views formerly did?

 

I would like to not have to go step by step rebuilding my sort/filter (Query?) process each time I have altered my data table.

 

 

PS: thanks for your response and help

@KSNelson1 

Custom Views never worked with structured tables, and it didn't work with Lists which was introduced before tables long ago.

 

If we speak only about sorting/filtering you may use Sheet Views Sheet Views in Excel (microsoft.com) if file is kept online (on SharePoint/OneDrive). 

With all due respect, I viewed a video that clearly showed, mentioned, and discussed using Custom Views with Tables. Apparently, it was an earlier version, perhaps several versions earlier, but I would hesitate from saying Custom Views never worked with Tables. I can provide the video name and the time within the video when such a reference was made.

I will explore the Sheet Views option.

Again, thank you for your help.

@KSNelson1 I appreciate if you give the reference, will be very sorry then.

https://youtu.be/5DwKhLVnMEg at 10:42

(This should be a video titled "Excel Database Part 1 - How to Use Excel as a database - Excel 2010, 2013, 2016 database tutorial")

@KSNelson1 

Thank you, but I see no table in this video. Range with frozen first row and filtering is used.

Does that mean the answer to my original and remaining question is: convert this table to a range?

@KSNelson1 

An opposite - if you'd like to use Custom Views work with ranges, not with structured tables. Or use Sheet Views, they keep filtering of tables.

When I converted this table to range and pasted it into a new file, the Custom Views operated as expected. And this is what I meant by saying the answer to my original question was to use ranges not tables. I will try Sheet View and see if it accomplishes the similar goal using tables.