Microsoft Word and Excel were kind of a big deal in the business community when they first came out, and are still the standard. But it’s also probably fair to say that Google Docs and Sheets are also a big deal, because they maximize the benefits of online collaboration (although Microsoft came back with Excel Online), they aren’t tied to any device, and they’re free.
They also take on a whole other level of importance when you’re on the road, unable to access your desktop, but still able to perform a lot of these functions and/or at least have access using your Android tablet or smartphone. And if you’ve ever used Microsoft Word or Excel, you’ll most likely have had minimal trouble adapting to how Google Docs and Sheets works, at least for the most part.
But in addition to the maybe obvious advantages of those Google apps, there might be a few that you didn’t know about as well. For instance, Sheets can import data based on other Google functions, like using the formula GOOGLEFINANCE() to retrieve past and present security information from Google Finance.
And in today’s business environment, it’s more important than ever that you keep your data secure (especially since Google Drive is a single account with access to both of these programs, plus Slides to create slides for presentations). Are you doing everything you can to safeguard this information?
Overview of Software Functions
If you have ever looked at accounting or statistical compilations of data, they can easily seem far too overwhelming to handle manually (although people amazingly used to do this); but luckily computers, including your Android mini-computer, have a knack for this.
The processing power of Androids has grown exponentially, although some Google Sheets functions still just aren’t as practical to implement on your smartphone as they would be on a desktop or laptop.
As we touched on earlier, Google Docs is a word processing program that excels at cross-device and application integration and the ability to accommodate collaboration between multiple users.
Both programs have been part of Google Drive since 2012, which also includes the Google Slides app, in addition to being a platform for data storage space.
Overview of Special Features
Both programs permit you to manage and share files/folders somewhat extensively. If you are working on a team, this means that you can collaborate on documents in real time and grants users appropriate privileges, as well as track changes.
You can use Google Docs to perform tasks such as writing reports, creating forms, and generating joint proposals. In Google Sheets, you can download add-ons or even code your own; in addition to the usual spreadsheet features of performing calculations, sorting, analyzing, and storing data.
Overview of Spreadsheet Terms
You have probably already heard of some of these terms already, but a quick reminder of spreadsheet terminology never hurts. A cell is a single rectangular field where you can input a piece of data, identified by the junction of its row and column.
A row is a horizontal group of cells, identified by a number. A column is a vertical group of cells, identified by a letter.
A range is a set of adjacent cells and can be vertical or horizontal.
A formula is an equation that can be used to calculate simple or complex functions involving multiple cells, rows, columns, and ranges.
These functions are predefined and built into the software, but to call on them you must precede the name with an equal sign.
Formulas can delve into statistical and mathematical applications at levels that require a class to fully understand. For the sake of brevity, we will only touch on their meanings as necessary to demonstrate what you can do with the software.
Warning: Not all advanced features are available within the Android apps themselves, but the Android apps will at least sync with any advanced changes made from your desktop or laptop. On the other hand, the Google Drive app on Android can do some things that most desktops can’t do, like scan documents using optical character recognition, and then convert those documents to PDF format.
Most likely you already know how to create a new spreadsheet in Google Sheets. From Google Drive, you can select NEW > Google Sheets; or you can select a sheet type from the Google Sheets homepage; or from within a spreadsheet, choose File > New.
To import a spreadsheet, go to File > Import > UPLOAD. You will also find various options to import online data by going to Insert > Function > More…, where you will find info on how to import data from URLs, RSS Feeds, HTML tables, specific ranges of cells, etc.
You can also recruit the help of Google Sheets’ Add-Ons to add analytics, email scheduling, reports, graphs, displays, online assignment data for teachers, and more to your sheet with just a few clicks. And if you can’t find the Add-On you need (out of the 250 available) to gather your data, you can build your own using Google Apps Script.
Creating a new document in Google Docs is pretty much identical to how you do it in Google Sheets, although the templates are bound to differ, with options like Resumes and Brochures.
You also have options to insert equations, charts, and tables here; and recall that one of the options for upload from the Import feature was to import from other Google Drive sources, so you can easily swap data. You can also get Add-Ons specific to word processing, or in some instances, you’ll notice some overlap Add-Ons like g(Math) are applicable to both Sheets and Docs.
You’ll also find the same option to script your own Add-Ons under Tools > Script editor, and you can still use it to add dialogs, custom menus, and sidebars as well as convert Android apps to an Android Add-Ons so that it can swap data with Docs or Sheets on your Android. If you get good at it, you can use it to publish your own web apps.
To transport your old documents over to Google Docs, go to New > File Upload > choose your document, right-click, and select Open with Google Docs. You don’t necessarily have to convert Microsoft Word documents to Drive in order to edit them; you can also use a Chrome extension or save changes from Word to Drive using a plug-in.
As with Sheets, files created in Google Docs can be published directly to the Internet by going to File > Publish to the Web… Although Google Forms used to be part of Google Docs, you now have to use the Create option from Google Drive to access it, or you can insert it into Sheets directly.
2. Edit and Format
Let’s discuss how you could hypothetically put some of those formulas to use. Data often isn’t static, and walls of words and numbers just aren’t as user-friendly as say, a chart sometimes.
It’s possible to pull information from a source like a Google Form or spreadsheet using ARRAYFORMULA, add the Match function to search for the position of a consistent string of data if other parameters change, and then use the Offset function to format or place it in the correct position.
Although carrying out that process might not be intuitive, it goes to show that formulas and functions can build on and be used in conjunction with each other to account for variations in data and to create less future manual work for you.
When it comes to manual formatting, there are of course the usual font, number, style, and alignment settings that apply and are located above the first row of cells; but the more interesting ones fall to the right. The Insert Comment option allows you to share your comments with others who have access to the sheet. The Insert Link icon will give you the opportunity to put a hyperlink inside of a cell. Then there’s charting, filtering, and access to your list of functions, from left to right.
To quickly edit your charts, you can access Quick Edit Mode by first selecting the chart and then clicking on the pencil icon or for more options, click on the drop-down in the upper-right corner and select Advanced Edit.
Perhaps the best way to insert an image into both Docs and Sheets is via URL. In sheets, you can use the IMAGE function, and in Docs click Insert > Image > By URL. In Docs, the Insert menu will also allow you to add links, drawings, tables, bookmarks, and tables of contents.
The Explore option under Tools allows you to search not only your documents, but the Web, for related topics, images, and research. Save time by formatting your text using Paragraph Styles. Under the Format menu, you can customize a string of text in a way you would like to use again and again, select it, and navigate to the paragraph option of your choice (i.e. Title, Headings, etc.). Click on the arrow to the right, choose Update Style Name to Match, and when you are done go to Options > Save as my Default Styles.
3. Share and Collaborate
Both Docs and Sheets offer the ability to protect and sync data. It’s also possible to keep files offline for editing and to sync later. Most syncing takes place automatically. It’s simple to invite others to join via email or with a link.
It is important to understand that you will only be able to use all the collaboration features for files that were actually created in Google Drive. You can go to File > Share and let anyone view any document you like, however, and there are Advanced sharing options available if you click the link in the lower-right corner of pop-up that appears.
The nice thing about it is that the people you share with will always have the most recent version. If others share files with you, you can find them in your Shared with Me folder.
Sharing and collaboration are straightforward in both apps. But let’s talk security. It’s best to enforce 2-step verification to protect your data. Go to the Google 2-Step Verification page, where you most likely will have to sign in. Click on Get Started and it will guide you through the process in which you can review your settings and add a phone number where you want your verification code to go to, or you can also opt to use a security key instead.
There are a lot of Add-Ons available, but what are some examples that can fill in gaps? Change Case will change the case of a selected block of text. Send faxes directly from Google Docs with HelloFax. HelloSign allows you to send or request signatures electronically.
There are tons more, but we can’t go through all of them. There are so many features and functions to Docs and Sheets it’s difficult to go into much detail. But hopefully, we’ve whet your appetite for what is possible. And perhaps you will create the next must-have Add-On or brilliant combination of formulas and functions.
What advanced tips and tricks do you have to offer for Google Sheets and Docs? Send us your thoughts.