The Definitive Guide to Google Sheets

By Abhilash Menon
Definitive-guide-to-Google-Sheets-feature-image

Imagine the world without data, take a minute to grasp that idea.

Data has played an important role in the evolution of homo sapiens from the stone age into the modern world, the need to organize such large chunks of data gave way for the invention of spreadsheets. The use of spreadsheets has evolved with time, from managing inventory of construction materials in ancient Egypt to stacking complex data streams for bringing in the next wave of advanced artificial intelligence.

The hunger to do more with our spreadsheets and to do it in a better way led to the era of digitization of spreadsheets and thus, Google Sheets was born.

With dynamic sharing abilities, Google Sheets has been a primary player in easing the everyday labor of creating and analyzing peripheral data, at the same time, has created an enormous space for breaking down more elaborate data. The fact that everyone has the same data at the same given time has enabled productivity to be an inclusive entity and not an exclusive one.

You are only as good as your data

The better your data, the better are your decisions, but to do so, you need to know your Google Sheets in and out. Being a Spreadsheet pro helps you get the best out of your data, whether it’s an accountant who is maintaining a balance sheet or a manager who needs to come up with a strategy or a family, trying to figure out their monthly home budget.

This guide has been carefully designed to give you an intricate understanding of how Google Sheets can help you achieve your goals. The guide will cover the following topics:

1. Glossary

2. Elementary - Google Sheets

3. Templates for Google Sheets

4. Collaborate - edit, comment, and share

5. Data portability

6. Shortcut keys - Google Sheets

7. Formulas - Google Sheets

8. Charts 

9. Add-ons - Google Sheets

10. Working Offline

11. Spreadsheets on-the-go

12. Create a mobile app with Google Sheets

13. Collect data for survey

14. Manage Stock Portfolio using Google Finance function

15. Data Security

 Glossary

-A-

  1. Active cell - The cell that you select for entering data. If you wish to change the cell, then using your mouse or a keyboard click on another cell.
  2. Anchor cell -  The first cell which is highlighted in a range. This cell remains highlighted in white as opposed to other cells which are highlighted in blue.

-B-

  1. Bar - A type of chart that outlines and compares categorical data. Each bar’s length represents the aggregate value of that specific category.

-C-

  1. Cell - It is the rectangular area formed by the crisscrossing of rows and columns. Cell name is used to identify a cell.
  2. Cell Reference - It is a name which is used to identify the cell. For eg- the cell in column 'A' in Row "3" would be cell A3
  3. Column - They are the lines on the spreadsheet that run vertically.

-F-

  1. Filter - Establish criteria for deciding how you want the data to be displayed on the screen. Once filters are put in place, the data that don't meet your criteria will temporarily disappear from your sheet. The data can be viewed back again when the filters are deactivated.
  2. Formula - A data function that calculates results and displays it on the active cell. The formula is written using cell references and begins with a ‘=’ sign. For eg: =D8+C9. It will take the value from D8 and C9 and add them.
  3. Function - They are used to enter complex formulas. Functions begin with a ‘=’ sign and use cell references. For eg: =sum(A9:A99) would add all values from cells A9 through A99.

-R-

  1. Range - A group of cells selected. If a group of cells is selected together, they are called as adjacent range and if there are gaps between selected cells, they are called as non-adjacent cell. An adjacent range has cell references which are separated by a colon and non-adjacent range cell references are separated by a comma. Examples of adjacent and nonadjacent respectively - E5:F5 and A9, G7, P4.

-V-

1. Value It is the numeric data entered in the cell. It can be referred to formulas and functions and is used in calculations.

Elementary - Google Sheets

For obtaining pro status in the world of Google Sheets, you have to go through the basics first, the better your foundation, the better the structure. These features might look generic in nature but they are the backbone of your spreadsheet.

1. Create spreadsheet

Step 1 - If you are on your Sheets homepage, then click +.

Step 2 - It will ask you to create a spreadsheet using the blank spreadsheet or choose from the template gallery.

Google Sheets create spreadsheet

And if you are using Google Drive,

Step 1 - Click on New and select Google Sheets.

Step 2 - Choose Blank spreadsheet or From a template to create a new sheet.

Google Sheets create spreadsheet 2

2. Enter, save data and rename

Once you have created a spreadsheet, you can start entering data. Google Sheets has an Auto-save feature which saves time from manually saving your work.

Step 1-  By default, your spreadsheet will be named Untitled spreadsheet, to change the name, simply click on the name and type a new name.

Step 2 - Click on a cell and start entering data.

Step 3 - To add notes, functions, charts, images, drawing, links, form, and more, click Insert.

Google Sheets enter data

3. Format Data

Select the cell you want to customize and then use the menu and the toolbar to customize data.

  • Edit - You can search and replace words in your spreadsheet by using Find and replace. Delete notes with ease, by just clicking Clear Notes.Google Sheets format data 2
  • Format - Choose from a wide array of different font options to format your text, this includes: Font size, Bold, Italics and Underline options. Establish conditions to format cells, rows, and columns by using Conditional formatting.Google Sheets format 3
  • Data -  Creating drop down list for cells by selecting Data validation, these lists are created based on particular criteria. You can also sort and filter your data using Sort range.Google Sheets format data 4

4.  Rows, columns, and cells

  • Delete rows and columns - Right click on the row  or column letter and select Delete or Hide
  • Add rows and columns - Select a cell or a block of cells, click Insert and choose where you want to add the row, column, or cells.
  • Move rows and columns  - Select the row number or column letter and just drag it to a new location.
  • Delete cell or block of cells - Click the cell/ block of cells you want to delete it. Click Edit> Delete cells and shift up or Edit > Delete cells and shift left.Google Sheets Rows, columns and cells

5. Multiple Sheets

Google Sheet allows you to work on multiple sheets from the same spreadsheet.

Step 1 - To add a sheet, click Add sheet at the bottom of the spreadsheet.

Step 2 -To delete a sheet, Click the down arrow on the sheet tab, located at the bottom of the spreadsheet, select Delete.

Step 3 - To create a copy, from the down arrow on the sheet tab, click Duplicate.

Google-Sheets-multiple-spreadsheets

6. Revision History

Other than providing an auto-save feature, Google Sheets allows you to easily navigate through through previous versions of the same spreadsheet. If you think an older version of your spreadsheet is a better fit than your current version, then simply click on the particular version and select Restore This Revision and just like that your old spreadsheet is back.

Google Sheets revision history

7. Add images

Support your data findings, add relevant images. To insert an image, go to Insert > Image, either Upload, Take a snapshot, use a URL or select from your own Album or Google Drive.

Google Sheets add images

Though Google Sheets enables images into the spreadsheet, the images are by default inserted over the cells rather than in the cells, this invariably affects the presentation of your spreadsheet.

If looking at images over your cell rubs you off the wrong way, then use the function: =IMAGE to insert an image inside a particular cell and based on the aspect ratio, the image will get smaller or larger as to adjust in the cell.

8.  Split data into columns

Split text which is separated by a comma, into different columns. This is really handy when project managers have to track tasks, they can simply split the task and updates into separate columns. It helps to present data in a cleaner fashion.

To split the data which has not been entered into the spreadsheet yet:

Step 1 - Paste the data you want to split into columns, in your Google Sheets.

Step 2 - Click Paste formatting > Split text to columns, right next to the cell where you pasted the data.

Step 3 -  (Optional) In a separate box, click Comma, to change the delimiter.

Google Sheets split data

9. Freeze headings

Often while going through large spreadsheets, we skip the heading of the column and have to carry out the hectic exercise of scrolling up again to know exactly which heading it was. To ease the navigation around large spreadsheets, keep your columns at one place as you scroll down.

To freeze columns. Follow these steps:

Step 1 - Select the row or a column you want to freeze.

Step 2 - on your menu bar, go to View > Freeze.

Step 3 - Select the number of rows or columns you want to freeze.

Google Sheets Freeze

10.  Set up Email notification for spreadsheet updates

Get notified through your Gmail anytime anyone makes a change to your spreadsheet. You will get a detailed notification regarding what changes have been made and which member made the change. To set up an email notification, follow these steps:

Step 1 - Go to Tools > Notification rules.

Step 2 - A window will appear, select when you want to be notified: When changes are made or When the user submits the form.

Step 3 - In the same window, select how you want to be notified: Email - daily digest or Email - right away.

Step 4 - After selecting your options, click Save.

Google Sheets email

-- 

After covering the basics, it's time to graduate to the more nuanced features available on Google Sheets. These features help in optimizing your spreadsheet to whole a new level. The more knowledge you have about these features, the more room you have to play around with your spreadsheets.

Templates for Google Sheets

For a robust data analysis - the ability to analyze large chunks of data on a spreadsheet is not enough, saving time is also an important factor that many forget to consider. Professionally created templates for Google Sheets save time and increase the efficiency of teams and individuals to comprehend data effectively.

Whether it's an account manager who needs to create an annual statement or a teacher who needs to keep a track of student attendance or an individual who needs to create a to-do list - there is a Google Sheets template for everyone.

Google-Sheets-templates

Users can custom create their own template based on their needs. Moreover, Google provides users with limited access permissions, which does allow users to approve and edit templates, however, it does not allow these users to change any sheet settings. Thereby making the admins who have the full drive and sheet access privileges - the sole authority of managing templates. This safeguards privileged data, at the same time, helps in managing custom templates in a scalable way.

Templates available on Google Sheets might serve many purposes and there are some unique templates which have been a relevant factor in the popularity of Google spreadsheets - here are some popular templates:

1. Gantt Charts

Google Sheets templates 2

A type of chart designed with horizontal lines, it is used to track the amount of work completed within a certain period of time as compared to amount planned for those periods. Google Sheets provides pre-designed Gantt chart which helps in tracking tasks as they are updated.

2. Wedding planner

Google Sheets templates 3

Weddings require grand management strategy, from guests, budgets, inventories, seating arrangements, these ardent tasks might make a wedding, an overwhelming experience.

The wedding planner template has been exclusively designed for Google Sheets to simplify the wedding planning process. Plan a spectacular ceremony by monitoring the budget, tracking to-dos, comparing vendors and much more.

3. Grade book

Google Sheets templates 4

Google Sheets help you maintain a grading system which gives instant knowledge of where everyone ranks. Grading systems are used by not only teachers but by businesses and individuals to evaluate an employee or one’s own performance against established criteria.

These grades can be shared with other members which help them to understand the performance of each and make changes accordingly.

Collaborate - edit, comment, and share

Push the limits of your Google spreadsheets by instantly collaborating with other team members. Make changes, add comments and share your spreadsheet effortlessly while making sure your data is secured and remains intact within the circle of people who have access.

1. Edit

  • Concurrent editing

Google Sheets enables concurrent editing, as many as 50 shared members can edit the same spreadsheet at a given time. It allows team members to keep the data updated irrespective of their location. An updated data increases the accuracy of inferences and reduces the scope of errors.

  • Collaborate with chat

To enrich the collaborative editing experience, Google Sheets provides a live chat option. As you edit, chat with other co-editors, to fine tune your edits, which are acceptable to all collaborators. This reduces situations of conflict and makes editing a truly engaging experience.

  • Limited editing

You might feel that certain portions of your spreadsheet do not require any edits and you are worried that since your sheet can be edited by other members, the particular data can be changed. Here’s your escape route - you can lock certain sections of the content on your spreadsheet from being edited by anyone else except you, Just follow these simple steps:

Step 1 - Select the cells, which have data that you want to protect.

Step 2 - Click Data > Protected sheets and ranges

Step 3 - Select Set permissions, and then choose the following options:

  • Select Restrict who can edit this range, it restricts changes to certain people.
  • Display a warning/ alert when other collaborators try to edit the protected section by selecting Show a warning when editing this range.

Step 4 - Click Done.

2. Comment

  • Add a comment

In case you want to assign a particular task or make a remark regarding a particular section of the spreadsheet, use the Comment feature to address tasks and give remarks. Click Resolve to signify that the comments have been addressed. To add a comment, select the section you want to address, go to Comments on the top right corner of your sheet and click Comment or go to Insert > Comment.

  • Get email notifications

Receive email notifications every time a comment is added to a sheet. To make sure you are not pestered with irrelevant comments on your Gmail inbox, set filters for receiving email notifications. Just go to Comments > Notifications, and choose the following:

  1. All - If you think all comments are equally important, you will receive notifications for all comments.
  2. Only by yours - Get notifications which only involve you, this way you are able to reduce email clutter and be more productive.
  3. None - You will not receive notifications regarding any comments for that spreadsheet, this helps reduce strain caused by all the unwanted notifications.
  • Comment vs. Note

To enhance its commenting features, Google Sheets comes with an added feature called Note. For adding simple information in a cell, Note is a great tool. However, lengthy messages create unwanted complexities and worse of all, users might overlook the notes and focus only on the data in the cell. Therefore Note has its uses but it is still not as scalable as Comments tool.

Comment function is an enhanced version of Note, it can be easily applied to spreadsheets with multiple users as it allows exchange of information through back and forth conversations, which can be played out without altering the contents of the cell.

3.  Share

  • Share with an email address

There are times when you want to share your data with a limited number of people, this is where Google Sheets allows you to limit sharing by feeding the email addresses of only those to whom you want to provide access.

  • Share with a link

If you believe in ‘sharing is caring’ and think that your data needs to have unlimited access, then share your sheet with anyone who has the link. This is an efficient way of sharing between large teams or groups where instead of typing email addresses, one can simply share the link.

  • Establish action

Choose between - Can edit, Can comment, Can view, to establish the action you expect other members to carry out with your spreadsheet.

Data portability

Sharing spreadsheets is just the tip of the iceberg, if you go deeper, you realize just how flexible it is to move your data. Google Sheets can travel between different platforms and in different formats. This allows even the non-Google Sheet users to get a taste of the spreadsheet action.

1. Import data

One option is exporting data from Google Sheets but what about importing data into Google Sheets, are you going to sit down and type in the data manually?

Why waste time when you can easily import data into Google Sheets, using simple Google spreadsheet functions.

Seamless imports of data can be carried out irrespective of the original format they were made in, this includes - HTML, RSS, XML, AND CSV. Import your product inventories, blog posts and much more straight into Google Sheets, without breaking a sweat. Here are some functions which will ease the process of importing data:

  • IMPORTXML(URL) - Imports data from any of these formats -  XML, HTML, CSV, TSV, and RSS and ATOM XML feeds.
  • IMPORTDATA(URL) - Imports data at a given url in .csv (comma-separated value) or .tsv (tab-separated value) format
  • IMPORTFEED (URL, query, headers, num_items) - Imports a RSS or ATOM feed.
  • IMPORTHTML (URL, query, index) - Imports data from a table or list within an HTML page.
  • IMPORTRANGE (spreadsheet_key, range_string) - Imports a range of cells from a specified spreadsheet.

2. Downloadable in other formats

Google spreadsheets are downloadable, making it easy for you to open them in other systems. Moreover, you can download your spreadsheet in other formats like - Microsoft excel (.xlsx), PDF document (.pdf), .csv, .zip and more. To download spreadsheet into your personal system - go to File > Download as and choose the format that will best fit your needs.

3. Email a copy as an attachment

If you need to send a spreadsheet to your peers on an official email, then instead of going through the pain of downloading and then attaching it to your email, simply create a copy of your spreadsheet and email it as an attachment.

Step 1 - Go to File > Email as attachment

Step 2 - Select a format

Step 3 - Feed in the email addresses or email groups you want to send the spreadsheet to.

Step 4 - Enter a message if you wish to, click Send.

4. Integration with Google Docs

Embed charts from Google sheets straight into Google Docs and subsequently update changes on Google Docs as and when made to the original data on Google Sheets. A seamless integration between these two platforms allows Google Sheets to be used across multiple Google documents.

To know more about how to integrate your spreadsheets, read The Ultimate Guide to Google Docs.

Shortcut keys for Google Sheets

It might sound almost romantic when typing large volumes of paragraphs for a novel, but it may not be the case when it comes to data entry and analysis. To increase your productivity in coming up with faster insights for more complex spreadsheets - shortcut keys is the best way to go about.

Google sheet shortcut keys are standardized across different systems - Windows and Mac. The table below illustrates the same:

Google Sheets Shortcut keys

However there some actions which have different commands based on the system you use:

    • If you are on Google Chrome, the command for Data menu, Alt + D, for other browsers:  Alt + Shift + D
    • To insert a row above on Google Chrome: Alt + I, then R, any other browsers: Alt + Shift + I, then R
    • To open the Add-ons menu on Google Chrome: Alt + N, for other browsers: Alt + Shift + N

To get the full list of Google Sheet shortcut keys -  Click Here

Formulas - Google Sheets

Instead of switching to another platform for computing both basic and complex algorithms, Google Sheets performs various calculations by using functions to create formulas. In order use a formula to perform a specific function: insert  ‘=’ sign in the cell and type the function you want to use.

Even if you are not well versed with the different functions, a function help box is provided where you can view the definition of the function and its syntax along with an example for reference.

Having said that, depending on a help box every time you are computing data is not feasible. That is why this guide aims to equip you with an elaborate knowledge of formulas and functions which include both - commonly used formulas and the NOT so commonly used formulas.

Commonly used formulas

  • SUM

As the name suggests SUM function allows you to add cell ranges. Let’s take a simple example, you are listing your revenue for the current quarter for all your international offices. Now, instead of using a calculator to add these revenues, save time by using the SUM function.

Step 1 - Type in the function =SUM in the cell you want to view the result.

Step 2 - Select the cell range for which you want to add the values up.

Step 3 - After selecting, press Enter.

Google Sheets functions

  • AVERAGE

Say, you have your quarterly sales figures and need to know exactly how much average sales you are currently making, instead of manually adding the values in the cell and then dividing by the number of terms, use the AVERAGE function.

Step 1 - Type in the function =AVERAGE in the cell you want to view the result.

Step 2 - Select the cell range for which you want to compute the mean value.

Step 3 - After selecting, press Enter.

3. COUNT

COUNT function essentially calculates the number of cells which have a value i.e. it checks to see if the cell range are empty or not.

Say, you receive your sales figures from all the different international offices and for some reason, one of them was unable to provide their sales figures. If you proceed to find the total sum or the mean values of these sales figures, it will not be accurate, since certain cell do not have any value. Thus, to validate your data better, use the COUNT function.

Step 1 - Type in the function =COUNT in the cell you want to view the result.

Step 2 - Select the cell range for which you want to find the empty cells.

Step 3 - After selecting, press Enter.

Google Sheets functions 3

4. MAX and MIN

Say, you want to check which of your sales offices performed well and which ones didn’t. Easily find the sales champion with highest sales numbers, at the same time, find the weakest link by knowing who has the lowest sales figures. To find the maximum or minimum values in a cell range, use the MAX AND MIN function respectively.

To find the maximum or minimum values in a cell range, use the MAX AND MIN function respectively.

Step 1 - Type in the function =MAX or =MIN in the cell you want to view the result.

Step 2 - Select the cell range for which you want the highest or the lowest value.

Step 3 - After selecting, press Enter.

Google Sheets functions 4

5. Multiplication (*)

Google Sheets makes it terribly easy for multiplying long chains of numeric values by using the sign  ‘*'.

Step 1 - Type in the function =cell name*cell name

Step 2 - Select the specific cells you want to multiply.

Step 3 - After selecting, press Enter.

Google Sheets functions 6

Not so commonly used formulas

1. Concatenate

By definition, Concatenate means linking things together in a chain or series, concatenate function links several text strings, in different cells, into one string. For instance, you want the names of all your sales executives in one single cell, then instead of manually inserting, use the function Concatenate.

Step 1 -  Type in the function =CONCATENATE in the cell you want to display the string of data.

Step 2 - Select the specific cells for which you want to link the text string.

Step 3 -  After selecting, press Enter.

Google Sheets functions 8

2. Filter

It displaces rows and columns which meet the specified conditions. Say, you want to check how many of your sales were above or lower a certain number, using the Filter function, view data based on that exact criteria.

Step 1 - Type in the function =FILTER in the cell you want to display filtered data.

Step 2 - Select the specific cells for which the conditions will apply on.

Step 3 -  After selecting, press Enter.

Google Sheet functions 9

3. GoogleTranslate

Translate your spreadsheet into another language, making data communication seamless. If you need to send your sales report to your peers in Spain, by translating spreadsheet into Spanish, you will be able to avoid any 'lost in translation' moments, which means, data will be understood by everyone irrespective of language barriers.

To translate your spreadsheet type in the function:

=GOOGLETRANSLATE(“word to convert”, “source language”, “language to be translated into”)

Google Sheets functions 10

4. Match

In Google Sheets, the MATCH formula gives us the relative position of an item in a range of cells. Say, you have a range of cell, which shows the sales numbers in an ascending order, if you need to know the position of a particular sales figure, use Match function.

Step 1 - Type in the function =MATCH(search_key, range, [search_type])

Step 2 - Select the specific cells for which you want to apply the match

Step 3 - Press Enter

Google Sheets functions 11

These are just some of the many functions that Google Sheets provides, to get the entire list of functions - click here

Charts

We all know the purpose of charts, it breaks down complex data into beautiful visual summaries. And when it comes to charts, no one does it better than Google Sheets. It not only auto generates a chart but comes with some unique built-in features.

Once you select a cell range, go to Insert > Chart, a Chart Editor window will open up:

1. Recommendations

Google Sheets can generate suggestions providing various variants, style and type of charts based on your data selection. If you like what you see, then just click the particular chart variant and click Insert.

Google Sheets charts 1

2. Chart types

If you feel the recommended chart types will not do justice to your data, then you can skip the recommendations tab and go to Chart types. Select from an array of chart types which includes - Line chart, Area chart, Column bars, Pie charts, Scatter charts and much more.

Google Sheets charts 2

3. Customization

Google Sheets lets you present charts the way you want. Google Sheets personalizes your experience by enabling you to create your own custom-made charts. With a range of customization options, create a chart that will complement your data to the absolute.

  • Chart title - Edit font styles, size, and color based on your choice.
  • Axis labels -  Change font style, size or color for both horizontal and vertical axes of your chart.
  • Legend - Edit your legend by selecting font style, size, data series, color, or position.
  • Data series - Make quick edits to color, line thickness, dot size as well as the axis it belongs to.
  • Chart Background - Resize the chart, move it around the spreadsheet, and adjust the number of grid line to be displayed.Google Sheets chart 3

4. Chart hacks

Along with providing a powerful recommendation engine and a wide range of customization options, here are some neat tricks you can do with your charts:

  • Annotations

Annotations are small notes or comments that can be added to your Google charts. To make sure your charts remain dynamic - update annotations, even after adding them to the chart, you can also change the type or appearance of your annotations.

Google chart 4

If you wish to add or edit annotations, select the chart, click the down arrow and select Advanced edit.  In your customization tab, select the Trendline option, by default it is set to None, to change it, simply click and choose between - Linear, Exponential, Polynomial.

Google chart 5

Trend charts encourage higher engagement, as each annotation can be linked to a number inside the chart and they can be viewed based on different time periods or selected range bar.

Google chart 6

  • Heat Maps

Data can be presented in the form of maps. Say, you have to present your sales numbers for all your international offices, you can show it in a table, however, a heat map would really add a charm to your presentation.

To add a map:

Step 1 - Select the data and Go to Insert > Chart.

Step 2 - In the chart editor, Go to Chart types > Map.

Step 3 - Select the map type and click Insert.

Google Sheets chart 7

To make changes to the heat map, go to customization tab and make edits to color, font, type of map.

  • Estimates

Google Sheets allows space for inserting estimated data into your chart. Say, you have your sales numbers for this quarter, but you also want to add the estimated sales figures for the next two quarters, in order to establish an achievable objective for your teams in the future.

Step 1 - Insert the data which includes the next two quarters and estimated sales numbers.

Step 2 - Next to the cell of estimated sales numbers, type FALSE

Step 3 - Go to Insert > Chart.

Step 4 - Select the chart type and click Insert

Google Sheets chart 8

Two of the bars are highlighted differently as they represent the estimated sales value.

Google Sheets chart 9

Add-ons - Google Sheets

Google Sheets add-ons

Add-ons is what makes you go from ‘Liking ’ Google Sheets to ‘Loving’ it - they expand the functionality of Google Sheets which you otherwise might not have thought about.

To become a spreadsheet pro, you need to understand how to manage your add-ons, the better you get a hang of it, the better you are able to master Google Sheets. To manage your Add-ons, Go to Add-ons> Manage add-ons.

Here are some of the many areas where you can use Google Sheets Add-ons:

1. SEO

When it comes to SEO, Google Sheets isn’t exactly the name that strikes a bell, however, with just a couple of add-ons you can automate your keyword research efforts and get instant insights on your SEO performance.

Convert your Google Sheets into the main database system for all your SEM, SEO, and social media channels. Integrate your sheets with Google Analytics, Adwords, Facebook ads and other platforms. Google Sheets doesn’t just act as a database center, it also generates professional looking SEO reports using pre-designed templates.

Google Sheets add-ons supermetrics

Access Google Analytics data directly from your Google spreadsheet and analyze the performance of your keywords by selecting -  Create new reports, Run reports or Schedule reports. Generate custom calculations from your analytics data and using the same data, embed data visualizations like charts and tables.

Google Sheets add-ons Google analytics

2. Data portability

Google Sheets doesn’t just analyze your data, it gets you ‘The data’. Importing data is not a new concept to Google Sheet users, but how many of you know the extent to which we can import data. Google Sheets add-ons break those barriers and encourage data import from a host of sources.

Automatically gather data from sources like Excel and Google Sheets by connecting spreadsheets via one-to-many connections, many-to-one connections. With the ability to merge up to 30 spreadsheets in a single sheet, automate hourly, daily or weekly data transfers between multiple sheets.

Google Sheets add-ons sheetgo

Automate collecting and curating objective data into your spreadsheet, find unconventional ways of acquiring data through dynamic computations based on various algorithms and methodologies.

Google Sheets add-ons WolframAlpha for Sheets

Upgrade data collection techniques by importing data from over hundreds of web apps and data services. Irrespective whether you have the technical knowledge or not, it can re-engineer your entire data science by pulling data from apps, twitter, financial news, government data, all of this without having to leave Google Sheets.

Google Sheets add-ons blockspring

3. Data crunching

Get your maths right with add-ons which are designed to solve complex mathematical equations almost instantly, saving time and allowing you to focus on more important aspects of your spreadsheet.

Write and solve virtually any mathematical equation without knowing math codes or programming language. Save your precious time by picking equations from a wide array of ready-made formulas and functions but if you prefer dictating your equations, then use the voice typing feature.

Google Sheets g math for sheets

Insert a power regression equation (y=a*x^b), at the same time, plot a trendline based on the results of the same power equation. Add a custom function that puts each of the equation’s parameter into your spreadsheet.

Google Sheets power regression tool

4. Spreadsheet Formatting

The neater your data looks, the easier to understand, that said, with multiple complex equations, graphs, and charts, it might be difficult to maintain a clean spreadsheet. But with Google Sheet Add-ons, you can make even the most cluttered spreadsheet look like a piece of beauty.

Maintaining attendance sheets can be an overwhelming task, as it’s probably the most dynamic spreadsheet one could hope for. But with Google Sheets, you can view attendance in a neat and easy- to-understand format with automated data sorting. Now, see who attended which meeting, attendance of each meeting, and much more.

Google Sheets add-ons attendance sorter

Unwrap a set of amazing formatting toolkit, which helps you not only keep it a tidy affair but also reduces your chances of hitting formula errors in your spreadsheet. From removing duplicate data and extra space to enabling auto-sum of all numbers in rows and columns. Ease the stress on repetitive tasks and use features for organizing and unifying data in Google Sheets.

Google Sheets add-ons power tools

5. Email integration

Communicating Google spreadsheets via email is still one of the popular means of data outreach. Google Sheets Add-ons enhances productivity by reducing time and effort of creating an attachment, at the same time, with some extra features, it enables a seamless path for distribution of your sheet.

Create email campaigns with Google Sheets and personalize and track all the emails sent. CC and BCC to different addresses for each email and by inserting the link to files stored in your Google Drive, you can automatically upload the file as an email attachment.

Google Sheets add-ons yet another mail merge

Merge your spreadsheets with emails as attachments and integrate your Google Sheets with Google Docs. Effortless data merges via emails increases the overall productivity of your data outreach as well as your ability to conduct large volumes of communication at a faster pace.

6. Managing finances

The extensive use of Google Sheets by finance professionals is very well known, that is why there are exclusive add-ons for helping financial managers get more out of their spreadsheets.

Google Sheets allows financial managers to get more out of their balance sheets by providing pre-designed templates for reporting various financial reports, this includes - cash flow statement, 26 financial ratios, company valuations and 8 performance charts.

Google Sheets add-ons Financial Analysis

Quickly anticipate crisis and opportunities regarding your cash flow management by creating financial forecasts. Easily create forecast reports for as long as six months from the starting date, this helps in maintaining a robust financial management for both businesses and individuals.

Google Sheets add-ons Financial Forecast

Working offline

Imagine sipping the cool mojitos while soaking a warm sunbath in the middle of nowhere and suddenly you are contacted by your peers, asking you to make some quick edits to an important spreadsheet, but here’s the deal breaker - you do not have any internet connection. No sweat, like many of G Suite products, you can work on your spreadsheet even if you have no internet access.

Create, open and edit recent spreadsheets on your device effortlessly by enabling offline mode.

1. Enable offline settings

Step 1 - on your Google Sheets Homepage, click  Menu> Settings> Offline.

Step 2 - Switch on the offline sync capability.

Step 3 - A dialogue box opens up, Which requests permission to activate Google Sheets offline - click the Enable Offline button.

Step 4  - Google will also notify that an extension is also required to be installed, click the Add Extension to complete the process.

Google sheets offline 2

2. Offline editing

Once you have synced your Google spreadsheet to offline mode, the editing process is the same as in the online mode. Though, there are some minor differences:

    • Some features which will be deactivated like - Add-ons, Explore, Help (Except Keyboard Shortcuts)
  • Next to the Spreadsheet name, at the top left of the screen, a small gray offline icon will appear.Google Sheets offline 3

3. New sheets

You can create new spreadsheets the usual way through Google Drive. Go to your Google Drive homepage: New>Google Sheets.

4. Auto-sync offline spreadsheets

All the spreadsheets and edits are stored in the local server, it auto syncs once you gain back internet access.

Spreadsheets on-the-go

Google Sheets mobile app

The traditional limitation of an internet connection and a need for a desktop has always created barriers for getting the most out of one’s spreadsheet, Google understood this and launched the Google Sheets mobile app for Android and iOS.

Google is making continuous efforts in closing the bridge between desktop and the mobile experience by providing corresponding editing abilities, number crunching proficiencies, sharing routes and a host of other functions, and the best part, all of these options can be used on-the-go right from your mobile device.

1. Editing

The editing panel in Google Sheets mobile app allows easy navigation. Once you open your spreadsheet, you can see a compact view of editing options at the bottom of the screen, these include - Bold, Strikeout and alignment options. However to view the entire spectrum of editing options, Click A at the top of your screen.

Google Sheets mobile app 2

For Google Sheets, the edit options are categorized into two tabs - Text and Cell. In Text tab, you can view all text editing functions - Bold, Italics, Underline, paragraph alignment and much more. You can also format your data on the basis of established criteria by using Conditional formatting.

Google Sheets mobile app 3

Format cells in your spreadsheet by using the editing options in the Cell tab -  Fill colors, Wrap text, insert and edit dashed and dotted borders, so forth and so on.  Moreover, format data in a cell or a cell range by selecting  Number Format and view cells the way you want them to be viewed.

Google Sheets mobile app 4

2. Add-ons

Google Sheet allows third party applications, in order for you to get more spreadsheet work done right from your mobile. To install add-ons on your Android device, simply go to your spreadsheet, click three dotted lines and select Add-ons.

Google Sheets mobile app 5

Here are some of the add-ons available on Google Sheets Android version:

  • Google Classroom - Teachers and students can achieve more by connecting through virtual classes, schedule and manage assignments and track progress straight from your mobile phone and tablet.
  • Zoho CRM -   Save time and money by easily importing contacts from Google spreadsheet directly into Zoho CRM.
  • ProsperWorks - Make your CRM data to better use, by creating and updating advanced dashboards, reports, and graphs on Sheets, straight from your device.

3. Chart

Take your chart game a notch higher by inserting and making quick edits to charts and graphs on the go from your Android device. To generate a chart in your spreadsheet, simply select the data range, click + sign, and select Chart.

Google Sheets mobile app 6

The chart editor window will open, select the chart type, legend, titles, and color and click the tick mark, to create a chart. Similarly, make quick edits to the chart from the same window, the chart will automatically be updated.

Google Sheets mobile app 7

4. Functions

One of the impressive aspects of Google Sheets mobile app is the ease with which you can compute data using functions. From your spreadsheet, select the data or data range, the bottom of the screen in the function bar, you can view automated functions and results for the data. But if you wish to manually type, then go ahead and choose from the symbols below the text box to add to the formula.

Google Sheets mobile app 8       Google Sheets mobile app 9

5. Working offline

Google Sheets allows you to work on your spreadsheets from any Android or iOS device, even if you have no internet connection. To work with Google Sheets in offline mode, click on the three dots, activate Available offline. Moreover, all the changes made in offline mode will automatically sync once you gain back internet access.

Google Sheets mobile app 10

Create a mobile app with Google Sheets

Want to do something amazing with your spreadsheet, how about making a mobile app!

Yes, you heard right, you can create a mobile app from your spreadsheet, all you need is AppSheet, a Google Sheets add-on.

Say, you want to create a live mobile app for your sales team

1. Connect your spreadsheet into AppSheet

Create a spreadsheet with 'proper meaning' column names, this includes - column1: Name of sales manager, column 2: Sales (in USD) made by each manager, column 3: Country they operate in.

Once you have created a spreadsheet, go to Add-ons > AppSheet > Launch. A sidebar will appear, that will allow you to create or access a custom mobile app, click Go and you will be redirected to AppSheet dashboard.

Google Sheets appsheet 1

2.  Customize your mobile app

In the app editor, you can customize your app without the use of any sort of coding. Modify your user interface (UX) to make your app engaging:

  • Add a chartGoogle sheets appsheet 3
  • Add a mapGoogle sheets appsheet 4
  • Add a form
    Google Sheets appsheet 6

To enhance the visuals of your app, you might also consider adding background images, colors, and fonts.

3. Run deployment check

AppSheet has a built-in AI which runs a deployment check, to ascertain whether the mobile app is ready to be live or not. A full fledged report is provided which flags errors that might need urgent attention.

Google Sheets appsheet 5

4. Dynamic app

The mobile app will be dynamic as it will auto sync updates using Version history, as and when changes are made to the spreadsheet, that means your sales team will always have the latest version of the app which will provide up to date data.

Google Sheets appsheet 7

Collect data for survey

A major part of conducting a survey is the methodology of collecting responses. It’s easier to collect data when the number of respondents is small but when the number is huge, it is better to have a proper system of collecting responses, either way, Google Sheets can optimize your survey responses by collecting and analyzing data.

Google Sheets form

1. Collecting data

With the help Google Forms, you can create surveys. Say, you are appointed as the event manager to organize the office Christmas party and you decide to take inputs from your peers to get ideas for the party.

After creating a survey on Google Forms, you send it to people to be filled up, all the responses can be easily seen in the Responses tab, where you will notice a Google Sheets icon - click it.

Google Sheets form 2

A window will appear and ask you to create a new spreadsheet, once you create the sheet, you can view all the responses. The data is automatically updated as the responses come in.

2. Analyzing data

Once you collect all your responses in Google spreadsheets, analyze the data by generating charts, applying formula and function, adding filters. Due to Google Sheet’s auto-sync feature, all changes will be updated to the spreadsheet, charts, and tables, moreover, the insights can be shared with other co-workers.

Google sheets form 3

Manage Stock Portfolio using Google Finance function

“The most valuable commodity I know of is information.”

- Gordon Gekko

Gordon Gekko, the famous character portrayed by Michael Douglas in the movie, Wall Street, gave us a sneak peak into how the financial markets think and how one should approach investments. It’s no surprise that information is the key to building a robust stock portfolio, it’s how risks can be mitigated by knowing and tracking the business you want to buy or sell.

Google Finance helps manage your portfolio by providing stock prices, financial news, currency conversions and much more. The stock prices change rapidly and to stay ahead in the market, you need to be updated and track stock prices very meticulously.

Using Google Finance function in Google Sheets, you can manage and track stocks in real-time straight from your spreadsheets. Being the first person to know will help you evaluate the market value of companies, which will help you take quick and accurate decisions.

The formula for Google Finance function:

=GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])

With the help of this function, you can maintain a sturdy stock portfolio:

1. Stock names

Say, you are interested in building a portfolio in coffee stocks, you have listed the stock symbols for the companies, but you wish to translate the symbols into names of the holding companies. To find out the names of the holding companies from their stock symbols, simply use the function:

=GOOGLEFINANCE(Cell reference,“name”)

Google Sheets finance 1

Select the company name and drag it down, Google Sheets will automatically find the names for the rest of the companies based on their respective symbols. To get stock symbols, simply Google search or go to Google Finance.

Google sheets finance 2

2. Stock prices

You would want to find out the latest stock price of the coffee companies through which you can access the latest performance of each of these stocks. To find the price, use the following formula:

=GOOGLEFINANCE(Cell reference,“price”)

Google sheets finance 3

Select the stock price and drag it down, Google Sheets will automatically find the latest stock prices for the other companies.

Google sheets finance 4

3. Price change

Find out the change in price from previous day’s closing stock price to the latest by using the Google Finance function. Price changes help in forecasting the potential for a particular stock. To find price change, use the formula:

=GOOGLEFINANCE(Cell reference,“change”)

Google sheets finance 5

Select the stock price change and drag it down, Google Sheets will automatically find the latest stock prices for all the companies.

Google sheets finance 6

4. Market capitalization

Market cap signifies the value of a company that is traded on the stock market, it is an important metric when considering to buy and sell stocks. From your Google Sheets, find the market capitalization of each of the coffee holding companies. To find out the market cap:

=GOOGLEFINANCE(Cell reference,“marketcap”)

Google sheets finance 7

Select the market capitalization value and drag it down, Google Sheets will find the market capitalization value for all the companies.

Google sheets finance 8

5. Currency conversions

Convert one currency into another, it's a useful tool, especially when you are dealing in international markets. Say, you need to convert the market cap value from US dollars to European pound, use the formula:

=Cell reference*GOOGLEFINANCE(“currency:USDEUR")

Google Sheets finance 9

Select the new currency value and drag it down, Google Sheets will convert the corresponding USD values into Euro.

Google sheets finance 10

6. Create reports

Based on the data, you can create reports that include - charts and graphs. Present your data using the tools provided by Google Sheets to be able to make an informed decision.

Google sheets finance 11

Data Security

Security is an integral aspect of all G Suite products and like all, Google Sheets enjoys the same sophisticated level of Google app security. Moreover, Google regularly monitors its security apparatus for not just its billion Google users but for the entire internet. Here are some of the ways Google makes sure that your data remains secure:

1. Professionally skilled security team

Google has handcrafted a team of more than 550 security and privacy professionals who work round the clock to maintain Google’s defense systems, developing security review processes, building security infrastructure, while the whole time implementing and updating Google’s security policies.

The team is comprised of some of the world’s foremost experts in information, application and network security. They bring their highly specialized skills to the table by giving project specific consulting services, at the same time, monitor for suspicious activities and perform routine security evaluations and audits.

To stop targeted cyber attacks, a special team has been built called - Project Zero. The team responsibilities include: conducting research and outreach activities to provide a macro level protection to users irrespective whether they are Google users or not.

2. Add-ons

Customize security needs for your spreadsheets by using Google Sheets Add-ons. Users hold the power to provide access permission to other users and a lack of knowledge or carelessness regarding access settings can lead to data leaks. In such cases using Add-ons provides an additional blanket to your G Suite security. Here are some of the add-ons you might consider to keep your data protected:

  • Drive Permissions Auditor -  Scan all your files in Google Drives, which also includes your spreadsheets and get a detailed report which highlights the member who have shared access to your spreadsheet and what level of access permissions do they possess.
  • Password Protect - Keep your spreadsheet data secured by assigning passwords while keeping them in their native format. Moreover, set a duration for a secured file to remain unsecured, upon the completion of the said time, the file is locked from view by anybody that has the spreadsheet opened and is placed back into a local server.
  • Keep It Confidential -  Keep the data confidential by replacing the content with a secure link, this way you are able to share large volumes of data while keeping it confidential during view or download mode.

3. Response to Government requests

In special circumstances where government bodies who wish to get backdoor access to data are required to put in an application, the security team at Google reviews their application and if they do not see any merit in the request, the government body is debarred from accessing any data.

4. State-of-the-art data centers

G Suite cloud platforms store data on Google servers, which are located at Google’s highly advanced data centers. These data centers are vetted with the highest level of security infrastructure, apart from being monitored 24/7, access to the data center floor is only possible through security doors which function only through a security badge and a biometric test. The security is so vigorous that less than one percent of Google employees have and will ever step foot in these data centers.

These facilities are powered with high-tech backup systems to make sure that data is secured even in a case of a total blackout. Cooling systems are in place so that all equipment work in proper temperature reducing wear and tear of hardware. The system is designed to contain natural fires by placing fire detection and suppression equipment.

The servers are run on a custom-designed operating system, which has inbuilt self-healing mechanisms and empowers Google to monitor and take immediate action on destabilizing events, instant notifications on incidents and avoid any risks which might compromise the network.

5. Encrypting data

To keep data secure when it is in-transit, Google replaces your data with an unreadable code known as ciphertext, to decrypt the code, one needs to employ the key used in the encryption algorithm. A dedicated encryption team is appointed to make sure that your spreadsheets are protected by providing high-end cryptographic solutions.

Encryption is an integral part of G Suite's security strategy, which is aimed at making sure that your data does not become vulnerable to attackers, at any given point. Initially, a certain part of data is encrypted and stored on the disk and for data which is in-transit, the entire data is encrypted.

Attackers are coming with new hacks which can break obsolete encryption algorithms. To keep old encryption codes less vulnerable to new methods of hacking encryption, Google uses Forward Secrecy technology.

6. Third party certifications

Apart from conducting internal evaluations, Google also carries out audits from third party bodies. Here are some of the third party certifications that Google has been recognized with:

  • ISO 27001 -  one of the most respected independent international security standard, all the systems, technology, processes and data centers that run G Suite security are certified by this body.
  • ISO 27017 - An international standard practice for information security controls, specifically for cloud services.
  • ISO 27018 - An international standard for protection of personally identifiable information in public cloud services.
  • SOC 2/3 - Audit framework for a non-privacy principle that involves security, availability, processing integrity, and confidentiality. Google has both SOC2 and SOC3 accreditations.
  • FedRAMP - The Federal Risk and Authorization Management Program, is a government-wide program that involves a standardized approach to security assessment, authorization, and continuous supervision of cloud products and services.

Conclusion

If you have gone through the entire guide then congratulation, you are very well on your path to becoming a spreadsheet pro. Having said that, to actively remain on top of your spreadsheet game, it is important that you keep updates on your G Suite applications since they do go for regular updates.

We have tried to cover all bases regarding Google Sheets,  but if you feel that we have missed out on something, let us know in the comment section below.

About the author

Abhilash is the content marketer at Hiver, a glorified coffee snob and a passionate blogger. Understanding the right perspective when it comes to content marketing.

Comments

Leave a Reply

  • Dominic

    May 29, 2017 9:33 pm

    Thank you. Information have been really usefull!

Quick Guide to Google
Apps security for your business