PHD reader and commenter Vishy contributed this post through e-mail. Thank you so much Vishy for this very useful tip.
The Problem:
- You have created a specific view of your data in Excel (say by filtering, zooming out, changing column width, hiding specific rows, customizing window settings, print settings etc.). This is your reference point say view ABC.
- Then, you change some of it (say remove certain filters, change column width etc.), and then some more.
- ABC view is so helpful as a reference that you need to revisit that view over and over again after changing it in different ways (say applying different filters each time)
Solution: Custom Views in Excel
- Define and use Custom Views (a set of display and print settings that you can name and apply to a workbook)
Create a custom view in Excel 2003, 2007
-
Change the settings that you want to save in the view. - (2003) View menu >> Custom Views
- (2007) View tab >> Workbook Views group >> Custom Views
- Select Add
- In the Name box, type a name for the view (Make sure to include the active sheet name in the name of a view to make it easier to identify).
- Under Include in view, select the options you want.
Change the settings that you want to save in the view.Activate a Custom View to See it
- (2003) View menu >> Custom Views
- (2007) View tab >> Workbook Views group >> Custom Views
- In the Views box, select the name of the view you want, and then select Show.
Delete a custom view
- (2003) View menu >> Custom Views
- (2007) View tab >> Workbook Views group >> Custom Views
- In the Views box, select the name of the view you want, and then select Delete.
You can use a custom view to save specific display settings (such as column widths, row heights, hidden rows and columns, cell selections, filter settings, and window settings) and print settings (such as page settings, margins, headers and footers, and sheet settings) for a worksheet so that you can quickly apply these settings to that worksheet when needed.
You can also include a specific print area in a custom view. You can create multiple custom views per worksheet, but you can only apply a custom view to the worksheet that was active when you created the custom view. If any worksheet in the workbook contains an Excel table (2007) or Excel list (2003), the Custom Views command will not be available anywhere in the workbook.
PHD’s note: Once again thanks to Vishy for sharing this idea. Please drop your comments here to share your questions or love, I am sure Vishy will respond.
24 Responses to “Custom Views in Excel – Save filter and header settings for quick reference”
Thank u so much Mr.Chandu and Mr.Vishy for providing another tip in my Excel game.
[...] Get Full Tip 56. To save data filter settings so that you can reuse them again, use custom views… Get Full Tip 57. To select all formulas, press CTRL+G, select “special” and check [...]
Hi, is it possible to create hyperlink to a custom view? Please and thank you!
Thank you for an excellent solution to the problem that I was having.
Thank you for sharing this. I'm using Excel since years, never used this.
It might be very usefull!
Yes its possible, not as a direct way.
You can right click on the menu bar>>select customize>>select view in the categories >> drag and drop the customize view in the menu bar.
>>>>>>>> Hi, is it possible to create hyperlink to a custom view? Please and thank you!
Is there a way to use custom views for different worksheets with the same content? When I copy-paste somethings in a new workbook it would be extremely convenient to select a created custom view. This doesn't seem to work or do I do something incorrect?
This seems like a good tip, but I am confused. How can I filter data in columns without setting it up as a table?
For some reason my Custom Views button is grayed out and I can't select it. Do you know why that would be?
@Jesse Walker... I just discovered that if you have a Table, Custom Views won't work. Go to the Table Layout screen and convert the table to a range and Custom Views will be available again.
Ah Hah! Thank you very much!!
There seems to be a catch to this. Let's say you have a column of data, and you set the column to filter for values A, B, and, C in that column. It's easy to save the custom view. But if, later on, those rows with, say, value B have their value changed to some other value, then there is no more value B on the sheet. So the filter unchecks B. When, later on, value B shows up again, it won't be displayed because the filter now has it unchecked. In other words, the filter selector will only allow data that actually exists to be selected, and since it was temporarily missing, it gets unchecked and is permanently turned off (unless you go manually turn it back on, which sucks).
I'm trying to figure out a way to solve this, including through macros. But there is no event that fires when the customview changes, so you can't take action when changing views to update the filters according to the data that actually exists at the moment.
Any ideas?
Re: iPcc's and janand's query/solution to hyperlinking to a custom view. My solution doesn't affect the menus and is useful for versions 2003/2007/2010:
1. Create your view(s).
2. Create a macro unique to activation of the specific view(s).
3. Create a text box/macro control on the spreadsheet for each macro created and attach the macro to its box/control. For multiple views, I use boxes with abbreviated descriptions for clarity.
NOTE: For quick switching between views, ensure all boxes/controls are available for each view. If a box/control is within a cell, don't hide the cell's row or column in other views.
Addition to my comment at 12):
The following three lines of VBA code are the meat of a macro I created to switch to a view of the sheet "Budget" in a multiple sheet Excel 2003 file ("Budget.xls"). Switching to the "Budget" sheet the view is based on is inherent in the macro. This allows the user to embed a box/control on any other sheet within the file.
Sub Budget_View_Standard()
ActiveWorkbook.CustomViews("Budget View").Show
End Sub
The following four lines activate "Budget View" from a different workbook when the "Budget.xls" file is open, but not active. This allows the user to embed a box/control in the other workbook. It is useful when the user prefers that data in the two workbooks should be kept separate.
Sub Budget_View_from_Other_Workbook()
Windows("Budget.xls").Activate
ActiveWorkbook.CustomViews("Budget View").Show
End Sub
Is it possible to select one cell with the cursor in a custom view and then when I switch to another custom view of the same worksheet the second custom view to be displayed actually where the cursor was in the previous/first custom view?
Working in excel 2007, the custom views do not seem to be saving the row height or the column width. I've even attempted on a brand new spreadsheet. The print area seems to be saving and displaying when changing custom views, but not the row height or column width. Any ideas?
[...] Custom Views in Excel – Save filter and header settings for quick reference | Chandoo.org – ... [...]
is there any way that i can turn off the option for custom views in excel? i keep deleting them but then it makes one for me (as a user), i don't want to use them as it increases the file size of an already large file and there are lots of formulae between the many sheets, custom views seem to stop it from updating.
Just added a couple of custom views to my spreadsheet and noticed two things - 1) you have to clear the filters each time you want to change the view? and 2) although the count is accurate while the spreadsheet is being filtered, I noticed the final tally in the lower left corner of the spreadsheet is wrong when it's completed. Any way to get around either of these? Thanks!
Bob - my experience is that the filters are saved with the views. And, in fact, everything on every worksheet is saved in the view, so that if, for instance, you have two worksheets and you want a couple custom views on one of them, changing views on that worksheet will also change the other worksheet to whatever happened to be in place when you saved the view, including hidden/shown columns/rows, filters, active cell, pretty much everything. (I actually wish that, when defining a view, you could specify "entire workbook" or "just this worksheet".) I have been successful saving filter settings on the sheet that I intended to capture in the view, but it can have unexpected effects on other sheets. This is 2010, btw. fwiw...
Don't know about the count thing...
[...] Bonus tip: Use specific filter criteria all the time? Try custom views. [...]
Is there a way to save a custom view from a user form?
For example, I have a user form where user can specifiy filter criteria and it will return the filtered data on the userform itself (using a list box). I want to Save this search (filter) with a button named "Save Search". It should prompt the user to type in the name of the Custom View and then Save it.
Let me know if this is possible or if there are any ideas on this anyone can suggest. If you are looking for an exmaple, let me know.
Thanks!
[…] http://chandoo.org/wp/2008/12/17/custom-views-excel/ […]
Is there anyway to set up custom views while still having drop down lists in the spreadsheet? E.g using a macro, etc?