May 12 2020 12:47 PM
I have a spreadsheet in which I'd like to create a Custom View, but that option is grayed out on the menu and toolbar. It appears on some other (most other) workbooks I have. I've not been able to find an explanation in Excel Help for why in this case it would not appear as well.
It IS a workbook stored on OneDrive and shared with another person. That's a distinctive feature in this case; the only one I can think of.
May 13 2020 06:41 AM
SolutionIf you have data in a Table on any worksheet, then the Custom Views is not available.
May 13 2020 05:48 PM
Thanks. I never knew....and couldn't find a reference in Help that pointed in the direction of anything like that.
Fortunately I ended up being able to skin this particular cat another way, thanks to all the many ways Excel offers. And what I did was probably more effective any way. But oh, my, I have Tables in most of my workbooks, so I guess Custom View may become a thing of the past.
May 13 2020 10:56 PM
I think you are right there. Formatting data in a table is very useful so Custom Views is as you say now 'a thing of the past'.
Nice work buddy.
Sep 14 2020 09:39 PM
Hi,
could you pl share on what is that you have done as alternative to custom view with Table formats present in Excel. It will be of help to me, as i too have the same issue.
thanks
sadasivam73
Sep 16 2020 05:12 AM
I'm sorry to say that I don't even remember what workbook I was working on back in October when I posted that (my first question on this site).... since then I've discovered so many other new (new to me, that is) features in Excel that I've not even missed Custom View.
So here's a short list of some of the things that I might have had in mind at the time.
More recently, if you have the most recent version of Excel, there's a set of Dynamic Array functions that are really useful, and these work especially well with Tables
Here's a link to a YouTube video that serves as an excellent intro to these Dynamic Array functions: https://www.youtube.com/watch?v=9I9DtFOVPIg
Feb 16 2021 02:13 AM
Feb 16 2021 05:43 AM
@androo2351wrote: Probably the most useful thing that custom views did, which no other out of the box feature seems to do, is present a view of a very wide (many columns) table with some or many columns selectively hidden. I don't think any of the above methods are an alternative to that.
I avoid macros as much as possible...generally finding ways with built-in functions to accomplish things. In this case, FILTER can be used to produce "custom views" of a table; in a very straightforward way selected rows, but it can also do selected columns.
Feb 16 2021 05:57 AM
@mathetes you can't hide columns with filter though. A macro that hides columns and creates a view is very quick and easy to make or use/adapt these,
Sub hidecols()
'
' will hide columns of active worksheet listed in the first range line below
'
Range("D:D,E:E,F:F,G:G,H:H,J:J,K:K,L:L,N:N,U:V,X:AA,AH:AJ").Select
Selection.EntireColumn.Hidden = True
End Sub
Sub unhideallcolumns()
'
' unhideallcolumns Macro
' selects all cols of current worksheet and unhides them.
Columns("A:XFD").Select
Selection.EntireColumn.Hidden = False
End Sub
To create views with particular rows visible or hidden there's now a built in feature called sheetview on the view menu. That said with macro's you can create views based on hiding columns and selecting/hiding certain rows.
Feb 16 2021 06:00 AM
@machetes I just noticed that you said filter can hide columns, are you sure? How?
Feb 16 2021 06:12 AM
Maybe more accurately, FILTER can be used to accomplish it, but not in a straightforward manner. First, for the sake of clarity, I'm talking about the FILTER function, only available in the newest versions of Excel. Second, you would need to use FILTER several times in parallel, selecting the columns you want, omitting the columns you don't want. In other words, depending on the circumstances, two or more FILTER formulas would be written to create a dashboard's selective view.
I miss Custom Views.
Feb 16 2021 06:21 AM
@mathetes I see. I suppose it depends on preference, if I'm just viewing data I'd use a pivot and one or more slicers or perhaps use power query with a/some parameter/s - so I've never felt the need to use the filter function. But, good to know I guess.
Sep 07 2021 11:09 PM
@mathetes Thanks for your answer. However as mentioned in the next message of the same thread, i would like to organize a bigger excel sheet with few columns hidden and few filters applied to few columns, and then edit the data. For this i understand that custom views if the only option, based on my limited knowledge. If there is any other option that you are aware of, please share.
Feb 04 2023 02:14 AM
Feb 04 2023 02:53 AM
I realise this is from a long time ago and we have all moved on over the past two years.
Just in case any one else seeks inspiration from this discussion, the headings on the output array can be used to generate the filter criterion
= FILTER(Table1, COUNTIFS(filterHeaders, Table1[#Headers]))
May 02 2023 11:10 AM
@mathetes so if you are trying to import the data into SQL, and you have "other data" on the sheet, using a custom view was a way to do this, however, as mentioned if tables exist you can't also use custom views, so what is the answer to then importing to SQL without the ability to use them...as the WHOLE sheet is not an option....
Jason
May 02 2023 11:19 AM
May 13 2020 06:41 AM
SolutionIf you have data in a Table on any worksheet, then the Custom Views is not available.