8 Must-Learn Tips for Working in Google Sheets

Spreadsheets are probably one of the greatest inventions when it comes to calculations. They changed the way accountants and finance professionals work and thanks to them, we stepped away from pens and calculators. 

With spreadsheets, we could build customized tools that will solve our manual tasks, which opens up the space to increase our productivity. After all, as long as the formulas are correct, the results are predictable. 

Things changed when we got new tools like Google Sheets and later cloud-based accounting solutions like QuickBooks. Google Sheets is probably the best thing to happen to spreadsheets since Lotus 123 and Excel, and we think everyone should master it. Here are our top 10 tips to become a Google Sheets guru. 

‍

1. Protect Cells 

Spreadsheets are great – except when you have to share them. Deleting or altering one cell can corrupt an entire sheet, and when unskilled users have that opportunity, it’s bound to happen. The best way to prevent that is to protect cells that should not be altered once your spreadsheet is built. 

Select the cells you want to protect, and then set the Range Editing Permissions to “only you.” That way, no matter who you share the sheet with (and how) it will be safe from accidental edits. 

‍

2. Data Validation 

Data validation is a great way to ensure that information can only be inserted in an approved manner. This could mean that the cells can only be numerical, or you can use advanced options like lists to create drop-down menus for selecting information from a pre-defined range. You can use this along with functions like VLOOKUP (vertical lookup) to automatically populate adjacent cells too. 

Go to “Data” in the menu -> Choose “Data Validation” and select your Criteria such as Number, Text, Date and others.

3. Pivot Tables 

Let’s face it. Accountants work with a lot of numbers, and no one apart from finance professionals want to wade through all those digits to get to the crux of the matter. 

Pivot tables let you present a huge variety of data as a graphic that’s easy to understand. 

It’s a lot easier than you think too. 

Simply click on “Insert” in the menu -> “Pivot Table”.

Choose a “Data range” and under “Insert to”, choose where to add your pivot table.

4. Add Filters 

We’ve all had spreadsheets that have a ridiculous amount of data in them. You can use tools like “Find” to look for individual instances of a particular piece of information, but when you have many instances of that data, that’s not a big time saver. 

1. Highlight the cells that you want to add filters to (usually the top or title row, where you don’t have information to be filtered) and

2. Then go to “Data”. Click “Create a Filter” and choose the options you want.

3. You should now be able to click on the filter buttons on each of the header cells, and sort according to whatever criteria you want. 

‍

5. Freeze Rows and Columns 

Another valuable tool for accountants and finance professionals who have spreadsheets with a lot of data is the option to freeze rows and columns. 

1. Go to “View” on the top menu and select “Freeze”.

2. Then between Rows and Columns that you want to freeze.

3. If you choose both, everything below and to the right of those rows will remain in place when you scroll, and you can undo this by reversing the “View” tab. 

‍

6. Use Conditional Formatting 

Any accountant or finance professional will tell you that when you’re staring at numbers all day, they all look the same! Google Sheets has an easy solution to create a visual difference.

1. Simply highlight the data range and then click “Format” and “Conditional Formatting”.

2. Choose the parameters for the formatting (above or below a particular value and so on) and apply. The text and background can be altered this way, and it will apply to all cells in the selected range. 

‍

7. Master Macros

Google Sheets allows you to create macros. It doesn’t have some of the advanced coding opportunities that Excel offers, but you can still automate all kinds of tasks easily. 

What is Macros?

Macros automates your repetitive cell references. Google Sheets lets you record Macros that duplicate a specific series of interactions with cells that you define.

1. To create Macros, go to “Extensions”, then “Macros”, then click “Record Macro”.

2. Refer the cells, and when you’re done, stop recording.

3. You’ll then create a quick command key, or you can assign the macro to an object like a button. Every time you want to do the same thing, you can click once, and it’ll repeat all the steps. 

Once you've recorded a Macros, you can link it to a keyboard shortcut in the form Ctrl+Alt+Shift+Number. You can use that shortcut to quickly execute the exact macro steps again, typically in a different place or on different data.

8. Integrate with Other Tools

Google Sheets, like many Google Cloud products, allows you to use all kinds of add-ons and tools. There are also many online services that integrate with Google Sheets with just a few clicks. 

LiveFlow is one of those tools, and it can act as a very effective bridge between your various spreadsheets and accounting software, so you can do things faster and increase your productivity even more. This means you don’t have to do any of the hard work to export and import data. Just connect it and use it to push and pull the reports you need when you need to. 

One of the Best Tools for Accountants and Finance Professionals

Whether you choose to use LiveFlow and the Google Sheets Add-On or not, there’s no denying that Google Sheets itself is one of the most versatile and flexible tools for accountants and finance professionals. Since it’s completely cloud-based, you can also access it from anywhere, which makes it perfectly portable. 

With the ability to import data from accounting packages and other tools, all kinds of features for productivity and more, Google Sheets are kind of like a Swiss Army knife. So, if there’s one thing you start doing to improve your productivity this year, make sure you master Google Sheets. It’s the best all round spreadsheet platform out there.

‍

Still have questions? Get in touch at founders@liveflow.io, we’d love to hear from you!