Spreadsheet Project Management

10 Google Sheets Hack That Will Make Your Job Easier

Google’s free-to-use software is ubiquitous in today’s corporate world, whether you’re employed at a large or small firm. So why aren’t you a pro at it yet?

In 2020, Google shared that its suite of productivity tools, including Gmail, Google Docs, and Google Sheets—Microsoft’s Excel software competitor—had surpassed 2 billion monthly users. Like everything else in Google’s software suite, Google Sheets syncs automatically with multiple users so you and others can collaborate seamlessly. It is perhaps the most accessible and powerful piece of software available today for workers wanting to wrap their hands around large sets of data.

Whether for work or simply for budgeting in your personal life, developing a stronger grasp of the shortcuts available in Google Sheets is an easy way to save time better spent on something besides hunching over a keyboard.

ClickUp scoured the most-followed social media influencers sharing spreadsheet tips and tricks, including CheatSheets and Your Excel Dictionary, to compile this list of ten Google Sheets hacks you should be putting in rotation.

Start by bookmarking this artificial-intelligence-enabled tool called Excel Formulator, which lets users dictate what they want a Google Sheets function to do and spits out the function—and keep reading for more helpful tips.

Work smarter, not harder, with these time-saving productivity hacks from ClickUp. 

1. Quick-and-dirty charts

Creating charts in Google  Sheets
Creating charts in Google Sheets

Heading into a meeting and need to grab a quick trend line so you know what you’re talking about? In Excel, you would use Alt + F1, or Option + F1 on a Mac, after selecting a data table to generate a quick chart of the data you have selected. In Google Sheets, you’ll need to use your mouse not just to select your table but also to select “Chart” from the “Insert” drop-down menu.

Learn how to make a Google Sheets dashboard to easily increase your company’s productivity.

2. Add everything up in a second

Automatically add numbers in Google Sheets with the sum formula
Automatically add numbers in Google Sheets with the sum formula

In Excel, selecting your data with Ctrl + A and then using Alt + = will run the Sum function for an entire data table if the table is formatted in a standard way. Google’s spreadsheet processing software attempts to make this easier by sensing when to automatically fill in a function.

You should only need to enter a function once before getting the autofill prompt. This is helpful for the commonly used =SUM function, which will add up all of the values in the preceding rows for you.

Google Sheets functions a little differently, and you may have an easier time prompting it to autofill functions after you create a filter view. Simply click the filter button on the far right of your toolbar and select “Create new filter view” to enable this.

3. Split up messy data or lists into nice columns

Split up a cell's data with the Split function in Google Sheets
Split up a cell’s data with the Split function in Google Sheets

The =SPLIT function lets you split up a cell’s data on the character you put within the quotation marks.

4. Hide those ugly errors

Hiding errors in Google Sheets
Hiding errors in Google Sheets

The =IFERROR function allows you to tell the spreadsheets to recognize any errors and display whatever values you place between the quotation marks—including blank space if you prefer. This requires that you write all of your formulas nested within the =IFERROR( expression’s parentheses.

5. Auto-size your data

Auto-sizing data in Google Sheets
Auto-sizing data in Google Sheets

Are your columns and rows making it hard to see the full value in each cell? There are two ways Google Sheets gives us to fix this. First, you can right-click a column or row and select “Resize column” or “Resize row,” then choose “Fit to data” and click “OK.”

A quicker shortcut is to double-click the line separating the row or column, and it will snap into the perfect size for the values it contains.

6. Fill your columns automatically

Filling in columns automatically in Google Sheets
Filling in columns automatically in Google Sheets

The next time you need to enter a series of dates or copy a formula, enter the first two, then stop. Use the Ctrl + D key combination, or Command + D if you’re using a Mac, to fill a column down. Google Sheets will guess and fill in the rest of the dates or other figures based on the patterns it recognizes in your row or column. You can also use Ctrl + R or Command + R to fill a column to the right. This is also called “flash fill.”

7. Import data straight from webpages

Importing data straight from webpages in Google Sheets
Importing data straight from webpages in Google Sheets

Quit copying and pasting poorly-formatted tables from webpages.

You can enter the =IMPORTHTML function in Google Sheets to import a table or list from a webpage you’ve copied the URL from. Include “table/list”, “1” in the function to grab the first table. It’s important to note that not every data source will import nicely. If your import doesn’t work the first time, make sure there are quotation marks around each component, including the URL.

Make a Gantt chart in Google Sheets to map your project’s progress in a simple, yet effective way.

8. Create buttons to hide and reveal certain things

Creating buttons to hide and reveal identifiers in Google Sheets
Creating buttons to hide and reveal identifiers in Google Sheets

You can utilize the Slicer function to look at “slices” of your data set. Highlight the data and choose “Add a slicer” from the “Data” drop-down menu. From there, you can choose which identifiers you want to show through when you turn the slicer on and off with a click.

Learn how to make an org chart in Google Sheets!

9. Clean up capitalization and spacing

Cleaning up capitalizations and spacing in Google Sheets
Cleaning up capitalizations and spacing in Google Sheets

Try using =PROPER(insert cell containing values here) to capitalize poorly formatted text in a flash. You can also nest the trim function in here as =TRIM(PROPER(insert cell containing values here) to remove pesky unwanted spaces.

10. Automatically change a cell’s color based on the value

Changing a cell's color based on the value in Google Sheets
Changing a cell’s color based on the value in Google Sheets

Use conditional formatting to create rules for a column or row of data. You can have cells in that range turn colors based on a word, character, or positive or negative value. One of the most obvious applications for this includes coding cells to turn green or red for positive and negative change across data sets.

Fast forward your Google Sheets project management with these effortless templates.

Work Smarter and Faster with Productivity Hacks and Tools

Utilizing productivity tools such as shortcuts and ClickUp can transform your entire working experience, allowing you to achieve far more than ever before.

By integrating these hacks and tools, teams can streamline their workflows, eliminate time-wasting activities, and boost their productivity. Use these Google Sheets hacks to speed up and automate manual processes, and ClickUp to keep your tasks and projects organized all in one place. Moreover, teams can use the Embed view in ClickUp to embed Google Sheets in ClickUp tasks, creating better project visibility, improving accountability, and resulting in better collaboration and communication.

Cut out the tedious parts and open up more space and energy for the more creative bits of your projects. Try ClickUp for free today!

ClickUp Blog Simple CTA

Guest Writer:

Questions? Comments? Visit our Help Center for support.

Sign up for FREE and start using ClickUp in seconds!
Please enter valid email address