10 Google Sheets Tips & Tricks To Make Life Easier
Google Sheets is the Google Answer to Microsoft Excel and continues to grow in popularity. While some may think of it as “Excel online,” Google Sheets is a very different product. Regular Excel users might find themselves puzzled by the Google Sheets navigation and not know the platform’s full potential.
Whether you’re a daily Google Sheets user or diving in for the first time, these tips and tricks can help make your time in online spreadsheet land a little more efficient.
1. Conditional Formatting
Google Sheets conditional formatting and color scale functions are different from Excel but let you accomplish the same outcome. Rather than being part of the main ribbon, you can find the conditional formatting option in Google Sheets under the Format > Conditional Formatting. Learn more about Conditional Formatting in Google Sheets here.
2. Drop Down Lists and Data Validation
Data validation allows you to restrict the values that are entered into a particular cell. Using data validation in your Google spreadsheet lets you create drop-down lists within cells. You can learn more about data validation and drop-down lists here.
3. Keyboard Shortcuts
Like most Microsoft products, there are keyboard shorts in your Google spreadsheets. These can save you a ton of time once you’ve got them down. These keyword shortcuts will let you quickly execute tasks like formatting cells, navigating the spreadsheet, adding new sheets, editing notes and comments, adding formulas, and more. You can find a complete list of Google Sheets keyword shortcuts for Mac and PC here.
4. Google Translate
If you have cells in different languages, Google Sheets can translate them for you directly in the spreadsheet. You can add =GOOGLETRANSLATE(cell) to any cell of the spreadsheet to automatically translate it. Learn more about Google Translate in your spreadsheet here.
5. Check for Valid Email Address Formats
If you’re using Google Sheets to collect or send a list of contacts, being able to quickly check that all emails are in the correct format and not listed as “.con” or missing other parts can save hours down the line. Google Sheets has an ISEMAIL function that lets you check if any cell is the correct format for an email with the formula =ISEMAIL(cell). You can learn more about this function here.
6. Import Data From Other Sheets
The IMPORTRANGE function of Google Sheets allows you to import data from one sheet into another. With this function, you only have to update data in a single sheet, saving time with big data sets. You can learn more about IMPORTRANGE here.
7. Create Sparklines
Sparklines let you quickly and easily visualize data sets to see trends. A simple formula of =SPRAKLINE(cell range) will create a sparkline in the cell below the data. You can learn more about the SPARKLINE function from Google here.
8. Clean and Trim
Data sets often end up with extra characters and spaces when they’re being imported or copied and pasted. Having these additional white spaces can cause big headaches down the road when taking action with your data. CLEAN and TRIM functions can help clean these up. The CLEAN function will remove any non-printable ASCII characters in a cell, and the TRIM function will remove any whitespace from the beginning and end of cells. You can learn more about CLEAN here and TRIM here.
9. Hyperlink Formula
Inserting links into large sets of data can be a time-consuming task, but luckily Google Sheets has a quick solution with the HYPERLINK function. The formula =HYPERLINK(URL, LINK_LABEL) lets you quickly add the link destination and the anchor text. You can learn more about the HYPERLINK function here.
10. Count of Unique Cells
If you’ve got a data set with lots of duplicate values (names, numbers, etc.), getting a count of all the unique values is easy with the UNIQUE function in Google Sheets. The formula =UNIQUE(cell range) will give you a count of unique rows. You can learn more about the UNIQUE function here.
Want to learn even more Google Sheets tips and tricks? ONLC has Google Sheets training classes happening online and in-person to help you become a spreadsheet master.
