Adding dropdown menu or checkbox
Are you ready to save time and learn some cool new productivity tips for Google Sheets? Well, then this video is for you because I'm sharing with you seven different tips, so you can get the most out of your sheets.
Hello everyone, Scott Friesen here at Simpletivity, helping you to get more done and enjoy less stress. And let's dive in into tip number one. So, in many cases, we want to keep track of what is happening
with our data within our sheet. And for that purpose, it may be helpful to add either a dropdown or a checkbox. Well, it's actually a lot easier to implement than you might think. So, in this example here, I've got a a final column called Shipped and I want to keep track if something has been shipped or not.
So, all I need to do is select the range or select the cells where I would like that dropdown to be. I'm going to come up here to Data and then select Data validation. Now, the second dropdown here,
which currently says List from a range, I actually want to come down and select List of items. So here, in the field next to it, is where I'm going to put what I want within that dropdown.
Yes, you don't have to create that list somewhere else in the spreadsheet, you can do it right here. so I'm going to say, Yes, comma. I'm going to say No and then comma, maybe I'm going to put something like Delayed or something along those lines. So, you can add as many different options as you want.
We're going to leave this checked: Show dropdown list in cell, and I'm going to hit Save. Now, you can see we've got a little dropdown arrow beside each and every one of these rows. So, I can go down and say, "Yes, that was shipped," "No, that was not shipped," and, "We're still waiting for this.
That has been delayed." Adding a dropdown to your sheet will not only make it easier for you to make certain selections, but also prevents the chance of you misspelling something or having to type in the same thing over and over again.
But in some cases, all you need is simple checkbox
and we can add that quite simply as well. Once again, we're going to select the range of cells where we want that checkbox to appear and we're going to go back to Data and then Data validation.
Now this time, from the dropdown, we're going to come all the way down and select Checkbox and then hit Save. And now, we've got a simple checkbox waiting for us. So we can check things off, maybe in this case, meaning that they have been shipped and I can check and uncheck as I go along.
You can also apply conditional formatting to both of these options, which may just spruce up and make things pop that much more within your Google Sheets.
Apply alternating colors
Now, speaking of making things stand out, sometimes it can be hard to analyze a large group of data or a large number of rows and columns. It's really hard to distinguish the differences here and maybe if I want to point out or see a particular number.
Well, it's very easy for us to apply some alternating color. And you've probably seen this many, many times over in other reports or things you've seen in magazines or other places online. All we need to do is select the area where we want to apply this.
We're going to come up to Format and then we're going to come down here and select Alternating colors. Now, immediately, it's going to apply something here. It's kind of a grayish color here where we've got a deeper gray at the top for our header.
And then it's sort of alternating between light and dark, but we can also customize that color. So maybe I want something that's a little brighter, maybe with this orangey color here. Maybe I want something a little more bluish, which is in line with my own branding.
And now, it's that much easier to see the different figures here within our sheet. And, of course, if you don't like any of the default styles here, you can come down here to Custom styles and choose your header, color 1, or color 2 to suit your needs.
Sum up new rows
Now the next tip on our list has all to do with a formula that we all use on a frequent basis. And that has to do with summing up a particular set of figures. So let's say I want to sum all of the total cost here.
I'm going to go to this cell over here and I'm going to type in sum and then all I need to do is just select this area here. Pretty straightforward, right? I can hit Enter on my keyboard and there is the total sum.
But what if I start adding other items here? Let me add in, I don't know, maybe $250
and then $350. Oh, wait a minute. It's not adding things up because I selected just this range. So, anything below is not going to be added here. Well, if we come back to this formula all we need to do is come over here and remove the 20.
Remove the 20 from the row here and then hit Enter. And what it will do, it will continue to sum up everything within this particular column. It's essentially ignoring the row number over here and will continue to add things up.
So not only is it giving me the current value here, but if I add something like $500, yes, that's been added. Let's add $1,000, you see it will continue to add things up. So the next time you need to use these Sum formula, see if you need to put a specific end to your range or just to leave it blank.
Dynamic currency exchange
Now, this next tip is especially helpful if you are dealing with multiple currencies or currency exchange. So in this example, I have a total, which is listed in US dollars.
But maybe I want to see the value of these dollar figures in Canadian dollars. Well, what we can do is rely on the power of Google Finance to give us an accurate exchange rate right here within our spreadsheet.
So in this case, I'm going to start with this cell. I'm going to type in the equals sign. And, of course, I'm going to reference the cell that I want to convert. Next, I'm going to hit the asterisk, which, of course, means multiply, and then I'm going to start typing in Google Finance. Now, I don't even have to type in the entire thing. You can see it's giving me that suggestion.
So I'm going to hit Enter to complete that so Google Finance is in the formula. Next, we want to hit our open quotes and then type in the word currency. All right, currency and then colon and then in this case, I'm going to type in USDCAD.
Meaning, I want to convert from US dollars into Canadian dollars. If you're not aware of the currency code, you may need to look that up in advance. We are going to close quotes, and then close the bracket, and then hit Enter.
And now, we have an actual up-to-date currency exchange for this amount. If I want the entire row, all I need to do is drag this down. And now you can see, I've got an accurate currency conversion
of these US dollar figures on the left. So a great formula to know, especially if you want to have those multiple exchange rates live on your sheets.
Sparkline mini graph
Now, the next tip on our list is one of my favorites and such a great way to see some visual data without having to create a complete chart. You're probably familiar with the ability to create a chart, whether that's a pie chart, or a bar chart, or something else that may take up much of a particular tab.
But what if you just want to see a quick visual of a particular set of data? So in this case, maybe I want to see how our sales of units are trending over time and I just want to include that down here below.
Well, all I need to do is click on any cell, hit the equals sign, and then type in the word sparkline, and we are going to have a miniature chart. So sparkline, and then within the brackets, we just need to select the range that we want to view and then hit Enter.
And now, we've got a great visual of what is happening just within that particular set of numbers. So I can see that we started off quite strong, and then dipped, and we've sort of been going up and down with the sale of our units.
This miniature sparkline report can be a great way to see how things are trending without taking away and taking up valuable real estate from the rest of your sheet.
Pick date from a calendar pop-up
Next up, let's take a look at how we can make it easier to input dates within any particular cell. So you may already know if there is a valid date anywhere within your sheet. You can double-click on it and it will bring up a Calendar view.
So this can be really easy. If I want to change this date over here, I can just select it by double-clicking. But what if this entire column is a ship date and I want to just double-click in this row? Well, I can't do that. It's waiting for me to input a value.
So in order to add a clickable calendar for every single cell within this column,
first, I'm going to select the entire column or you can just select a specific range.
I'm going to come back up here to Data and select Data validation. Now, we've been here before, but in this case, what I'm going to do is I'm going to select this dropdown
and I'm going to select a date. Once we select Save, we can return to our sheet.
And now, anywhere where I double-click within this range, you can see it's going to bring up that calendar. No, there's no identification letting me know that this is a date field. But wherever I click, I can pull up that calendar, select a date, and then get on with what I'm doing.
You may need to adjust your date formatting to make sure that everything is in line, but this can be a much quicker way for you to select the date that you're looking for and then get on with the rest of your sheet.
View column stats
Now, this next tip is something that I have ignored for years, thinking that it was maybe too simple and wasn't actually helping me out enough. But since I've found it,
I've started using it a lot more frequently. Sometimes, when you have an awful lot of data here, it can be hard to decipher what you are looking for and rather than trying to come up with the right formula. So, for example, maybe I want to see, "Well, how many pencils are actually in this list? I don't want to come up with the formula.
I don't want to have to sort everything here. Isn't there a simpler way?" Well, all I need to do is select the range that I want to analyze, come up here to Data and then select Column stats. And then on the right-hand side, what it's going to do is show me a variety of different data.
So in this case, let's come over to our item and I can see immediately, "Oh, pencil, there are nine pencils, there are eight binders, but I've only sold three pens." And I can actually see that frequency down here below.
In fact, as I hover over these different sets, they will also highlight as well. So if I need to find out where is that desk in this set of data, I can just highlight it here as well. Maybe if I want to jump over to my representatives here, I can see that it looks like Jones is my most successful rep, but maybe Howard needs a little more help.
And again, I can have this option to filter through and highlight that information as well. So, column stats, a great quick and easy way to find what you are looking for. Now, I am absolutely sure
that there are some Google Sheets tips that you find helpful but I didn't include on this list. Well, if you'd like to share that with others, be sure to let us know in the comments down below. Now, if you enjoyed this video, you might also enjoy some of my other tips and tricks listed here.
Remember, being productive does not need to be difficult. In fact, it's very simple.
