[MUSIC PLAYING] DANIEL GUNDRUM:
Welcome, everyone, to our Sheets session, 30
tips and tricks-- thank you-- 30 tips and tricks to help
you uncover data insights. I'm Dan Gundrum, a
product manager on Sheets. ANDREW RUDD: I'm Andy Rudd, also
a product manager on Sheets. DANIEL GUNDRUM: All right. And with that,
let's kick into it. Let's help you
analyze your data. Are we seeing the slides? Yeah. Awesome. Perfect. This is the session. So, as you're analyzing
some of the data, maybe you're feeling
a little overwhelmed. There's literally
mountains of data out there that's generated every day,
petabytes and petabytes of it. And you need the right tools. There's a lot of
information to get through. You need the right tools
to find those insights. But don't be overwhelmed. That's what we're here for
you today to help you with. And that's what
Sheets can help too. So we want you to
be feeling like this at the end of the talk,
jumping up for joy. You have the tools
that you need in order to get those insights
out of your data. So how are we going to do this? A few different ways. First, we're going to show you
a bunch of time-saving tips to help you analyze your
data, things that you just need to know about Sheets. Secondly, we are
going to tell you about some of the
new things that we launched within the last year. These are really cool things
that we hope you'll enjoy. And third-- and one of
my personal favorites-- we're going to be talking about
our upcoming roadmap a bit, sneak peek things that we
haven't discussed before today. So you'll get a
sneak peek into that. All right. So we wanted to also
frame and structure this talk around workflow
in terms of doing analytics. It's a series of steps to
help you prepare and analyze your data. So one of the first
steps to do that is to collect the information. It comes in a variety of
different sources and ways. So you want to bring
it into Sheets in order to start analyzing it. After that, you
need to prepare it. You need to get it
in the right format, clean it up a little bit. Because then you're going to
be doing some analysis on it, writing those formulas,
doing the pivot tables, filtering, sorting,
that kind of thing. And then you need to
make it understandable. You need to help people get
some insight out of that data. So you want to visualize
it and get it in a format that everyone understands. So that's the
analytical structure. And then all of
this is built on-- and this is where the
G Suite really shines is collaboration in the cloud. So we're going to run
through those five sections in this
talk, and that's how we're going to
structure the talk today. So with that, let's jump
into collecting data. One of the best ways to
actually collect data is by asking people. You can actually ask
people using Google Forms. It's a great tool that
integrates directly with Sheets so that you're able to
ask questions, get data in a structured format. It immediately comes
into a spreadsheet so you can start analyzing
and understanding the data. We also know that data comes in
a variety of different formats. For example, images and cells. So this is one of the new
features I was talking about. We actually just launched
this a couple of weeks ago. Brand new feature. It allows you to put images
directly inside of cells. So as you filter
and sort content, the images stay
associated with it. It's great for a bunch
of different use cases like the ones listed
on the screen. We also know that
businesses and enterprise uses a bunch of
different applications to store your data. For example, you might be
using SAP to store your data. So we proudly worked with
SAP to provide an export to Sheets option. So if you're using SAP,
just a couple of clicks from SAP to get your
data inside of Sheets to start collaborating
and analyzing it. If you're interested in learning
more about the integration, there's a link on the screen
that you can check out. It tells you the details
on the SAP partnership. If you are managing customer
relationships as well, maybe you're using Salesforce. So we've also partnered
with Salesforce to provide an add-on that
allows you to pull data from Salesforce,
bring it into Sheets. You can then, again,
collaborate, analyze the data, and then actually put the data
back into Salesforce as well. And this is available
already today. It's in an add-on. So you can go to Add-ons menu
in Sheets, search for this, download it. It was released
about a year ago, but we've actually-- this
is one of the things we've been updating. We've been making some updates
to it based on requests that many of you have given us. You can now refresh the
data on various intervals. You can either manually
trigger a refresh, or you can even set timers to
refresh the data at a scheduled time. Many of you are
probably also using GCP, so we have you
covered there as well. At Next last year, we
previewed this feature, which was a data
connector for BigQuery. And earlier this year, we
launched it live to everyone. And what this allows you to do
is write some SQL in Sheets, pull data from BigQuery
directly into spreadsheets, and then start analyzing
it and understanding it. And you never have to leave
the comfort of your spreadsheet in order to do this. So I mentioned a few
sneak-peek features, one of the things in
our upcoming roadmap. So I'm pleased to talk about
today as well on-premise data connectors. So we are taking what
you had with BigQuery but opening it up to data
that sits at your company as well, on-premise. So this is currently in
very early alpha stages. So I have an invitation
for all of you as well. If you would like to
join our alpha program, there is a link on
the screen there. We're actively
supporting the databases that are listed on the screen. And of course,
with your feedback, we will make it
better and add more. So without even
talking about it, I'll actually show you a demo. It's actually better to
see this live in action. So why don't we actually switch
over to the other computer? And you'll see what I'm talking
about with on-premise data connectors. So to access data connectors,
you go to the Data menu. There's a Data Connectors
option and on-premises under this Managed
Data Sources option. We have Oracle already set
up, so let's choose that. And then you are presented
with a SQL editor. We'll just paste some stuff
in, I've written this before. If you're familiar with SQL,
it's a pretty basic query. You're pulling in first name,
last name, and some order information. You're able to preview the
results to make sure everything looks good. This is good. And then you can
insert those results. And, again, this is now from
my on-premise Oracle database running at Google somewhere. And now, I have
pulled it into Sheets and can start analyzing
and collaborating with it on the cloud. So yes, I had to be familiar
with SQL in order to do this. But what if you're not super
familiar with SQL as well? What if you wanted
to enable people at your company to use this
who aren't as familiar? So I have another tab here
where I started setting up a configuration sheet. It's a very simple--
just a dropdown with a list of first names
of people at my company. So what I'm going to do is I'm
going to go back and modify the SQL query. So I can go and
edit the query here. What we're going to
do is we're going to add a parameter to this. So we're just going to add
something to the first name. So we're going to
add this parameter. It's basically like a variable
that can point to a cell. So I'm going to go back
to that configuration and say that this cell controls
that part of the query. I'm going to add that in there. So that's the new SQL query. I'll update the results. And as you see, now I'm only
seeing Nanette's information. So now if you share this-- and let's say,
someone else comes in. Let's say Christopher comes in. Christopher doesn't actually
even need to know any SQL. He can just come in and
go back to this data sheet, press Refresh, and
then he sees his information. So it's linked to that data,
and Christopher didn't actually have to modify the
SQL or do anything. He's just able to choose from
a dropdown, press Refresh, and it's just like that. Again, this is all running
from on-premise data with this. All right, so that is
on-premise data connectors. We can switch back to
the slides as well now. So if you are interested
in that-- and we'll have links again if you didn't
get a screenshot of it or a picture towards
the end of the talk. To wrap up this section
on connecting data, I also wanted to
talk about what you might have seen earlier
in the keynote, which is the connected sheet. So what the connected
sheet is is it's taking what you
actually just saw and taking it to the next level. Remember, I had to
write SQL in order to get that information in? With the connected
sheet, no more SQL. You can just point
to your data set, and you're able to
connect to the data. And as you saw, I actually
had to bring in the data when I was doing the
other data connectors. With the connected sheet,
you're not actually bringing in the data. We're linking to the data. So literally, we have eliminated
Sheets' size limitations. You can now analyze billions of
rows of data inside of Sheets. So we are-- [APPLAUSE] Cool. Woo. Some excitement. [APPLAUSE] We are entering a beta
program in a few months. If you are interested in this--
you have data in BigQuery that you'd love your company to start
analyzing inside of Sheets, really just bringing it
to the rest of the people at your company-- there's a URL there that you can
learn more about and sign up. So with that, I'm
going to hand it off to Andy who will talk about
the next step in the process. ANDREW RUDD: Awesome. Thank you, Dan. So Dan has talked us through
a lot of exciting ways that we can bring
data into Sheets. But we know that, depending
on how that connection was created, where that
data came from, it's not always exactly
ready for the next step. So we want to focus here
on how do you get data prepared, formatted,
cleaned up, standardized, so that you can
move on to analysis? So the first important
feature is text-to-columns. We know that there are certain
file formats that you might bring into Sheets that have
different delimiters in them, and they don't
always snap exactly in the way you would want
them to into columns. We have a couple of ways
we handle this for you. The first is through a
text-to-column feature in the data menu. You can also achieve
a similar outcome with the split function. So pass through data, indicate
what your delimiter is, and then have it broken
out into columns. And then we also have
a nice little import dialog that happens when you
bring file formats into Sheets. Here, we see a fixed
width formatted file being brought into Sheets,
the separator being indicated, and then we have some usable
data on the right for us. We're excited to talk about
two data prep features that we're really excited
to bring into Sheets. The first is Trim Whitespace. So there's some funny stuff that
always happens with formatting. Sometimes you have leading
or trailing white spaces on your data. It mucks up your
standardization. With this feature, we allow
you to grab a range of data, simply trim the
whitespace on it, and then you should
be good to go. Another one that we
hope you guys are as excited about as
we are is the ability to remove duplicates. So we know that people
have been asking for this one in particular
for a very long time. And we're excited now to allow
you to, as you would expect, select a range of data,
indicate some removal criteria, and then execute the removal
to only see unique results. [APPLAUSE] Awesome. Woo. [APPLAUSE] Woo-hoo. Great. So once you've brought some
structure to your data, we know that we need
to help you organize it a little bit better. Grouping rows and columns
is a really magical way to do this for yourself. So let's say here we have a
beautiful-looking spreadsheet. We might want to
groups like things together so that
when it's appropriate we can organize,
expand, and collapse subsections of our data. We think this makes-- particularly when you're
working with a lot of data-- it a little bit easier to
navigate through a spreadsheet. Checkboxes in cells are
something that we love. A really magical way-- a really magical way
to represent binary values in a more
interactive fashion. So let's say you
have a column that represents true or false
values, maybe yes or no. Here we see whether a task
has been complete or not. We can represent
those with a checkbox, and then have any user
interactively toggle that to change the value
of the underlying data. And, magically,
checkboxes can be passed to many of the
spreadsheet capabilities that you would hope that
they would be able to. So these work with functions,
with conditional formatting, and with filters as well. And while we're talking
about formatting, we want to mention
everyone's favorite-- macros. So macros we think are
an incredible time saver, and particularly when you need
to automate repetitive work. Often, we see folks
have spreadsheets with many, many Sheets
in them, each of which have a similar data structure. Rather than painfully going
through and formatting each one in the same way, you
can record those formatting actions once with
a macro and then play that back on different
Sheets within your spreadsheet. This not only saves
you time, it also prevents manual
errors you might have made in replicating that
process on different Sheets. So let's see some of these
prep items in action. We're going to jump
to a scenario here. It looks like-- I'm in my inbox. I see a message from Dan. He knows I'm the cleanup
guy, so I kind of think I know what this
task is going to be. It looks like he's gotten
some exportive opportunities from another one
of our colleagues. He wants me to throw them into
Sheets and then clean them up. It also looks like there's
another export that will be coming to us in a little bit. So we'll wait with bated breath
to see what that looks like. So I'm going to go ahead and add
this first drop into my Drive. And I'll jump over to a
Sheet I prepared to represent these opportunities. So let's bring in that file. I'm going to import. I see that Opportunities
tab-separated file in my Drive, so I'll select it. So here, we just want to
replace the current sheet of our spreadsheet. We can indicate the
separator type if we want to, but let's see what Sheets
does for us out of the gate. OK. So I've got some data in. Immediately, I'm seeing some
things that aren't as usable as I would want them to be. Notably, this column B
says Opportunity Name, and it's showing me
a company and then some number of widgets
that are up for grab with that opportunity. So I'd really like to represent
those values separately. So let's split them
out into two columns. First here, I'm going to use
the Split function to do that. So I'll point to that data. I'll indicate what
the delimiter is. I'll hit Return. And then-- awesome. So I see those broken out. I'll go ahead and
apply that down. And now I have a standalone
opportunity and then a number of widgets
that are up for grabs. I'm going to go ahead
and copy those results and paste them over
themselves as values. So that just gets rid of the
underlying formula for us. We think there's something
goofy happening here. Because we split
on that hyphen, we see there's a space before
and a space immediately after. That's some whitespace we're
going to want to get rid of. So with that data selected, I'm
going to go into the Data menu and use that Trim Whitespace
feature we talked about. OK, great. So we had two columns here. It looks like it's
evaluated 30 rows and removed whitespace
from every single cell in that selection. So now we're
getting much closer. This feels almost like
we're in good shape to use what we've got here. But because I have this
header called Widgets, I don't really need that
label on every single cell. So I'm going to
select those values. And this time, I'm going to
use the Split Text to Columns option within the data menu. I'll indicate that
our separator here should be a space, and boom. I'm good to go. I'm going to go ahead and
delete that Widgets column since we no longer need it. All right. We're getting closer. I know sometimes
in the past when I've gotten these
exports from Dan, there are duplicates in them. We don't want duplicates here. We want to look at only
unique sales opportunities that we have. So let's show off that
Remove Duplicates feature we talked about. Again, I'm entering
the Data menu. I'm going to remove duplicates. I'll leave all of
these columns selected. I really want to
find exact matches. So let's execute that removal. Looks like we found one row. And now we are confident
that we have 30 unique rows that we're working with. Great. So let's imagine here that
I've talked to Dan offline. He's made sure that the next
time he does that export, we won't have that weird
column-splitting issue that we had to do for
ourselves this time. Thank you, Dan. Awesome. And there won't be
duplicates next time either. Great. So now that we have confidence
that our next data dump will look just like
this, let's go ahead and do some formatting here. And we'll record a macro so
we can speed up the process the next time we do this. So I'm going to go into
Tools, and I'll Record Macro. And watch as I'm
doing these actions. You can actually see
what I'm doing show up in that recording box
that's popped up here. So I like to bold my headers. I like to freeze the first
row so that when I scroll, those headers remain
at the top with me. With that done, I like to apply
some conditional formatting-- alternating colors, rather. So let's do some alternate
color banding on these rows. I picked yellow there. That looks beautiful. Off to a good start. With that done, I can
remove the grid lines. OK, so this is looking
nice and readable. I love it. Let's move to the data itself. So we no longer
needed this column B. It's too much data for us. So let's go ahead and hide that. I see this revenue
number in column E. I think that probably wants to
be represented as a currency. So I'm going to change that
over and decrease the precision. I see a column we have
here called Close Date. I think that probably
wants to be a date. Let's see what happens when we
change the number formatting. All right. That looks really nice. And then lastly, I see a
column here called Probability. That looks like it probably
wants to be a percentage. So I'm going to change
that number formatting, decrease the precision again. We've got a nice, big, whole
numbers we're working with. And I think we're
looking pretty good. So I'm going to save that. Let's just call this
macro Prepped Data so we know we can use
it when data is prepped. We could add a shortcut. I'm not going to for now. And we're good to go. So let's jump back
over to our inbox and see if Dan has come
through with that second batch of opportunities. Of course he has. He's so reliable. Incredible. This time, let's download it
just to show you a little bit different flow. So I've downloaded
that drop from Dan. Thank you very much. No more duplicates. Dan's the best. DANIEL GUNDRUM: Thank you. ANDREW RUDD: So we'll
import here, again. This time, I'm going to upload
and just drag that download into this section. Great. So this time around, I
want to insert a new sheet. Get rid of that. Then we'll import the data. OK. So I think this
looks pretty nice. It looks like Dan did work out
that column-splitting issue that we talked about. So I think the only
step remaining here is to run that macro
back against the Sheet and see where that gets us. So I just clicked
Run Macro, and I can see all those actions that I
performed play back immediately for us. And now we've got an
Opportunities Two tab that we got from Dan,
Opportunities One tab, and they look the same. We're ready to move on to the
heart of finding insights. AUDIENCE: Woo. [APPLAUSE] ANDREW RUDD: Woo. Very cool. OK, so our data is beautiful. It's standardized. It's consistent. It's formatted in a way that
everyone-- makes them happy and smile when they look at it. So let's move on to
analysis, actually finding meaningful insights from
the data that we've brought in. Of course, when you
start to do analysis, one of the first things you
might do is some calculations. Sheets has over 400
functions, everything from the rites of passage
like VLOOKUP, INDEX, and MATCH that you're used to and then
some unique functions as well. Whether it is doing some
mathematical calculations-- we have an AVERAGE.WEIGHTED
formula that does exactly what
it sounds like, a SORTN formula that allows
you to turn some top number of results from your data set. Whether those formulas
are showing some results for you visually in a cell-- so a SPARKLINE function
that we provide is a really nice, quick
way to visualize a value within a cell-- or whether it's leveraging
Google's unique strengths-- so the GOOGLETRANSLATE function
is a really magical way to translate text from
one language to another-- or pull data quickly from
some other external source, which is what we do with
the GOOGLEFINANCE function. And when we feel confident
about what you're trying to do, we will suggest
formulas for you. So we know sometimes that
remembering specific names is hard, or knowing exactly
the range of data that you want to pass into
that function can be tricky. And so when we have
high confidence, we'll recommend those
actions for you. We've found that with
this acceleration, these suggestions can make
formula entry up to five times faster than they
would be otherwise. And if you are a real
sucker for time savings, sometimes we'll guess
for you before you've typed in anything. So what we see happening here,
the user's entered a cell. They've hit the equals sign. And we're already guessing
what they might want to do. So here I'm below
a range of data. I might want to find
the average or the sum. That's a really magical way
that we speed up your analysis. I want to shift focus
here to talk about some of the different
filtering mechanisms that we provide in Sheets. We have a couple of
different ways of doing this, and they're really powerful
under different circumstances. So the first is
with basic filters. We think these are
really powerful when you are working collaboratively
with a peer in a spreadsheet at the same time. When you apply a
basic filter, that applies to every viewer
of the spreadsheet. When you filter, when
you sort, that view persists and will be
what any future visitor to the spreadsheet
sees when they enter. Really great if there's
some splice that everyone at your company cares
about or everyone that's going to enter that
spreadsheet cares about and you can persist
that view for them. Let's imagine, however,
you're working in parallel with someone. Maybe you and your
analyst colleague have different
insights you're trying to uncover at the same
time, and you don't want to disrupt each other. Filter Views are our
mechanism for allowing this parallelized collaboration. So with the Filter View,
you have this stealth mode, and you can have the
confidence that when you're applying filters
and sorts, it's only affecting you as the viewer. You can also save
these Filter Views. So maybe there are different
splices of your data that will be powerful
in the future. You can name them to represent
why that's a meaningful filter. And you can share them. So Filter Views have
unique URLs that can be shared with your
colleagues-- a really quick way to just show them one
particularly interesting slice that you might have done. And we are excited to give you
a sneak peek of a new filtering paradigm. So Slicers are a new
filter type that we're bringing into Sheets
that are really powerful, we think, for bringing a
new level of interactivity. These are great
when maybe you've made a report or a
dashboard and you want to give your colleague some
kind of a remote control access to the analysis that they might
want to do for themselves. So, again, Slicers only
affect you as the viewer. Another colleague could
come into that spreadsheet and work with a
slicer independently. And they modify the underlying
data for objects in Sheets. So here we see
someone using a slicer on charts and pivot
tables maybe and getting particular views of that data. We'll show you this in action
in a couple of minutes. So that was a lot. Let's recap that. Three different
filtering paradigms. Filters are really powerful
working collaboratively when you want all viewers to see
filters that have been applied. Filter Views are
great when you don't want to disrupt what others
are up to in your sheet. They're also-- I didn't mention
this before-- really powerful when you only have view access. So you're not an
editor of a sheet, but you've got some
questions about it anyways, you can use a Filter View
with view-only access. And then Slicers, which we
gave you a sneak peek of, are really great next level of
interactivity for your reports and your dashboards. And everyone's
favorite, pivot tables. So we know that pivot tables
are a crucial, crucial tool for any spreadsheet
analyst when you're doing some initial
investigation to aggregate your underlying raw data. So these can be great
when you're first attempting to zero in on
some interesting trends. It can be a really
powerful way also to represent your
findings visually if you want to show some
buckets of your underlying data. And they've come a really,
really long way in Sheets. We're proud of some of
the interactive mechanisms that we give you around
drilling down to see the values that underlie a given
bucket, some calculations that you can do in the
Pivot Table Builder itself. So we've done some
incredible analysis. Let's start to bring
these things to life with visualizations. DANIEL GUNDRUM: Thank you, Andy. So we have taken you through
three steps of the journey so far. We've brought the data in. Andy showed you
how to prepare it. And we've analyzed
a bunch of it. So now, we need to help
people understand this data. We've done a lot
of great analysis. We need to bring
it to life, help them find those key insights. One of the best
ways you can do this is through simple pop of color. You can use conditional
formatting in Sheets with using a variety of
different conditional formatting methods to
really help the data shine and stand out. Where does your eye get
drawn in this data set? Probably to those red cells, the
things that are closer to zero. You probably should take
a look at those cells. So conditional formatting
and grading conditional formatting this example
helps you with that. Another way to visualize
data, is through charts. Sheets has dozens
of chart types. We have a bunch of them
listed here on the screen. We've also been actively
working on improving charts. One of the features that
we're happy to have launched within the last
year was the ability to highlight a
part of the chart. So this really draws your
attention to that green bar, doesn't it? Your eye almost goes
immediately to that section. You're telling your
audience, hey, look here. And so you're able to highlight
particular points or bars in a chart. But we're not stopping there. We're also improving
charts as well. One of the top
things that we hear from you is you want to
interact with charts in a more natural, native way, just
want to click on a point and drag it or click and delete. So we're going to
be updating charts to allow you to do
that more as well. And we're introducing
a new type of chart-- scorecard charts. So this is a great
chart type if you really want to emphasize a key point
or a KPI, a Key Performance Indicator, in your data
set, this chart type allows you to do that. But maybe you're
not an expert user and you don't know how to
create charts yourself, or maybe you're just
getting started. That's where Explore in
Sheets can help you as well. If you're not super
familiar with it, Explore is kind of like an
assistant that's by your side helping you with Sheets. You can ask it
questions in English just like you would as a
co-worker or anyone else. And even if you don't ask,
it'll suggest you things. Here's a pivot table to add
to your dashboard or a report or spreadsheet. Here's a chart that
looks interesting, that might be relevant
for your data. So basically, what this
leads to as well is-- we've done all this analysis. You have all of this data. You want to start
interacting with it, to drill in, and
really understand it. You can bring all
these features together through Reports in Sheets. Lots of sneak-peek features
in our roadmap that are going to be launched within the next
few months to come together to help you build
really great reports-- like this one showing some
AirAsia flight information. Andy was talking
about slicers earlier. I just mentioned
scorecard charts. There's a bunch
of other features that are going to be
launching to really help you create these beautiful
reports directly in Sheets. But without-- let's
show you it, all right? It's best to see
this live in action. So we're jumping
to the demo here. This is the same spreadsheet
that I was showing before with the conditional formatting. Let's say you are analyzing some
inventory in one of your stores or region of stores, and
you want to create a report. So I have this other tab here
where I started creating one. Just to save us some time, I
started adding a few items. I was mentioning
Explore can help provide some instant
insights into the data. So we'll take a look at that. It's in the bottom right corner. So I can see this
Explore button down here. You'll notice that
there's actually no data. There's no raw
data on the sheet. So when I open up Explore, it's
going to be blank for a second. But what I can do
is actually point it to the data or any other
sheet in the spreadsheet and start seeing
immediate results over it. So now, I immediately
get some insights. I can start asking questions. I see a pivot table
that's suggested to me. I see a bunch of charts
that I'm automatically able to start interacting with. Let's say I like this one. I think that looks interesting. It's a list of our inventory and
a bunch of different regions. So let's say I wanted
to insert that. I can just scroll
to an open space and literally just drag and
drop that into the spreadsheet. And now I have that chart
inserted into my spreadsheet. I didn't have to even know how
to create it or do anything like that. Sheets just suggested that. Let's say I'm looking for
something in particular, as well. I can ask Sheets a question just
like I would almost a person. So let's say I was
looking for something-- let's say the total
quantity that I need to order in my stores,
because quantity to order is something that I'm tracking. And I want to see that
broken down by location. So how much inventory do I
need to see in each location? And there you go. The result was instantly
returned to me. And actually, if you see,
this is a pivot table. And I actually didn't even need
to know what a pivot table was in order to insert this. All I had to know
was, I just want to see inventory by location
and how much I need to order. So I don't have to be
even a spreadsheet expert to now be able to interact
with and use pivot tables. So let's insert this
into the spreadsheet. Let's say I want this. This is actually a new
part of the flow as well. You can choose where you
want to put the pivot table. So that's great because
I want it on this sheet. I don't want it on a new sheet. Let's choose right there, and
let's insert that pivot table. So great. There we go. Now the pivot table
is on my report. It's starting to
look pretty good. I notice this chart
is a little big, kind of hanging off the side there. So let me actually
make a little smaller. One of the things I can do is
use some of the new features. See that red line that's
appearing off to the left side? That's a guide to tell
me that it's perfectly aligned with the other chart. And when I resize this chart,
look at what starts to happen. Let me just scroll up so you
can see it a little bit more. See those blue lines
that appear as well? That means it's perfectly sized
with that other chart as well. So now I know things
are looking great. You might be familiar with some
of these features in Slides already. We're bringing those
to Sheets as well, just really making sure that
everything looks great together. Andy mentioned slicers before. Here's one example slicer
that you could use. Let's actually show you
how to insert another one. So this is under the
Data menu, Slicers, kind of in this Filter section. So we can choose that slicer. And let's, again, align it,
make sure it looks good. That's great. And then all you have
to do is choose-- so it's kind of a filter. So we have to choose a
column of data to filter on. So we can go over here. Let's choose Location
as our filter option. Great. So that's it. That's the ease of setting up
a slicer on your dashboard. This is starting to
look really good. Before I share it
with Andy, I want to make sure that this
matches my company's brand. But I don't want to spend--
there's so many things on here. I don't want to spend
so much time doing this. I can use the new themes feature
to style this really quickly. I can choose from a variety
of different options. And as you see, everything
updates with one click as I choose the
different theme options. And I can even
customize this theme. Like I said, it needs to
match my company's brand. Our company color-- let's
say our primary color is red. You can quickly do
that with one click. And the charts and
even the pivot table has this little hint
of red added to it. So everything's
starting to look great I can send this to Andy
now, and we can start collaborating on this report. So let's say we do that. I want to see
information because I'm in control of the supplies
in the European region. So I can use the
slicer and say, I only want to see data for Spain and
Portugal, countries in Europe. So when I do that,
the dashboard updates. You saw some of the bars change. This total in the
scorecard chart updated. Now what Andy was
talking about earlier-- let's say Andy is now working
on the same spreadsheet at the same time and
messing with this data. Let's say he was in control
of countries in Asia, so he wants to see
this information. If he does that and modifies
the slicers at the same time while I'm viewing this, my
view will stay the same. I'm still able to look at what
I want to see in this dashboard or report and Andy can also
collaborate and use the slicers at the exact same time. And we're not
impacting each other. We can still look at what
we want to look at and do our own analysis. This is really
powerful because we're collaborating on the same
file at the same time, the same underlying
data, but we can look at what we're interested
in looking at without impacting each other. So that is Reports,
and we'll switch back to the presentation. [APPLAUSE] Thank you. Thank you. So we have taken
you through a lot of the steps of this workflow. We've shown you collecting
information, preparing it, analyzing, visualizing it. But I did hint at
one thing earlier, and you might be wondering,
whatever happened to that connected sheet? I didn't show you
a demo of that. So we've gone through
all of these steps, and what we've seen
is we've done-- all this data so far
has been in Sheets. But what if I start analyzing
and doing this stuff with linked data in BigQuery-- millions and millions, even
billions of rows of data? So I'm going to
switch back to a demo now, again, and
actually show you a lot of this with the new
feature, the connected sheets feature. So now, let's say we are
analyzing grocery store information. You're trying to
understand information to make some decisions
about your stores. You might have
noticed earlier if you saw when I went to
data connectors, there was a new option for
the connected sheet here. So let's do that this time. So we're just going to
choose from our project list. These are BigQuery
projects and databases. We have this Groceries table. Let's connect to that. And just in a couple of seconds,
we're connected to BigQuery. As you remember, that first
flow that I showed earlier, I have the data linked here now. I didn't have to write any SQL. The data is now linked. And if you see up
here, we're connected to 128 million rows
of data in Sheets, and there's dozens of columns. This is literally over
a billion cells of data now that we're
working within Sheets. Let's analyze it. And you don't even
need to be a power user to start analyzing
this information. Let's get a high-level
sense of the data. Let's write some formulas. So let's say you
want to understand, for example, how
many products are sold in your different stores. Whoop. You know, that happens
from time to time. It's an early alpha program. So we'll just wait for it. But that's what you could
expect in the alpha. Yeah. So let's write a COUNTUNIQUE
formula, and we will do that. And then when we
start typing in-- ooh, maybe-- looks
like maybe I have to reconnect this data set. So give me one second
to just do this because it looks
like that crash might have impacted--
so I'm just going to delete this sheet here. That crash might have impacted
the formulas that I'm writing. So I'll quickly do it because
it's super simple to do this. Again, don't have to do any SQL. I'm just going to
reconnect the data set. Let's insert a new sheet. Let's do some other
types of analysis. Let's start inserting
some pivot tables. And let's say I wanted to look
at the different product names by the stores that they're in. So I can do product
names by the stores, and let's say I wanted to
look at this by the regions that the information
is to understand what products are popular in
different types of regions. I can add values
to the pivot table. So let's say I want to see
the quantity that's bought. I can change this
to a percentage to view it a different way. And you see this Apply button. So when I execute
this and press Apply, it is now basically
generating the SQL required to run this
against BigQuery and then return
me a pivot table. So I didn't actually
have to write the SQL. It's getting returned to me. So it is already done. I got the results
returned to me. Again, over 100 million
cell rows of data. I was mentioning before good
way to visualize the data. This is a little hard
to see and understand. If I want to understand
it and visualize it a little bit better,
I can add a pop of color. So I can add some conditional
formatting over this. Let's do that. Format, Conditional Formatting. Let's add a gradient so that
the large values stand out. Great. So now what are you
starting to see? To me, I see in
the, for example, Southeast region it looks
like a lot of seafood products seem to be pretty interesting,
like smoked salmon, this lobster thing, anchovy. But actually in
the Central region, those actually seem to
be less popular items. That's interesting. Maybe we want to start
stocking up the items-- seafood more in the
Southeast than in Central US. And then if I
scroll down, apples seem to be much more
popular for whatever reason in the Central United States. So again, maybe we need to
shift some of our supplies over to that region. Let's show you some other ways
to analyze your data as well. Let's create a chart
over this data set. So we can do that as well. We can put it on a new sheet,
make lots of room for it. And let's say you then want to
look at this data over time. Let's say you wanted to look
at the sales, for example. So you can do that. Let's just type in
sales, find that. And we want to see if sum of it. You can actually use
this new grouping option as well to say how you want the
transaction date grouped by. So let's say we have a
hypothesis where we want to see the data grouped by the month. Maybe there's some
seasonal effect to our sales in
our grocery store. So again, we could
just click Apply to run this and execute
against BigQuery, and we get the results
returned to us. So, interesting. It actually looks like there
is not a seasonal effect. I see February has a
little bit of a dip. Maybe that's 28 days in
February [INAUDIBLE] little bit. So let's change this grouping. Maybe there's a weekly cycle. So we can change it to day of
the week, maybe try that out. Execute that. And there we go. So yeah, so I'm
starting to see now some different interesting
trends in the data. It looks like we have a lot
more sales on the weekends. People go shopping
on the weekends. And then Tuesday,
Wednesday, less sales. So now I can start making actual
some business decisions off of this. For example, maybe
I should offer some promotions to get
people shopping on Tuesdays and Wednesdays. Or maybe I need more
staffing or more people in my store to support
on the weekends because we have higher
traffic driven on those times. So you can really start
getting these business insights and data really quickly. And it is in alpha. It is early stages right
now, so you saw it's maybe a couple of rough edges. But if you are
interested, we'd love to have you join the
early stages of this and start using it and analyzing
your data over BigQuery, billions of rows of it,
directly inside of Sheets. So I think with that,
we will hand it back to the presentation. I'll hand it off to Andy to
talk about that last section. So we did that with
the connected sheet with collaboration. ANDREW RUDD: Awesome. Thank you, Dan. So we've gone through
this analytical workflow. We've brought data into Sheets. We've prepped it. We've analyzed it. We've visualized it. We want to come
back to some of what we think the core
strengths of the G Suite and Sheets are, A, our
leading with collaboration, finding collaborative moments
for you whenever we can. And then some of
the benefits that we have of being a product
that's native to the cloud. So let's dig into some of those. The first is that Sheets,
obviously, allows everyone to edit together seamlessly. We've heard from
enterprise leaders again and again that this really
changes the baseline level of transparency in
their organization and it makes every moment
more collaborative. Here we see two colleagues
working together in a sheet. They're changing values. And one thing that they might do
to call attention to each other is a comment. So we have these
rich conversations that we enable in Sheets
with threaded conversations. Maybe there's a
value that I see-- thanks to Dan's
conditional formatting-- that needs a closer look. I can just type in his
username or email address and then say, hey, Dan. What's going on here? Anything that sticks out to you? That's a great way to
get feedback immediately in the place where you need it. And one more powerful way, I
would suppose, of doing this is with action items. So in that same
commenting workflow, you can actually
assign an action item to one of your colleagues. So let's say I really needed
Dan's feedback on something. Maybe I need his approval. Maybe he's the person
responsible for that row or that region, whatever it is. I can assign an action item to
Dan, draw his attention to it. And then he can come
in at his leisure, mark it resolved when
he's given me feedback, and then we can move on. Version history is a
really powerful way of tracking the
evolution of a document. So it's really powerful
to see what changes were made to your spreadsheet,
who made those changes, and when those
changes were made. And that's what we're
doing with version history. One even more powerful way
to track that evolution is with named versions. So with named versions, you
can mark different milestones, annotate why they were
relevant, give your colleagues some kind of indicator as to
what that version represents. Here we see maybe
a couple of weeks ago we consolidated
around a draft outline. And then more recently, we've
come to a final version. It's really great to be
able to toggle between those to see the differences that
happened between those two milestones. And an even more
granular version of that that we're excited
to give you a sneak peek of is the edit history of a cell. So often, version history
is great for looking at big changes in
the spreadsheet. Sometimes there's a value that
is really, really important and you need to understand
how it's gotten to the place that it currently is. With the edit history, you'll
be able to see on a cell level what changes were
made by who and when. AUDIENCE: Oh, crap. [LAUGHTER] [APPLAUSE] ANDREW RUDD: Awesome. So another sneak peek for you. So let's say we've
reached the end of this analytical workflow. Often, you'll bring your
amazing insights out of Sheets, maybe into
Docs or Slides for a more final, presentable artifact. Here we see a document. And we've been
making progress here. So in 2017, we allowed you to
bring charts out of Sheets, place them in a Doc or a Slide. Last year, we did the
same thing with tables. And we're excited to give you
this sneak peek of the ability to update all of those things
at once wherever they now live. So here we see an
awesome report that's got many, many tables and
charts copied over from Sheets. Rather than going to every
single one and updating it, you can update them all
at once and make sure that they match the underlying
data, whatever changes have been made in the sheet
where those objects were created. Sometimes you want a
little bit more metadata about how the document is moving
through your organization, who it's been meaningful for. And the Activity
dashboard was a feature that we launched last year to
give you some of this metadata. As the document
owner or editor, it can be really cool to see who's
looked at your spreadsheet. You might want to know if Dan--
whose input we desperately need-- whether he's
had the opportunity to make it into a sheet or not. It's a great way
to do just that. And no worries. If you don't want
to be seen, you can opt out of the
Activity dashboard as well. So here we see we
provide you a lot of metadata about the viewers. We also provide you metadata
about the trend in viewers, so maybe it's cool for you to
see when a document went viral within your organization
or when a lot of people came into it to make a decision. And we're excited to
have updated the Activity dashboard to track a
couple different things. So the first is comment trends. Same thing with viewer
trends, but what if you could track the
conversation around a document rather than just
who's entered it? So here we can see when did new
comments enter that document? When did replies
enter that document? And what was the trend of
unresolved comments over time? This can help you see when
consensus was reached again, maybe, when a conversation
was particularly active about the
contents of a document. And we also allow you to get
a little bit more granular with a sharing history in
the Activity dashboard. So these things
are great when you want to spread your
docs far and wide. Sometimes you want to have
a little bit more control. A couple ways we do that. The first is with
expiration dates. So you can set expiration
dates on your documents, maybe if they're
particularly sensitive or you're working with
folks that shouldn't have ever-long access to them. You can set expirations
of a week, a month, or some arbitrary
date in the future. And similarly, you can prevent
viewers of a spreadsheet from downloading, printing,
or copying the contents. Again, super powerful
when data might be particularly sensitive. We announced today the ability
to edit Office documents directly within
the G Suite editor. So here we can see I might be
working with someone that's still outside of G Suite. That's great. That's OK. But I want to still have the
added benefits of G Suite collaboration, interactivity. And so I can make changes
directly within Sheets here to an Excel file. And then not have to
worry about re-uploading, re-downloading the document. It'll convert back to
the original file format that it came from. We're really excited to have
brought that to you guys today. And we know that, particularly
with spreadsheets, there's some incredible
muscle memory that people have built up over time. And so, of course,
Sheets has had keyboard shortcuts for a while. Last year, we
talked about how we allow you to override some
of the browser shortcuts that were in collision
with those shortcuts. And we've come a
long way, and we're excited to give you a
sneak peek that we've matched a lot of our
keyboard shortcuts back to other legacy
spreadsheet products so people can come in and have
the same time-saving they're used to with the crazy
things that they don't even know what they're
doing in their head. We've all seen that person. Awesome. And then just close
us out here, so we want to make sure that
you can get to your data wherever you are. Security does not have
to mean inconvenience, and that's why you can go
offline and edit your documents and then, once you
come back online, sync any changes that you've
made back to that file. Additionally, you
should be able to make those changes on whatever
device you have at hand. So Sheets, obviously, is a
really strong Android and iOS app. And, of course, we're always
available in the browser where we were born. OK, so that's been a whirlwind
for us for us at least. I think for you guys too, maybe. Let's do a quick synopsis
of where we've been. We talked about how we
bring data into Sheets, that collection phase. We talked about how we get
it standardized, cleaned up, formatted beautifully for
digging into insights. That's the analysis step. How do we bring that stuff
to life with visualizations? And then we brought
ourselves home talking about collaborative
strengths and being a cloud-native product. We talked about two really,
really exciting early access programs today. The first is the
on-prem data connectors that Dan did a
wonderful demo of. The second also had
an incredible demo-- the connected sheet. We wanted to give you
another opportunity to see those links if
you want to potentially be part of that early access. There are a couple of awesome
other G Suite sessions still happening at Next, a
couple opportunities to hear how people are
thinking about using the connected sheet. We wanted to give you
a sneak peek at those. And guys, we did it. My goodness. We are at the summit. We made it. We can now bring-- [APPLAUSE] We hope you can bring
some of these tips back to your colleagues and
help them reach new heights. [MUSIC PLAYING]
