Top Tips and Tricks to Try with Google Sheets in 2020

Let’s face it, unless you’re the kind of person who deals with data and numbers every day, there’s a solid chance that you regard spreadsheets with a sense of confusion and mystery. You know that these tools are helpful, and you might have used them in the past to track your income, maintain information, or just compare statistics. However, it’s likely that you’ve only just scratched the surface of what’s possible – particularly if you’re using Google Sheets.

Google Sheets is one of the leading tools in the G-Suite portfolio, offering easy access to spreadsheets and number management. You can use Google Sheets to create custom graphs, charts, and other useful visual information from scratch. What’s more, the built-in formulas, conditional formatting and pivot tables included with Google Sheets means that anyone can simplify common tasks.

If you still haven’t discovered the true benefits of Google Sheets for yourself in 2020, now could be the perfect time to make a change. We’ve put together a list of some of the best Google Sheets tips and tricks to try this year.

1. Save Time with Shortcuts
2. Create Unique Sheet Shortcuts
3. Grab Data Quickly
4. Pull Data from Social Media
5. Make it Easy to Collaborate
6. Simplify Data Sharing
7. Learn How to Format Like a Pro
8. Get Answers Quickly with Pivot Tables

We’re all in a hurry these days. It seems like we’re constantly dealing with things like impending deadlines and overwhelming tasks. It’s no wonder that many of us search for ways to save time. Fortunately, you might be able to save some crucial moments when you’re creating spreadsheets on Google. Instead of clicking through commands on the Google Sheets site, just type sheet.new into the browser address bar on Google Chrome.

As long as you’re signed in to your Google account, there are plenty of keyboard shortcuts that you can access through Sheets. For instance, you can insert the current time or date wherever you want. For instance, hit Ctrl and the semicolon key to add the date, or Ctrl and Shift to add the time.

You can also fast-format your solutions with things like:

• Ctrl-Shift-6: Format as exponent
• Ctrl-Shift-5: Format as percentage
• Ctrl-Shift-4: Format as currency
• Ctrl-Shift-3: Format as date
• Ctrl-Shift-2: Format as time
• Ctrl-Shift-1: Format as decimal

Once you’re done getting used to the Google Sheets shortcuts that already exist – why not make some of your own? Open the Tools menu in Google Sheets and click on the “Macros” option. Basically, this will give you the tool that you need to automate complex series of actions with just one command. Click on “Record Macro”, and you’ll be ready to go.

If you want to make sure that the shortcut is always performed on a specific set of cells, you can use absolute references. Altenratively, you can stick with using relative reference. When you’re recording, perform whatever actions you want to add to your macros.

You might decide to set a specific sort of formatting for the contents in a cell. Or you might want to manipulate the data in your spreadsheet by copying a cell content, deleting the cell, and pasting the contents into a different cell. Once you’re done building your automated action, click on the Save button in the macro recording panel and give your shortcut a name. Remember that you’ll also need to assign your shortcut to the right key combination for activation in the future too.

When you start learning how to really make the most out of your Google Sheets experience, you might discover a moment when you need to show live data from one spreadsheet in another. To do this, copy the full URL of your spreadsheet, including the data, and paste it into your Sheets IMPORTANGE function. You can use the following format to access this function:

=IMPORTRANGE (“your url,” “sheetnumber!cellrange”)

Simply paste the function into the sheet where you need your new data to appear. You will have to hover over the cell and click on the right button of your mouse to allow the two sheets to be connected. Your data from your other sheet will appear on your document, and it should automatically sync whenever changes are made too.

If that wasn’t exciting enough, did you know that you can pull data from any public web page too? All you need to do is have the correctly formatted table to get started. Using the IMPORTHTML command will allow you to import the right table. For instance, you might use this command to import the 2nd table from a Wikipedia list:

=IMPORTHTML(“https://en.wikipedia.org/wiki/List_of_sandwiches”,”table”,2)

In a matter of seconds, you can get all of the info you need from elsewhere on the web into an easy-to-share Google Sheet. Once you’ve got all the data that you need in one place, you can share that content with other people.

If you can pull your data from any public webpage, it makes sense that you’d be able to pull the details for an interesting spreadsheet from your social media channels too. If you’re looking to use Google Sheets to track and analyze the efforts of your social media posts, then you can do that very easily. All you need to do is access the right marketplace application.

There are tools available as part of the G Suite app marketplace, like Automate.io, which allow businesses to connect their Google Sheets environment to their social profiles. You can sync these environments to import and export data from LinkedIn, Facebook, Twitter, and more.

For social media marketers, this can be an exceptionally helpful tool, as it will make it easy to track the success of different campaigns and even pinpoint your most valuable followers.

One of the most beneficial things about using Google Sheets, and the rest of G-Suite for that matter, is that it’s so easy to collaborate with your team members over the cloud. Once you’ve shared your Google spreadsheet, either with specific people, or publicly, you’ll be able to ask Google to create a custom link for you that allows anyone with access to quickly copy your spreadsheet into their preferred Google account.

Allowing people to copy a spreadsheet means that they can use it as a template for additional reports or graphs. Alternatively, you can give your spreadsheet to people who need help recording similar information. The person who edits the spreadsheet template won’t affect the original version of your spreadsheet. All you need to do is add “copy” to the end of your spreadsheet URL. The result will look something like this:

https://docs.google.com/spreadsheets/d/document/copy

When someone opens your link, they’ll be asked to make a copy with a single click so that they don’t harm the original content.

Alternatively, if you want your team members to be able to annotate your document, and make changes in some places, but you want to protect other cells, then you can “lock” aspects of your Google Sheets. All you need to do for this is to highlight a cell or range of cells that you want to make sure remain the same. Click on the data from the top of your menu then click on “Protected Sheets and Ranges.” Google will present a sidebar that you can use to identify the protected range and set the permissions for the range. You can even arrange to show a warning from Google when someone tries to edit important content on your spreadsheet.

If it’s absolutely essential that you don’t lose the data in a specific area of your spreadsheet, you can also restrict access to only you.

Because the G Suite is designed to make collaboration between team members as simple as possible, it comes with a number of fantastic ways to share data. For instance, you can export one of the spreadsheets that you’ve created in a range of formats. All you need to do is click on the File Menu and scroll down to the “Download as” option.

Alternatively, if you would prefer to give your employees, contractors, or even clients a direct link that they can use to download your data as a PDF file, you can use aw hidden command to do that. All you need to do is copy your spreadsheet’s URL and add the export?format=pdf link at the end, instead of the word “edit”.

Another option for anyone who wants to get fancy with the way that they share information is to embed charts and graphics onto your website directly from Google Sheets. You can publish your content directly to your website by clicking on the “More” option on the top right-hand side of your chart. Once you’re there, click on “Publish Chart” them “Embed” and paste the HTML code that Google generates on your behalf.

The more time you spend playing with Google Sheets, the more you’ll discover that you can do with this unique solution for spreadsheets and documents. For instance, did you know that you can hide rows on Google sheets? Simply click on the number of the column that you want to hide in the grey section and click on the “Hide row” option from the menu that appears.

If you decide that you want to hide the row, you can click on the black arrows that replace the row art the left-hand side of the screen. Other formatting options you can use include:

• Alternating colors: If you’re creating a presentation or an important document that really needs to pop, you can choose the alternating colors option in the Format menu of Sheets. This gives you a unique way to add color patterns to your rows with virtually no effort.
• Rotate the text: When you’re boosting the overall look of your spreadsheet, you can also rotate the text in your header row. You can pick from various eye-catching effects by clicking on the icon with the angled A and an upwards pointing arrow.
• Group your cells: If you want to group your cells in a specific order, you can use Sheets “Array” function. With the formula =ArrayFormula(range or mathematical expression). There’s a complete guide available on how to use the ArrayFormula solution here.

Finally, a pivot table is an excellent tool on Google Sheets, but it’s something that many average users simply don’t know how to use. With a pivot table, you can summarize large sets of data, and use the information to make informed decisions. When you create a pivot table, you take your primary two-dimensional data, and use aggregation of data to come up with a third-dimension answer.

For instance, you can use your pivot tables to find out which sales representative had the most success in a specific store, or how many of a certain product you sold in each store you run throughout the first quarter of 2020. To make a pivot table, just go into Google Sheets and highlight all the cells that contain relevant data. Click on the Data option in the top menu and select Pivot table.

The suggested table provided by Google may automatically answer your question. If it doesn’t then you will need to create a custom solution by clicking on Rows and Columns and selecting the “Add” option next to it. Click on the values that you want to display within the rows and columns and use Google Sheets filters to display the values most appropriate for your needs.

Making the Most of Google Sheets

Google Sheets is more than just a simple spreadsheet environment that you can use to keep track of quantitative information and numeric data. If you learn how to use Sheets properly, you can use it as your go-to solution for making informed and educated decisions in your company.

Which of our top Google Sheets tips and tricks will you be using as we head into 2020? Do you have any other ideas to share? Let us know in the comments.