Well the good news for Bill is that Excel provides a way to create a custom views for a workbook that can be reused over and over again. He can setup a Custom View to save specific display settings (such as column widths, row heights, hidden rows / columns, filter settings and print settings (such as page settings, margins, headers / footers, and sheet settings). He can then reuse these custom views whenever he needs.
There is one limitation with custom views. If any worksheet in the workbook contains an Excel table, the Custom Views command will not be available for use anywhere in the workbook.
To create a custom view you start by opening the file. Next change the settings that you want to save in the view (i.e. add filters, hide columns, etc.)
In Excel 2007 select View tab > Workbook Views group > Custom Views
A name box will open. Click the add button and then type a name for the Custom View. To help you in the future include the active sheet name to make the custom view easier to identify. In Bills case he might name his custom filter “Bills Quarterly Report”.
Under Include in view, select the options you want. In the above example both Print Settings and Hidden, rows, columns and filter settings are checked.
To use the filter Bill would do the following…
Excel 2007 View tab > Workbook Views group > Custom Views
A view box will appear. Bill then would select the name of the custom view he wants to apply. Then he would click the show button.
Bill can also add the custom view button on the quick access toolbar.
Right mouse click on the menu bar > select customize the quick access toolbar > select view in the categories > drag and drop the customize view in the menu bar.
A warning about custom views. If you save a worksheet with a custom view applied, the next time you open the worksheet, that custom view will still be applied.
Right about now you are saying… Hey! Why not just record a macro to accomplish the same custom filter?” Well truth be told, you can. However using a customized view is another tool available for data analysis.