When Google Sheets was first released, many felt it was lacking the deep functionality and analytical capabilities available in Excel. In fact, some businesses that moved to Google Apps would still use Excel for their spreadsheet work.
Over the years, Sheets has done a lot of catching up and is now a valid alternative to using Excel, with much of the same functionality and more intuitive security and collaboration features. Mastering these skills and feeling comfortable navigating and manipulating sheets can drastically improve the overall efficiency of your business and your workday.
1. Use Quick Sum to quickly add up cells without a formula
This is an excerpt from Quick Sum in Google Spreadsheets.
If you’ve spent any time in Google Spreadsheets, you know formulas are your best bet for calculating data. If you want to get quick insight on your Spreadsheet without using formulas then you should definitely check out Quick Sum.
This video outlines some valuable use cases for Quick Sum, specifically Form responses. There’s also some slightly hidden features available with Quick Sum that you might not be aware of!
2. Protect Sheets and Cell Ranges to ensure security in your shared spreadsheets
This is an excerpt from Protect Sheets and Cell Ranges.
When sharing a spreadsheet you often have to worry about which cells will be modified by viewers. If you only want them to be able to edit select cells in the spreadsheet then you should try protecting the sheet or the range of cells you want to remain untouched.
With this much control you will never have to worry again about which cells are going to be changed on a shared spreadsheet.
3. Link to external content by creating a hyperlink in a spreadsheet
This is an excerpt from Create a Hyperlink in Google Sheets.
Linking to external sites in a spreadsheet is a great way to take your reporting to the next level. You can go beyond just showing the data you have compiled but also provide data sources. In Google Sheets, you can hyperlink your cells in four easy ways.
If making hyperlinking easy wasn’t enough, Google Sheets also handles email addresses very well. If you hyperlink text and use an email address as the link, Google will automatically create a mailto link, which will open up a new compose window with your contact’s address pre-filled when clicked.
4. Use the VLookup function to easily analyze separate lists of data
This is an excerpt from How to do a VLookup in Google Sheets.
Tricky things such as associating student IDs with student names and other information about that student can be a thing of the past. The VLookup function in Google Sheets is used to find whether a value exists in multiple ranges. This tool has limitless uses for sorting through and making sense of large lists of data.
5. Pull in data from a live webpage using the ImportHTML function
This is an excerpt from ImportHTML and Filter Views in Google Sheets.
We’ve covered the importHTML function before, and it really is one of our favorite functions in Google Sheets. There is so much you can do with the function, but once you pair it with the new Filter Views you can accomplish even more.
The importHTML function allows you pull in live data from websites into a Sheet so you always have an updated set of data. If the function is pulling in more data than you need, you can always use Filter Views to streamline the process.
6. Track the market by pulling live financial data directly into a spreadsheet
This is an excerpt from Pull Stock Market Data into Google Sheets.
Researching specific stocks and pulling accurate up to date information can be tedious. Google Sheets cuts out a lot of the busy work and allows you to get the data you need in one convenient location.
With the Google Finance function, you can use Google Sheets to easily keep up with several different financial metrics. This video will show you how to pull real time and historical data directly into a Google Sheet.