The old Custom View feature in Excel never worked with Tables. A new Sheet View feature is coming to Win32 versions of Office 365. This new Sheet View allows different sorting and filtering in each view. Episode 2333 takes a look at this feature.
Table of Contents
(0:00) Introduction
(0:15) Question from Bob about new greyed-out feature on Review tab
(1:30) Creating a Sheet View
(2:33) User Interface if multiple people editing
(3:05) How do they handle SUBTOTAL or AGGREGATE functions?
(3:25) Different Sorting in each view
Table of Contents
(0:00) Introduction
(0:15) Question from Bob about new greyed-out feature on Review tab
(1:30) Creating a Sheet View
(2:33) User Interface if multiple people editing
(3:05) How do they handle SUBTOTAL or AGGREGATE functions?
(3:25) Different Sorting in each view
Transcript of the video:
Learn Excel from MrExcel podcast Episode #2333. Sheet View comes to Win32.
Okay welcome back to the MrExcel netcast. I'm Bill Jelen.
Today's question comes to us from Bob.
He says hey what's the thing that appeared on the View tab, Out here in the left hand side.
It is called sheet view, and it's been greyed out ever since it arrived. It arrived in Insiders Fast earlier in May.
Alright, so, there's an earlier video where I talked about Sheet View that came to Excel online.
In that video I lamented that although this is a really cool feature it's only in Excel Online. Well here now it is in Win32. Why is it greyed out?
It is grayed out because I've saved this file to my local hard drive.
What we have to do is File, Save As, and save to OneDrive or SharePoint Online.
And, bingo it is alive.
Sheet view is kind of like Custom Views that we used to have. Custom Views is right here. There are a few differences.
Number 1: Custom Views would never work if this was a table so Ctrl+T to make it into a Table. On the View tab see Custom Views is now grayed out.
But Sheet View continues to work. Custom Views would work across all the sheets in a workbook.
Sheet View is just one sheet.
I am going to come here and Filter to just Andy. Click OK. Alright and I get just the Andy records.
You will see up here this is called Default.
Currently, no one else is editing this file so I actually have to create my own View. The first thing I do is I click New. You will see the headers turn black.
Look down here in the sheet tab we get a little eye icon that tells us that there's a Sheet View that's been applied.
It is currently called Temporary View.
If I want to be able to use this and I want others to be able to use this, I'll call it Andy. Like that. And I can have multiple views.
I come back here go back to "Default". Clear the filters.
Let's filter to "Rose". Filter to Selected Cell's Value. I get the Rose records.
I'll create a new Sheet View and I'll call it "Rose View".
Alright, and then I can very quickly jump back and forth between Andy View and Rose View.
And you can have as many different views as you want.
Now this behavior is actually very different if someone else is editing the file.
So here I am in an Excel Online and now two people are editing this file at the same time.
I'll do a filter and choose just Zeke and here we are: "Others are also making changes do you want to see sorting and filling from others?" See just mine or see everyone's.
If you click See Everyone's then the changes are going to happen to everybody.
But if you click See Mine, then Excel will automatically create a new temporary view for you.
I could call Zeke. Now, a couple of things.
if you make a view, it is not private to you.
Anyone who is editing this workbook will be able to choose any of the views. A view is universal across the workbooks.
What if, at the bottom, there had been a SUBTOTAL function or an AGGREGATE function?
These include only the visible values. The "Default" view is the official version.
That's the one that's going to get saved.
Everyone else is just looking at a different version. The other thing that's unusual here.
The Custom Views never did this. Let's say that I go into the Andy view. We have Andy.
And Andy likes to see his data sorted by quantity. So I go into Quantity.
Sort descending. That just moved these records around.
If I come back to another version, someone else who's editing this workbook, those records are still in the original sequence.
So it's the sheet view that gets the temporary sorting.
It is pretty crazy that this works. This is different than custom views.
Sheet Views is certainly necessary if multiple people are editing and each person wants to filter to just their records. I'm really glad that this came to Win32.
I was a little alarmed when it came to Excel Online first.
I thought, "Oh no", this is going to be one of those XLO-exclusive features which I'll never get to see.
Because I don't use Excel Online.
But now that it came to Win32 - there are a lot of good reasons, whether you're collaborating or not.
Well hey, if you like these tricks please click "Like" down below the video. Subscribe, and ring that bell.
Feel free to post any questions in the comments below.
My new book, MrExcel 2020 Seeing Excel Clearly.
Click that "i" in the top right hand corner for more information. I want to thank you for stopping by.
I will see you next time for another net cast from MrExcel.
Okay welcome back to the MrExcel netcast. I'm Bill Jelen.
Today's question comes to us from Bob.
He says hey what's the thing that appeared on the View tab, Out here in the left hand side.
It is called sheet view, and it's been greyed out ever since it arrived. It arrived in Insiders Fast earlier in May.
Alright, so, there's an earlier video where I talked about Sheet View that came to Excel online.
In that video I lamented that although this is a really cool feature it's only in Excel Online. Well here now it is in Win32. Why is it greyed out?
It is grayed out because I've saved this file to my local hard drive.
What we have to do is File, Save As, and save to OneDrive or SharePoint Online.
And, bingo it is alive.
Sheet view is kind of like Custom Views that we used to have. Custom Views is right here. There are a few differences.
Number 1: Custom Views would never work if this was a table so Ctrl+T to make it into a Table. On the View tab see Custom Views is now grayed out.
But Sheet View continues to work. Custom Views would work across all the sheets in a workbook.
Sheet View is just one sheet.
I am going to come here and Filter to just Andy. Click OK. Alright and I get just the Andy records.
You will see up here this is called Default.
Currently, no one else is editing this file so I actually have to create my own View. The first thing I do is I click New. You will see the headers turn black.
Look down here in the sheet tab we get a little eye icon that tells us that there's a Sheet View that's been applied.
It is currently called Temporary View.
If I want to be able to use this and I want others to be able to use this, I'll call it Andy. Like that. And I can have multiple views.
I come back here go back to "Default". Clear the filters.
Let's filter to "Rose". Filter to Selected Cell's Value. I get the Rose records.
I'll create a new Sheet View and I'll call it "Rose View".
Alright, and then I can very quickly jump back and forth between Andy View and Rose View.
And you can have as many different views as you want.
Now this behavior is actually very different if someone else is editing the file.
So here I am in an Excel Online and now two people are editing this file at the same time.
I'll do a filter and choose just Zeke and here we are: "Others are also making changes do you want to see sorting and filling from others?" See just mine or see everyone's.
If you click See Everyone's then the changes are going to happen to everybody.
But if you click See Mine, then Excel will automatically create a new temporary view for you.
I could call Zeke. Now, a couple of things.
if you make a view, it is not private to you.
Anyone who is editing this workbook will be able to choose any of the views. A view is universal across the workbooks.
What if, at the bottom, there had been a SUBTOTAL function or an AGGREGATE function?
These include only the visible values. The "Default" view is the official version.
That's the one that's going to get saved.
Everyone else is just looking at a different version. The other thing that's unusual here.
The Custom Views never did this. Let's say that I go into the Andy view. We have Andy.
And Andy likes to see his data sorted by quantity. So I go into Quantity.
Sort descending. That just moved these records around.
If I come back to another version, someone else who's editing this workbook, those records are still in the original sequence.
So it's the sheet view that gets the temporary sorting.
It is pretty crazy that this works. This is different than custom views.
Sheet Views is certainly necessary if multiple people are editing and each person wants to filter to just their records. I'm really glad that this came to Win32.
I was a little alarmed when it came to Excel Online first.
I thought, "Oh no", this is going to be one of those XLO-exclusive features which I'll never get to see.
Because I don't use Excel Online.
But now that it came to Win32 - there are a lot of good reasons, whether you're collaborating or not.
Well hey, if you like these tricks please click "Like" down below the video. Subscribe, and ring that bell.
Feel free to post any questions in the comments below.
My new book, MrExcel 2020 Seeing Excel Clearly.
Click that "i" in the top right hand corner for more information. I want to thank you for stopping by.
I will see you next time for another net cast from MrExcel.
