- Hi, welcome to Excel Table Talk,
our monthly web show for our community.
My name is Blake Walsh and I'm a PM on the Excel team.
Before we get started, I'd like to plug
our online community at office.com/excel/community
where you can go create an account if you haven't already.
In there, you'll find a bunch of different spaces
where you can post questions and participate in discussions
and the like.
You'll also find my peers and I in there
as we're in there quite a lot
answering questions, offering tips.
It's a great opportunity to interact with us
and provide feedback to help influence the future direction
of what we do to make Excel better for you.
You'll also find that's where our blog is
where we're posting the latest and greatest.
So please be sure to check it out
and come back regularly for updates.
So without further ado, time to get to our main topic.
I bring a guest here to talk about
what it is they're working on, give a demo,
share some tips and tricks,
and show how you can be more effective in our product.
So I have here with me John.
Welcome, John.
- Hi Blake, nice to see you.
- Yeah, nice to see you as well.
- Work together everyday. - Yeah.
- My name is John Campbell.
I'm a PM Manager on the Excel team.
And today, we're gonna talk about new data types in Excel.
So let me take this opportunity
just to give a little bit of context.
So far, Excel and most productivity apps
really have only been dealing with text and numbers,
in terms of what you can put in a cell and calculate on.
We have a few other things
like errors get special treatment, so do Booleans.
But moving forward, we're starting the beginning
of a new journey where cells can start to contain
much richer data types that can represent
more real-world objects.
That's what I'm gonna be showing you today.
- So let's get into it.
- So what I've got here is a spreadsheet
and there's nothing special at this point.
It's just a table
and you can see I've got a bunch of strings.
These represent countries.
But I'm gonna select all of them and I'm gonna go up
and I'm gonna hit this Geography button in the ribbon.
And that's actually gonna convert these
to these new rich data types we're talking about.
Now, some of the strings in the build
you're gonna see aren't final.
You see we're talking about linked records
and these are sort of these record shapes.
But just think about these as human-friendly data types.
So you can see something happened there.
I've got this icon now next to a bunch of these countries.
If I clicked one of those,
you'll actually see it'll pop this card out
and I can start to explore
all of the values of Brazil, in this case.
Now, all of this data is there and available to me
and I can start working with it.
So what that's telling you is
that cell isn't just a string anymore.
These are now these rich data types.
- So what actually is in the cell there?
- If you sort of look under the covers in Excel,
all of that data is actually in that cell.
I can go offline with this workbook now
and I can still use this in the calculations.
The card would still be available.
So the way to think about this is,
just like there can be text in the cell
or just like there can be numbers in the cell,
in the future, there's gonna far richer objects,
these new data types, that eventually customers
will be able to define or it can be defined by services.
- So if I've already got a workbook like this
and I hop on a plane, I'm still good to go.
- That's right.
The calculation engine still works
and you can still work with it.
So let me show you a few of the things you can do.
So because I'm in a table,
you notice there's this little widget
and I can drop that down and it says, hey.
This is Excel's way of saying,
we can make it easy to add a column for you.
So for example, we can reach inside all of those values
and those cells, those data types,
and pull out the population field.
And just like that, in kind of a single click,
I get the population numbers in the grid.
If you look at what that did, it didn't just copy them.
It actually wrote a function
and it dereferences the data type in that cell
and pulls out the population.
So now, from this,
if I wanna do something like maybe map it.
So I'll go over here and I'll insert a map chart.
Because again, we know that these are places,
it will draw a map of South America.
So let me switch over now
and talk a bit more about all these.
So in this other sheet, I've got cities
and you see these are various cities from around the world.
And again, these are these rich data type objects.
You can see there's Paris in a cell,
got a bunch of facts about Paris.
We talked about being able to use them in calc.
So I just showed you mapping population,
but what if I wanted to compute something?
So these are real values.
So I can do something like equals and I'll select that cell
and I'll hit the dot key.
So that's the dot operator and that's a way of saying
in the formula link, which, hey, I wanna dereference that
and reach inside and grab a value from a property.
And I get auto-complete here.
You can see I can just auto-complete Population
and divide it by Area
to compute some density.
And again, it's just formulas in this case.
So I can take that and fill it down
and pretty quickly and easily compute something
against these.
Again, these are true first-class values in the product
and they represent more sort of human-centric
and human-friendly data types that are in cells.
So I'm gonna switch to another sheet
and I'm gonna show you, we already have a map chart
of the United States, in this case.
And you can see it's a table
and I pulled out some of the values.
In this case, the percent of population change.
What I wanna do now is actually I wanna filter that.
So other features in Excel can work with these.
So I've opened the filter.
Now, at the top, I'll hit this drop-down.
Say, let's move it to do the filter
against the timezone, in this case.
Make the dialog a little bigger.
So I'll just say, let's filter this data
just to the central timezone here.
Click OK.
And you can see the chart updates and so does the table.
So again, there are different features that can let you
reach inside and work with these data types.
And so it's not just demographics and geography.
I've shown you that so far,
but it also works with other things like stocks.
- So where does all this data come from?
You know, the population and you know, stock data.
Yeah, what's the source?
- That's a great question.
So we get this today from the Bing knowledge graph.
They have engine called Satori
and it's this intelligent engine that can recognize
these strings and then do the conversion to rich data types.
And in this case, we're actually grabbing the data
through MSN, if you look on MSN Money.
So and you notice, what's in there now is a mix
of company names and some are ticker symbols.
I'll click Stocks.
You'll see that smart Satori service,
make the cell a little wider,
that actually goes out and converts those
and we get the proper company name.
It convert Google to Alphabet, for example,
and FB to Facebook Inc.
And again, these are these rich objects.
They've got cards, they've got a bunch of data.
And the data, in this case, is refreshable.
I can hit the Refresh button and if the markets are open,
I'll get prices that are sort of real-time in some cases
and delayed in other cases.
- Sure.
- And we show you that on the card.
But we try and look this up intelligently.
And again, these are stocks.
They work the same way so I can reach inside
and grab the price or the open, high, low, close.
There are many, many fields in there.
And when I say intelligent, it isn't just fixing typos
and converting the final text to the true company name.
It also things like this.
So I'll go back to demographics for a second.
So I'll type in some city names.
So Seattle, Portland,
Olympia.
And these are all cities
that are in the Pacific Northwest here.
And if I convert those to Geography,
and you can see there's Seattle, there's Portland,
and Olympia as cities.
And I can explore Portland and there's its card.
Now, it's in context because these are all cities
in the Pacific Northwest.
But if I'm somewhere else...
- Right, 'cause there's a lot of different Portlands.
- That's right, there are many different Portlands.
So if I'm somewhere else in the grid.
So I come over here in this cell and I type in Portland.
And I say, let's go ahead and convert that one.
Now, it's gonna ask me, which Portland do you mean?
And there we go.
So this pops out and you can see
here's all the different Portlands we know about.
And I can go in here and I can explore each one of these
by clicking on them.
And I can see lots of facts about it.
I can go back.
Now, in this case, maybe I mean the Isle of Portland.
So if that's the one that I like, I can look at it
and I can select it.
And now, that cell will contain a rich data type
that represents the Isle of Portland.
And so that's what I wanted to show you today.
This is the beginning of a much longer journey.
For now, it's just stocks and these geographic domains.
We do have plans to add many more domains of data over time.
We have plans to let our customers apply this to data
that they own and they care about in some of their
more mission-critical processes.
So this is going to be shipping soon.
You'll see us change and evolve this feature over time
as we get feedback from the customers.
So I invite all of you to go out and get a subscription,
if you don't have one already.
Play with these two domains of data
and give us your feedback.
- That's great, thanks for showing that demo, John.
- Thanks for having me, it was a pleasure to be here.
- Yeah, definitely, but before I let you go, one more thing.
What's your favorite Excel tip, trick, feature?
- Oh wow, there are so many cool features.
But one of the things I really enjoy
and I see this particularly with beginning users
that are just starting with the product is,
just learning to work with data in the grid.
So writing some simple functions against it,
you know, SUM, VLOOKUP, eventually INDEX MATCH,
and just some of the keyboard shortcuts
like Ctrl-Shift-down to select a range of data,
Alt-equals to enter a quick function.
These are things that just, for me,
increase my productivity throughout the day.
Well worth people spending some time to learn
some of the basic keyboard shortcuts and functions.
- Definitely, that's great.
And so to learn tips and tricks like that and many others,
you can actually go to our community at
office.com/excel/community.
And our community really wouldn't be what it is
without the help of all of our amazing MVPs.
And we can actually hear from some of them
since they were here on campus.
- One of the most amazing things I saw was
when my friend Ivan Moala imported a JPG file into Excel
and used it as a photo editor, which was mind-blowing.
I didn't think Excel was really meant for that, still don't,
but it was pretty cool to see.
- Early, early on, I found Chandoo's work
and the spreadsheets that he built,
really I didn't know Excel could do.
- I learned that I could sort and filter my way to glory.
- Favorite function is VLOOKUP.
- VLOOKUP is my favorite.
- My favorite function of all time is VLOOKUP.
- VLOOKUP is like a rite of passage to Excel.
- Definitely, if you can master VLOOKUP,
you can master anything that's in Excel.
So I'd have to say that's probably the one
that's most important to learn.
- I used to be a manager who would hire financial analysts
and I had one rule.
You have to able to do VLOOKUP with your eyes closed.
If you can do VLOOKUP, everything else is a piece of cake.
- INDEX and MATCH.
Most of people like VLOOKUP,
but VLOOKUP is not so dynamically as INDEX MATCH
and I like more.
- I think probably INDEX MATCH is in there.
But then, also something like SUMIFS,
which can do something that INDEX MATCH can do
and if you're just adding up numbers.
And lots of people end up trying to do LOOKUPs
and INDEX MATCHs when a simple SUMIFS can do the same thing.
I think it's just a global community of people
who share the same passion about, you know,
a product you love.
- The community.
- The connections with other MVPs.
- It's just an amazing group.
If I get stuck somewhere, you can bounce it off
the other MVPs and they're very generous
and they'll come up with answers.
I love this group of people.
- Folks who can enjoy a really good Excel joke.
You know, you don't really always get that everywhere else.
- I really love being able to send feedback
directly to Microsoft and the guys who are making Excel
and know that I can help make a difference
and shape the future of the product that we love.
- When I learned to sort and filter,
I felt like I had power.
- Power query, 'cause for me, that's like the coolest thing.
Like, when I found out about that, I was just like,
oh, this is like a total game changer.
- VLOOKUP.
VLOOKUP was the thing that saved me all the time.
I'd get bad data from the IT department
and it would take them weeks to come up with the data.
But using VLOOKUP, I can combine this file and that file
and my problems were solved.
- The feature that really blew me away
was the unpivot feature.
That's something that would've been a day's worth
of VBA programming and now, it's just a right click.
- Array formulas would be one the feature
that when I learned, really, everything in Excel clicked,
because there's so much you can do with them.
- When I figured out how to find the last populated cell
in a column.
- Power query, that just like solves so many things
that I was just like, wow.
- Power pivot, getting transform now, power query,
as most people refer to it as.
- All right, thank you for joining us
for this episode of Excel Table Talk.
And thank you for joining me, John.
- Thank you. - This has been great.
I look forward to joining you on the next episode.
Thanks.