Add a series in seconds
[Scott] Do you want to save time while using Google Sheets? Well, in this video,
I'm sharing with you seven tips and tricks so you can get in, get out, and get on with the rest of your day. (gentle upbeat music) Tip number one, let's take advantage of series.
And what do I mean by series? Often you may need to create a long list of numbers or maybe things such as the days of the week, months, or year. And rather than coming up here and typing in the numbers by two, let's say I want to have a list here of even numbers.
Better yet, all I need to do is select those numbers, come down here, and then drag this little, blue box as far down as I need to and let go, and now I have that full list of numbers here.
Days of the week, for example, maybe I'm going to start by typing in Monday, and then the day Tuesday, all you need is a minimum of two. And now all I need to do is come here and drag it down, and once again, I've got every single day of the week in order.
Now, when it comes to months, I'm going to do something a little bit different here, because let's say I want each month at the start of the quarter. So I can start off with the first month of the year, which is January, but I'm going to put in the month of April, because that's the start of Q2.
Now what I can do is drag over these two and then start to drag down, and you will see that it is doing the math for me, it's taking the month from the beginning of each quarter
and has just repeated it down below.
Clean up your sheet
Tip number two, let's clean up our data and remove any duplicates. When you have a long list of data, sometimes it can be very difficult to see if you have any duplicate entries. But on top of that, as you can see in my example, I've got a few cells here where things are unevenly spaced.
It looks like there's a few too many space characters here where this pencil, for example, should be aligned, and this name of Jones shouldn't be sticking out like this either. Well, all we need to do is come up to Data, and then come down here to Data Cleanup.
Now we can either select Remove Duplicates or Trim the Whitespace, but I'm going to suggest that you do Cleanup Suggestions 'cause that's going to allow us to look at everything on this sheet.
So for example, it's telling me that I do have duplicate rows, that row 21 and 45 are the exact same. And you can see if I hover over it with my mouse, it's going to show me where those rows are located. I can either choose to ignore this, or I can select Remove and it will remove the duplicate for me. Next up, it's identified a few cells
where there is some additional white space here. We can see that Jones, and if I hover over it, it's going to show me what it's going to do by removing that white space, and then a few other places as well.
At this point, I can either choose this check mark to accept all of these suggestions or I can choose them by each individual column. So first starting with column C, and then I could come down here
and select Trim All for column D.
Quickly filter your data
Tip number three, let's make better sense of all of this information by filtering our data. The easiest way to filter our information is to come up here to the top-left hand corner, select it, which is going to select every cell on the sheet, and then come over here and select Create a Filter.
You'll now see that we have this little Filter icon at the top of each of our headers. So for example, maybe I only want to see units sold that are above 50. I'm going to come up here, I'm going to select that filter, and in this case I'm going to say Filter by Condition.
So I'm going to select this dropdown and I'm going to say, greater than or equal to, and I'm going to type in the number 50. I'm going to select OK, and immediately my data set is reduced. Now I am only seeing the rows which have units with 50 or more, but maybe I want to go one step further.
I want to continue to see this, but I also want to see the things that only cost more than $10. So I'm going to come over here to the unit cost. Again, in this case I'm going to choose Filter by Condition.
You can choose to sort it if you like or maybe just zero in on a particular price point, but I want to be a little more granular. So I'm going to say greater than, and in this case I'm going to type in my value, which is going to be 10.
I'm going to say OK, and perfect. Now I only have the items that have sold more than 50 units and that cost more than $10. Now the great thing is, is that you can save your filters.
o if I want to save this as a filter view, so maybe I want to call it something like 50+ units, something along those lines, and then I can quickly and easily come back to it. Now another great way to save time within Google Sheets is by sharing your sheets with others.
But if you're a part of a remote team, there may be an even better way, and that is by using a virtual workspace. Here I am within SpatialChat, and not only do I have one, but two of my Google Sheets visible to me.
And not only are they visible, but they're editable as well, so I can work in real time with members of my team. But in addition to bringing in as many Google Docs or Sheets, you can bring in any application into your virtual workspace.
Want to edit your Miro board or review that video? No problem. Need to access Notion or share code with your team? You got it. SpatialChat is the ideal solution for teams who need to collaborate in real time. To learn more about SpatialChat and to get started with their free plan, click the link in the description below.
Make columns freeze in place
Tip number four, let's freeze certain columns so that we can keep relevant information in front of us. When working with sheets, it is very common to have many different columns, which is going to force you to scroll to the right-hand side of the screen.
But what if I want to make sure that I can align this information with my particular sales reps? Well, here you can see the sales rep information is in column C.
All I need to do is come over here and hover my cursor right over here, and then drag this bar until it's between column C and D. Now, these first three columns will remain frozen.
With these three columns frozen into view, I can scroll as far as I like to the right-hand side and see the association between these cells and these particular sales reps.
Now, if I only care about the date, again, I can come up and hover over this thick, gray line and drag it to, let's say, the first column, and now only that first column will be frozen as I analyze and make use of this sheet.
Make your sheets easier to read
Tip number five, let's make our data that much easier to read. When you're working with a spreadsheet, especially with a long list, it can be hard to differentiate the different rows and columns.
But all we need to do is apply alternating colors to make each row stand out that much more. So in order to have our rows and columns stand out, the first step is to select where you would like these alternating colors to appear.
So I'm going to select these first columns here. Then, I'm going to come up to Format, and I'm going to select Alternating Colors. Here on the right-hand side, we have a menu which is going to pop up with a number of defaults.
Now the very first default here is just sort of a gray scale, so we have a darker shade for our header, and then sort of an alternating between a white and a light gray just so it's that much easier to read these numbers. But there's a number of different color schemes which we can choose from.
Maybe I'm going to select this orange shade here, and if I like, I can tweak the different colors here as well. I can select Done, and now we have this alternating pattern in front of us which is going to make it a lot easier for us to pick out a particular row and find that information.
Create drop-down menus
Tip number six, let's add a dropdown menu so we don't have to manually enter in everything, and also make sure that we have the correct formatting. Here you can see I've added a column called a product line. And what I'm going to do is first off
is I'm going to select where I would like that dropdown to be. So I'm going to select that area, and then I'm going to come up here to Data and I'm going to come down here and select Data Validation. So I've already selected the area. If I need to adjust that, I can select the data range again. Now, I can either choose a list from a range in my sheet, so maybe I have a list of the things that I'd like to include in that dropdown menu, I could select them here. But I haven't created that yet, so I'm going to select this dropdown and I'm going to come down and select a list of items. So here I can type in what I want available within that dropdown. So maybe I have a consumer product line, I'm going to enter in a comma to space between the options. Maybe I have a small business product line, and then I also have an enterprise product line as well. So I'm going to type that in here as well. At the end of entering in my options,
I'm going to select Save. And now you can see I have a small dropdown arrow within the selected cells. So I can select that, and I have those three options now available to me. Maybe this is for small business, this is for consumer, and this one here is also for consumer.
Visualize your data
Number seven, column stats. When you're dealing with a large quantity of information, it can be hard to see any particular themes. But if you select any of your columns, then come up to Data, and then come down to Column Stats, a new window will open in the right-hand side of your screen.
Within this view, I can quickly see the count or distribution of the different items in that column. Now, pay special attention. You may wish to ignore the first row, because that's my header in this case,
but now I can quickly see that I have more binders in this column and only three desks in total. If I scroll down a little bit further, I can actually hover over these different values and see exactly where they are listed within that particular column,
along with their specific number. And if you'd like to see the stats of other columns,
all you need to do is come up to the very top and select the left or right arrow.
So here I can quickly see my regions, for example. And if you want to save even more time within Google Sheets, make sure that you get familiar with pivot tables. Don't know how to use a pivot table? Then watch this video next where I show you everything you need to know.
And remember being productive does not need to be difficult, in fact, it's very simple. (gentle upbeat music)
