You don’t need advanced data science skills to make the most out of Google Sheets.
Nearly all professions require some spreadsheet and data literacy to make tracking projects more efficient, managing datasets less overwhelming, drawing insights a breeze, and presenting information more intuitive. Use Google Sheets for easy solutions to any of these tasks.
This guide will arm you with our Top 10 Skills to make organizing, navigating, and using functions and formulas in Sheets easy. We’ve also got our Top 10 Rapid-fire Formatting Tips you’ll use over and over again.
And if you’re using Excel, don’t worry, these tips still apply!
Top 10 Google Sheets Skills
There are lots of ways to make a spreadsheet with hundreds (or thousands) of rows completely manageable so that you can easily and quickly draw insights. Here are the Top 10 Skills that you need to know to automate, formulate, and format your way to more effective spreadsheets.
Think you’re not a formula person? Hear us out.
1. Consistency is key. Being consistent with how you format text is crucial to being able to accurately use the skills below and more sophisticated formulas. For example, if your data uses a combination of TRUE, YES and Y (or DC, Washington D.C. and District of Columbia), this will make sorting and filtering your data a challenge.
2. Filters make navigation, organizing and sorting a breeze. Select the row or cells where you would like to add a filter and click the Filter button in the menu bar. From there you can do things like (1) sort alphabetically; (2) display select information; (3) hide blank cells; (4) display only cells that meet certain criteria. Learn more here.
3. Perform simple calculations. You can use Sheets for lots of simple (or complicated) math. Simply enter “=” in the cell and then input or click the cells to build your equation. Use the correct order of operations when formatting your equations. For example =B5/D9 or =(A1+C10)-B2 or =E12*A8+A1. You can also use formulas such as =SUM(A1:A10) for arithmetic in a given range. Learn more here.
4. Cross-reference information across multiple sheets. If you could only master one function – this is the one to master. The VLOOKUP function allows you to cross-reference information across multiple sheets and answer questions like “Who in our outreach tracker registered for the event?,” or “If X person from our outreach list registered for the event, what did they submit as their job title?” Learn more here.
5. Count cells in a range OR count based on certain criteria. The COUNT and COUNTIF/COUNTIFS functions allow you to count the number of cells in a range or the number of cells that meet certain conditions. Let’s say you are working on a gradebook in Sheets and you want to count the number of students who received an A (or an A or a B), these functions will automate that task. Learn more here.
Many of the same tips and tricks outlined here also work in Excel and in other common spreadsheet apps.
6. Assess if an argument is true and produce your desired response. The IF function allows you to produce certain responses based on the validity of a statement. For example if you would like to produce letter grades based on a 0-100% scale you can use the IF function (e.g., If the student scored higher than 90, produce an A). You can also use the IFS formula for multiple arguments. Learn more here.
7. Replace an error with a designated value. Pair the ISNA function with the VLOOKUP and IF functions to identify and replace a #NA error. For example, If the result of a VLOOKUP is #NA, produce “Yes,” if it is not #NA, produce “No.” Learn how here.
8. Split text across multiple cells. Have you ever had a list of names that you needed to split into multiple cells to personalize an email, update a database or something else? Do not go line by line manually splitting your text. Use the “Split Text to Columns” feature in the “Data” menu or one of several functions to do this quickly and easily. Learn more here.
9. Lock your formulas. When you copy formulas into a new cell, Sheets will auto-adjust the reference cells. For example when you copy the formula =A1/B1, down to the next row, Sheets will automatically adjust the formula to =A2/B2. You can instead lock parts of the formula by adding a “$” before the row or column you would like to lock. Let’s say you want to lock the divisor (B1). Add the “$” to the row (if copying to a new row) or column (if copying to a new row) or both. Now, if you copy the formula down to the next row it will adjust to =A2/B$1, preserving the divisor.
10. Nest your functions to automate more complicated tasks. When you nest functions, or include multiple functions in a single cell, you can perform more complicated tasks and analyze data faster. Let’s say you are using Sheets to manage a program recruitment process and you want to calculate the conversion rate for applicants that attended an informational webinar to understand if they applied at a higher rate than those who did not attend the webinar. Or, you want to identify the phone numbers for program applicants who have not paid their application fee. Nested functions will allow you to answer multiple questions using your data. Learn more here.
Top 10 Rapid Fire Formatting tips! Did you know….?
Thoughtful formatting is key to easy reading, navigation, and analysis. Try these rapid fire formatting tips for clean, beautiful spreadsheets.
1. You can automatically alternate row colors for easy reading? Find it under the “Format” dropdown menu.
2. You can freeze columns or rows for easy scrolling? If you want to ensure certain rows remain visible as you scroll, you can put a freeze in place. Find it in the “View” dropdown menu.
3. You can insert checkboxes directly into a cell? Try it to help track a project, manage a to-do list or other purposes. Find it under the “Insert” dropdown menu.
4. You can insert dropdown lists directly into a cell? Use this to for clean formatting or to restrict the data that users can enter into a cell. Select “data validation”under the “Data” dropdown menu.
5. You can auto-adjust the size of one or more cells? You have two options here: (1) when you highlight more than one row or column and drag the border to adjust the size, all highlighted rows or columns will be auto-sized (2) double click on the border of one or more columns or row to auto-adjust the size to fit the text.
6. You can auto-format cells? Use this to set formatting rules based on criteria that you identify, for example change the fill or text color based on the data. Select the range you want to auto-format and find “conditional formatting” in the “Format” dropdown menu.
7. You can remove duplicate data? Select the full table of data and find “Remove Duplicate Rows” in the “Help” dropdown menu.
8. You can merge two or more cells together? Highlight the cells you want to merge and click the “Merge Cells” button in the menu bar:
9. You can adjust the way text appears in a cell? Selecting one or more cells click the “Text Wrapping” button in the menu bar to allow text to overflow past cell borders; wrap text onto a new line; or clip text that extends past the cell borders.
10. You can auto-fill data? Select one or more cells, and click and drag the square in the bottom right corner of a cell, or double click the square to copy the content down the full range of your table. Note that if you are autofilling certain data (like numbers or dates), Sheets will automatically adjust the data (1, 2, 3; January, February, March). To duplicate the content (1, 1, 1; January, January, January), first select two consecutive cells before auto-filling .
Bottom line?
There’s almost always a faster, easier way to do things in Sheets. Before spending hours manually sorting, organizing and calculating, start with these tips. A quick Google search of the problem you’re trying to solve will find tutorials and guides with the solution you need.
If you’re ready to up your–or your students’–game with data literacy skills check out more learning and teaching resources here.
