May 12 2020 12:47 PM
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 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.
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,
' will hide columns of active worksheet listed in the first range line below
Selection.EntireColumn.Hidden = True
' unhideallcolumns Macro
' selects all cols of current worksheet and unhides them.
Selection.EntireColumn.Hidden = False
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: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.