Three new features coming to Excel.
1) Value Preview will evaluate part of your formula when you select it in the Excel formula bar. You don't have to press F9 and Esc anymore.
2) AutoSave moves from an icon to Words in the Excel Title Bar
3) Small preview of Personalized Toolbar in Excel
Table of Contents
(0:00) Three new Excel Features
(0:30) F9 replaced with Value Preview
(3:57) AutoSave icon moves to Title Bar words
(5:07) Personalized Toolbar in Preview
(10:38) Wrap-up
1) Value Preview will evaluate part of your formula when you select it in the Excel formula bar. You don't have to press F9 and Esc anymore.
2) AutoSave moves from an icon to Words in the Excel Title Bar
3) Small preview of Personalized Toolbar in Excel
Table of Contents
(0:00) Three new Excel Features
(0:30) F9 replaced with Value Preview
(3:57) AutoSave icon moves to Title Bar words
(5:07) Personalized Toolbar in Preview
(10:38) Wrap-up
Transcript of the video:
Now, three new features in Excel. And the big one is less F9 Escape in the Formula bar.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
I'm going to show you three different things today.
The AutoSave icon moves from the Quick Access Toolbar to words in the Title. The Personalized Toolbar is in Preview.
And then the one that I think is really good is this new thing they're calling Value Preview.
It's like pressing F9 in the formula bar, but on hover.
Let's start with that one.
Okay, so here is a formula and, of course, up here in the formula bar we can see the formula.
But the thing that's different now is, if I would select any bit of that formula with the mouse and then hover, they'll show me that the month of today is one.
And that's right, it's January, so it's the first month.
And we can actually, anywhere inside the index, we can just click on array and it'll show us that item.
And then what's the row number? And the row number is one.
It's pretty cool.
Of course, if you watch ExcelIsFun, he always shows us this trick where he presses F9.
But then has to remember to press Escape to go back to the original formula. And, now, we won't have to do that anymore.
We can just simply hover and it'll work.
Now this is version one and, of course, the very first formula that I pulled up, because I'm working on it for another video, was something that would generate a date.
And, unfortunately, when we generate a date, it's giving us the serial number instead of the date itself.
And I said to Steve on the Excel team, I said, "Hey, any chance that could be a date?" And he's like, "Well, yeah, but it's complicated. How are we going to show the date?
Is it your default date in your local format?" He says, "We understand we want to get there, but we're not sure what date is the right date to show". And I get it.
It's more complicated than just show us a date. So they're certainly aware of that.
You can click any cell there. So 44962, I can click the holiday range.
See, I have to get used to this. The tooltip is right here.
It would've been easier just to click on end date and then holidays, rather than trying to scoop it out using the formula bar. So this is something we'll get used to.
It's pretty cool. I tried it on an array.
So we have an array here, so sequence of 4, 2, 2, 4, and they'll show me.
And even in the array formula nomenclature there, where a comma means go to the next column, and a semicolon means go to the next row.
Here, I have an XLOOKUP, and once I click inside the XLOOKUP, I can click on each item in the tooltip.
So the lookup value is fig, the lookup array is those items.
The return array is these items. That's right, so it's cool, it's cool.
Over here, we have an if function.
Click inside the if, I can just click on the logical test.
If I need to see what today is, the serial number for today is 44952.
Now, what if you select part of something like ODA? They have no clue, what are you talking about?
We don't know what that means. It's also possible to do...
Okay, let's do a strange one. Let's do equal 301 + 12345.
And I will select 301 + 12 and it will evaluate that part.
Now, F9, I don't think F9... What would F9 do?
Oh yeah, F9 would do it too. Okay, so I guess it acts just like F9.
So, to me, that seems a bit of a silly one. Just be aware that it's there.
All right, so I think this arrived yesterday for Insiders Fast.
I had to do the update to get it. And it's probably rolled out to 50% of the people.
So it's one of these things that's coming. I really like it.
I can't wait to hear what ExcelIsFun has to say, because he uses this method constantly.
And, hopefully, he will love that. All right, next one.
This seems really small to me.
Back last week, and even on my laptop, AutoSave is...
This isn't actually part of the Quick Access Toolbar, is it? Everything to the left of that vertical bar.
And AutoSave was taking up a lot of space up there, with an icon to show whether it was on or off. They remove that now by default.
And where have they moved it? They've moved it up here to the title bar.
So there is the title bar, and at the end, they show you if AutoSave is off or on.
And then, in this little dropdown here, they tell me, "Well, hey, if you want to use Auto Save, you need to upload your document to the cloud". And then we can see the toggle, if it's on or off.
If we right click and customize Quick Access Toolbar, is AutoSave available to bring it back in case you like it there? And it looks like, no.
This one's going to drive you crazy a little bit if you use this a lot.
I never auto save, so I don't look there.
It's going to take some getting used to, instead of looking up here on the top left to look all the way over here on the right.
But you'll get used to it. Why did they do this?
My theory is that they're introducing this thing called a Personalized Toolbar.
All right, now, I have two computers here on my desk.
Neither of these have this.
But my teaching laptop, when I go do a Power Excel seminar, I pull that teaching laptop out, and it has it.
All right, so we get this little notifier, "A Toolbar Just for You.
Welcome to a more personalized Office experience.
We'll create a toolbar just for you, which contains your most frequently used commands for quick and easy access.
You can still view the classic ribbon whenever you want". All right, so I said, "Sure, try it".
The great news is you can switch back.
And, of course, on that day I was leaving for class, I couldn't bother to look at it. But I looked at it today.
And we'll take a look.
And then you can re-expand the ribbon by clicking any tab, right click any action to add it to your new toolbar.
So we can add new things to the toolbar.
Okay, so here's a look at the Personalized Toolbar on my laptop.
And, of course, on my laptop, I'm signed in as Bill Jelen, so it's the same account.
I'm fairly convinced that when they looked at what I'm doing a lot, they're looking at all of the machines, like insert shape, and insert screen clipping, and shape quick styles.
Those are all things I do when I'm doing a podcast, and I never do a podcast from the laptop.
All right, so these are things, yes, I do these a lot, but I do it on the desktop.
And it's interesting that it showed up on the laptop.
That might be a great feature if they were that clever to do it.
All right, here's all of the things that I had on the Quick Access Toolbar, and all of these things that are lost.
So these are things that I think that I do a lot, but they don't perceive that I do it a lot.
And then, the other thing you'll notice is that when this toolbar is shown, the ribbon is automatically minimized.
So I started to look at these items right here, this whole group right here. "Wow, I do these a lot," I said.
But I don't need them on the Quick Access Toolbar because they're right here available to me on the home tab.
I don't take things from my home tab and add them to the Quick Access Toolbar.
But now that the ribbon is collapsed by default, they had to add these back. Pivot table, yep, certainly do those a lot.
But I don't mind going to the insert tab, insert picture, insert shape, screen clipping.
This one, the print area group, to set or clear the print area, I'm constantly doing set print area, never doing clear print area.
So it's a little weird that they chose the whole group instead of set print area. Yeah.
And then, okay, for those of us who are just going to reject this and just use the Quick Access Toolbar, a couple things you'll notice.
First off, right click, see, I can customize the Quick Access Toolbar. I can show above or below the ribbon.
And I can choose, if I go below the ribbon, then I can hide or show command labels.
All right, of that UI has been moved.
When you right click now, on the new Quick Access Toolbar, after you get this custom toolbar, when you right click on the Quick Access Toolbar, all you have is either remove or customize.
And then, once you go into Excel options, the customize, that's where they say toolbar position above or below, always show command labels.
And then, later, I also found that if you go to that little dropdown at the far right side of the ribbon, that they've moved above or below there as well.
See, it's not there in the new way, once you have the Personal Toolbar.
So they've move some things around which, even if you just reject the Personal Toolbar, is going to cause a little bit of adjustment for you. I can add things to the Personal Toolbar.
Right, so I added the filter, just to see, because they added the sort and filter group, which now I have two clicks to get to it. So I just added filters.
But the one thing I can't seem to do is, in this Personal Toolbar, I can't move things around.
I can't move them left to right.
Like, for example, a sort dialogue is here, which is quite a way from the sort and filter group.
And that's probably because this is on home and this is on data.
They've organized it in the same order as the ribbon that they're trying to simplify.
All right, so there we are.
I think the big news today is this replacement for F9 in the formula bar.
That's the one that a lot of people, I think, will be able to use.
And really like the AutoSave, it's going to take some getting used to, but I'll get used to it.
And then, this new Personal Toolbar, they're going to have to notify you on a day when you have nothing else to do, and you can actually take a look at it, and see what will work better for you.
For me, I'm probably just going to keep using the Quick Access Toolbar because, like I say, there's a lot of things...
I've customized my Quick Access Toolbar to show things that I use a lot that aren't in the ribbon.
And these things that aren't in the ribbon Subscript and Superscript, how would I ever get those back?
I don't know how I could right click on something that's not there to say, "Add to the Custom Toolbar". So this is interesting.
And it's also interesting that this one only appeared on one of three laptops.
And everyone I've talked to does not have this yet.
So maybe this is only rolled out, what, to 1%, 5%.
So they're probably doing some testing to see what the reaction is before they roll this one out wider.
All right, there you go. That was going to be three quick tips.
I think one really good one that I'm excited to use, and then two others that I will...
Well, in one case get used to.
In the other case, probably turn off for right now. I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
I'm going to show you three different things today.
The AutoSave icon moves from the Quick Access Toolbar to words in the Title. The Personalized Toolbar is in Preview.
And then the one that I think is really good is this new thing they're calling Value Preview.
It's like pressing F9 in the formula bar, but on hover.
Let's start with that one.
Okay, so here is a formula and, of course, up here in the formula bar we can see the formula.
But the thing that's different now is, if I would select any bit of that formula with the mouse and then hover, they'll show me that the month of today is one.
And that's right, it's January, so it's the first month.
And we can actually, anywhere inside the index, we can just click on array and it'll show us that item.
And then what's the row number? And the row number is one.
It's pretty cool.
Of course, if you watch ExcelIsFun, he always shows us this trick where he presses F9.
But then has to remember to press Escape to go back to the original formula. And, now, we won't have to do that anymore.
We can just simply hover and it'll work.
Now this is version one and, of course, the very first formula that I pulled up, because I'm working on it for another video, was something that would generate a date.
And, unfortunately, when we generate a date, it's giving us the serial number instead of the date itself.
And I said to Steve on the Excel team, I said, "Hey, any chance that could be a date?" And he's like, "Well, yeah, but it's complicated. How are we going to show the date?
Is it your default date in your local format?" He says, "We understand we want to get there, but we're not sure what date is the right date to show". And I get it.
It's more complicated than just show us a date. So they're certainly aware of that.
You can click any cell there. So 44962, I can click the holiday range.
See, I have to get used to this. The tooltip is right here.
It would've been easier just to click on end date and then holidays, rather than trying to scoop it out using the formula bar. So this is something we'll get used to.
It's pretty cool. I tried it on an array.
So we have an array here, so sequence of 4, 2, 2, 4, and they'll show me.
And even in the array formula nomenclature there, where a comma means go to the next column, and a semicolon means go to the next row.
Here, I have an XLOOKUP, and once I click inside the XLOOKUP, I can click on each item in the tooltip.
So the lookup value is fig, the lookup array is those items.
The return array is these items. That's right, so it's cool, it's cool.
Over here, we have an if function.
Click inside the if, I can just click on the logical test.
If I need to see what today is, the serial number for today is 44952.
Now, what if you select part of something like ODA? They have no clue, what are you talking about?
We don't know what that means. It's also possible to do...
Okay, let's do a strange one. Let's do equal 301 + 12345.
And I will select 301 + 12 and it will evaluate that part.
Now, F9, I don't think F9... What would F9 do?
Oh yeah, F9 would do it too. Okay, so I guess it acts just like F9.
So, to me, that seems a bit of a silly one. Just be aware that it's there.
All right, so I think this arrived yesterday for Insiders Fast.
I had to do the update to get it. And it's probably rolled out to 50% of the people.
So it's one of these things that's coming. I really like it.
I can't wait to hear what ExcelIsFun has to say, because he uses this method constantly.
And, hopefully, he will love that. All right, next one.
This seems really small to me.
Back last week, and even on my laptop, AutoSave is...
This isn't actually part of the Quick Access Toolbar, is it? Everything to the left of that vertical bar.
And AutoSave was taking up a lot of space up there, with an icon to show whether it was on or off. They remove that now by default.
And where have they moved it? They've moved it up here to the title bar.
So there is the title bar, and at the end, they show you if AutoSave is off or on.
And then, in this little dropdown here, they tell me, "Well, hey, if you want to use Auto Save, you need to upload your document to the cloud". And then we can see the toggle, if it's on or off.
If we right click and customize Quick Access Toolbar, is AutoSave available to bring it back in case you like it there? And it looks like, no.
This one's going to drive you crazy a little bit if you use this a lot.
I never auto save, so I don't look there.
It's going to take some getting used to, instead of looking up here on the top left to look all the way over here on the right.
But you'll get used to it. Why did they do this?
My theory is that they're introducing this thing called a Personalized Toolbar.
All right, now, I have two computers here on my desk.
Neither of these have this.
But my teaching laptop, when I go do a Power Excel seminar, I pull that teaching laptop out, and it has it.
All right, so we get this little notifier, "A Toolbar Just for You.
Welcome to a more personalized Office experience.
We'll create a toolbar just for you, which contains your most frequently used commands for quick and easy access.
You can still view the classic ribbon whenever you want". All right, so I said, "Sure, try it".
The great news is you can switch back.
And, of course, on that day I was leaving for class, I couldn't bother to look at it. But I looked at it today.
And we'll take a look.
And then you can re-expand the ribbon by clicking any tab, right click any action to add it to your new toolbar.
So we can add new things to the toolbar.
Okay, so here's a look at the Personalized Toolbar on my laptop.
And, of course, on my laptop, I'm signed in as Bill Jelen, so it's the same account.
I'm fairly convinced that when they looked at what I'm doing a lot, they're looking at all of the machines, like insert shape, and insert screen clipping, and shape quick styles.
Those are all things I do when I'm doing a podcast, and I never do a podcast from the laptop.
All right, so these are things, yes, I do these a lot, but I do it on the desktop.
And it's interesting that it showed up on the laptop.
That might be a great feature if they were that clever to do it.
All right, here's all of the things that I had on the Quick Access Toolbar, and all of these things that are lost.
So these are things that I think that I do a lot, but they don't perceive that I do it a lot.
And then, the other thing you'll notice is that when this toolbar is shown, the ribbon is automatically minimized.
So I started to look at these items right here, this whole group right here. "Wow, I do these a lot," I said.
But I don't need them on the Quick Access Toolbar because they're right here available to me on the home tab.
I don't take things from my home tab and add them to the Quick Access Toolbar.
But now that the ribbon is collapsed by default, they had to add these back. Pivot table, yep, certainly do those a lot.
But I don't mind going to the insert tab, insert picture, insert shape, screen clipping.
This one, the print area group, to set or clear the print area, I'm constantly doing set print area, never doing clear print area.
So it's a little weird that they chose the whole group instead of set print area. Yeah.
And then, okay, for those of us who are just going to reject this and just use the Quick Access Toolbar, a couple things you'll notice.
First off, right click, see, I can customize the Quick Access Toolbar. I can show above or below the ribbon.
And I can choose, if I go below the ribbon, then I can hide or show command labels.
All right, of that UI has been moved.
When you right click now, on the new Quick Access Toolbar, after you get this custom toolbar, when you right click on the Quick Access Toolbar, all you have is either remove or customize.
And then, once you go into Excel options, the customize, that's where they say toolbar position above or below, always show command labels.
And then, later, I also found that if you go to that little dropdown at the far right side of the ribbon, that they've moved above or below there as well.
See, it's not there in the new way, once you have the Personal Toolbar.
So they've move some things around which, even if you just reject the Personal Toolbar, is going to cause a little bit of adjustment for you. I can add things to the Personal Toolbar.
Right, so I added the filter, just to see, because they added the sort and filter group, which now I have two clicks to get to it. So I just added filters.
But the one thing I can't seem to do is, in this Personal Toolbar, I can't move things around.
I can't move them left to right.
Like, for example, a sort dialogue is here, which is quite a way from the sort and filter group.
And that's probably because this is on home and this is on data.
They've organized it in the same order as the ribbon that they're trying to simplify.
All right, so there we are.
I think the big news today is this replacement for F9 in the formula bar.
That's the one that a lot of people, I think, will be able to use.
And really like the AutoSave, it's going to take some getting used to, but I'll get used to it.
And then, this new Personal Toolbar, they're going to have to notify you on a day when you have nothing else to do, and you can actually take a look at it, and see what will work better for you.
For me, I'm probably just going to keep using the Quick Access Toolbar because, like I say, there's a lot of things...
I've customized my Quick Access Toolbar to show things that I use a lot that aren't in the ribbon.
And these things that aren't in the ribbon Subscript and Superscript, how would I ever get those back?
I don't know how I could right click on something that's not there to say, "Add to the Custom Toolbar". So this is interesting.
And it's also interesting that this one only appeared on one of three laptops.
And everyone I've talked to does not have this yet.
So maybe this is only rolled out, what, to 1%, 5%.
So they're probably doing some testing to see what the reaction is before they roll this one out wider.
All right, there you go. That was going to be three quick tips.
I think one really good one that I'm excited to use, and then two others that I will...
Well, in one case get used to.
In the other case, probably turn off for right now. I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
