5 Google Sheets Tips And Tricks To Increase Your Productivity
While there are a ton of basic things you can learn to boost your productivity in Google Sheets, sorting through all the different features can be difficult. While things like freezing rows, querying multiple sheets, inserting basic functions, and using shortcuts are useful, intermediate and expert Sheets users can further save time with some savvy tips and tricks you may not have known about.
Because there is just so much potential within the free tool, it can be easy to miss certain tricks, especially because Google is updating the application regularly and new functions are being added all the time.
If you feel like you're wasting time doing redundant and repetitive tasks, and wish there were a way to streamline your time when using Google Sheets, you've come to the right place. Here is a list of Google Sheets tips and tricks you can use on a daily basis to boost your productivity and efficiency when using the spreadsheet program.
Use Google Sheets Intelligent Apps
Google Sheets has a wide array of different add-ons that you can add to Sheets via the Extensions dropdown menu. Many have specific uses, like mail merge apps, calendar apps, and email platform integrations. While these are useful on their own, there are also extensions inside the Intelligent Apps section of the Google Workspace Marketplace that are integrated with generative language models like OpenAI.
These AI add-ons can go a long way in streamlining data, as add-ons like GPT for Sheets or the Google-sponsored plugin using Duet AI hook directly into AI models. When looking for add-ons, keep an eye out for the small green shield with a check mark, which means that the app passed an independent security assessment.
These Intelligent Apps are especially useful if you don't have a lot of experience with advanced functions, as Google Sheets Chat GPT integration and other AI models can do things like clean up columns of data, pull important data from other columns, summarize text, or do other basic, redundant tasks that a more complex function can't accomplish at times. A great example of this is summarizing longer paragraphs of text into a few words, something mathematical formulas are unable to accomplish.
To utilize the GPT for Sheets, you must create an OpenAI account and retrieve your personal API key, which can be found within your account settings.
Master useful Google Sheets functions
The =CONCATENATE function is a popular function that combines data from different cells into a single cell and is one of the more useful functions, especially when dealing with large data sets that need to be merged into a single row. It's especially useful because =VLOOKUP functions can also be nested in it. Other useful functions you should get yourself acclimated to using include:
- =sequence is an easy way to create numbered lists quickly.
- @Dropdown is an easy function you can type to bring up different dropdown options.
- =QUERY can pull data from multiple tabs or sheets.
- =IMPORTRANGE can pull data from entirely different Google Sheet files.
- =SPLIT can split data in a single cell into two separate columns.
- =ISEMAIL can detect whether data is formatted correctly for email addresses.
- =TRIM can remove any unnecessary spaces in your data.
- Using a $ sign to lock a cell, row, or column. It will be formatted something like =$B$21/3. This is useful when you need to utilize specific data from a cell(s) in a formula.
It's also important to note that most functions can be combined with each other for even greater control and specificity. There are a ton of other useful functions out there, with the full list found on the Google website. If you ever have issues figuring out what a function does or how to format it, use the helpful popup that Google provides after typing in the name of your function.
Keep your spreadsheets clean and clutter-free
Keeping your data organized is often a good idea, but not just for aesthetic purposes. Certain features within Sheets can go a long way in boosting productivity by having fewer hanging data or random tables placed in your sheets. These data entries reduce data cohesion and increase the potential for unintended errors.
One way to avoid this unorganized data is by using Sheet's built-in Group function rather than hiding rows or offsetting other sub-data into separate tables, as groups can be condensed or expanded with a button click. Groups can be made of both columns and rows and can also be broken down into further subgroups, making the spreadsheet a lot easier to navigate for anyone viewing it and helpful when displaying data in a presentation.
To organize your spreadsheet, you can use the Borders function next to the paint bucket tool to artificially hide grid lines for specific tables you want to feature. You can also color-code data you want to feature using Format>Conditional Formatting and clicking the Color Scale tab. This will feature a range of data on a scale, giving a visual representation of high, medium, and low.
Master useful Google Sheets keyboard shortcuts and macros
While it's the most obvious answer to increasing your productivity within Google Sheets, it's also the most legitimate. Mastering the most useful Google Sheets shortcuts will save you the most time in the long run, but not all shortcuts are actually useful. Here are a few useful, non-obvious shortcuts to know about in Google Sheets:
- Ctrl + Y uses the last function created.
- Ctrl + the arrow keys moves to the next blank cell, which is helpful in finding blank rows or columns.
- Shift + F11 inserts a new sheet.
- Ctrl + Alt (or cmd) + Shift + semi-colon inserts the date and time while Ctrl + semi-colon just inserts the date.
- Ctrl + backslash clears cell formatting.
- Ctrl + forward slash brings up the full list of Google Sheets shortcuts.
Google Sheets macros are also a useful feature you can use if you find yourself having to do the same keystrokes over and over and can't accomplish what you need with a formula. To use macros, click Extensions>Macros>Record Macro and record the keystrokes you want to use, then set a shortcut for the macro. When you want to use the keystrokes, simply insert the shortcut you created.
Keep up with changes to Google Sheets using Google Workspace Updates
One of the most realistic ways to boost your productivity, especially if you're an expert Google Sheets user and already know a lot of the tricks on this list, is to make sure you're keeping up with the latest changes and improvements that Google makes to the app. These updates and notes can be accessed on the Google Workspace Updates page, where you'll find new tools that have just been rolled out for Sheets.
The last update, which introduced Enhanced Smart Fill for the Google Workspace Enterprise add-on using Duet AI, recognizes data patterns and can provide suggestions for things like formatting addresses, structuring numbers and emails, and generating text, much like the OpenAI add-on. This is also the place where you'll be able to view new functions and formulas as Google adds them to the app.
Using these tips and tricks together instead of having a more rudimentary spreadsheet will save time exponentially in the long run.