Its like a Clip Art pane for Data! Data Explorer from Microsoft is a brand New, Free to download Add-in for Excel 2010 or 2013. Today, in Episode #1688, Bill demonstrates the new Data Explorer Add-in and shows the usefulness and utility of this handy, brilliant little addition to the Excel Add-in family.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
And for more information on Excel 2013, check out "Microsoft 2013 InDepth" -- by Bill Jelen. Excel 2013 In Depth is the beyond-the-basics, beneath-the-surface guide for everyone working with Excel 2013. Excel expert and MVP Bill Jelen provides specific, tested, proven solutions to the problems Excel users run into every day: the types of challenges other books ignore or oversimplify. Jelen thoroughly covers all facets of working with Excel 2013. Amazon.com: Excel 2013 In Depth (9780789748577): Jelen, Bill: Books
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
And for more information on Excel 2013, check out "Microsoft 2013 InDepth" -- by Bill Jelen. Excel 2013 In Depth is the beyond-the-basics, beneath-the-surface guide for everyone working with Excel 2013. Excel expert and MVP Bill Jelen provides specific, tested, proven solutions to the problems Excel users run into every day: the types of challenges other books ignore or oversimplify. Jelen thoroughly covers all facets of working with Excel 2013. Amazon.com: Excel 2013 In Depth (9780789748577): Jelen, Bill: Books
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel form MrExcel podcast episode 1688.
Data Explorer like the ClipArt Pane for Data.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today we want to talk about Data Explorer.
This is a brand new free download.
If you have Excel 2010 or Excel 2013, just published here at April 11th.
Data Explorer is, I've used it a few times it is amazingly cool.
Here's how you think about Data Explorer, you know you have Insert, ClipArt and you can search for ClipArt imagine if you could do the same thing for data, that's what Data Explorer is.
So, we come out here Data Explorer, let's say, I had sales by US state and I want to come up with population by state.
So, I can calculate per capita.
So, I click on Online Search get a task pane on the right hand side and we'll say population by state, press Enter.
They'll go out, look at that 250 results and it has live preview.
So, I just hover, I don't even click hover, on the right hand side and I see the data on the left hand side.
So, this state and then this weird column population with you know inhabitants per square mile, no that's not what I want and we have Mexico, Brazil, Brazil, Mexican.
I should have said US states. Hi! here we go the sixth one states and territory the third column is state and then fourth, fifth and sixth column are population that's perfect.
So, I click and that builds a query that goes out and gets that table from the web page this particular one is coming from Wikipedia.
All right! Now, there's extra columns, columns that I don't need, but I'm not gonna just delete those in Excel.
I'm going to use the Filter and Shape icon and using Filter and Shape, I'm gonna change the query here, right?
So, when I do things in Filter and Shape, it's actually remembered in the query.
So, I don't need this column here, I'm going to right click on the heading and say, Hide, right click on the heading and say, Hide. I realized this would have been faster in Excel because I could have deleted all the columns at once and maybe, that will come along in a later release of this.
You know, it's still in beta basically it's in a preview stage.
So, behind these columns there's also as I scroll down through this data some bizarre things there's some territories here that just have a dash and then the 50 states, 50 states plus DC, total US territory.
All right! So, they have the auto filter drop down.
So, I can say, if anything doesn't have a number, I don't want to click OK and then over here in states and territories, I can get rid of 50 states plus DC get rid of constantly the 50 states and then was a grand total.
Well, actually now, we got rid of the total by getting rid of the dashes.
All right! So, those things I just did hiding columns and then getting rid of some rows look over here on the right hand side there's a steps panel that I can expand.
All right and so, we got the data from the source we hit some columns and up here in the format bar you can even see what columns I hit.
So, it's like a Macro recorder almost for data.
All right and you could, you could change this or you can click this little query icon here and see the whole query that you wrote based on just doing these clicks.
All right, so, click Done here, my data changes it should be a table yeah there we go with a table formatting, but here's to him an amazing thing so, I did all of these steps wait, wait there's one more state or territory and population estimate those are, those are horrible headings.
So, let's come in here I'll right click and I'll rename, I'll just call this state and right click and rename and we'll call it population.
All right, click Done.
So now, it even looks cleaner is that really spaces before, no it's they're using it in debt.
So, if I you know I'm just gonna it's fine with my VLOOKUPS will work with that indent although it annoys me a little bit, but whatever.
Okay so, we have our query right?
Let's say, that this was data that changed every week or every day or every month when I come back here to query and click Refresh, it's not going to just go to the webpage and pull the data in it's going to go out pull the data in hide those columns get rid of the rows that I got rid of you know using the rules where we get rid of the dash and then get rid of the 50 states and total and so on.
this is an amazing, amazing tool it's called Data Explorer.
It's free you need Excel 2010 or newer.
I'll take a look at some other Data Explorer tricks in this week.
All right! There we go.
Hey, thanks for stopping by.
See you next time for another netcast from MrExcel.
Learn Excel form MrExcel podcast episode 1688.
Data Explorer like the ClipArt Pane for Data.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today we want to talk about Data Explorer.
This is a brand new free download.
If you have Excel 2010 or Excel 2013, just published here at April 11th.
Data Explorer is, I've used it a few times it is amazingly cool.
Here's how you think about Data Explorer, you know you have Insert, ClipArt and you can search for ClipArt imagine if you could do the same thing for data, that's what Data Explorer is.
So, we come out here Data Explorer, let's say, I had sales by US state and I want to come up with population by state.
So, I can calculate per capita.
So, I click on Online Search get a task pane on the right hand side and we'll say population by state, press Enter.
They'll go out, look at that 250 results and it has live preview.
So, I just hover, I don't even click hover, on the right hand side and I see the data on the left hand side.
So, this state and then this weird column population with you know inhabitants per square mile, no that's not what I want and we have Mexico, Brazil, Brazil, Mexican.
I should have said US states. Hi! here we go the sixth one states and territory the third column is state and then fourth, fifth and sixth column are population that's perfect.
So, I click and that builds a query that goes out and gets that table from the web page this particular one is coming from Wikipedia.
All right! Now, there's extra columns, columns that I don't need, but I'm not gonna just delete those in Excel.
I'm going to use the Filter and Shape icon and using Filter and Shape, I'm gonna change the query here, right?
So, when I do things in Filter and Shape, it's actually remembered in the query.
So, I don't need this column here, I'm going to right click on the heading and say, Hide, right click on the heading and say, Hide. I realized this would have been faster in Excel because I could have deleted all the columns at once and maybe, that will come along in a later release of this.
You know, it's still in beta basically it's in a preview stage.
So, behind these columns there's also as I scroll down through this data some bizarre things there's some territories here that just have a dash and then the 50 states, 50 states plus DC, total US territory.
All right! So, they have the auto filter drop down.
So, I can say, if anything doesn't have a number, I don't want to click OK and then over here in states and territories, I can get rid of 50 states plus DC get rid of constantly the 50 states and then was a grand total.
Well, actually now, we got rid of the total by getting rid of the dashes.
All right! So, those things I just did hiding columns and then getting rid of some rows look over here on the right hand side there's a steps panel that I can expand.
All right and so, we got the data from the source we hit some columns and up here in the format bar you can even see what columns I hit.
So, it's like a Macro recorder almost for data.
All right and you could, you could change this or you can click this little query icon here and see the whole query that you wrote based on just doing these clicks.
All right, so, click Done here, my data changes it should be a table yeah there we go with a table formatting, but here's to him an amazing thing so, I did all of these steps wait, wait there's one more state or territory and population estimate those are, those are horrible headings.
So, let's come in here I'll right click and I'll rename, I'll just call this state and right click and rename and we'll call it population.
All right, click Done.
So now, it even looks cleaner is that really spaces before, no it's they're using it in debt.
So, if I you know I'm just gonna it's fine with my VLOOKUPS will work with that indent although it annoys me a little bit, but whatever.
Okay so, we have our query right?
Let's say, that this was data that changed every week or every day or every month when I come back here to query and click Refresh, it's not going to just go to the webpage and pull the data in it's going to go out pull the data in hide those columns get rid of the rows that I got rid of you know using the rules where we get rid of the dash and then get rid of the 50 states and total and so on.
this is an amazing, amazing tool it's called Data Explorer.
It's free you need Excel 2010 or newer.
I'll take a look at some other Data Explorer tricks in this week.
All right! There we go.
Hey, thanks for stopping by.
See you next time for another netcast from MrExcel.
