Wyn's book for shipment to USA:
mrexcel.e-junkie.com
This video covers six exciting things:
1) The Show Changes feature which had been in Excel Online is now in Desktop Excel
2) Data Validation partial matching is working again
3) There is a new look for Excel Options
4) A new IMAGE function comes to Excel to display an image
5) Wyn Hopkin's new Power BI for the Excel Analyst book... it drops on November 1, but I have 3 cases to ship now.... US only.
6) The 14 functions for text and array manipulation are coming to general availability.
Table of Contents
(0:00) Four new features today
(0:54) Show Changes made in last 60 days
(1:30) Validation List partial matching is back again
(2:10) Excel Options has a new look
(2:39) Wyn Hopkins' Power BI book
(3:31) IMAGE function to display from a formula
Power BI for the Excel Data Analyst Print Book to USA Only
Buy Power BI for the Excel Data Analyst Print Book to USA Only from Holy Macro Books
mrexcel.e-junkie.com
This video covers six exciting things:
1) The Show Changes feature which had been in Excel Online is now in Desktop Excel
2) Data Validation partial matching is working again
3) There is a new look for Excel Options
4) A new IMAGE function comes to Excel to display an image
5) Wyn Hopkin's new Power BI for the Excel Analyst book... it drops on November 1, but I have 3 cases to ship now.... US only.
6) The 14 functions for text and array manipulation are coming to general availability.
Table of Contents
(0:00) Four new features today
(0:54) Show Changes made in last 60 days
(1:30) Validation List partial matching is back again
(2:10) Excel Options has a new look
(2:39) Wyn Hopkins' Power BI book
(3:31) IMAGE function to display from a formula
Transcript of the video:
Wow - August 25th, 2022, four new Excel features, plus Wyn's new Power BI Book. This was going to be three new features, but as I was working on the video, a fourth feature just came out.
Here's what we have, Show Changes now comes to desktop Excel. The Validation List Partial Matching works again.
It was there, they took it away, now it's back.
A new look for Excel Options and an amazing new function called the IMAGE function that will pull an image from the web into your Excel spreadsheet.
Let's take a look at these. All right.
First off, Show Changes. I'm going to switch over here to a Workbook.
This Workbook saved to OneDrive.
So, any changes that I make here, I'll change the 1, 2, 3, 4 to 2, 3, 4, 5. They are logging all of these changes.
And before to see the changes from the last 60 days, you'd have to go to Excel online and open the Workbook there.
But now on the review tab, Show Changes and we can see that cell B4 was edited from 1, 2, 3, 4 to 2, 3, 4, 5 just now.
Five hours ago, I was doing a seminar and I changed this cell out here.
I changed the formula in that cell.
Now the thing that remember here is like, if I would change this number 5,5,5,5, these cells are all calculated. They're going to change.
Those changes aren't logged, it's just the cell that I went to, I type something in or changed the formula.
I can even come here, right click and say, Show Changes and I'll get just the changes for that item.
This has been a great feature in Excel Online, and I'm glad that it finally came to Desktop Excel.
Next up, Validation List Partial Matching is working again.
Here's my list, apple banana, cherry, Fuji apple, gala apple and pineapple, that's the tricky one.
Data Validation, I'm going to allow a list and that list is right over here.
All right.
We've always had this before, but now if I start to type apple, they were going to shorten the list down to apple, Fuji apple, and gala apple, but not pineapple, right?
If it just starts to match in the middle of the word, no deal.
Case insensitive, it's not that the A was lowercase, it's that it doesn't have a space before it.
It's a cool feature, something we've needed for a long time.
New look for Excel Options.
So, if we come here to file, options, they kind of change the color here that we have some dark gray that wasn't there before, the check boxes have a little bit of green, some gray over here.
I like it.
This first showed up in some of the Chart Format Panes and we're like, oh, what is this? But it seems to be consistent and everywhere.
It's pretty cool. All right.
Big news, Wyn Hopkins and Excel MVP in Perth.
Australia has written a beautiful book, Power BI For The Excel Analyst.
It's the first Power BI book aimed at people who use Excel all the time. I'm excited about this book.
The book's been printed in color.
It's coming out in most of the world on November 1st. That's when Amazon will have it.
That's when Amazon in your country should have it.
But if you happen to be in the USA, I have snagged several cases straight from the printer.
Between the printer and the distributor, I had them shipped here and I can ship those. Unfortunately USA only, $39.95 and that includes media mail shipping to the United States.
There'll be a link down in the YouTube description below or an I in the top right hand corner, and check that out.
Limited time till these cases run out and then we're all waiting for Amazon on November 1st.
All right.
The last feature, a great new function called the IMAGE function.
What we do is we pass it a URL where that image is, Some Alt Text if we want, and then a sizing option, we'll take a look at that.
And height and width. This image right here came from Wyn's website.
There is the URL, alternate text, and we'll take a look at that sizing option.
That's the one that's a little bit confusing.
So, if we put a sizing option of zero, it's going to fit the image in the cell and maintain its aspect ratio.
So, as a cell gets taller, it will go until it hits the right edge, but then not get any larger than that.
If I would change that here, let's make it a little bit smaller like this.
If I would change that argument from a zero to a one, then it's going to fill the cell no matter what.
Even though it looks kind of scrunched like that, scrunched that's the technical term.
If I would change to two, it's going to maintain the original size, even if it's bigger than the cell boundary.
And then if I change it to three, then I have to specify the height and the width that I want it to be.
Wow.
I remember one of my favorite clients called me back in 2002 and said, "I want to display pictures in an Excel spreadsheet, the best selling products can we do that?" And we had to use VBA and wow, with this IMAGE function, we would've been able to just do it. All right.
I called Jerry.
I said, "Jerry, you were 20 years ahead of your time.
They finally came up with your idea and boy, is it beautiful". All right.
So, for those of us in Insiders Fast, Show Changes comes to desktop Excel. The Validation List Partial Matching works.
New look for Excel Options, the amazing IMAGE function.
For those of you in the general release, check this out there, rolling out the 14 new functions to general availability, probably the monthly channel.
If you're on semi annual, you probably still have to wait until I guess January.
So, that's text split, text before, text after, and all of these other great functions. What an exciting day.
I happen to just discover the review, Show Changes when I was doing a seminar and I was about to say, "It's not here.
You have to go to Excel online" and low and behold it was there. So, how exciting is that?
I want to thank you for stopping by, we'll see you next time for another Netcast from MrExcel.
Here's what we have, Show Changes now comes to desktop Excel. The Validation List Partial Matching works again.
It was there, they took it away, now it's back.
A new look for Excel Options and an amazing new function called the IMAGE function that will pull an image from the web into your Excel spreadsheet.
Let's take a look at these. All right.
First off, Show Changes. I'm going to switch over here to a Workbook.
This Workbook saved to OneDrive.
So, any changes that I make here, I'll change the 1, 2, 3, 4 to 2, 3, 4, 5. They are logging all of these changes.
And before to see the changes from the last 60 days, you'd have to go to Excel online and open the Workbook there.
But now on the review tab, Show Changes and we can see that cell B4 was edited from 1, 2, 3, 4 to 2, 3, 4, 5 just now.
Five hours ago, I was doing a seminar and I changed this cell out here.
I changed the formula in that cell.
Now the thing that remember here is like, if I would change this number 5,5,5,5, these cells are all calculated. They're going to change.
Those changes aren't logged, it's just the cell that I went to, I type something in or changed the formula.
I can even come here, right click and say, Show Changes and I'll get just the changes for that item.
This has been a great feature in Excel Online, and I'm glad that it finally came to Desktop Excel.
Next up, Validation List Partial Matching is working again.
Here's my list, apple banana, cherry, Fuji apple, gala apple and pineapple, that's the tricky one.
Data Validation, I'm going to allow a list and that list is right over here.
All right.
We've always had this before, but now if I start to type apple, they were going to shorten the list down to apple, Fuji apple, and gala apple, but not pineapple, right?
If it just starts to match in the middle of the word, no deal.
Case insensitive, it's not that the A was lowercase, it's that it doesn't have a space before it.
It's a cool feature, something we've needed for a long time.
New look for Excel Options.
So, if we come here to file, options, they kind of change the color here that we have some dark gray that wasn't there before, the check boxes have a little bit of green, some gray over here.
I like it.
This first showed up in some of the Chart Format Panes and we're like, oh, what is this? But it seems to be consistent and everywhere.
It's pretty cool. All right.
Big news, Wyn Hopkins and Excel MVP in Perth.
Australia has written a beautiful book, Power BI For The Excel Analyst.
It's the first Power BI book aimed at people who use Excel all the time. I'm excited about this book.
The book's been printed in color.
It's coming out in most of the world on November 1st. That's when Amazon will have it.
That's when Amazon in your country should have it.
But if you happen to be in the USA, I have snagged several cases straight from the printer.
Between the printer and the distributor, I had them shipped here and I can ship those. Unfortunately USA only, $39.95 and that includes media mail shipping to the United States.
There'll be a link down in the YouTube description below or an I in the top right hand corner, and check that out.
Limited time till these cases run out and then we're all waiting for Amazon on November 1st.
All right.
The last feature, a great new function called the IMAGE function.
What we do is we pass it a URL where that image is, Some Alt Text if we want, and then a sizing option, we'll take a look at that.
And height and width. This image right here came from Wyn's website.
There is the URL, alternate text, and we'll take a look at that sizing option.
That's the one that's a little bit confusing.
So, if we put a sizing option of zero, it's going to fit the image in the cell and maintain its aspect ratio.
So, as a cell gets taller, it will go until it hits the right edge, but then not get any larger than that.
If I would change that here, let's make it a little bit smaller like this.
If I would change that argument from a zero to a one, then it's going to fill the cell no matter what.
Even though it looks kind of scrunched like that, scrunched that's the technical term.
If I would change to two, it's going to maintain the original size, even if it's bigger than the cell boundary.
And then if I change it to three, then I have to specify the height and the width that I want it to be.
Wow.
I remember one of my favorite clients called me back in 2002 and said, "I want to display pictures in an Excel spreadsheet, the best selling products can we do that?" And we had to use VBA and wow, with this IMAGE function, we would've been able to just do it. All right.
I called Jerry.
I said, "Jerry, you were 20 years ahead of your time.
They finally came up with your idea and boy, is it beautiful". All right.
So, for those of us in Insiders Fast, Show Changes comes to desktop Excel. The Validation List Partial Matching works.
New look for Excel Options, the amazing IMAGE function.
For those of you in the general release, check this out there, rolling out the 14 new functions to general availability, probably the monthly channel.
If you're on semi annual, you probably still have to wait until I guess January.
So, that's text split, text before, text after, and all of these other great functions. What an exciting day.
I happen to just discover the review, Show Changes when I was doing a seminar and I was about to say, "It's not here.
You have to go to Excel online" and low and behold it was there. So, how exciting is that?
I want to thank you for stopping by, we'll see you next time for another Netcast from MrExcel.
