Custom Views or Excel Tables (Sophie’s Choice in Excel)
Custom Views or Excel Tables? This is really a lose – lose situation in Excel and therefore a true Sophie’s Choice. Let’s elaborate on the two commands.
Excel Table
A table is the best way to make Excel dynamic. If you format your range as a table, all your charts that would take data from that table would be dynamic. So you would never have to select the data for a chart when you input new data again. Your formulas also take on a new look. Instead of =SUM(A1:A100) you now have =SUM(Table1[Column1]) and that formula is dynamic. Among other benefits, the Total Row should be mentioned. An aggregation formula can be chosen from the dropdown list and in addition takes into account all your filters. In the picture bellow, you can also see that the title row replaces the Headings of columns. Instead of A you now see a “Column 1” title.
Above are just some of the reasons why you should use Excel Tables in Excel if that is in any way possible, particularly if you want to use Pivot Tables.
Custom Views
Now imagine that a certain Excel file is viewed by many different users, or being used by one user but in many various ways. Now once you need to filter your data by three conditions and sometimes you wish to have six totally different filters applied. Also sometimes you hide a few columns for printing purposes and at different times, you might wish to see all the columns or better yet, hide a different set of columns.
All this can easily be solved by using the so called Custom Views. You can find the command on the VIEW tab.
When you use the Custom Views command, you get the Custom View manager. 
Here you can Show different views or create a new Custom View by choosing the Add button. When you choose to create a new Custom View, you get the Add View window.
Here you give it a name but more important, here you set if you want this View to remember the current Hidden rows, columns and Filter settings and/or Print settings. If you do save all of them, the next time you want to use them, all you need to do is to regardless of the current view you have of your data, you just choose VIEW/ Custom Views, select the desired view and press Show. All saved settings will be applied instantly. Pure brilliance.
The Choice
But there’s a catch. If you only have one Table in your entire Workbook, you cannot use Custom Views. The command is inactive.
So it really is a Sophie’s Choice. You can only use one or the other but not both. And this goes even deeper. If you use any of the Power BI features, you are almost certain to use Excel Tables and therefore you already know, that you cannot use the Custom Views command. The disappointment is almost as great as the first time you figure out that you cannot use the =Table1[Column1] syntax to define a Data Validation list source. So this really is a great shame and has even gotten me to a point where I installed Visual Studio on my computer, started studying C# and VSTO to try to make it work myself 🙂 . The lengths we go to for a good cause 🙂
Learn more
Check out our YouTube channel and subscribe for more amazing Excel tricks!
Follow us on LinkedIn.
Check out our brand new R Academy!
Comments 21
Leave a Reply
Excel Unplugged acknowledgements
Donate
Nice article.
Most users are unaware of the Custom View option so they don’t miss it.
You probably know the workaround for the data validation list issue is to create a range name that is defined using the table syntax and use the range name to define the list for the data validation drop down.
@Neale
Yes, that is the way you get it done, but doesn’t it seem like just an additional hassle?
I am familiar with both tables and custom views, but apparently I never tried using them together as I wasn’t aware of that limitation. Interesting… I shall have to go off and play with this. Thanks for sharing the dilemma!
Am used to custom view in doing my things, But what I will like to know is computing grades using “if else” statement, I don’t thing it will work fine in table format. If it will be possible please show me how?
Example of what am saying =if(column A1>=60 “B”, else “C”) and multiple if in such manner
Merry, as far as formulas go, a Table is a great blessing. You can do anything you can do in a normal excel range but mostly much more efficiently and also much faster! The If formula is no exception, and yes you can have multiple If’s nested within a table just as you would in a normal Excel ranges…
To define a Data Validation list source you can use the command INDIRECT this way: =INDIRECT (“Table1[Column1]”)
My 2 cents
You *could* set up “custom-view” macros, but that would be even more of a p.i.t.a. for most people, especially getting all the original view settings caught so they could return to them.
@Lynda
I agree with you. But it bothered me so that at my company we are developing a custom Add-In and one of it’s features will be Custom Views that work with tables.
Let us know when the Add-On is done and how it worked out. I really love using Tables and having a Custom View option would be fantastic.
as far as making a ‘sophie’s choice, 90% of the time, I choose the Tables
For what it is worth – this does not appear to be the case with Mac Excel (2011). I use tables extensively and only recently learnt about Custom Views and think this feature is priceless. Was worried when I read this article – but quick testing seems to show no issues with custom views and tables. Even when the table is on the same sheet where the view is created – it seems to work. Perhaps this article may be referring to a older version of Excel on windows – otherwise, this would be a rare instance where Mac Excel has more functionality than the windows one!
Then it must be, since even on Excel 2016 it still works as described in this article so I guess you found the loophole for using Excel on Mac 🙂
I have run across a problem with custom views and wanted to share and ask about it in case there’s a solution I’m not finding when I Google the issue.
I created custom view for a report back in June. The header of this report has a title, fiscal year and an “as of date” that changes as needed. I update the “as of” date to, say, today’s date but when I select a custom view, the header reverts back to how it was when I saved that custom view so now the header shows me an old fiscal year and “as of” date, as well as any other font or title changes that happened since I created the custom view. Why does this happen and is there a fix?
I’m gonna use an old IT wisdom, that’s not a bug, IT’S A FEATURE… Since custom view works in a way where it remembers your headers and filter settings, it’s only logical that it reverts to the saved headers and filter settings so you should take this into consideration and find a different way to get your due date and fiscal year data in there…
“Custom Views” for Tables Add-In
https://sites.google.com/site/beyondexcel/project-updates/savesortsfilterscolumnsettings
Thanks, Craig…
How can I save a Custom View that I can reuse in a pivot table?
There is a large pivot table file of data that is updated daily. The format is the same, the #s change.
I would like to create a view to the specific items I want and apply it each day rather then manually filtering over and over.
I created the custom view, and added it to the tool bar, but when I click it and Show, it does not change the appearance of the spreadsheet/data.
Thanks!
Custom views work on regular filters, but not on Pivot Table Filters. The reason being that regular filters hide rows whereas Pivot Table filters just replace the filtered rows and don’t hide any physical rows.
What you would be looking for is a custom set of pivot table fields that can be saved when you create a Power Pivot data based Pivot Table.
My work-around on this is to write several lines of vba to simulate the custom view
Just to update for anybody reading this article – in 2021 sheet views now work in tables. Thank you MS – finally.
Exactly! Jan Thank you for pointing that out.