SOLVED

Custom VIews--menu selection is grayed out-

Silver Contributor

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.

17 Replies
best response confirmed by mathetes (Silver Contributor)
Solution

@mathetes

If you have data in a Table on any worksheet, then the Custom Views is not available.

@Alan Murray 

 

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.

@mathetes

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.

@mathetes 

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

@sadasivam73 

 

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.

  • Using the various D____ functions to extract data from a table (DSUM, DGET, DAVERAGE....)
  • Using INDIRECT extensively for references to specific cells on other sheets in the same workbook

 

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

  • FILTER--this one has some features that might come closest to "Custom View"
  • UNIQUE--which does what you might expect
  • SORT--likewise

 

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

 

 

 

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, but were limited to power users. With Dynamic Arrays we have rebuilt the calc engine, effectively turning all formulas into array formulas ...
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. However, what is is, writing or recording a macro to create the view you need with hidden columns, or alternatively, using power query to create as many cut down views of the data as you need. Only the first method is any use if you want to edit the data.

@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.

@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.

 

@machetes  I just noticed that you said filter can hide columns, are you sure? How?

@androo2351 

 

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.

@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.

works well for me, thank you!

@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.

Hello Alan Murray
I have not any data on any other worksheet, even after that , custom views is not working.

@mathetes 

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]))

 

@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

I've never imported data into SQL so really can't even begin to answer your question. May I suggest that you start a new thread on the main board with a description of your situation, the task or goal, and your question. That will enlist the help of others who may have dealt with importing into SQL
1 best response

Accepted Solutions
best response confirmed by mathetes (Silver Contributor)
Solution

@mathetes

If you have data in a Table on any worksheet, then the Custom Views is not available.

View solution in original post