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.
20 PRODUCTIVITY HACKS TO GET MORE DONE
Work smarter, not harder, with these time-saving productivity hacks from ClickUp.
This Article Contains:
- 1. Quick-and-dirty charts
- 2. Add everything up in a second
- 3. Split up messy data or lists into nice columns
- 4. Hide those ugly errors
- 5. Auto-size your data
- 6. Fill your columns automatically
- 7. Import data straight from webpages
- 8. Create buttons to hide and reveal certain things
- 9. Clean up capitalization and spacing
- 10. Automatically change a cell’s color based on the value
- Work Smarter and Faster with Productivity Hacks and Tools
Summarize this article for me please1. Quick-and-dirty charts
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.
QUICK GUIDE TO PROJECT DASHBOARDS
Learn how to make a Google Sheets dashboard to easily increase your company’s productivity.
Summarize this article for me please2. Add everything up in a second
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.
Summarize this article for me please3. Split up messy data or lists into nice columns
The =SPLIT function lets you split up a cell’s data on the character you put within the quotation marks.
Summarize this article for me please4. Hide those ugly errors
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.
Summarize this article for me please5. Auto-size your data
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.
Bonus: AI tools for Google Sheets!
Summarize this article for me please6. Fill your columns automatically
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.”
Summarize this article for me please7. Import data straight from webpages
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.
VISUALIZE PROGRESS WITH A GANTT CHART
Make a Gantt chart in Google Sheets to map your project’s progress in a simple, yet effective way.
Summarize this article for me please8. Create buttons to hide and reveal certain things
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!
Summarize this article for me please9. Clean up capitalization and spacing
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.
Summarize this article for me please10. Automatically change a cell’s color based on the value
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.
10 INCREDIBLE TEMPLATES FOR PROJECT MANAGEMENT
Fast forward your Google Sheets project management with these effortless templates.
Summarize this article for me pleaseWork 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!
Guest Writer:
Everything you need to stay organized and get work done.
