Excel lets you create custom views of the same data. It’s a flexible tool that your users will want to know about. By creating custom views, they can view the same data in different ways, which is a lot quicker than manually changing settings. For instance, custom views retain hidden columns and rows, some filters, zoom settings, print settings, and more.
When defining a custom view, the first step is to arrange things for the view-set all the properties, flag all the settings, set the filter, and so on. Once the sheet has the specific look, create the custom view as follows:
- Click the View tab and Choose Custom Views in the Workbook Views group. In Excel 2003, choose Custom Views from the View menu.
- Select Add.
- Enter a name for the view, select the appropriate options, and click OK.
To activate a view, repeat step 1. Then, double-click a view from the Custom Views dialog. Or, select a view and click Show.
Users trying to create custom views might run into trouble using Excel 2007 and 2010. That’s because the presence of even one table in the workbook will disable the Custom Views feature. The table doesn’t have to be in the same sheet as the custom viewing area either. One table equals no custom views.
At this point, users should ensure that the current sheet isn’t part of a group, because Excel will disable the Custom Views feature in a group (in Excel 2003 too). If that’s not the problem, users have a choice: they can keep the tables and forget about using custom views or they can delete all the tables and define custom views. I don’t know of any way to support both in the same file.
If they decide to delete the tables, you can provide the following macro:
Sub ConvertTableToRange()
'Convert all tables in workbook to range.
Dim sht As Worksheet
Dim objTable As ListObject
For Each sht In ActiveWorkbook.Worksheets
For Each objTable In sht.ListObjects
objTable.Unlist
Next objTable
Next sht
End Sub
This simple macro will cycle through each sheet in the workbook, removing all tables (ListObject objects) from the workbook. This macro doesn’t delete the data, it just removes the table object. Once they delete all the tables, they can define custom views. For better or worse, this macro doesn’t remove the table formatting. Nor will it warn you if it finds no tables to convert.