I’ll be talking about Google Sheets and giving you some tips and tricks to boost productivity. You can ask questions at any time on the platform. And we have Googlers standing by to answer them. So with that, let’s get started.
All right So today, we’ll be covering tips to boost productivity that will span the typical analytical workflow. Now, the first stage in this analytical workflow is the collect phase. This is to get data from wherever it may live and bring it right into your spreadsheets. Next, we have the prepare flow.
This is where you the data in your Sheets and you clean it and get it ready for analysis. And for the next flow, the analyze and visualize stage. Now, in this stage, you’re taking the data and you’re actually generating those insights and those learnings to help figure out and make sense of that data. Finally, we have the share phase. This is where you’re sharing out your data in your spreadsheet in order to get, either your collaborators in the spreadsheet to help you, or to share with end recipients who will be viewing and consuming that content.
Now, all of this is on top of real-time collaboration. This is at the heart of not only Sheets, but of G Suite itself. And we’ll go into a bit more about how to get the most out of the collaboration. So that’s the order of topics we’ll be going through in today’s talk. And we’ll end it with talking about how. Sheets is ready for Enterprise so you can get the most of spreadsheets in your company.
So with that, let’s dive in. Let’s talk about getting data into your spreadsheet. So first, let’s talk about templates. So templates have been added to Sheets. We have dozens of professionally-designed templates to choose from.
They cover a wide variety of use cases And rather than starting from scratch, you can open up a template and start entering data using an already crafted template. Additionally, you can create your own domain templates. These are templates that are specific to your particular company. Let’s say, for example, that your finance team has a particular way they like to report out every month.
Rather than recreating that report every month from scratch, you can create a template once, save it to the Template Gallery. And that way, within your company, that domain template is available for you with all the themes, colors, font, et cetera, that your company uses already in the spreadsheet. Next, you can get data into your spreadsheets by importing it. Let’s say someone sends you a file It could be many different types of files.
We have support for CSV, for example, Comma Separated Value files, in Excel And right from Gmail, there will be an attachment card like you see on the screen. And you would simply click the Pencil icon It will open up that file into Sheets. And you can edit right within Sheets with one click– very simple.
You also can get data into Sheets by import functions. So we have a whole host of import functions that you can use when you’re writing your formulas, things that will pull in data from externally saved files, from RSS feeds, from web pages, from XML. But the one that’s used the most often is the first one, the Importrange function. This allows you to pull in data from other spreadsheets. So you can write a formula that has an import range, put in the URL of the other formula, and that will pull in data from another spreadsheet.
It’s an easy way to link spreadsheets and pull in data from other sources. So I would encourage you to check that out. You don’t have to remember all the information on this slide. If you just start typing equal and start typing import, you’ll see autocomplete will show up along with some information that will help you learn a bit more about these functions. Google Forms is another really popular way to get data into Sheets.
So with Google Forms, you can simply create a series of questions of all different types. You set up the form. You can send it out to a bunch of people from whom you want to collect that information. And as they fill it out and submit, that data will flow right into Sheets where you can do further analysis, you can create your charts, your pivot tables and all that on that data– very simple. Next, you can get data into Sheets via our API.
So APIs allow third parties to integrate with Sheets. So you can get data both from Sheets into third party sources as well as from third party sources right into Sheets. And we worked hard to continue to update the API to support all the functionality in Sheets. As we add new features, we’re continually adding more capabilities into the API One thing we’ve added in last year is called Developer metadata.
What that allows developers to do is to stay on top of how users interact with Sheets. So you have data within a sheet that’s linked to a CRM system, for example And the users in the sheet are rearranging columns, or renaming things, or changing rows. The metadata allows developers to stay on top of those changes so that everything is still in sync. So that’s a pretty powerful feature.
Next, we’re excited to talk about SAP to Sheets. This is a new feature that launched in the last year. Now we have seamless export from SAP into Sheets. Now we know a lot of companies live on SAP All the operations they have stored there is very critical for business.
And so now with– very simple, you can export from SAP right into Sheets, open up the file in Sheets, and see all the data, and do simple analysis in Sheets. If you want to check out more about this, you can go to the link on the screen there, cloudgooglecom/sap/sap and gsuite We’re also excited to announce the Sheets data connectors for BigQuery.
Now, this is actually something that we just announced a month ago at our Google Cloud Next conference. This is available today in a beta. And what this allows you to do is connect to BigQuery and to pull in data from BigQuery right into Sheets. So you can write a simple SQL query in Sheets. So if you see on the screen there, you can pick your billing project.
You hit write query You can enter in some SQL And then, when you run your SQL, if you hit Insert Results, it’ll now run that query, pull the data, and pop it into Sheets. What that does now is, if you want to refresh the data, all you need to do is hit the Refresh button, and it will refresh the data for you automatically. Then they can share with their collaborators.
They also can refresh that data if they have access to the underlying data source. No need for them to write additional SQL queries. It’s very simple to have that connection. If you’re interested in trying out this feature in limited beta, go to that link on the screen, bq-sheets, and sign up for the beta I’m also excited to talk about a data connector to Salesforce. So with Salesforce, we now have an add-on that was created by Google Cloud in beta program. You can now pull in data from Salesforce into Sheets, and you also can push changes from Sheets back into Salesforce. So for those of you who are interested in that, please check that out.
All right. That’s a few different ways to get data from wherever it may live into Sheets. Now, let’s talk about transforming that data and getting it ready for analysis. So in order to prep your data, there’s a few things you can do. First, we have Text to columns.
So if you have data that you want to turn into a table of columns, you can use Text to columns feature We’ve had support for that for a while on things like comma separating values, tabs, et cetera So that’s all been there for a while You can use that today And we’ve also added support for what’s called fixed-width formatting
So the file on the screen on the left is an example of fixed-width formatted file And if you import it now using Text to columns, it will automatically be turned into the tabular data on the right So again, Text to columns is how you can transform raw data into a table of data. If you’re preparing your data for analysis, you also might want to be expanding and collapsing rows and columns. This is a common Enterprise request we’ve had.
And we’ve added it in the last couple of months. So now you can easily collapse and expand columns and rows with ease by using this Grouping feature. We’ve also introduced Checkboxes into Sheets. So what Checkboxes will allow you to do is, if you have a column, and in that column you have two values– true/false, yes/no, reviewed/unreviewed– whatever it might be– you can transform it into a checkbox, where one value will be checked, and one value will be unchecked. Then you can treat the checkbox like any other value in Sheets.
So if you want to use it in a formula, you can. If you want a filter on it, you can. You can use conditional formatting. The underlying values are still there. But it just looks like a checkbox, which we’ve found users are really excited about.
We’ve also introduced macros. This is a huge productivity booster It can save you a lot of time by automating repetitive work. And so what macros are doing is, you hit Record, and you can do a bunch of stuff in Sheets, whether it’s formatting data, creating a chart, whatever you might be doing, you are recording that using the Macro Recorder And then, when you hit Save, it will remember everything you did.
So then you can go back and replay that macro, and it will repeat all the steps for you. That way, it’ll save you time It will prevent you from missing a step when you need to repeat it. So it’s a big productivity booster I definitely encourage you to check that out as well.
Sheets also has what are called Add-ons So add-ons are a way to add additional functionality into Sheets. So when it comes to data prep, there are a bunch of add-ons that you can use to enhance your Sheets– things like removing duplicates, merging data add-ons, normalizing values, detecting outliers, et cetera. And so all that, again, will enhance your Sheets if you add it into your spreadsheet. There’s also add-ons of other types outside of data prep– so things like Mail [? Merge, ?] et cetera.
But if you’re interested in supercharging your spreadsheets, definitely check out the Add-ons store and find some great add-ons that you can use. Next, I’m going to talk about Google Apps Script So Google Apps Script– what is it? It’s a scripting language in the Google Apps platform. What can it do? It can do a bunch of stuff One, you can create automation.
So when you are actually recording a macro, what it’s doing underneath the covers is actually writing those steps in Apps Script. You also can manually do that as well if you want to get more complex than what you’re trying to automate. So you can create those automations with Apps Script You also can write custom functions. So Sheets gives you over 400 functions out of the box.
But let’s say you have a new function that you’ve come up with. You can create it using Apps Script and then use it like any other function within your formulas in your spreadsheets. And finally, you can use Apps Script with triggers So triggers are when there’s actions that happen in Sheets. So when someone opens a file, when someone makes an edit to a file– all those are triggers that then can kick off some of the automations you created in Apps Script.
So with that, I’m going to jump in to show you a demo of how to use some of this stuff. So let’s take a look at a file here So this file has four tabs Each tab is quarterly data. The data is different in every tab, but the columns are all the same– so all the same structure.
So what I like to do is I like to clean it up I like to prepare it just once. I’ll do it on this Q1 tab and then replay those changes on subsequent tabs. So to do that, I can go to the Tools Menu, go to Macros, and hit Record Macro. What this is going to do is, now you see a blinking red light at the bottom.
So now, it’s listening It’s recording what I’m going to do. So every action I take now, it will track those actions. And so what I can do is if I– let’s say I want to highlight all of the data in the spreadsheet I can do that.
And I want to resize it now to fit You’ll see that, next to the red light, it’s capturing all of those actions as I’m recording it So let’s add some alternating colors We’ll change it to green Let’s be done with that
And we also can freeze the first row so that’s easy to scroll. And then there’s a bunch of true and false values in that column. Let’s see, how about let’s change this to percentage. And we can fix the precision to be– yep, that’s better And then for the true and false, we can actually use checkboxes, rather than saying true and false.
So Data Validation– I’ll change this to Checkbox and hit Save. And so now that’s been changed And then finally, these columns, Start and Finish– I want to hide those columns so I can add the new grouping feature. Let’s group columns G and H, and I can collapse this now. So this looks good.
Let’s say I’m done with this I feel like this is an adequately prepared sheet. I will save it I’ll give it a name. So here, you can give it whatever name you want for easy recall later.
And then I can give it a keyboard shortcut, in this case, Command-Option-shift-1, and we’ll hit Save. So now it’s saving the macro– Macro Saved– great So now if I go to Q2, this tab– rather than redoing everything manually, I can go to the Macros Menu, find that macro, and just replay it. And it will do all those steps for me instantly. It helps me prevent making mistakes, helps prevent me from missing a step– very simple, like magic.
So that’s how macros work. All right Moving on to Analyze and Visualize– so this is where the rubber meets the road. This is where we are going to discover all those insights to get the most out of our data. So pivot tables are in Sheets.
For those of you who are unfamiliar, pivot tables are a way to summarize your data, to group and aggregate data in useful ways. Sheets has had pivot tables for a while, but we worked hard to make them even better for you this past year. So this is the old pivot table format. We’ve actually overhauled them to look a little nicer like you see on the screen. So we think it looks a little better.
We’ve added a bunch of other features as well. We now have drill downs. This is the ability to either double click or say Show Details on a particular cell and see all the underlying values. We have groupings So you can now group up arbitrarily on values and also expand out in different ways.
We have Show Value as a Percentage– was a common request, as well as being able to customize the text in the headers. You can also choose whether or not to repeat row labels And again, as I mentioned before, we have the Refreshed UI So all that has made pivot tables even better. So again, I encourage you to check that out.
It’s a great tool in doing analysis. Next, I want to talk about different ways to filter data in Sheets Sheets actually has several different ways to do this. So first are what are called filters. Now filters change content for everybody.
So when you filter down to, let’s say, a particular value, anyone else doing that spreadsheet will see the same thing. They’ll all see the filtered data. We also have what are called Filter views. Now, Filter views– when you filter down to something using a Filter view, it’s only doing it for you, the viewer. It only impacts you.
Anyone else in that spreadsheet will not see the changes. That’s the difference between that and filters Just to really hit this home, you should use filters when you want to purposely change the view for everybody viewing the spreadsheet. If you want to impact everybody, use filters. Also, any changes you make while using filters– for example, changing the sorting– that will impact everybody even after you removed the filter.
Now filter views, you should use those when you don’t want to disrupt others’ views of the data. So again, if you don’t want people to see what you’re doing, use filter views. You also can save multiple filter views in the spreadsheet. So let’s say you have multiple different ways of cutting and slicing and dicing the data. You can use filter views to save all of those.
If you want to share a link to another user with things already pre-filtered you can share a link to a particular filter view– another useful way to use. Filter views And if someone shares a sheet with you, and you don’t have edit access, you actually can’t use filters because that’s going to change the underlying data. But you can use filter views, because filter views allow even viewers who don’t have edit access to the filter of the data. So again, definitely encourage you to check out filter views if you haven’t. They’re like filters, but built for collaboration.
Now Sheets has over 400 spreadsheet functions– some of them listed on the right there. We have everything you can probably think of. We have all the basics– VLOOKUP, SUMIF, MATCH, et cetera. We’ve also added a bunch in the last year– some of the more complicated ones you see on the screen there– statistical functions, et cetera. We’ve even added functions that are unique to Sheets, things like AVERAGE.
WEIGHTED, so that’s a weighted average. And then we have a new one called SORTN, which actually shows you the top N values in a range. Let’s go a little deeper on that one just as an example. So let’s say you want to find the top five values from some Salesforce data. So in this case, I want to find the top five regions by revenue.
What I can do– and this is the results that I’d like to see. The way to get that is I can use the SORTN function, specify the range, and say I want the top five values. And voila, you get the data that you see above. Now, if I didn’t have this function, it would be a lot of work to make this happen, pretty complicated. Luckily, SORTN is there– makes it much simpler for me.
Sheets also has Formula suggestions. This is another productivity booster. Let’s say you’re doing some analysis. You have some data. You go below your table data, and you type Equals.
All of a sudden, you’ll see some suggestions of different formulas that you can use. So Sheets is predicting what it thinks you will want. You can choose one of those rather than typing things out manually. We found that Suggestions have made formal entry five times faster for those who are using it It helps speed things up for you, helps prevent you from making mistakes.
So if you see those popping up, hopefully, you’re taking advantage of those and getting that productivity boost with formula suggestions Next, we’ll talk about charts. Now, no analysis is complete without some charts. We, at the Sheets team love our charts. We have all the basic charts that you need.
We have the pie charts, line charts, scatter plots, bar charts We’ve also introduced a new chart this year called waterfall chart You can see that on the left there Waterfall charts allow you to see incremental changes between different segments We’ve also introduced a lot of other features to charts in the last year– things like total data labels
We’ve added a way to customize your colors, tight alignment, moving data labels, customizing the line styles, and we’ve revamped the chart UI to make it more user friendly as well So again, play around with charts and see how to get the most out of visualizing your data. Next, I want to talk about Explore. So Explore is bringing intelligence into Sheets. You can think about it as almost like having your own personal analyst built right into Sheets.
So With Explore, you can ask natural language questions about your data, and it’ll give you answers. Explore will suggest charts. It can adjust pivot tables. It can give you formatting suggestions– very simple. So on the bottom right there, you see the Explore icon.
You click on that That will open up the Explore sidebar And you can click on the alternating color suggestions. You can ask those natural language questions very simply. Again, it’s like having an analyst right within your spreadsheet.
In the last year, we’ve continued to enhance what’s possible with Explore Now, you actually can use natural language to ask for charts and pivot tables. And even if you don’t know to ask for those questions, it will actually even suggest pivot tables to you. So that’s a new feature that we’ve also launched in the last year. So with that, let’s do a demo.
So here, we have some Salesforce data And we can go down to the Explore icon. Click on that to open it up. And we see now, the Explore side bar is open. So what I can do is I see a bunch of suggestions here.
I see some pivot tables, some scatter plots, some different charts. Let’s say I want to ask a question about my data I can type in this box here. So I want the total revenue by region. And voila, I have a pivot table.
If I want, I also can view this as a chart as well. So I click over here. There’s the chart. Back to the pivot table. Now you’ll notice that I didn’t even use the word pivot table.
I just said total revenue by region. But yet, it knew that a way to summarize that is to the pivot table. So let’s now insert this into your tab OK, great. Now you’ll see that I can expand this out by date.
Let’s choose Close date And this has added date as columns for me. And so I see the individual days here. What I want to do is actually group them by quarter. So now, it’s going to summarize all those dates by quarter as my columns against the regions in the rows.
So I can see all of my data summarized by both region and quarter I can click around here. And I notice that in Q2 Southwest region is an unusually high value. So I want to dig into that a little bit more I’ll right click, go to Show Details.
And now I’m doing a drill down into the data underlying that $5 million. So here, you can see all the values in Q2 corresponding to the Southwest region– so very simple. So that is how to use both Explore and Pivot Tables. So with that, let’s jump into the next section. This is share and distribute.
So again, the reasons to do this are either A, you want to share out to collaborators so they can help you refine your analysis and get more out of your data, or B, maybe you want to share to your leadership team or to your managers to show them all the great analysis and results that you found in Sheets. So let’s talk about the first use case– collaborating. So Sheets allows everyone to edit together seamlessly. Again, real time collaboration has been there since day one. Here, you see a bunch of people clicking around, interacting, changing values.
It’s very simple to work together in a spreadsheet. No more sending around attachments. No more worrying about getting locked out of a file or being in an out-of-date file. Everyone’s going to get the right file at the same time. It’s very seamless.
Next, let’s say you wanted to distribute your files by putting them into either Slides or Docs Well now Google supports embedding. So with this, you can embed both charts and tables into both slides and docs. And when you do that, it actually stays linked to the underlying Sheets file. So the Sheets file remains the single source of truth.
That way, if later, you change the data in your spreadsheet, you can go back to your Slides and Docs and, with one click, refresh those charts and tables very simply rather than getting out of date or worrying about showing the wrong analysis. So again, that’s a very simple way to keep data up to date all within the Google Editors. We’ve also worked hard to help you with printing. We know a lot of you like to share and distribute via printing. And so we’ve added a bunch of new features in the last year, things like custom page breaks, custom headers and footers, custom paper sizes, print preview, custom margins, and saving print settings.
So all of that is there. For those of you who like to print, check that out Another huge productivity booster we find– and we use all the time here at Google, in fact– are Comments So Comments are a rich, discussion-style functionality built right into. Sheets You can simply click on a cell, add a comment.
If you Plus mention someone– so you put a plus sign and an email address, or it at sign and an email address, it will autocomplete your contact’s name. They’ll get an email notification telling them that they were mentioned in a sheet. They click in that email It’ll bring them right to the file– to the right file, to the right tab, to the right cell– where they can either take action on the cell directly, or they can reply to the comment itself. So you can have a threaded conversation between you and your collaborators.
It’s very useful. We also have what are called Action items. These are a special kind of comment, where you actually can assign a particular cell to someone. So let’s say you need someone to review a number or fill out a cell Rather than just passively mentioning them as an FYI, you can say, hey, Sarah, can you please take care of this.
And so she’ll be assigned that cell so that it’ll get done. Next, when you’re collaborating with people, sometimes it’s useful to know who has seen that file. So we have now Activity Dashboard– another new feature in Sheets. So let’s say you share it out with your team It’s been a few days, and you’re curious– OK, well, who has actually reviewed it, and who hasn’t yet? Well, the Activity Dashboard– it’ll show you all that.
And then from this dashboard, you actually can email the various people you’ve collaborated with. So you can find those who haven’t reviewed it yet and email them from this interface and say, hey, please take a look I need answers to this by next Tuesday. So again, if that is a use case that resonates with you, check out Activity Dashboard Sheets has something called Version History.
This is very powerful. All the edits that are created in Sheets are saved within Sheets. And so in Version History, you can see who’s made changes, what have they changed, and when they made those changes. So all that is available to you as an editor of the spreadsheet. We also have what are called Named Versions.
So Named Versions actually allow you to save a name, or multiple names, to different important milestones in your spreadsheet’s history. So let’s say you have a couple of different drafts you want to mark as important milestones, or you have a final version that was read out to your leadership review, you can keep track of all of that. And you can use this now to actually create what’s called a Diff, or see what’s changed between adjacent important milestones. That’s called Named Versions. Also check that out.
Sheets also has Notification rules. So Notification rules are useful if you want to be notified as soon as there are edits being made. So let’s say you have a bunch of collaborators, and you want to stay on top of when they’re making those edits Using Notification rules, you can say email me every time a change is made, either when the change happens, or email me a digest, or a summary, of all the changes at the end of the day So again, this is a more proactive way of staying on top of all those changes in your spreadsheet.
Now, great We’ve gone through a lot of features in the analytical workflow. We want to talk now about how Sheets is helping users get even more out of their spreadsheets for business. So one, Sheets works hard to keep your data safe and secure. No longer do you need to send around attachments that could get easily downloaded or put onto a USB drive.
Everything is in files that require you just to authenticate. In fact, you can also use two-factor authentication for even extra added security Also recently announced is Confidential Mode in Gmail where the text itself in the email is locked down and harder to distribute beyond that email itself. It’s harder to forward, copy, or print, et cetera. So all of that is working hard to make sure that your data and your company’s stay safe and secure.
We also have something called Prevent download, print, and copy So if you are the owner of a file– you’re the one who create that file– you have the ability in the Advanced Sharing dialog to check the Disable options to download, print, and copy feature. Now, what that will do is, for commenters and viewers, they won’t be able to do those items in that file any longer. So again, it’s a way to help prevent accidental leaking of data, people accidentally printing out a very sensitive file and having it go beyond where it was intended to go. You also can set expirations on Sheets files.
So let’s say you have someone you want to share with who only needs limited access a limited amount of time. What you can do is you can find that file, you can go to share, you can pop in that person’s email address. And what you can do is you go to the Advanced version of dialogue, and you share with them, there’s a little clock. If you click on that clock, you can choose that you only want to share with them for seven days. And you hit Save.
What that’s done is that’s now said that for seven days, they’ll have access But at the end of the seventh day, they’ll automatically lose access to that file. Again, that prevents them from having access to files that are more sensitive for a longer time than you intended. Additionally, we know that sometimes folks in business like to travel. And so when you’re traveling and you’re on a plane with spotty Wi-Fi or no Wi-Fi at all, don’t worry.
Sheets works both online and offline So we’ve got your back– will work no matter how good your Wi-Fi connectivity is Speaking of traveling, we also work across your devices. Whether you’re on your laptop, tablet, or mobile, we have a Sheets experience there across different devices that you can stay productive even on the go Finally, we know how important keyboard shortcuts are for folks.
And so we’ve worked to try and provide abilities to help transition to Sheets by making it easier to mirror some of the shortcuts in Sheets with those that you might be used to in other programs. So there’s now a switch that will allow you to override some of the browser shortcuts that were originally claimed by browsers like Chrome and Firefox to now match what you might be expecting the spreadsheet behavior to be And so if you are someone who loves their keyboard shortcuts, check that out. All right So that was a lot of different tips and tricks there.
So now I’m going to do a brief recap I won’t read through all these items here. But going over the high-level buckets– again, we talked about how to get data into Sheets and all the different ways you can do that through importing from your Gmail, using Google Forms, through the APIs, et cetera We talked a bit about Preparing your data. So again, this is you can do things like splitting out your columns using text to columns, you can use macros, you can use Apps Script, et cetera.
In the analyze phase, we talked about pivot tables, checkboxes, charts, formulas, and using Explore so you can bring intelligence to your Sheets. In sharing and distribution, we talked about all the ways you can both share with your collaborators, as well as using things like embedding, using notification rules, and Version History to really get the most out of collaboration. And finally, we talked a bit about how Sheets is Enterprise-ready So we talked about how you can take advantage of using links, expirations, preventing copy, printing, download, et cetera, to really protect your data. All right, so that concludes the main part of this talk.