Google Sheets Basics

 A lot of us are familiar with Google Sheets. Or maybe you're used to using Excel on your desktop. Either way, when you're using a spreadsheet program, you have an endless amount of tricks that you can use to manipulate your data. Whether you're making a spreadsheet to keep track of finances, a checklist that helps you remember your grocery list, or something that shows data from a Google form that live updates as users fill out the form and creates a beautiful graph for you, there are a ton of possibilities in Google Sheets.

Lets start with the basics:

What is Google Sheets? Sheets is the Google equivalent of Microsoft's Excel program. It is a lighter version that is based in the cloud and can be shared and edited in real time with a huge amount of users or collaborators. You can have up to 100 editors going at the same time! However, having that many edits occurring simultaneously may cause the program to jump around too much so I don't recommend it!

Lets look at a basic spreadsheet and build a set of data that we can manipulate.

When you open a Sheets document, you'll see the above table. Google will offer some suggestions on the right hand side to help you build your table. These are pre-built (cookie cutter) forms that you can then enter your data into. Depending on what you plan on using, you will need to look at each possibility and see if that form style works for you. However, for today's example, we will build our own.

Lets enter some data. I want to keep track of how much students have paid for an upcoming field trip. In each column, I want to have specific data that I can sort by, search by, and total so I'll label my columns as I need:

Across the first row you'll see I labeled my column with information I need to gather for my class. I can format these cells any way I'd like by centering the text, making it bold, or whatever suits my needs like I would in a word document. Next I want to "lock" this row because its the title of all the different columns of information. To do that I right click on the number 1 on the left:

Scroll down to the bottom and select "View more row actions" and click on "Freeze up to row 1". This locks row 1 in place so when you sort your columns later by alphabetical order, row 1 isn't included in that range.

Now row 1 has a gray line to indicate its locked in place.

Next we need to enter our data:

Once we've entered all our data, we see that we have a few students who haven't paid for the field trip yet, some parents are attending and only one has paid for the parent to attend as well. We have a total amount in cash on the right and no one has needed a refund yet for the trip.

Now that we have our data, how can we use it to our advantage so that its easy to read and can help us become more efficient in our day? Lets start by creating some formatting to give us an "at a glance" look of our spreadsheet.

Select a column you want to edit and click on "conditional formatting" at the bottom:

This menu opens up on the right:

Edit your choices to match what you'd like to see. You need to change the range of the cells to start on "D2" because D1 is the title of the column. The range will then read "D2:D1000". Then select your format rule. I like to use "Text is exactly" and enter the "y" in the box since that is how I marked the columns:

After that, select the color you would like your cells to be if they contain the letter y. I chose light green for this example. Click on "Done" and your first column will be edited to show that formatting. Now, when you enter a "y" into that column, you will see it change to green. If you enter any other letter into that column, it will not change colors.

To change that, simply go through the above steps again, select the range and color you want in that column and select "Done".

Lets format the date column next. You want only "date" data in this column so click on the column headed E and select "Data Validation":

Click on "Add Rule" on the right and adjust the information for the range from E1 to E2:E1000. You'll notice it changed all of our information to a drop down menu. That will not work if we are trying to select only dates in this column. So adjust the Dropdown criteria to "Is Valid Date":

Leave the "Show a warning" option and select "Done". Now when you enter something other than the date in this column it will show a small red triangle which means that the data is invalid according to the rule we just created:

Next, lets create a drop down option for our "Cash/Check" column since we have two choices here for student payment. Right click on the column and select "Drop down" toward the bottom:

This is just another data validation rule we are creating. This menu will pop up and you will need to adjust the range of the cells to begin on F2. Then you can edit the options to be the ones you want to be able to pick and allow for multiple selections or not. Since we only have two options, we will create one for check and one for cash. Then pick the colors for each of those options so they are easy to see at a glance. Once you click on "Done" you'll see the column is now edited to have a drop down menu in each cell:

Now you can go and edit the remaining columns how you would like using the conditional formatting rule. Apply the range of the cells and the color like you prefer for each column.

Now we need to make sure that our money column only accepts dollar amounts in order for our calculations later to be correct. Select the column you want to make the currency column. In this example, I highlighted the I column. Go to "Format" at the top of the window, select "Number" and then "Currency". 

Everything in that column will now only be marked as currency with a dollar sign.

Now how can we use this data to our advantage? We can create a sheet to keep track of everything, color code those columns that are important to us, create rules and data validation so that we don't enter incorrect data, but now we need to know how to use it.

Lets keep track of how much we have collected for this field trip. Begin by selecting a cell that is not near any of the data you might enter, somewhere off to the side. I chose K16. Write the word "Total" in that cell and move to L16.

Click on "Insert", then "Function", and select "SUM". 

This box will pop up underneath your selected cell:

Select the space in between the parentheses, type "I2:I1000" without the quotes in order to select the entire column of "Total Paid". Hit enter. Your cell should now tally your total paid amount. If you enter another paid amount, it will add it to the sum total and also highlight the box because you created that formatting rule earlier.

Now that we have our spreadsheet done, we can adjust it for the view or print it if we need. Here's a few of the things we can do:

Hide or Unhide columns:

Highlight the columns you wish to hide:

Right click on the highlighted column letter and select "Hide Columns"

Now you will see two little arrows between D and H indicating that there are hidden columns here.

To show the hidden columns, simply click on one of the little arrows and the hidden columns will come back into view.

Hide grid lines for printing purposes:

To show or hide gridlines, go to the top of the page and click on "View" and select "Show" then click on "Grid Lines"

You can also hide the grid lines when you print by deselecting the "Show gridlines" box in the Print settings when you click on print:

What if we want to create a graph from our data:

Click on "Insert" and select "Chart". This box will show up on the side:

Edit your preferred style and pick your chart style under Chart type. I like pie charts so I used that for this example. Click on the little rectangle next to the Data Range. This box will pop up:

If we want to see how many have paid, we would type in "D2:D1000" to keep the entire D column in this pie chart. Click OK.

Be sure to click on "Aggregate" and your pie chart will show up.

Now our pie chart is in our graph. I dragged it down so it wouldn't cover up my data:

If I change the data in that column, my pie chart will adjust as well.

So you can see there are a ton of things you can do with sheets. This is just a small example of how to manipulate them to help you keep track of your data. These features are available to use in any google sheets file you have access to depending on your level of editing permissions.