November 6, 2018

Working with Google Sheets to Create a Student Tracking System

Thanks to Emmy Luna for the original idea!

There are many reasons to have a Google Sheet tracking system. One is to have students keep track of tasks they've completed. These instructions are to create this tracking system, but you can use these individual hints to help you create anything you'd like in Google Sheets!

1. Changing the size of rows and/or columns:
   • Highlight all the rows you'd like to change.
   • Right click on the rows and choose RESIZE ROWS - put in a number and if it isn't the size you need, you can change it the same way. Columns are the same. Simply highlight the column(s) and right click to find RESIZE COLUMNS.
   • VIDEO
2. Put in all the text in the cells you need filled.
   • You can format the font, size, etc. To have text wrapping on in cells, you can either choose a specific cell, or choose all cells by clicking on the empty space on the spreadsheet between the 1 and the A (it's right below the FX. This selects all the cells in the spreadsheet.
   • Go to FORMAT - TEXT WRAPPING and choose WRAP. This will keep your cells the size they currently are and wrap the text within them. If you have too much text, some won't show and you'll need to resize the row. Simply get the arrow when you have your mouse between the 1 and the 2 and drag down until all the text fits.
   • To bold or change fonts, select the cells you want to edit and choose the formatting. If you want to edit the entire spreadsheet, click on the empty space between the 1 and the A.
   • VIDEO
3. Change the color of cells/columns/rows
   • Select the cells you want a certain color. On the toolbar, find the paint can and choose your color. Remember, you may have to change the font color depending on the color you choose for your cell.
   • To color an entire row, click on the row #. For example, click on the 1.
   • To color an entire column, click on the column letter. For example, click on the A
   • VIDEO
4. Adding check boxes into a cell
   • To add a check box to a cell, click in the cell, go to INSERT - CHECKBOX. Then you can copy the checkbox and paste it into the cells that need it.
   • VIDEO
5. Adding drop down menus into a cell
   • To add a drop down menu, click in the cell. Go to DATA - DATA VALIDATION.
   • A box pops up. The range is the range you've selected and you don't need to change anything here.
   • For CRITERIA, depending on what you're doing, you'll choose the correct item. For this demo, let's choose LIST OF ITEMS. Now, add your text into the box with commas following all the terms you want in your drop down.
   • Make sure that SHOW DROP DOWN LIST IN CELL is checked!
   • Click SAVE
   • VIDEO
6. Conditional Formatting - Here is the magic!! Conditional formatting is how you have your formatting change if something happens in a cell. For example, if you have a drop down and a word is chosen, you can have the color change, the font change, or other editing tasks! If you have a checkbox, when that check box is checked you can again have certain things happen to the cell!
   • FOR DROP DOWN MENUS - Select the cells that have the drop down menus (and that you want to do the same thing).
     • Go to FORMAT - CONDITIONAL FORMATTING. A box pops up on the right.
     • Apply to range - this is all the cells you've selected. If you want the entire column, in case you add more items, simply type in the first cell, colon, the the column letter. For example: B1:B instead of just B1.
     • Format cells if - you'll need to choose this. For this demo, I chose TEXT CONTAINS and put the word in I wanted. Now on the formatting, choose the items you'd like. I usually choose a color for sure!
     • You can add more rules to this cell(s) by creating a new rule (on the right)
     • VIDEO
   • FOR CHECK BOXES - Put tasks you want to check off in the column next to the check boxes.
     • In the cells you have your tasks listed, select these cells
     • Go to FORMAT - CONDITIONAL FORMATTING
     • Apply to range - this is all the cells you've selected. See above to add the entire column.
     • Format cells if - choose custom formula
     • Value or formula - Write this formula =D1=TRUE (or change the D1 to whatever cell the checkbox is in)
     • Now change the editing - bold, strike through, color, etc. and click DONE.
     • Test it when you click a check box.
     • VIDEO

Lastly, THANK YOU LISA EDWARDS for this great tip!!

Do you want to make your sheet a smaller size? For example, sheets have thousands of rows and columns. How do you make it smaller (I'm not sure this is the correct wording, but we are eliminating all the other rows and columns that are not being used.)

• To hide all the unused columns and rows is to highlight the entire first unused column by clicking on the alphabet header of the column, then press control, shift and the right arrow. This will highlight all the columns to the right of the used area up to the end of the sheet. Now right click anywhere on the highlighted area and choose from the drop-down list if you want to simply hide all those unused columns or even to delete them. Press Enter, voila
• Similarly to delete unwanted rows do the same procedure beginning by highlighting the first unused row control, shift and down arrow to highlight all the rows below the last row used, Right click select-hide etc
• VIDEO