[ad_1]
Microsoft Excel’s sheet view collaboration feature has been around for a while, but now it supports hiding rows and columns and applying groups.
A few years ago, Microsoft Excel improved collaboration by adding sheet view to shared workbooks. To read about this feature, read How to use sheet view for more flexible collaboration in Excel. Now, sheet views support hidden rows and columns and groups while collaborating. After applying a sheet view, a collaborator can hide data and other collaborators won’t see this change.
SEE: Google Workspace vs. Microsoft 365: A side-by-side analysis w/checklist (TechRepublic Premium)
In this tutorial, I’ll show you how to create a sheet view in a shared Excel workbook and then add groups as well as hidden rows and columns. The process is simple, but it’s something users might not realize is available. Unfortunately, the process doesn’t save with the custom sheet view. This feature would be more helpful if they did so, but perhaps this capability will be added in a future upgrade.
I’m using Microsoft 365 Excel for the web on a Windows 10 64-bit system and OneDrive for Business. For collaboration to work, you must save the workbook to OneDrive or SharePoint. iOS, iPad and Android mobiles now support sheet view. All subscribers should have this newish functionality in Excel sheet views.
How to use sheet view to hide rows and columns when collaborating in Excel
Sheet view allows you to customize the way you view data in a shared sheet without interruption by others working in the same sheet. Perhaps you work with a filter, or perhaps you work only on specific columns or rows of data. By creating a sheet view, changes made in real time by other collaborators will not change your view of the data.
First, you must share the workbook. To do so, click the Share button in the top-right corner and follow the prompts to share the file. If you know you’re using Microsoft 365 but you can’t share a workbook, call your administrator.
Now let’s create a custom view that displays the simple sheet shown in Figure A:
Figure A
1. Open the workbook and turn on AutoSave. If you can’t enable AutoSave, call your administrator.
2. If necessary, share the file and send the link to other collaborators.
3. If someone else is working in the file, click the appropriate option (Figure B). You might not see this prompt. Seeing changes in real time is the best part of collaborating, so don’t hesitate to choose the See Everyone’s option.
Figure B
4. If Excel doesn’t display the sheet view options (Figure C), click the View tab.
Figure C
5. Choose Default from the Sheet View dropdown if necessary.
6. Click New and then enter a name for the view (Figure D) and press Enter.
Figure D
7. Choose Denny from the Personnel filtering dropdown to display only his records.
8. Click Keep in the Sheet View group.
With a sheet view applied, you can now hide rows and columns while collaborating without changing other collaborators’ views.
Figure E shows two instances of the same shared workbook. To the left, a sheet view displays only records for Denny and the rate table in columns H and I is hidden. To the right, columns H and I are still visible and the collaborator is viewing Mark’s records. This also works the same with hidden rows.
Figure E
How to use sheet view with groups when collaborating in Excel
Similar to hiding columns and rows, grouping also hides rows by collapsing groups. To apply grouping, select the records you want to expand and collapse. Click the Data tab and then choose Group from the Outline dropdown.
As you can see in Figure F, I’ve grouped the records for Marge and collapsed them. To the right, you can see that another collaborator is using the grouping feature, but the feature doesn’t change their view of the same data.
Figure F
Excel doesn’t save hidden rows or columns or groups with a sheet view. However, if you save a grouping, the ability to collapse and expand will be immediately available to other collaborators.
How to recognize a few odd behaviors with sheet view in Excel
All sheet views are available to all collaborators. Applying a view is an easy way to see the way other collaborators are working with the data. Unfortunately, Excel changes views in real time and this behavior is confusing. Here are a few warnings and recommendations:
- When you open the shared workbook for the first time, sheet view is set to default and should display the entire workbook. If this doesn’t work for you, click New, display all the data and save it with a name, such as “See All.” Use this sheet view instead of the Default sheet view.
- Before creating a new view, always return to the default view or the custom sheet view you created for that purpose and click New. If you don’t, you might change the current sheet view in a way you don’t intend. Make sure all collaborators know about this step or custom sheet views can quickly devolve into a mess.
- If someone changes a sheet view that you created, that change will also make it to your sheet view. That’s disappointing, and perhaps this will change with future updates.
- Because all collaborators have access to all sheet views, consider giving your views names that express your desire that no one else use them and potentially change them. Perhaps add your name to the beginning of each view name. It won’t keep others from using the view, but if you discuss this preference with collaborators and come to an agreement, it might work.
- After applying sheet view and making changes to the structure while you’re working, other collaborators using the same sheet view will also see those changes in real time. This is another reason you should train collaborators to use only their custom sheet views.
[ad_2]
Source link
