Forum Discussion

mathetes's avatar
mathetes
Silver Contributor
May 12, 2020
Solved

Custom VIews--menu selection is grayed out-

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 f...
Excel on Mac
  • Alan_Murray's avatar
    Alan_Murray
    May 13, 2020

    mathetes

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

mathetes's avatar
mathetes
Silver Contributor
May 14, 2020

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.

sadasivam73's avatar
sadasivam73
Copper Contributor
Sep 15, 2020

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

  • mathetes's avatar
    mathetes
    Silver Contributor
    Sep 16, 2020

    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

     

     

     

    • sadasivam73's avatar
      sadasivam73
      Copper Contributor
      Sep 08, 2021

      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.

    • androo2351's avatar
      androo2351
      Brass Contributor
      Feb 16, 2021
      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.
      • mathetes's avatar
        mathetes
        Silver Contributor
        Feb 16, 2021

        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.

Resources