How to Save Hidden Rows and Columns in Excel Custom Views
Question details
The user wants to know how to save specific hidden rows and columns in a custom view so they can easily switch between different spreadsheet layouts without manually hiding and unhiding data.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing large datasets and creating multiple reporting layouts within the same worksheet.
- Observed behavior
- Users need a reliable way to preserve the visibility state (hidden or unhidden) of rows and columns alongside active filters when navigating through different data views.
Ensure your worksheet does not contain any formatted Excel Tables (Insert > Table), as the Custom Views feature is automatically disabled when tables are present in the workbook.
Create Separate Custom Views for Visible and Hidden Layouts
The most effective method is to create a baseline view with all data visible, followed by additional views that save your specifically hidden rows and columns.
Custom Views in Excel act like snapshots of your worksheet's current display settings. To seamlessly toggle layouts, you must define both the 'unhidden' state and the 'hidden' state as distinct views.
Select your entire worksheet, right-click the row and column headers, and choose 'Unhide' to ensure no data is hidden. Go to the 'View' tab on the ribbon, click 'Custom Views', then select 'Add'. Name this view 'All Data Visible' and click 'OK'.
Manually select the specific rows or columns you wish to conceal for your alternative layout. Right-click the selected headers and choose 'Hide'.
Navigate back to 'View' > 'Custom Views' and click 'Add'. Give this view a descriptive name (e.g., 'Summary View - Hidden Rows'). Ensure the box for 'Hidden rows, columns and filter settings' is checked, then click 'OK'.
Whenever you need to change your view, simply go to 'View' > 'Custom Views', select your desired layout from the list, and click the 'Show' button.
Use WPS Spreadsheet to Manage Custom Views
WPS Spreadsheet provides a highly intuitive and robust environment for handling custom views. You can easily save worksheet layouts with hidden rows, specific filters, and print configurations with just a few clicks.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open the spreadsheet file you want to manage.
- 2. Access the Custom Views option: Navigate to the 'View' tab on the top ribbon and click on the 'Custom Views' icon.
- 3. Add and configure your view: Click 'Add', input a clear name for your current layout, ensure the visibility settings are checked, and click 'OK'.
- 4. Toggle your views instantly: To change layouts later, return to the 'Custom Views' menu, choose your saved configuration, and click 'Show'.
Frequently Asked Questions
Why is the Custom Views button grayed out?
The Custom Views feature becomes disabled if any worksheet within your entire workbook contains an Excel Table (created via Insert > Table). To restore the feature, you must convert all tables back to standard data ranges by selecting the table, navigating to Table Design, and clicking 'Convert to Range'.
Do Custom Views also save print settings?
Yes. When you create a Custom View, you can check the 'Print settings' box. This allows you to save specific print areas, margins, page breaks, and headers/footers alongside your hidden rows and columns.
Will Custom Views work properly in the web version of Excel?
Excel for the web has limited capabilities regarding traditional Custom Views. It primarily uses 'Sheet Views' to save active filters and sorting for collaboration purposes. For full functionality including saving hidden rows and columns, it is highly recommended to use the desktop version of Excel or WPS Spreadsheet.
