How to Keep Hidden Rows and Columns Separate in Excel Sheet Views
Question details
The user wants to hide or unhide rows and columns in an Excel Sheet View without affecting the display for other collaborators in the same shared workbook.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Collaborating on a shared Excel workbook where multiple users need different data visibility to focus on their specific tasks.
- Observed behavior
- Hiding or unhiding rows and columns applies globally to the entire workbook, instantly changing the view for all other users and collaborators.
Ensure you have editing permissions for the shared workbook, and inform your team before making structural changes, as standard 'Hide' commands will currently affect everyone's view.
Use Custom Filters to Hide Rows in Sheet Views
Since the standard Hide command affects everyone globally, use Filters within an isolated Sheet View to selectively hide rows just for yourself.
Excel's native Sheet Views feature isolates sorting and filtering for individual users, but standard hidden rows and columns remain global. By adding a helper column and applying a filter instead of manually hiding rows, you can achieve the same visual result without disrupting others.
Navigate to the View tab on the Excel ribbon, and click 'New' under the Sheet View group. Your screen will get a dark border, indicating your view is now isolated.
Insert a new column at the end of your dataset (e.g., name it 'Visibility'). Mark the rows you want to hide with a specific value, such as 'Hide' or 'Exclude'.
Select the column headers of your dataset, go to the Data tab, and click the 'Filter' button to enable dropdown arrows on the headers.
Click the filter dropdown arrow on your new helper column and uncheck the 'Hide' value. This effectively hides those rows only in your active Sheet View.
Duplicate the Worksheet for Individual Use
If you absolutely must use the standard Hide/Unhide commands for both rows and columns, create a dedicated copy of the sheet for each collaborator.
Experience Seamless Collaboration with WPS Office
Looking for a cost-effective alternative to Microsoft Office? WPS Office offers highly compatible spreadsheet tools, a familiar interface, and robust features without the hefty subscription fees.
- 1. Download WPS Office: Visit the official WPS Office website and click the free download button for your operating system.
- 2. Install the Software: Run the downloaded installer file and follow the quick on-screen instructions to set up WPS Office.
- 3. Open Your Spreadsheets: Launch WPS Spreadsheet and open your existing .xlsx files directly without any formatting loss.
Frequently Asked Questions
Why do my hidden columns show up for other users in an Excel shared workbook?
In Excel, hiding rows or columns is a global structural setting. Unlike filtering, which can be isolated using the Sheet Views feature, manually hiding structural elements changes the file for everyone collaborating on it simultaneously.
Will Microsoft add independent hidden rows and columns for Sheet Views?
While Microsoft continuously updates Excel, it currently applies row and column hiding changes to the workbook globally. There is no official release date or supported feature for isolating hidden rows and columns per user.
Can I use Custom Views instead of Sheet Views to hide columns?
Custom Views can save hidden row and column settings locally. However, Custom Views are generally not supported if your worksheet contains Excel Tables, and they do not work seamlessly for real-time co-authoring in Excel for the Web.
Does grouping columns solve the issue for multiple users?
Grouping columns via the Data > Group feature allows quick expanding and collapsing of data. However, just like the hide command, the collapsed or expanded state is synced across all users in a co-authored workbook.
