A new feature announced at #MSIgnite and now available to Office Insiders: Excel can use Artificial Intelligence to Answer Questions About Your Data.
Transcript of the video:
Learn Excel from MrExcel Podcast Episode 2298.
Ask Me a Question About Your Data.
Hey welcome back to MrExcel Netcast.
I'm Bill Jelen.
This one so good, I might just put it up out of order.
It's Sunday when I'm recording this and tomorrow's a holiday in the US.
I think I might just let it go today this was announced at Ignite, but when I was recording the other six videos at Ignite I didn't have this one yet.
It hadn't been released to me. It's only for Insiders Fast.
I have a data set here. Less than 250,000 cells, so 11 cells by 564 rows.
Region, Category, Product name, and let's assume I know nothing about it. So I just have this data that I have questions so.
Yeah, we've had the Ideas icon for a couple of years now and it is mildly interesting.
You know, because it's trying to discover is trying to discover trends in your data, but now they taking this to a whole new level with this new feature called Ask a Question About Your Data, right?
So rather than just let them randomly try and find something that's interesting, you can tell them what you want to know.
And it does an amazing job of giving the answer.
So let's say [ Typing ]Who are the top 5 customers [ finish typing ].
And it shows me the top five customers based on total quantity. So "Top five customers by sales".
And now I get the top five customers based on sales.
Top five customers by sales this year.
This is freaking amazing. Look at this.
Assume you can't create a SUMIF. You don't know how to do Data, Filter, Advanced.
You don't know how to create a pivot table.
This artificial intelligence is taking your natural English sentence and giving you results.
Top five customers by sales this year.
So A Datum Corporation 447,577 I can ask for last year.
And it's Worldwide Importers. 440076.
Now I had a column here with names right?
We should enable this amazing thing to to work if I say who had the most sales this year.
Because I said "Who" they're going to look through the columns and trying to figure out which one has names.
So Chris Neilson was her top salesman.
Is nickel and then here we have something that's called category. What if I wanted the top five *categories*?
Now because I'm an English speaker, I know that category with a Y change this to "i e s", so actually there's only three categories, so top two categories by sales this year.
And they understood the categories and category is the same thing. Now here's a gotcha, right?
I now realize wow, this would actually be better if I had added a year field in.
And so I want to go back to the original data and my first try.
At this I just came out here and said year equal year of this.
Double clicking copy that down alright, but the problem is Ideas doesn't see that new data is kind of, sort of, not the same, but it's sort of like a pivot cache.
When you create the pivot table, they load that data in the memory.
When I first click ideas, yeah, well they loaded the data and now they don't see that new column.
So in order to get that column to be part of the analysis, you would close Ideas. Then come back in here click ideas.
and I can ask for sales by year.
That's funny. It's not funny at all.
I didn't want a chart, I just wanted I just wanted the numbers show me sales by year as numbers. That's not going to work.
OK Alright.
Tiny, tiny bit of frustration there, but here's the deal. This is brand new, right?
This will get better so far. A lot of sentences I'm typing in are working perfectly.
There's a few that I type in where I wanted one thing I'm getting something else, but it's still really, really good.
Now what if I want to top five customer sales by year with sales and profit.
And then insert that.
There we are. OK so Year, Customer, Sum of Sales, Sum of Profit.
Should that have been a pivot table maybe?
Although I guess if you're using this maybe you're a person who would be freaked out by a pivot table.
I don't know this really has the chance to enable a lot of people who can't figure something out in Excel to do something in Excel.
In fact, my one fear with this is it's such an important feature that I'm going to estimate probably 60% of the world can benefit from this feature, but it is not discoverable the discoverability rate is probably like 7% or something like that. Why?
Because people aren't going to click on Ideas.
It's not a compelling icon and if you are a pro and you've used ideas before, you might now dismiss it because it wasn't coming up with good ideas.
So to put this new box here where no one can see it. Yeah, I'm not sure that's the best thing.
Where should have been? It should've been up here in Tell Me.
The issue is you have to click on ideas for them to load the data into memory, right?
So up here would Tell Me it hasn't loaded the data into memory yet. I think it's really, really good.
You should try it if you're on Insider's.
If you have coworkers who are completely afraid of Excel, you should tell him about this.
As this gets better and it handles more of our sentences yeah, definitely could be a massive game changer.
Alright, there you have it.
The brand new Ask a Question about your Data.
Check out my new book MrExcel LX the Holy Grail of Excel tips.
I love when the Excel team great keeps creating new features.
Keep doing it Excel team but it makes this book more and more obsolete so will have to come up with another version of it at some point.
If you like what you see here - trying to get you those new features as soon as I know about them - please down below this video, Subscribe and ring the Bell.
The Bell will get you notifications when new features come along. I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Ask Me a Question About Your Data.
Hey welcome back to MrExcel Netcast.
I'm Bill Jelen.
This one so good, I might just put it up out of order.
It's Sunday when I'm recording this and tomorrow's a holiday in the US.
I think I might just let it go today this was announced at Ignite, but when I was recording the other six videos at Ignite I didn't have this one yet.
It hadn't been released to me. It's only for Insiders Fast.
I have a data set here. Less than 250,000 cells, so 11 cells by 564 rows.
Region, Category, Product name, and let's assume I know nothing about it. So I just have this data that I have questions so.
Yeah, we've had the Ideas icon for a couple of years now and it is mildly interesting.
You know, because it's trying to discover is trying to discover trends in your data, but now they taking this to a whole new level with this new feature called Ask a Question About Your Data, right?
So rather than just let them randomly try and find something that's interesting, you can tell them what you want to know.
And it does an amazing job of giving the answer.
So let's say [ Typing ]Who are the top 5 customers [ finish typing ].
And it shows me the top five customers based on total quantity. So "Top five customers by sales".
And now I get the top five customers based on sales.
Top five customers by sales this year.
This is freaking amazing. Look at this.
Assume you can't create a SUMIF. You don't know how to do Data, Filter, Advanced.
You don't know how to create a pivot table.
This artificial intelligence is taking your natural English sentence and giving you results.
Top five customers by sales this year.
So A Datum Corporation 447,577 I can ask for last year.
And it's Worldwide Importers. 440076.
Now I had a column here with names right?
We should enable this amazing thing to to work if I say who had the most sales this year.
Because I said "Who" they're going to look through the columns and trying to figure out which one has names.
So Chris Neilson was her top salesman.
Is nickel and then here we have something that's called category. What if I wanted the top five *categories*?
Now because I'm an English speaker, I know that category with a Y change this to "i e s", so actually there's only three categories, so top two categories by sales this year.
And they understood the categories and category is the same thing. Now here's a gotcha, right?
I now realize wow, this would actually be better if I had added a year field in.
And so I want to go back to the original data and my first try.
At this I just came out here and said year equal year of this.
Double clicking copy that down alright, but the problem is Ideas doesn't see that new data is kind of, sort of, not the same, but it's sort of like a pivot cache.
When you create the pivot table, they load that data in the memory.
When I first click ideas, yeah, well they loaded the data and now they don't see that new column.
So in order to get that column to be part of the analysis, you would close Ideas. Then come back in here click ideas.
and I can ask for sales by year.
That's funny. It's not funny at all.
I didn't want a chart, I just wanted I just wanted the numbers show me sales by year as numbers. That's not going to work.
OK Alright.
Tiny, tiny bit of frustration there, but here's the deal. This is brand new, right?
This will get better so far. A lot of sentences I'm typing in are working perfectly.
There's a few that I type in where I wanted one thing I'm getting something else, but it's still really, really good.
Now what if I want to top five customer sales by year with sales and profit.
And then insert that.
There we are. OK so Year, Customer, Sum of Sales, Sum of Profit.
Should that have been a pivot table maybe?
Although I guess if you're using this maybe you're a person who would be freaked out by a pivot table.
I don't know this really has the chance to enable a lot of people who can't figure something out in Excel to do something in Excel.
In fact, my one fear with this is it's such an important feature that I'm going to estimate probably 60% of the world can benefit from this feature, but it is not discoverable the discoverability rate is probably like 7% or something like that. Why?
Because people aren't going to click on Ideas.
It's not a compelling icon and if you are a pro and you've used ideas before, you might now dismiss it because it wasn't coming up with good ideas.
So to put this new box here where no one can see it. Yeah, I'm not sure that's the best thing.
Where should have been? It should've been up here in Tell Me.
The issue is you have to click on ideas for them to load the data into memory, right?
So up here would Tell Me it hasn't loaded the data into memory yet. I think it's really, really good.
You should try it if you're on Insider's.
If you have coworkers who are completely afraid of Excel, you should tell him about this.
As this gets better and it handles more of our sentences yeah, definitely could be a massive game changer.
Alright, there you have it.
The brand new Ask a Question about your Data.
Check out my new book MrExcel LX the Holy Grail of Excel tips.
I love when the Excel team great keeps creating new features.
Keep doing it Excel team but it makes this book more and more obsolete so will have to come up with another version of it at some point.
If you like what you see here - trying to get you those new features as soon as I know about them - please down below this video, Subscribe and ring the Bell.
The Bell will get you notifications when new features come along. I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
