If you have any questions you want answered by one of our instructors, please join our offsite community. The link is in the description as well as always. If this course has exercise files, you'll find them in the video description below. Before we dive in, let's take a look at Google sheets. Uh, as you notice, I just clicked on the app launcher and we can see that Google sheets is one of the apps. That's a part of the Google suite or the Google workspace. So Google sheets is the spreadsheet application of Google's cloud-based office, suite..
And this is what we'll be taking a look at here. So who should attend? Well, this is an accelerated class and it's meant for experienced users of Excel. These are users who know how to create formulas and apply common functions in Excel. And more specifically is for users who want to learn how to do the same and more in Google sheets. So as we go through our course today, you will recognize a lot of the same functions that you're used to in Excel..
We're able to apply them here in Google sheets as well. So here are our learning outcomes for today. By the time you're finished with this course, you will have learned how to create conditional formatting in Google sheets. You will learn how to limit what information users can add or edit into a sheet by using data validation, you'll be able to share a spreadsheet and collaborate in real time. You'll be able to make interactive sheets and forms to obtain information. You'll be able to get real-time financial data from the stock market. You'll be able to create macros to automate repetitive actions..
And then along the way, you'll be learning how to create visuals like charts sparklines and QR codes, and even a really nice dashboard. When we take a look at the interface, if we take a look at the actual work. You take a look at the B cell layout, the rows and columns. It's great. Identical to XL. The menu bar is slightly different. We do not have that ribbon interface. We have the traditional drop down menu, but one of the great things about Google sheets is that you do get the auto-save feature. Uh, collaboration is very easy to do..
Just click on that green share button, and you'll be able to go ahead and collaborate in real time. Uh, the menu is also collapsible. So if we click on the small arrow, uh, we can actually condense the menu where we can just take a look at the commands there as well. But overall, we're going to spend a lot of time playing around with the menu and more importantly, working in the actual work space as well. So go ahead and pause this video. Go ahead and open up your Google sheets and we'll get started..
Click on the link and make sure that you've make a copy of this file. Now that you've made a copy of that file. Let's go ahead and click on our very first worksheet tab, which is formatting and validation. So there's a few things that we want to do on this worksheet tab. We'll start from the beginning here. So I do have some info documented here. So we have each salesperson that were assigned three sales days for the past week, and now it's basically time for them to enter their daily sales..
So here's our sales team over here. They each have an ID and they have a name as well. And so what they each need to do is enter their ID, number, their name, the date of the sales and the actual amount of sales that they earned for that particular day. Now we want this data to be entered correctly. We don't want them to enter an incorrect ID number or have any errors. And so we're going to go ahead and kind of force them to just choose from a dropdown list. And that will allow us to connect this data because we know that it's accurate..
If you want to connect this data to another table or list as well. So what I'm going to do first, I'll go ahead and set this up. We'll look up by ID number. So if I click on my data tab, here's my data validation, and this is what will allow me to have those dropdowns. So step one, I'm going to go ahead and highlight the range that I want to apply the data validation. And then I'll go ahead and click on the data tab and I'll click on data validation. And so I have this dialog box now, and I have some options.
Here that I can choose from. Um, so here is my here's my current range that I want to apply it to. And I have some different criteria here, so I can choose a list from a range, which I do want right here. I already have my, my list here. My ID numbers, I can choose from a list of items. So notice as I change these, they actually, um, the dialog box updates for me. So I want to type these IDs individually. I could do that. Uh, we can have a number between certain values. We can have a date between certain values as well, but I want to go.
Ahead and use a list from a range. I don't want to go ahead and take advantage of this box here, where I can just go ahead and highlight all of my ideas here. Okay. So I'm pulling the data from cell range, J three to J eight, if I click. Okay. It brings me back to my dialog box here. So the next thing I want to do, I do want to make sure that I show the drop down list in the cell, and I don't want anyone entering any invalid data. So I want to choose the reject and put method here. So if they try to type something in that box, it will reject it or force.
Them to choose one of these existing. ID numbers. So I'll go ahead.
And if I want to, I can show the validation help text. So I can say, you know, click and enter a value from the range, or I can customize this. Okay. Maybe I can say, choose a valid ID. So I'll go ahead and click save. And so now each cell has a dropdown. Now what I've done here is I've actually created a V lookup formula and for the name so that when they enter the ID number, it's actually.Going to go ahead and pull the sales person and automatically populate our name column here. So I'll go ahead and do the first one here. So I'll go ahead and drop in to the first one. So as you can see a drops in our first salesperson here and Jordan, and we'll do another one here, so we'll do, maybe we'll do a random one here, 9, 8, 7, 6, 0. And that, so that's working fine here. So what we need to do now is go ahead and create data validation for the dates here as well..
I want to specify that they can only enter between two dates here, and this is important because this is for a specific week and we want to make sure that all of these dates are for that particular Salesforce. So just as I did before, I'm going to go ahead and click on my data tab here. I'll go back to data validation and this time I'll choose I'll click on date here. And instead of is valid date. I'm going to go ahead and say between two dates here. And so I'm going to choose my week and set this up here. So if I pull up my calendar here, I'll go ahead and choose March 28th, 3 28,.
And then to April 1st. And I want to go ahead and reject any, um, any invalid input here as well. So I'll go ahead. And this time I'll I won't showy validation, health techs. I'll go ahead and click safe. So let's give it a test here for our first sales person. We'll enter a date. So maybe we'll say let's try to enter an invalid date here. So we'll put four, two. Okay. And so the date, the data you entered in cell G three violates the data.
Validation rule set on this cell. Okay. So from here, we just have to go ahead and enter the correct date here. So maybe that first day was actually March 28th. So I'll put 3 28. Okay. And so that works here as well. So go ahead and enter the sales amount here. Maybe I'll just put to 8,500 and, um, I'll give this the currency format. Here we go. All right. So go ahead and complete this list here..
Right. Go ahead and enter three sales dates for each salesperson, um, dropping the date and dropping the sales as well. And then pause the video and come right back. Welcome back. Well, I finally finished, uh, and putting all of my sales here for each of my sales people here. So now what I want to do when they head over to my next section here, let's talk about conditional formatting. So it, conditional formatting can be found on the format menu.
And all the way to the bottom. I can go ahead and click on conditional formatting. What's really nice about conditional formatting in Google sheets is we can actually actually choose our own colors and not just choose from a list of color options. So what we want to do here, we want to go ahead and run some conditional formatting. Maybe we can kind of show the average sales let's see was below the average. And let's see who is below the average. I will take a look at some other conditional formatting tools as well. So the first thing I want to do is I want to go ahead and highlight. The range that I want to apply conditional formatting to..
And what I'm going to do here is instead of dropping the average function to find the average down here, if I take a look on my bar here, it's giving me some data, just like the status bar in Excel. And so right now it's showing me that the sum of all of those values is 139,250. I'm going to click on this down arrow here, and it will tell me that the average is 77 36 11, the minimum here. And then I have the maximum as 11,500. Okay. I do have a minimum here..
I need to go ahead and fix that. It should be 9,000. So I'll go ahead and change this to 9,000. So I'll go ahead and get this updated value and, okay. So my average is 81 86 11. So maybe I'll just write that here. 81, 86 dot 11. Okay. So that's my average. So now I can go ahead and create my conditional formatting. I'll highlight those values and I'll click on my format menu and I'll click on conditional formatting. So at this point I have two options..
I can either use a single color right here, or I can use a color scale.
So if I click on color scale, I do have my options here as well, but let's go ahead and use the single. And so here's the range that's currently highlighted age three to age 20. And I have some rules here that I can use. So if I click on this drop down. Okay. So I can say is greater than the average here. So the average was 81, 86 11. Maybe I'll just round that up to 81, 87..And as you can see, it's applying it in real time here. Maybe I want to change this color here, right? So I want, as you can change the font, some bold italicize underline we could strike through, we can change the text color as well. I just want to go ahead and use the fill color. Maybe I'll change that to orange. And maybe that's a little too bright. Take something a little more subtle here, maybe this nice blue. Okay. So as you can see, it went ahead and applied that. So now I can get a quick visual of who is hitting the average or who is.
Heading below the average here as well. Alright, so very good. Now I'll go ahead and use a color scale. I can go ahead and click done. And if I close this here, okay. So remember we can apply different rules on top of each other. So if I go ahead and highlight this range again, I'll go back to format conditional formatting. I can see my rule right here. So if I want to go ahead and delete this rule. So it doesn't conflict with another one..
I can just go ahead and remove this rule here. I'll actually do that because at this point, I just want to take advantage of the color scale. And so I'll go ahead and remove the rule. I'm going to add another one here. This time. I'll use the color scale and what's really cool is with this color scale, I can actually specify the color that I want for my minimum value. If I want to establish a midpoint, I could, or if I want to use the max here, I could as well..
Right? So this is the default. If I click on here, I got some other color scales. So I have this yellow to white scale and I have this really colorful one, this green to white, to red, obviously here, the red indicates the higher values, right? And the greens there represent the smaller values here as well. So I can do that or I can go ahead and choose my own colors here if I want to..
I'm not going to do that. I don't want to make this too visually frightening for everyone here. Okay. So I'm going to go ahead and click done. So go ahead and pause the video. Go ahead and add the conditional formatting. Okay. We'll use that first one to drop him the actual number. So go ahead and apply a single color format and then go ahead and apply a color scale as well. And as you can see, we've got a really. A visual of how everyone is performing, suppose the video and come right back..
We'll come back. Well, let's go ahead and move on to our next worksheet tab here, which is called name ranges. And one of the really useful features is being able to apply a name to a range of data, making it a lot easier first to refer to that actual range, as opposed to highlighting that range all the time here. So here we have our peer company sales for February, 2021, where our sales people here and we are tracking their weekly sales totals. So for example, on week one and Macquarie at sales of 95, 50 week, 2 92, 30.
Week, 3 8500 and then week 4 89 65. And so we're tasked with filling out this financial form here. We have to go and grab the total sales for all of these weeks graph the average sale, the max sales amend sales. And then we have to go ahead and grab the, the total sales for each of our salespeople here for the month of February. And so if we were to click on the insert menu here, for example, I'll go to M carries. We're going to go ahead and grab him. Kerry's weekly sales or February sales revenue..
If I click on the insert menu. And I go down to function. We can see that we have several functions here that we can drop in. Here are our basic functions, such as the sum, average count max and men. And so typically we would just drop in the summer function here, and then we just need to go ahead and specify the range that we want to sum. Okay. And the great thing, it gives us the answer before we click enter. So if something is wrong, we can go ahead and fix it. I'll go ahead and press enter. And at this point he wants to auto-fill, which is a really cool.
Feature here in Google sheets as well. I don't want to fill this right now because what I want to do is I want to go ahead and, and calculate the remaining sum of sales here using the name range. So I'll go ahead and cancel this auto fill. So this is okay because this is a pretty small dataset, right? It was pretty simple to highlight a very small data set here, but what if this were, you know, an entire table or a really long, very, very long range here as well. It would not be ideal to have to highlight all the time..
So we're going to use, what's called, named Rangers named ranges can be found from the data tab. So if I click on the data tab, Um, right here. I do have the option for named ranges here as well. So what I want to do, I want to go ahead and highlight the weekly sales hair for each mosque. And I'm going to go ahead and click on my data tab. I'll click on named arranges. So pops up right over here for me, because I already have the rain selected..
So it's a , which is correct. I can go ahead and give this a quick name. So I'll just call this III Musk and then I'll go ahead and click done. Notice it does show me the other names that I have created here as well as you can see, I have another, I have a table named countries on our SQL worksheet tab. I'll go ahead and click done here. And so now if I click on my name box, which is over here to the top left, so here's our name box. If I click on the dropdown here, I should see Musk here..
So I'll go ahead and click away from eMASS called click on an empty cell here and I'll click on a Musk from my dropdown and notice how it's locking on to that range. If I even go to another worksheet and click on that dropdown, it will bring me right back here as well. So let's see how we can use this in a formula. Okay. So this time I'll say equals seven. And I'll type the name in there. So eat Musk. So as you can see, I have not finished typing all of it, but here it's showing.
Me that I have a range that I can use, which is easy Musk in this range. , I'll press tap to accept that it's already given me my answer. I'll go ahead and close my parentheses and press enter. And just like that, I was able to use the name that I just created, as opposed to highlighting the dataset all the time. I'll give you the opportunity to go ahead and create individual names for J Hudson eight styles, a Rogers and all Winfrey. But right now, what I want to do, I want to take care of this form here. So I'm going to go ahead and highlight all this entire range.
Here, this entire dataset. Okay. Just the numbers, just the weekly values. So from before down to nine, and I'm going to go ahead and add a range and I'm going to call this fab sales. Okay. Fab sales. Everything is okay. I'm going to go ahead and click done. So now when I go to total sales, I don't have to highlight all of this here. I can drop in my sum function. I can say equal some parentheses FEV sales, right?.
Go ahead and press tab. There we go. So we have 213,610 total sales for February. Now, if I want to drop in my average and use that name, I can as well. Um, this time I use the insert head over to function and I'll drop in the average here and I'll just drop him. Fab sales again, 8,900 is the answer, press enter. Okay. So at this point, what I want you to do, go ahead and pause the video. Go ahead and create a name for J Hudson's weekly sales here,.
One for eight styles, one for a Rogers and one for older Winfrey. Go ahead and complete the data entry here, and then go ahead and complete the data entry here by creating a name for the entire range here as well. So pause the video, take care of those things and come right back. Welcome back. Well, now you can go ahead and check your work. If you take a look here, the average sales was 8,900 for all of our salespeople. Uh, the max sales was 11,200. As you can see here, uh, indicated by eight styles is week three, total..
And the minimum sales, the lowest number was 59, 75 as evidenced by Musk's week one totals here as well. And just take a quick peek over here at the remaining values here for our February sales freak, each individual sales person. So that's all correct. Let's go ahead and move on over to our protection and sharing a worksheet tab here. And so what's going on here as a member of our team will be responsible for filling out this form on a weekly basis. And specifically what they're filling out here is our team and our team members.
Need to specify which one of the days of the week they'll be working off site. And they also need to go ahead and specify their weekly sales goals as applicable here as well. But the problem is our team leader has brought it to our attention that some of the data is being modified and appropriately. So for example, when a team member is assigned to fill out this data, we noticed that some of the other data over here to the left is actually being tampered with, it could be just a keystroke or a wrong click of the mouse, or maybe they using an app..
And, but for whatever reason, we want to go ahead and protect the data over here. And so the whole idea is that we're going to go ahead and create a sandbox that they can only interact with the data. That's in this cell range, which is L two down to M 18. If we click on our data tab, we will see where it says to protect the sheets and the Rangers. And I do have the small picture here of what the dialog box looks like. And I have the steps recorded here as well. So we're going to go ahead and protect the sheet..
So I'll go ahead and click on my data tab. I'll click on protect sheets and ranges. And over here, I'm going to click on where it says, add a sheet or a range. Now notice I can protect an individual range if I want to. When in this case, I want to protect the entire sheet. So now that I'm protecting the sheet, I do have the option here to just say, I just want to protect certain cells. So I'll go ahead and click on that..
Now we could choose what sheep we want to protect. Not as we are currently under protect protection and sharing worksheet tab here. So this is the one that we want to protect, right? So we want to protect everything except for this range here. So if I highlight this range, this is what I want. So I'll go ahead and click on select data range. I'll go ahead. Highlight my sandbox here. So except certain cells. So except for L two to M 18. So again, I can go ahead and add another range if I want to..
Um, but that's okay. This is all I want for now. So I'll go ahead and click. Okay. At this point, I can go ahead and enter a description and this is, this is a good thing to do, because if I have several protections on here, I want to be able to easily identify which one I either want to edit or go ahead and remove. I'll go ahead and click on set permissions. And from here, I can choose this option just to kind of show a warning when editing a particular range, or I can go ahead and.
Restrict who can edit this range. So I've chosen the option that only I can actually edit this range. If I choose custom here, I can actually specify anyone that I'm sharing this file with. I can specify their access here as well. So I want to go ahead and click done here. All right. So my changes are saved. I'm going to go ahead and close this here. So if I come over here, I do have access, right? So I can actually type in here..
I'm going to go ahead and open up my second user here and let's see, my second user can actually interact with this file. So here's my second user. Here's my actual team member that needs to fill out this. And so let's see, we'll go ahead and fill out for Howard Smith, weekly sales goal as 5,000. Okay. So that works. We can enter the data here and let's go ahead and fill out for Jill here. So Joe wants Wednesday.
And Joe's goal is 5,500. Okay. So now let's see if I can interact with, you know, maybe I accidentally clicked over here and I'm changing this name here to Dave. And so I get this error. It says you were trying to edit a protected cell or object. Please contact the spreadsheet owner to remove protection if you need to edit. So it's working as planned. So again, we accomplished the goal here. Here's our sandbox. This is the only area that our user can actually interact with..
All of our data left is kept in tact. And so we no longer have to worry about the integrity of our data. So mission accomplished. Go ahead and pause the video and go ahead and set up your protected ranges there. If you do have the capability to use another account, you can go ahead and share the file. Just clicking on, share, share that file with your another account. Maybe a friend and see if they can access. And at that range here as well, I suppose the video take care of those things and come right back. Welcome back. Okay. Well, we have a really nice report here..
We have our pear Creamery sales for fiscal year 2021. And here we have a few sales people here, Bishop pawn and Watson, and they're selling different treats, such as ice cream, frozen yogurt, tasty treats popsicles across different regions, such as north central and west here as well. And so we're tracking their sales and each of those regions for the particular type of treat that they are selling and we're tracking the units here as well. And so very nice report. If we just kind of scroll down, we can see that it goes down to real 85. And so our team uses this report often. We're always kind of, you know, creating charts and pivot tables from this data here..
We always want a really nice update. And so one of the things that, uh, that we do when we're in here, maybe we want to go ahead and use some filters so that we can, you know, maybe filter by region or filter by salesperson or filter by type as well. So that works well for us. But the thing is when we're creating filters, feel different filter views here on this data, it's actually updating on everyone to view. So if another person is in here, somebody else from my team is actually in this.