Sheet view in Excel is useful if multiple people are editing the same workbook. Say that one person wants to filter to Open orders and the other wants to see closed orders. One wants the data sorted descending and the other wants it sorted ascending. Some want to hide some columns. Sheet view can help with this.
Table of Contents
(0:00) Sheet View is better than legacy file sharing
(0:54) Saving for collaboration in Excel
(1:45) Opening the same workbook on another computer
(1:59) Changes on one computer sync to all computers
(2:25) I want to see some records
(2:41) Avoid the "See Just Mine" button
(3:00) Excel Help is wrong: Default View isn't working
(3:28) Creating a new Sheet View for See All
(4:00) Create a view for Bill records and name it
(4:28) Switching between views works
(4:40) Changes while in a sheet view become part of the sheet view
(4:52) How your sheet view looks on a co-worker's computer
(5:10) Co-workers can change your Sheet View
(6:06) Exit or Default is not working
(6:51) Creating a view on Computer B for Ann
(8:24) Hiding Excel columns in Sheet View: Help says only in Online
(9:24) Setting up Sheet View for columns in Excel Online
(10:00) Good: Sheet View will unhide columns
(10:07) Fail: Sheet View won't re-hide columns
(10:22) Successfully using Sheet View to Hide Columns in Desktop
(11:15) Filters must be on for Sheet View to work with columns
(12:06) Closing and Re-opening remembers Sheet View
(12:50) Despite Excel Help's prediction, hiding columns in Sheet View seems to work
(13:27) Conclusion and Wrap-Up
(14:34) New Excel courses on Retrieve platform
Table of Contents
(0:00) Sheet View is better than legacy file sharing
(0:54) Saving for collaboration in Excel
(1:45) Opening the same workbook on another computer
(1:59) Changes on one computer sync to all computers
(2:25) I want to see some records
(2:41) Avoid the "See Just Mine" button
(3:00) Excel Help is wrong: Default View isn't working
(3:28) Creating a new Sheet View for See All
(4:00) Create a view for Bill records and name it
(4:28) Switching between views works
(4:40) Changes while in a sheet view become part of the sheet view
(4:52) How your sheet view looks on a co-worker's computer
(5:10) Co-workers can change your Sheet View
(6:06) Exit or Default is not working
(6:51) Creating a view on Computer B for Ann
(8:24) Hiding Excel columns in Sheet View: Help says only in Online
(9:24) Setting up Sheet View for columns in Excel Online
(10:00) Good: Sheet View will unhide columns
(10:07) Fail: Sheet View won't re-hide columns
(10:22) Successfully using Sheet View to Hide Columns in Desktop
(11:15) Filters must be on for Sheet View to work with columns
(12:06) Closing and Re-opening remembers Sheet View
(12:50) Despite Excel Help's prediction, hiding columns in Sheet View seems to work
(13:27) Conclusion and Wrap-Up
(14:34) New Excel courses on Retrieve platform
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2461.
How to effectively use Sheet View when collaborating in Excel.
Hey, welcome back to the MrExcel netcast. I am Bill Jelen.
Today, a conversation with Christine, who had been in one of my classes before.
And they were now starting to use collaboration instead of the old legacy file sharing. Which is great because collaboration in Excel works a million times better than the old legacy did. But she had some questions about Sheet View.
And while I've covered Sheet View on the podcast before, it became clear that I really didn't understand what's happening in Sheet View.
And I've learned so much, and I want to share it with you.
For those of you who find yourselves collaborating in Excel.
And want to understand how this Sheet View actually works. And how you can effectively make it work.
Alright so I have this workbook that has not been saved.
I'm going to follow my testing protocol #8 here. I'm going to first save this to OneDrive.
For collaboration to work, it has to be either in SharePoint Online or OneDrive.
And will save it as an XLSX file. Click Save.
I want to make sure to turn on Autosave.
I have this turned off by default because it drives me crazy when I'm just working alone. But for collaboration you need AutoSave on.
Right now we're going to switch over to Computer B and you'll hear the clicks over there on computer B.
First thing I'm going to do is I'm going to start recording on computer B.
And then I computer B I'm going to go to File, Open.
And it's beautiful, the file that I just saved over on computer A is immediately here.
And just to make sure that this is working, I'm going to choose a number on Computer B and I'm going to type a new value.
And you see that it syncs right? So that's good.
That means that everything is working great. OK, so, here's the deal with with the Sheet View.
The idea is you have two people working in the same file at the same time.
And maybe I'm interested in seeing Ann's records.
And the other person is interested in seeing Bill’s records.
So if I would apply a filter, this little box pops up that says, hey, do you want to see these changes, do you want to make these changes for everyone or just for you?
And that's kind of to let you know the Sheet View is there.
I'm going to recommend that we don't use that as a method to start Sheet View.
Let's consciously say that we want to start Sheet View.
And there's two great Excel help files out there from Microsoft.
One of them claims that the first time that you create a Sheet View, it's going to save the current state, the unfiltered state, as something called the default view.
But here's what I found: That does not work.
So right now, we're seeing all records from Andy to Lou.
And I'm going to create a new Sheet View. First, let's apply the filter: Data, Filter.
So we turn the Filters on.
On the View tab, I'm going to click New to create a New Sheet View. And initially it's called Temporary View.
I'm going to save it right and you can either click keep or you can just come here and type a new name. I'm going to call this See All.
The Help doesn't say that you have to create this, but in my testing with it, (this is my 8th test).
You need to have a See All. And then I'm going to create the view for me.
So we'll click New View. This is going to be called the Bill view.
And I'm going to filter here to Bill.
Click OK.
Let's see, out here in column L, I'm going to sort, largest to smallest.
Alright, so I've done two things after creating the Bill view. I filtered to Bill and then I sorted by Iceberg.
Alright, now let's just do a little test here. Let's come back to Show All.
And all my records come back. We'll come back to Bill.
And just the Bill sorted descending, right? That's an important distinction there.
That means that after I created the Bill view, as long as the Bill view was active, anything that I did became a part of that view. We're going to switch over to computer B.
On computer B. We're now in something called Temporary View.
And when I open the sheet view, I have an option to switch to Bill.
Alright, I get the Bill records sorted descending. Now this is funny.
I'm on computer B.
Computer B is not the person that created the Bill view. This is one of my coworkers.
But the person on computer B is going to further filter this.
So we'll filter this to just the Open orders.
Alright, and because computer B was looking at the Bill view.
That change has become part of the definition for the Bill sheet view.
This was super surprising to me.
It made me think, “well, I don't want anyone using my sheet views because they're not going to understand how this works”.
Like, “Go create YOUR OWN own sheet view”.
Alright, so we're going to come back to Computer A. And you'll see that it's now Bill and Open.
And if I would switch to See All. And then switch back to Bill.
Those changes that the other person made on the other computer become part of this definition.
Alright, so let's exit. Go back to the default state.
That's not the default state at all, but what is this?
It's like you need See All to get back to all items.
I have no idea where that default comes from.
You know the Excel help file says that it should be what we had before we created the First Sheet View, and that's clearly not what's happening here.
Alright, See All.
So now, let's say that I am Ann and I want to create my own view that I can use and no one else is going to use it.
Let's go over to the other computer over here for Computer B.
So we go to See All. Alright, now remember I'm Ann.
I am going to create a view, but if I would filter to Ann first while See All is open, that's going to screw up the See All view. So the very first thing.
Here's the recommend recommendation. Create your view for See All.
Second, when you need to create a view for you, you click New.
Right and initially called temporary view. If you give it a name that you will remember.
But none of your coworkers will ever want to use. Like, for example.
Right by starting with X, it'll be at the bottom of the list.
Format the hard drive hopefully will scare people away. Press Enter.
I've now just saved a new view. And now here we will choose just Ann.
Ann likes to see all records open or closed, but in this case she wants Iceberg sorted ascending.
Alright good.
Let's come back here to Computer A. We open this.
And there is “Format the Hard Drive”, which scares me, I don't want to click it.
And that gets me into Ann's view. So I think the lesson here.
Is that rather than allowing sheet view to happen, you have to consciously say “I want to create a view just for me”. I'm going to click the New button.
I'm going to give it a name.
And then I'm going to apply the filter or sort or whatever I need to do.
Now, the whole reason that I got into this, Christine - the question was well look, we have 40 columns.
Some people want to see these columns and other people want to see those columns.
And that's going to be working in Sheet View, but the Excel Online help file says that this is only working in Excel online. So we're going to switch to Excel Online.
I have this file open in two different Chrome browsers, one on computer A and one on Computer B.
I'm leaning to reach over on computer B and choose Apple January and change it to 9999.
I'm going to press enter and that change synchronizes immediately to computer A over here right?
So it's saved in OneDrive, the synchronization is working.
On the view tab, I'm going to create a new view called Show All Columns. Great.
And then I'm going to create a New view. For Just Andy Columns.
Alright, so we have that view. I'm going to come down here.
This is this is superstition on my part, but I'm going to apply the Filter.
Do we need the Filters there?
And I'm going to hide these three columns. That's Alt, H, O, U, C.
Good, and so we have our View. We should have two Views here.
Just Andy Columns and See All Columns. So I go back to Show All Columns.
Beautiful, they all come back. Then I go to Just Andy Columns.
Nothing, alright. So Excel help says it is working in Excel online.
I've asked Microsoft. We'll see if they come up with something.
If they do, down in the YouTube description with what I'm doing wrong.
I get it - I'm a rookie in Excel online, right? So I'm probably just doing something wrong.
Hey, here's an update.
It's Monday, January 17th and Christine wrote back and she said, well wait, Sheet View does allow one person to hide columns and not impact the other computers. It may not be remembering it, but it's working.
Alright, so here's what I've learned. I have two computers, both in Win32.
Here, let me start recording over on the other machine so we can see that.
OK, and on the on computer B over here, I'm going to come in here and type 9995.
And we'll see that up here back here for Ed. Alright good so it's working.
Notice on computer B I've created a new view called For Computer B and on computer A I've created a view called For Computer A right?
So they both need to be in sheet view and then oh and also I did do some checking, the filters have to be turned on.
If you do this without the filters turned on, yeah, it's not going to work.
That was a little annoying to me because I had all of my headings right aligned.
And when I turn the filters on I couldn't see them anymore, so I had a left align the headings, but I can live with that, right? So again, both computers in their own Sheet View.
I'm going to come here and hide the yellow columns.
Alt+O, C, H.
l press F4, press F4, press F4, press F4, press F4 and we go look at Computer B.
It is still seeing all the columns, which is beautiful.
I'm going to come here and type something new and 9994. And see that that syncs back to computer B.
That's beautiful, right?
And I was going to suggest that we're going to have to do is re hide the columns each time, but I even think that that's not necessarily true, right? So I'm going to close this File, Close.
And then the next time I come here and open it on this machine.
It remembers that I am in this view called For Computer A.
And I have not impacted yet the columns on Computer B.
So that's funny, Excel Help says this is not working in desktop.
But boy, it sure seems like it's working, provided that you turn these filter dropdowns on.
If the filter dropdowns weren't there, then any column hiding on any machine effects all the other machines. OK, so hey, you know this is a new feature, right?
It's evolving, they're improving it.
It's a little frustrating that Excel helps says it doesn't work.
They forced me over to Excel Online and in fact it does work. Yeah, so there you have it.
It doesn't remember it.
I'm not going to switch back and forth like like if I would come in here and go back to the Default or if I would click Exit to go back to the Default. Then I'm going to lose those hidden columns.
I'm not going to be able to be able to get them back, but if I just hide the columns I want to hide and stay in here and make sure that everyone else is in their own Sheet View and I'm in my Sheet View, yeah, it seems it seems like it might work.
So what's the lesson here?
I think the lesson is to use Sheet View, you have to be saved to OneDrive.
You have to consciously decide to create a new view.
I recommend, (even though they say you don't have to) to create a view called Show All so you can get back to that.
And then click New View and name it and then do any filtering and sorting you want to do.
Be aware that you know even though you kind of click this Keep button or name it, those both do the same thing.
That it's not just the items that have been applied at that point.
Once you have the Bill view displayed and you have the black column letters up here and row numbers over here.
When you are in Sheet View, any changes that you make on any computer.
Or any of your coworkers make on any computer when they have the Bill view displayed.
Will become part of the definition of the Bill view.
That is something I did not understand until Christine asked this question. I think it's important.
it's not documented in Excel help, and hopefully with your understanding of that.
It will help you in Sheet View.
Hey, check out my new courses on the Retrieve platform.
Its an amazing, interactive, searchable Artificial Intelligence. Type what you are looking for.
They will take you right to that spot in the video.
And then, if you don’t have time to watch the video, just read the transcript in any of several languages. It is an amazing platform. Check it out.
If you like these videos, please down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
Alright, well hey I want to thank you for stopping by.
This was a longer topic on something that is relatively new, I think for a lot of us.
But hopefully will help you understand Sheet View when you get into a collaboration situation at work. Thanks for stopping by.
I'll see you next time for another netcast from MrExcel.
Let's hear you, Nancy.
How to effectively use Sheet View when collaborating in Excel.
Hey, welcome back to the MrExcel netcast. I am Bill Jelen.
Today, a conversation with Christine, who had been in one of my classes before.
And they were now starting to use collaboration instead of the old legacy file sharing. Which is great because collaboration in Excel works a million times better than the old legacy did. But she had some questions about Sheet View.
And while I've covered Sheet View on the podcast before, it became clear that I really didn't understand what's happening in Sheet View.
And I've learned so much, and I want to share it with you.
For those of you who find yourselves collaborating in Excel.
And want to understand how this Sheet View actually works. And how you can effectively make it work.
Alright so I have this workbook that has not been saved.
I'm going to follow my testing protocol #8 here. I'm going to first save this to OneDrive.
For collaboration to work, it has to be either in SharePoint Online or OneDrive.
And will save it as an XLSX file. Click Save.
I want to make sure to turn on Autosave.
I have this turned off by default because it drives me crazy when I'm just working alone. But for collaboration you need AutoSave on.
Right now we're going to switch over to Computer B and you'll hear the clicks over there on computer B.
First thing I'm going to do is I'm going to start recording on computer B.
And then I computer B I'm going to go to File, Open.
And it's beautiful, the file that I just saved over on computer A is immediately here.
And just to make sure that this is working, I'm going to choose a number on Computer B and I'm going to type a new value.
And you see that it syncs right? So that's good.
That means that everything is working great. OK, so, here's the deal with with the Sheet View.
The idea is you have two people working in the same file at the same time.
And maybe I'm interested in seeing Ann's records.
And the other person is interested in seeing Bill’s records.
So if I would apply a filter, this little box pops up that says, hey, do you want to see these changes, do you want to make these changes for everyone or just for you?
And that's kind of to let you know the Sheet View is there.
I'm going to recommend that we don't use that as a method to start Sheet View.
Let's consciously say that we want to start Sheet View.
And there's two great Excel help files out there from Microsoft.
One of them claims that the first time that you create a Sheet View, it's going to save the current state, the unfiltered state, as something called the default view.
But here's what I found: That does not work.
So right now, we're seeing all records from Andy to Lou.
And I'm going to create a new Sheet View. First, let's apply the filter: Data, Filter.
So we turn the Filters on.
On the View tab, I'm going to click New to create a New Sheet View. And initially it's called Temporary View.
I'm going to save it right and you can either click keep or you can just come here and type a new name. I'm going to call this See All.
The Help doesn't say that you have to create this, but in my testing with it, (this is my 8th test).
You need to have a See All. And then I'm going to create the view for me.
So we'll click New View. This is going to be called the Bill view.
And I'm going to filter here to Bill.
Click OK.
Let's see, out here in column L, I'm going to sort, largest to smallest.
Alright, so I've done two things after creating the Bill view. I filtered to Bill and then I sorted by Iceberg.
Alright, now let's just do a little test here. Let's come back to Show All.
And all my records come back. We'll come back to Bill.
And just the Bill sorted descending, right? That's an important distinction there.
That means that after I created the Bill view, as long as the Bill view was active, anything that I did became a part of that view. We're going to switch over to computer B.
On computer B. We're now in something called Temporary View.
And when I open the sheet view, I have an option to switch to Bill.
Alright, I get the Bill records sorted descending. Now this is funny.
I'm on computer B.
Computer B is not the person that created the Bill view. This is one of my coworkers.
But the person on computer B is going to further filter this.
So we'll filter this to just the Open orders.
Alright, and because computer B was looking at the Bill view.
That change has become part of the definition for the Bill sheet view.
This was super surprising to me.
It made me think, “well, I don't want anyone using my sheet views because they're not going to understand how this works”.
Like, “Go create YOUR OWN own sheet view”.
Alright, so we're going to come back to Computer A. And you'll see that it's now Bill and Open.
And if I would switch to See All. And then switch back to Bill.
Those changes that the other person made on the other computer become part of this definition.
Alright, so let's exit. Go back to the default state.
That's not the default state at all, but what is this?
It's like you need See All to get back to all items.
I have no idea where that default comes from.
You know the Excel help file says that it should be what we had before we created the First Sheet View, and that's clearly not what's happening here.
Alright, See All.
So now, let's say that I am Ann and I want to create my own view that I can use and no one else is going to use it.
Let's go over to the other computer over here for Computer B.
So we go to See All. Alright, now remember I'm Ann.
I am going to create a view, but if I would filter to Ann first while See All is open, that's going to screw up the See All view. So the very first thing.
Here's the recommend recommendation. Create your view for See All.
Second, when you need to create a view for you, you click New.
Right and initially called temporary view. If you give it a name that you will remember.
But none of your coworkers will ever want to use. Like, for example.
Right by starting with X, it'll be at the bottom of the list.
Format the hard drive hopefully will scare people away. Press Enter.
I've now just saved a new view. And now here we will choose just Ann.
Ann likes to see all records open or closed, but in this case she wants Iceberg sorted ascending.
Alright good.
Let's come back here to Computer A. We open this.
And there is “Format the Hard Drive”, which scares me, I don't want to click it.
And that gets me into Ann's view. So I think the lesson here.
Is that rather than allowing sheet view to happen, you have to consciously say “I want to create a view just for me”. I'm going to click the New button.
I'm going to give it a name.
And then I'm going to apply the filter or sort or whatever I need to do.
Now, the whole reason that I got into this, Christine - the question was well look, we have 40 columns.
Some people want to see these columns and other people want to see those columns.
And that's going to be working in Sheet View, but the Excel Online help file says that this is only working in Excel online. So we're going to switch to Excel Online.
I have this file open in two different Chrome browsers, one on computer A and one on Computer B.
I'm leaning to reach over on computer B and choose Apple January and change it to 9999.
I'm going to press enter and that change synchronizes immediately to computer A over here right?
So it's saved in OneDrive, the synchronization is working.
On the view tab, I'm going to create a new view called Show All Columns. Great.
And then I'm going to create a New view. For Just Andy Columns.
Alright, so we have that view. I'm going to come down here.
This is this is superstition on my part, but I'm going to apply the Filter.
Do we need the Filters there?
And I'm going to hide these three columns. That's Alt, H, O, U, C.
Good, and so we have our View. We should have two Views here.
Just Andy Columns and See All Columns. So I go back to Show All Columns.
Beautiful, they all come back. Then I go to Just Andy Columns.
Nothing, alright. So Excel help says it is working in Excel online.
I've asked Microsoft. We'll see if they come up with something.
If they do, down in the YouTube description with what I'm doing wrong.
I get it - I'm a rookie in Excel online, right? So I'm probably just doing something wrong.
Hey, here's an update.
It's Monday, January 17th and Christine wrote back and she said, well wait, Sheet View does allow one person to hide columns and not impact the other computers. It may not be remembering it, but it's working.
Alright, so here's what I've learned. I have two computers, both in Win32.
Here, let me start recording over on the other machine so we can see that.
OK, and on the on computer B over here, I'm going to come in here and type 9995.
And we'll see that up here back here for Ed. Alright good so it's working.
Notice on computer B I've created a new view called For Computer B and on computer A I've created a view called For Computer A right?
So they both need to be in sheet view and then oh and also I did do some checking, the filters have to be turned on.
If you do this without the filters turned on, yeah, it's not going to work.
That was a little annoying to me because I had all of my headings right aligned.
And when I turn the filters on I couldn't see them anymore, so I had a left align the headings, but I can live with that, right? So again, both computers in their own Sheet View.
I'm going to come here and hide the yellow columns.
Alt+O, C, H.
l press F4, press F4, press F4, press F4, press F4 and we go look at Computer B.
It is still seeing all the columns, which is beautiful.
I'm going to come here and type something new and 9994. And see that that syncs back to computer B.
That's beautiful, right?
And I was going to suggest that we're going to have to do is re hide the columns each time, but I even think that that's not necessarily true, right? So I'm going to close this File, Close.
And then the next time I come here and open it on this machine.
It remembers that I am in this view called For Computer A.
And I have not impacted yet the columns on Computer B.
So that's funny, Excel Help says this is not working in desktop.
But boy, it sure seems like it's working, provided that you turn these filter dropdowns on.
If the filter dropdowns weren't there, then any column hiding on any machine effects all the other machines. OK, so hey, you know this is a new feature, right?
It's evolving, they're improving it.
It's a little frustrating that Excel helps says it doesn't work.
They forced me over to Excel Online and in fact it does work. Yeah, so there you have it.
It doesn't remember it.
I'm not going to switch back and forth like like if I would come in here and go back to the Default or if I would click Exit to go back to the Default. Then I'm going to lose those hidden columns.
I'm not going to be able to be able to get them back, but if I just hide the columns I want to hide and stay in here and make sure that everyone else is in their own Sheet View and I'm in my Sheet View, yeah, it seems it seems like it might work.
So what's the lesson here?
I think the lesson is to use Sheet View, you have to be saved to OneDrive.
You have to consciously decide to create a new view.
I recommend, (even though they say you don't have to) to create a view called Show All so you can get back to that.
And then click New View and name it and then do any filtering and sorting you want to do.
Be aware that you know even though you kind of click this Keep button or name it, those both do the same thing.
That it's not just the items that have been applied at that point.
Once you have the Bill view displayed and you have the black column letters up here and row numbers over here.
When you are in Sheet View, any changes that you make on any computer.
Or any of your coworkers make on any computer when they have the Bill view displayed.
Will become part of the definition of the Bill view.
That is something I did not understand until Christine asked this question. I think it's important.
it's not documented in Excel help, and hopefully with your understanding of that.
It will help you in Sheet View.
Hey, check out my new courses on the Retrieve platform.
Its an amazing, interactive, searchable Artificial Intelligence. Type what you are looking for.
They will take you right to that spot in the video.
And then, if you don’t have time to watch the video, just read the transcript in any of several languages. It is an amazing platform. Check it out.
If you like these videos, please down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
Alright, well hey I want to thank you for stopping by.
This was a longer topic on something that is relatively new, I think for a lot of us.
But hopefully will help you understand Sheet View when you get into a collaboration situation at work. Thanks for stopping by.
I'll see you next time for another netcast from MrExcel.
Let's hear you, Nancy.
