Getting good at Google Sheets can save you hours. Maybe weeks.
But, I’m not as much of an expert at Google Sheets as I could be. To cover this weakness, I’ve hired bookkeepers to do stuff like “financial forecasting” in Google Sheets so that my spreadsheets don’t look like garbage when I share our data room with investors.
With that said, I want to upskill when it comes to Google Sheets. That’s why I asked the community at Terkel for their best Google Sheets tips. Here’s 19 answers to help you become more of an expert when it comes to Google Sheets.
- Enhance Readability by Using Color Alternated Rows
- Control + Shift + Y to Divide Data
- Use Conditional Formatting
- Use Google Sheets to Capture Leads from An Automation
- Translate Content Or Detect Language Inside Sheets
- Rotate a Text in Google Sheets
- Convert Data into Charts and Graphs
- Use Data Validation
- Pivot Tables
- Custom Add-ons
- Tiktok is Full of Google Sheet Hacks
- Take Google’s Official Online Course for Sheets
- Automate Tasks With Macros
- Use The Explore Feature as a Google Sheet Virtual Assistant
- Make the Most of Heat Maps
- Enable Sharing Access
- Add a Drop-down List in Google Sheets
- Insert Images from URLs
- Create Template Spreadsheets
Enhance Readability by Using Color Alternated Rows
One of my top recommendations for using Google Sheets is to color alternate rows. This tool is built-in to the platform and allows users to make a dataset easier to read by coloring the rows. This is most suitable for those who prefer reading over viewing movies. The following actions should help you:
- Choose the data set’s header and every cell.
- Select “Format” from the tab menu.
- Then select “Alternating Colors.” You can make your modifications in this window after it opens on the right side. Make sure the style choice is set to “Header” while doing this. Do not select the option if your data does not include headers.
- Choose one of the built-in templates and give your header a color.
Voilà! You now have a much more transparent and easier-to-read document.
Control + Shift + Y to Divide Data
This function absolutely changed my day-to-day work. I do a lot of operations on databases and sometimes need to adjust the categories. The “Control + Shift + Y” enables you to easily divide data from one column to multiple more specific ones.
For example, when having emails in the column but wanting just the username, you can get it by simply adding a new column called “Username” and then adding the first two usernames from the emails section. After that, you can use Control + Shift + Y and divide all your emails from usernames at once.
Use Conditional Formatting
Use conditional formatting to highlight or format cells based on their values. To set up conditional formatting, select the cells you want to format, then go to the “Format” menu and choose “Conditional Formatting.” You can then choose the formatting rules you want to apply (e.g., cells with values above a certain threshold should be highlighted in green) and customize the formatting options (e.g., font color, background color, etc.).
Use Google Sheets to Capture Leads from An Automation
I run lead gen campaigns on Facebook and Google. Usually, it’s best to import those leads into a CRM or email automation software. However, there are times when you might want to create a spreadsheet of results.
Recently, I placed a help-wanted ad on Facebook, geotargeted to specific areas of New York. This was a temporary need and we didn’t expect the leads to have value for very long. We just needed help in the short term. So, it made sense to create an automation that populated a Google Sheet with the leads that came in every day. It took about an hour to set up the ads and the automation.
A quick way for you to do this yourself is with a tool like Zapier. Just Google “zapier Facebook lead ads Google Sheets” for the solution. If you have basic coding skills and a high volume of leads and want to save on cost, then instead Google “Facebook leads webhook” and follow the instructions on Facebook’s developer website. You can do the same with Google AdWords and other platforms.
Translate Content Or Detect Language Inside Sheets
Google Sheets has a great feature that allows you to translate text directly within the spreadsheet. This can be a huge time-saver for anyone working with multilingual data, such as international marketing strategies. Instead of copying and pasting text into Google Translate, you can use a special formula to translate a cell’s value.
Simply add a new column and enter the formula: =GoogleTranslate(A2, “es”, “en”), where A2 is the cell containing the text you want to translate, the second entry is the source language, and the third entry is the language you want to translate into. You can also use the DetectLanguage formula to figure out the language of a cell’s value. Just enter the formula: =DetectLanguage(A2).
For more information, you can check out these links:
Rotate a Text in Google Sheets
Use the sheet rotation feature in Google Sheets to change the text in a spreadsheet. This feature allows you to change the order of cells and make it easier to read. To rotate a sheet, click “Format” and select “Rotation” from the menu. You can then choose the degree to which you want to rotate your text. And voilà! Your text is now rotated.
Convert Data into Charts and Graphs
Take advantage of the capability to convert your data into charts and graphs. This can be very beneficial in business when analyzing things such as marketing and sales. Observing data visually allows one to easily recognize common trends and insights, which can inform future strategies.
Use Data Validation
My best tip for using Google Sheets is to use data validation to ensure the accuracy of your data. It allows you to set specific criteria for data entry, such as limiting the range of possible values or restricting user input to a list of predefined options. This can help to prevent errors and ensure that the data you are working with is accurate.
For example, you could use data validation to set a minimum value for a cell, which would prevent users from entering a value that was too small or large. You could also use data validation to ensure that users enter only numbers in cells, which can help avoid errors when you are working with currency and other numeric values.
Pivot tables are a powerful tool in Google Sheets that can help users to quickly and easily summarize and analyze large amounts of data. There are several reasons why pivot tables are considered one of the best functions in Google Sheets: They allow users to quickly summarize and analyze data from multiple columns and rows; they make it easy to reorganize and rearrange data; they allow users to filter and group data; and they are dynamic, meaning that they will automatically update as the data in the original sheet changes.
My best tip for Google Sheets is to make use of custom add-ons. Custom add-ons are available in the Google Sheets in Extension > Add-on > Get Add-ons. They can help you extend the functionality and capabilities of your spreadsheet by adding new features and capabilities that are not available in the default Google Sheets. These add-ons can help you automate tasks, calculate complex formulas, visualize data, and more. You can also find a lot of free and paid add-ons in the Google Workspace Marketplace to make your work easier and faster.
Tiktok is Full of Google Sheet Hacks
If you’re not already on TikTok, it’s probably time to join if you want to learn some amazing tricks and hacks for Google Sheets. I’ve seen some pretty mind-blowing ones that I wouldn’t even know how to explain in words. Simply search on the search bar “Google Sheets hacks” and you’ll find many smart people who have some of the best tips for Google Sheets.
Take Google’s Official Online Course for Sheets
It might sound a bit silly, but this is the best tip I can give to anyone looking to get more proficient with Sheets. The course is a good summary of just about everything you might need to know or might be missing about using the program and comes with recommendations on how you can learn more advanced ways of working with the program – either through Google’s training programs or through the myriad ones out there from private individuals or businesses.
Automate Tasks With Macros
Google Sheets is one of the most popular and useful utility software out there, regardless of your expertise. The “Macro” feature is an exceptionally useful feature for people in need of automating repetitive tasks. Its setup is straightforward. You’ll have to create your macro and then perform it subsequently initially. You’ll then be required to choose your cell reference, where your macro will only perform tasks on the exact cell you record. The relative references ensure the macro performs tasks on the cell you select and its neighboring cells. After choosing your cell reference, you’ll be required to complete tasks and save your work, and you will be good to go.
Use The Explore Feature as a Google Sheet Virtual Assistant
The Google Sheets Explore feature is a highly intuitive feature that very few get to maximize. Feel free to see it as Google’s free virtual assistant dedicated to Google Sheets.
The Explore feature is particularly handy when you are managing large piles of datasets. I admire how it manages to tap into the context of what you are working on and offer highly intelligent suggestions to speed up the process. The Explore feature is easily accessible from the bottom right of any spreadsheet you open on Google Sheets. Once you click on it, a sidebar panel launches. Explore’s answer section and formatting suggestions readily make it a darling.
The Answers section allows you to ask Sheets custom questions and get streamlined answers regarding the context of the dataset. You can simply ask it to pull X data from the set you are working on, and it easily fetches the values for you. More than asking yours, the Answers section already suggests answers to the likeliest questions you could ask.
Make the Most of Heat Maps
Most Google forms are replete with information, and it can be hard to keep up. This is where heat maps prove to be highly beneficial, because they help you sort through data with a glance. Heat maps use visualization and colors to represent various scales of data. Your attention goes to the data of interest and whatever requires immediate attention, as indicated by the color that the cell is represented with. When combined with other conditional formatting tools, heat maps simply make your job much easier in a fast-paced, high-data environment.
Enable Sharing Access
These days, remote work is the new normal, and it often involves collaborative spreadsheet work. If you use Google Sheets with other people, my best tip will be simple – always enable sharing options. It may sound obvious, I know. Still, forgetting about this step can delay workflow, cause stress, and be a serious inconvenience for your whole team. Sharing access is especially important when you work remotely with flexible schedules. Make sure everyone can access the compatible version of the Google Sheets file. It can also prevent issues with syncing or saving one’s work.
Add a Drop-down List in Google Sheets
Drop-down list is the best and easiest way to enter and display data. You have likely seen a drop-down list in many forms online. You have to select the cells where you want to create a drop-down list. When you enter @, you will see the search menu, and there are the drop-downs. Now, click “Insert” and “Dropdown.” After this, you can add data to the list.
You can use predefined data in the drop-down list, which keeps the data away from errors like misspelled words. It has various uses to make your information lucid and readable. Instead of writing all the values manually, drop-downs can reduce your work and time to complete it. Google Sheets allow you to edit, organize, and analyze different types of data.
If any changes are made, you can track them and know who made the last change. Many small businesses are using Google Sheets for managing and displaying data effectively. They also use it for the creation of forms and applications.
Insert Images from URLs
Google Sheets provides an easy and reliable way to insert images from URLs directly into your workbook. This means you can quickly access and share high-resolution images from the web without needing to download or store them locally.
As a CEO, this is invaluable for productivity as it streamlines our workflow, improving efficiency by reducing time wasted on manual tasks. Furthermore, using URLs instead of storing images locally also reduces storage costs and demands as the images are hosted externally.
Google Sheets is a powerful tool that can help you save time and money by improving your workflow – inserting images from URLs is an especially useful feature to maximize efficiency.
Create Template Spreadsheets
Google Sheets doesn’t offer the one-click “Format as Table” that Excel does, so you should have a few template spreadsheets. These spreadsheets should be for specific use-cases in your profession, as well as offer simple table formats that you can copy and paste, then insert your raw data into. For example, as a professional SEO, I have template spreadsheets for on-page optimization, competitor keywords, keywords I’m considering targeting, and information architecture.
Submit Your Answer
Would you like to submit an alternate answer to the question, “What is your best tip for Google Sheets?”