3.5 C
New York
Monday, February 6, 2023

Buy now

Columns and groups in a shared Microsoft Excel workbook

Microsoft Excel’s sheet view collaboration feature has been around for a while, but now supports hiding rows and columns and applying groups.

excel-4 on paper texture
Image: Araki Illustrations/Adobe Stock

A few years ago, Microsoft Excel improved collaboration by adding sheet view to shared workbooks. To learn more about this feature, read Use Sheet View for more flexible collaboration in Excel. Sheet views now 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 with 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 is something that users may not realize is available. Unfortunately, the process is not saved with the custom sheet view. This feature would be more useful if they did, but maybe this capability will be added in a future upgrade.

I use 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 in A disc or Share point. iOS, iPad and Android mobile phones now support sheet view. All subscribers should have this new functionality in Excel sheet views.

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 being interrupted by others working in the same sheet. Maybe you’re working with a filter, or maybe you’re only working on specific columns or rows of data. By creating a worksheet view, changes made in real time by other collaborators will not change your view of the data.

First you need to share the workbook. To do this, 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, please contact your administrator.

Now let’s create a custom view that shows the simple sheet in Image A:

Image A

We will create a sheet view for this shared workbook.
We will create a sheet view for this shared workbook.

1. Open the workbook and enable AutoSave. If you cannot enable AutoSave, call your administrator.

2. Share the file if necessary and send the link to other collaborators.

3. If someone else is working on the file, click the appropriate option (Figure B). You may not see this prompt. Seeing changes in real time is the best part of working together, so don’t hesitate to choose the ‘See everyone’ option.

Figure B

Choose a sharing option in Excel.
Choose a sharing option in Excel.

4. If Excel does not display the sheet display options (Figure C), click the View tab.

Figure C

Excel displays the sheet display options.
Excel displays the sheet display options.

5. If necessary, choose Default from the Sheet View drop-down list.

6. Click New and enter a name for the view (Figure D) and press Enter.

Figure D

Name the sheet view so that you can reuse it in Excel.
Name the sheet view so that you can reuse it in Excel.

7. Choose Denny from the Personnel Filtering drop-down list to display only his data.

8. Click Keep in the Sheet View group.

With a sheet view applied, you can now hide rows and columns while collaborating without changing the views of other collaborators.

Figure E shows two copies of the same shared workbook. On the left, a sheet view shows only records for Denny and the rate table is hidden in columns H and I. On the right, columns H and I are still visible and the employee views Mark’s data. This also works the same with hidden rows.

Figure E

For better or worse, the sheet view won't save the hidden columns or rows, even if you click Keep.  The next time you open the workbook, you need to hide the columns.
For better or worse, the sheet view won’t save the hidden columns or rows, even if you click Keep. The next time you open the workbook, you need to hide the columns.

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 Overview drop-down list.

As you can see in Figure F, I have grouped and collapsed the Margin records. On 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

If you group records in a shared workbook, other collaborators won't be able to see them.
If you group records in a shared workbook, other collaborators won’t be able to see them.

Excel does not save hidden rows or columns or groups with a sheet view. However, if you save a grouping, the ability to collapse and expand is immediately available to other collaborators.

Spotting some strange behaviors with sheet view in Excel

All worksheet views are available to all employees. Applying a view is an easy way to see how other collaborators are working with the data. Unfortunately, Excel changes the views in real time and this behavior is confusing. Here are some warnings and recommendations:

  • When you open the shared workbook for the first time, the sheet view is set by default and the entire workbook should be displayed. If this doesn’t work for you, click New, list all the data and save it with a name, such as “Show all.” Use this worksheet view instead of the Standard worksheet view.
  • Before creating a new view, always return to the standard view or the custom worksheet view you created for that purpose and click New. If you don’t, you can change the current sheet view in a way you don’t want. Make sure all employees are aware of this step, otherwise custom worksheet views can quickly become a mess.
  • If someone changes a sheet view that you have created, that change is reflected in your sheet view. That’s disappointing, and maybe this will change with future updates.
  • Because all collaborators have access to all worksheet views, consider giving your views names that indicate that no one else is using them and possibly changing them. Perhaps add your name to the beginning of each display name. It won’t stop others from using the view, but if you discuss this preference with collaborators and come to an agreement, it can work.
  • After you apply the worksheet view and make changes to the structure as you work, other employees using the same worksheet view can also see these changes in real time. This is another reason why you should train employees to use only their custom worksheet views.

Source link

Related Articles


Please enter your comment!
Please enter your name here

Stay Connected


Latest Articles