Don’t forget to share this post

Microsoft 365 has unlocked powerful features for collaboration, making it even easier to share an Excel file or work on a spreadsheet simultaneously using the Excel Co-Authoring functionality. The downside to having multiple users editing a file at the same time is that it may interfere with your viewing of the data. For example, you might try to analyze a column that suddenly disappears because come else has filtered or sorted it.

Now you can create customized views, aka Sheet Views, in Excel so that others can’t interfere with your work. In this article, you’ll learn what Sheet View is, and how to create a temporary Sheet View in Excel, remove it, and switch between your custom Sheet Views.

What is Sheet View in Excel?

Sheet Views are an Excel feature that allows you to create different views of the same data in a spreadsheet. It’s extremely useful for collaboration since it allows multiple users to view the same data in different ways at the same time.

If you find that the Excel Sheet View is missing or not working, you might not have the required version of Microsoft 365. If you do, you need to make sure it is saved to OneDrive or SharePoint. Let’s explore how you can enable Sheet View in Excel.

How do I enable Sheet View in Excel?

If you find ‌the Sheet View options disabled, then this is how you can check that your Excel is online.

Open your Excel workbook and click on the file name at the top. A “File Name” drop-down box will appear where you can select the online folder.

How To Use Excel Sheet View For Easy Collaboration Click filename
How To Use Excel Sheet View For Easy Collaboration - Click filename

Now, you’ll see how easy it is to create a Temporary Sheet View in Excel.

Top 11 Tips for Better Excel and Google Sheets Collaboration
Top 11 Tips for Better Excel and Google Sheets Collaboration

11 different ways to make collaboration better in Excel and Google Sheets so you can continue using spreadsheets as your go-to tool for managing your data.

READ MORE

How to create a Temporary Sheet View in Excel?

Sheet Views are temporary because you can easily remove them at any time. This is how you can create a Temporary Sheet View in your Excel.

  1. 1. Open an Excel workbook and click on any sheet to make sure it’s active.
How To Use Excel Sheet View For Easy Collaboration Select Sheet
How To Use Excel Sheet View For Easy Collaboration - Select Sheet
  1. 2. Head to the “View” tab and click on “New” within the “Sheet View” group.
How To Use Excel Sheet View For Easy Collaboration Sheet View
How To Use Excel Sheet View For Easy Collaboration - Sheet View

Notice how your column and row headers are now black, and Excel displays “Temporary View” as a drop-down option in the Sheet View group. Next to your sheet tab name at the bottom, an eye icon should also appear.

  1. 3. To remove the “Temporary View”, click “Exit” within the “Sheet View” group.
How To Use Excel Sheet View For Easy Collaboration Exit View
How To Use Excel Sheet View For Easy Collaboration - Exit View

You can now easily work on your data without being interrupted. In case another user applies a filter or tries to sort data, Excel will ask you whether you want to work on their view or continue to work on your own. Let’s explore how you can use Sheet View in Excel to have full control over how you can view your data.

How to Use Sheet View in Excel?

Excel first allows you to create a Temporary View, which you can easily exit, as shown before. However, if you would like to save the Temporary View, this is what you need to do.

How to save a Sheet View in Excel?

The great thing about Sheet View is that you can save more than one view per sheet.

  1. 1. In the “Sheet View” group, click on “Keep”.
How To Use Excel Sheet View For Easy Collaboration Keep
How To Use Excel Sheet View For Easy Collaboration - Keep
  1. 2. You should now see the default name of your view has replaced “Temporary View” in the drop-down menu.
How To Use Excel Sheet View For Easy Collaboration Default name
How To Use Excel Sheet View For Easy Collaboration - Default name

However, you can customize the Sheet View name so that it’s easier for you to switch between views. Let’s see how you can rename your custom view.

How to rename a Sheet View in Excel?

Renaming your custom views can be done in two simple steps.

  1. 1. Click on “Options” next to the “New” button. Then, select the Sheet View to rename, and select “Rename”.
How To Use Excel Sheet View For Easy Collaboration Options
How To Use Excel Sheet View For Easy Collaboration - Options
  1. 2. Type in the new name and click “Save”.
How To Use Excel Sheet View For Easy Collaboration Rename
How To Use Excel Sheet View For Easy Collaboration - Rename

But what if you’ve saved different custom views and want to switch between them? Here’s how to do it.

How to Share an Excel File for Multiple Users?

There are different ways to share an Excel file. Here's how to share an Excel file with multiple users for easy collaboration

READ MORE
How to Share an Excel File for Multiple Users

How to switch or leave a Sheet View?

You can switch between your saved custom views from the “Sheet View” drop-down box.

  1. 1. To switch to a different Sheet View, click on the drop-down list and select the desired Sheet View. Here, I would like to switch to “Sorted A-Z” a view I saved earlier, which filters the last column and sorts the first column as A-Z.
How To Use Excel Sheet View For Easy Collaboration Switch
How To Use Excel Sheet View For Easy Collaboration - Switch
  1. 2. To leave a Sheet View, click “Exit”. You can see how the drop-down box now shows “Default” as the current view.
How To Use Excel Sheet View For Easy Collaboration Default
How To Use Excel Sheet View For Easy Collaboration - Default

If you’re wondering whether you can access other views in the shared Excel, you’ll be happy to learn that it’s possible. However, this also means that there is no private view option. As a result, any other user can also see and accidentally change your custom view.

How to use Layer for Excel collaboration?

Layer is a collaboration platform for spreadsheets that works on top of your existing Excel files and Google Sheets. Share parts of your spreadsheet, collect and consolidate data, and review changes to make collaboration seamless and more efficient while keeping full control over your data. Using Layer, you can:

  • Manage Access: Give spreadsheet access on a tab or cell level to relevant stakeholders.
  • Collaborate: Automate tasks, set deadlines, and communicate on top of your files.
  • Review & Track: Consolidate input, track changes, and restore previous versions.

Sign up to get started with Layer right now or schedule an onboarding call so we can help you set up and share your spreadsheet flows.

Conclusion

Although Excel Sheet Views might seem like a basic feature, this feature makes collaborative work easier than ever before. You can perform any task on your spreadsheet - basic or complex - and it won’t affect or interfere with your colleagues’ work.

In this article, you have seen how Sheet View makes collaboration in Excel even easier. You should now know to use the Sheet View functionality in Excel in full. You should also know how to create a Temporary View, how to remove it, and how to manage Sheet Views to go back to a specific Sheet View at any time.

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published May 20 2022, Updated May 30 2022