How to create a static view of Excel data while collaborating

When collaborating in Excel, sometimes the collaboration can be distracting. Here’s how to make a static image so you can do your own work without interruption.

Collaborating online with other members of your organization is convenient, and with each version of Microsoft Excel, the feature improves. A shared workbook can be viewed and changed, depending on the author’s settings, and it all happens in real time. Viewers will see changes immediately. That can be good and bad. You should always view the most up-to-date data, but at the same time, if you’re trying to sort or filter data, this collaboration can be disruptive. In this article, I’ll show you how to create a static view of data that won’t update while you’re working.

SEE: 83 Excel tips every user should master (TechRepublic)

This feature is available only with Microsoft 365 and Excel Online. I’m using both on a Windows 10 64-bit system. There’s no demonstration file; you won’t need one. You can use any Excel workbook you like; even a blank one.

The basics

If you’ve ever been viewing online data and suddenly had data change right in front of your eyes? That’s the good and the bad of collaboration. By sharing a workbook in OneDrive, those you invite by sending a link to the workbook can view and modify it while you’re also working and viewing the workbook.

A workbook is easy to share. When working in the desktop version, click the Share button in the top-right corner of the tab bar, as shown in Figure A. If the workbook isn’t already in OneDrive, save a copy there when prompted. (You can also share to SharePoint, but we’ll stick with OneDrive because it comes with 365.)

Figure A

You have a few options when sharing. The default is to allow anyone with a link to the workbook to be able to view and edit its contents. To change that click the Anyone With the Link Can Edit option to display the Specific People link. Later, you can send the link to only those you want to edit the workbook. You can copy the link and send it any way you like. Or you can click Outlook and go from there. You can even enter a message with instructions, deadlines and so on.  Using the Share pane, you can even see who is viewing your workbook in real time. This sharing process is simple and with just a bit of exploration, you’ll catch on quickly.

While the feature is great, it’s unsettling when the data you’re viewing keeps changing. By creating a static, or temporary view of the data, you can do your job while others do theirs.

How to create a temporary view in Excel

When others are viewing and working in the workbook, you’ll see a small icon with their Microsoft account information (or organizational information). Mine shows SH, as shown in Figure B whether I use my work or private email because the username for both is Susan Harkins. The active cell at the desktop level is H10. As a collaborator working in OneDrive, I selected cell F4.

Figure B

If you run into something similar, consider adding a middle initial or something to distinguish between the two users when collaborating online. It’s a minor point and probably won’t be an issue for most users.

Back at the desktop level, we don’t want to be interrupted by SH, so let’s create a temporary view:

1. With the sheet you want to view active, click the View tab.
2. In the Sheet View group, click New. Then, click Continue. Doing so will change many of the visual properties, as shown in Figure C. Notice that your column and row headers are now black with white font. This is simply a clue to indicate that you’re not seeing changes in real time. You will still see who is working in the shared version. An eye icon will also be displayed in the sheet name tab.

Figure C

At this point, you can work without disruptions from others working in the same file. When someone else is filtering and sorting, Excel will ask you if you want to update your view.

If you find yourself in this situation often, you can save the temporary view and apply it as needed, instead of creating a new view each time. When your view is complex, this is convenient because you won’t have to recreate the structure each time.

To save a temporary view, click Keep in the Sheet View group. Doing so applies a default name, View1. If you want to change that, click inside the view name control and enter the new name, as shown in Figure D. To apply MyView, choose it from the dropdown. You can have several views of the same sheet. Simply choose a new view or the default view from the dropdown to apply it.

Figure D

Using the Options feature, you can rename, delete, or duplicate a view. The latter is helpful when you have a complex view and you want to make a subtle change, while keeping the original.

Temporary views are easy to implement. If you collaborate a lot, you’ll want to check out this feature and see how it can help you work more efficiently.