As a financial advisor, Google Sheets is one of your most valuable productivity tools. With Google Sheets, you can do practically anything. You can complete complex analyses. You can deliver beautiful reports to your clients.
But because Google Sheets is so robust, there are also a lot of hidden features. Let’s take a look at some advanced tips for using Google Sheets.
When in doubt, use Google’s version history.
If you’re worried that you may have overwritten something that you needed, you can look at Google’s version history. Version history stores every version of your file, so you don’t need to. You can revert to a previous version, see which changes were made, or download past versions so you can continue to edit them.
Don’t forget about data lookup functions.
There are a lot of surprisingly intricate functions provided by Google Sheets. Particularly, HLOOKUP and VLOOKUP. By using vlookup from another sheet, you can quickly pull in the data that you need without having to manually hunt for it. This is particularly useful if you’re trying to manage a lot of data.
Get familiar with macros.
Macros make it possible for you to automate a lot of tasks, such as pulling in data from a given source. With macros, you can optimize your efficiency — and custom-code your operations. Press a single button and you can open three worksheets at once. Press another button and you can save everything and close it.
Don’t bother writing algorithms yourself.
Most financial services algorithms are already built into Google Sheets. If you need to calculate interest or ROI, the functions are already in there. If you ever find yourself drawing up a complicated algorithm, it’s very likely that the algorithm is a function that’s already available. You’d be surprised at what’s built-in — you can even pull stock tickers directly from Google Sheets.
Share sheets intelligently.
When you need to share reports with clients, make sure you share them the right way. You can share a URL, but you can also make it “comment only,” “read-only,” or “edit only.” If you don’t want your clients messing around with your algorithms, it’s best to send a comment-only file. That will let them leave notes, but not edits.
Use checklists and status bars.
You can use Excel sheets to create checklists (with Insert -> Checkbox) and status bars (with the SPARKLINE) function. You can create checklists for reporting and tasks and use your status bars to track your progress. Both checklists and status bars are some of the best, easiest to use features in Google Sheets — but not everyone knows about them.
Memorize your shortcuts.
Shortcuts make it fast and easy to do things like save a file, skip to the bottom of a sheet, and otherwise complete routine or mundane tasks. You’ll find that you’re much faster at data entry and data management if you memorize shortcuts, much like you’re faster at typing if you learn how to touch type.
Freeze panes for better navigation.
When you freeze panels, it makes it easier for you to navigate your file. You can freeze panels to the top or to the side of each sheet, either to keep an index ready or to keep data that you need to compare.
Learn how to import data.
It’s easy to import data to Google Sheets through CSV files (comma-separated). CSV files can be imported and exported into most data-centric platforms. Being able to import and export data in Google Sheets can greatly improve the utility of your documents; you no longer need to cut-and-paste or complete manual data entry.
Use filters to pare down to the data you need.
Financial advisors need to sift through a lot of data. Learn to use filters to pare down to just the data you need. With filters, you can identify and sort the information that meets your criteria — so you don’t need to read all your data at once.
Enable notifications for your sheets.
If you’re working with clients or colleagues, enable notifications. You’ll be notified when anything is changed on your sheets or when comments are left. These notifications can be incredibly valuable because they tell you when other people are working on the sheets — and when your questions have been answered.
There are a lot of Google Sheets productivity hacks out there. If there’s something that’s taking you a long time, don’t be afraid to look it up. The odds are that there’s a shortcut out there.