The Beginners Guide to Google Sheets: Basics features and some advanced tips

Google Sheets is the tool used to store data, create reports and dashboards, and analyze performance metrics. This wonderful tool has a variety of features and functions that is hard for beginners to use at first, especially when you need to urgently make a report on Google Sheets.

You are in the right place if you want to know the features of Google Sheets and how to use it for business purposes. This article has covered all the basics of Google Sheets that everyone can easily understand how to use and some benefits of Google Sheets.

Use of Google Sheets

Google Sheets is online spreadsheet software that is used to assemble large amounts of data, automate calculations, create reports and collaborate with others. It is a part of the Google Workspace suite of products.

For Marketing purposes, Google Sheets Benefits

Excel was a good tool for creating spreadsheets, by Microsoft originally introduced in 1987. In 2012, cloud-based Google Sheets tools were introduced with collaboration. Many users now use this new source of Spreadsheets.

Let’s discuss some benefits of using Google Sheets for marketing purposes.

  • Free of cost: Google Sheet is completely free, and it is packed up with Google Drive, Docs, and Slides to share documents and online presentations, with no expensive software required.
  • Online: Automatically you can collect data with your spreadsheet and you can do anything you want, even when your spreadsheet isn’t open.
  • Cloud-based: Through a remote server, cloud-based spreadsheets are automatically saved, managed and stored. It means you can check your sheet online from any device, at any time. Additionally, Google Sheets is also accessible offline.
  • Adds-on: Ads-on is available in Google Sheets, which increased the functionality of features and makes the Spreadsheet tool more multifaceted and amazing. Add-ons can be downloaded, write custom code and create your own.
  • Works across devices: Throughout the day modern marketers switch between devices and Google Sheets lodge this behaviour. From any device, anytime (like android, computer, tablet) you can access Google Sheets and edit them, no more forgetting spreadsheets files at home.

Difference between Excel and Google Sheets

The difference between Excel and Google sheets is in collaboration, flexibility and price. Excel is paid tool which requires a subscription of 130$ while Google Sheets is free for all the users of Google accounts. Google Sheet has many features of integrations to accommodate which is more suitable than Excel. In the meaning of collaboration, Excel files need to save whenever edits are made and sent a new version while Google Sheets automatically saved and update a single shared file for all teammates.

Common Terms of Google Sheets to understand

Before using Google Sheets let’s understand some terms of Google Sheets that are used when to manage data within Google Sheets.

  • Active cell: For entering data, cells you select. If you want to change the cell click on other cells.
  • Anchor cell: In a range, the first cell which highlighted. As opposed to another cell that is in blue, this cell remains highlighted.
  • Bar: To compare categories of data and outline, a bar is a type of chart. The aggregate value of each specific category and the length of the bar represents it.
  • Cell: In Google sheets, cells are the single data points or elements.
  • Cell Reference: To identify the cell, cell reference is a name. For example cell in column “B” in Row “6”, is termed as B6.
  • Column: It is the vertical cell sets.
  • Filter: The data how you want to be displayed on a screen, the filter is establish that criteria. When the filter will place, data that don’t look similar, from the sheet will temporary disappear. When the filters become deactivated, data will show again.
  • Function: In Google Sheets, the function is the term for built-in operations that can be used to rapidly manipulate data, calculate values and others. The function begins with the sign “=” and uses references to the cell. Like =sum (A9; A99), add all the values from cells A9 by A99.
  • Formula: To generate a certain result formula combines ranges, functions, columns, cells and rows. The function of data that calculate results and appear on the active cell. For example A7+B9. It will take values from A7 and B9 and add them to get the result.
  • Row: It is the horizontal cell set.
  • Range: Group of the selected cell. Across row, column or both, Range refers to the selection of cells. If the group of cells are selected together they are termed as adjacent range and if selected cells have a gap between they are termed the non-adjacent cell. By a colon, adjacent range references are separated and cell references of the non-adjacent range are separated by a comma. Example of adjacent range if A5:C9 and non-adjacent range E5, A4, D8.
  • Value: In the cell, it is the numeric data. In calculations, they can be referred to as functions and formulas.
  • Worksheet: The sets of columns and rows make your spreadsheet; one spreadsheet can have many sheets.
  • Spreadsheet: The complete document that has your worksheets.

How Google Sheets use

Now let’s discuss in detail the basics of Google Sheets and how Google Sheets use. 

Understanding the Toolbar of Google Sheets

In Google Sheets, one of the first things to understand the toolbar is to master the icons located. So you can know the features and save your time with shortcuts. Look at the picture below to understand the purpose of basic icons in the Toolbar.

google sheet toolbar

  1. Create a Spreadsheet and Fill Data

The best thing about Google Sheet is it is free of cost and you can work it on any device. You just need a web browser or Google Sheets app on your mobile or tablet and a Google account. On PC or Mac, go to the sheets.google.com and it is ready to start.

In Google Sheets, there are three ways to create new spreadsheets. It is very easy to create a spreadsheet you just follow these steps to start.

  1. On the Google Drive dashboard click the “New” option and select “Google Sheets”.
  2. From the Google, Sheets homepage click on “Blank” or select a specific template.
  3. Open Google Sheets, click on “File” > “New” > “Spreadsheet.”

You can start putting data when you create a new spreadsheet. With the same text editing icons and tabs for extra sheets, Google Sheet might remind you of another spreadsheet app you have seen before. Google has lessened the number of display elements of the interface and clutter, is the only difference. Instead of Excel if you want to work with previous existing spreadsheets, you have to transfer data from the previous spreadsheet into your Google Sheet.

From this section, your file will upload and then automatically converted it into Google Sheet.

  1. Add Data in Spreadsheet

The white and grey grid employs most of the screen, and the first thing you’ll notice is a blue outline around cells or selected cells.

As you open the spreadsheet and start typing your data in the spreadsheet you will see that data start highlighted in the selected cell immediately. You don’t need to tap on your cells again and again to put information.

You can easily put data in cells and when you complete adding your data, you can use one of these options:

  • To save the data and move to the next row, press “ENTER”.
  • To save the data and move to the right in the same row, press “TAB”.
  • To move one in directions, use “ARROW KEYS” on your keyboard.
  • To jump directly to the particular cell click cell.

If you don’t want to type data you can also add data to your spreadsheet by other methods:

  • From the website copy and paste HTML.
  • In cells across a range of cells, copy any value by drag and drop.
  • In your spreadsheet copy paste numbers or text list.
  • In CSV, Xls, XLXS and other formats import previous spreadsheets.

Copy and paste are accessible, from the website or PDF you will try to copy a spreadsheet set of data and with the original style, it will paste into one cell or format everything. In your spreadsheet, to avoid getting weird pasted data, look for data that is actually in the HTML table.

Note: Before pasting data you click only once on a cell, so Google Sheets will put data in its cell. On a cell, if you double-click, all the data will paste into one cell which is not appropriate.

   

File import is also easy, with the imported data you can either import directly into the current spreadsheet, replace a sheet or create a new spreadsheet. The most common format in which you can import files is XLS, XLSX (from Microsoft Excel files) and CSV (comma-separated values). Outside of your Google Drive, from the menu bar go to the FILE > IMPORT > UPLOAD.

      

To keep old and imported data separate, every time import data into a new sheet. If you have Google Sheet (XLS, CSV or another format) instead of Google Drive, you can use the same process to directly import that spreadsheet by searching in your Import.

Dragging: In your spreadsheets, you’ll use this once to set up formulas. In the bottom right corner of highlighted downrange of the cell or across the cell, dragging the small blue dot to perform the different functions.

You can use this feature in the following ways:

  1. List of text data, creating an order.
  2. Including formatting, to the number of adjacent cells copying the data of cell.
  3. To adjacent cell, copying cell formula.
  4. Data Sharing and Protecting

Once your Google Sheet is created and you add data to it, now you’ll think about how to share data and protect it. In Google Sheet data is automatically saved, you don’t need to worry about saving data. You can share your file and restrict the privacy of files who can edit, comment or view this file. Within your sheet, you can protect your specific data, so when you share a file, only some cells can be accessible to edit.

Share a file

You can share a file by following these steps:

  1. Click on “File”, then tap on “Share” or click on the green share button in the top right corner.
  2. Enter the email address of the person you want to share the file with.
  3. Select the option, Viewer, Commenter or Editor according to your need.
  4. If any additional privacy is needed then click on “Advanced”.

For Data protection

  1. Go to “Data” and click on “Protect Sheets and Ranges”.
  2. To protect the complete sheet choose “Range” or “Sheet”.
  3. The data you want to protect, select it.
  4. Click on “Set permissions”.
  5. For certain people, choose to warn error who try to edit files without permission or customize editing permissions.
  6. For Easy Viewing, Format Data

You’ll have to manipulate and format data, when you record grades of students, track your expense or in homebrew CRM keep track of customers.

Above the first row, basic formatting options are available in Google Sheets. When you are working on the sheet for quick reference, is labelled in the image below.

What you expect from the word processor, Font settings/style functions, Undo/Redo, and Print are similar to other tools. Work like you are editing any other document as the shortcut keys are the same.

  1. Revision History

In addition to the auto-save feature in Google Sheets, it also allows you to review your previous version of the same spreadsheet by navigation. If you think that your previous version of the spreadsheet is much better than the new one you can get your old version, by “Restore this Revision” and you can get your previous version back.

  1. Add Images

To support your data you can also add relevant images. To add an image to the spreadsheet go to the menu bar “Insert > image”, you have four options there: Upload, take a snapshot or use a URL to select from Google Drive or your Album.

In Spreadsheet Google Sheets is capable of adding images. Instead in the cells, images that are by default inserted over the cells, invariably impact the presentation of the spreadsheet.

  1. For Spreadsheet updates, Set up an Email notification

 Whenever anyone makes changes in the spreadsheet you get the notification through your Gmail. The notification will tell you detail of the changes, who made changes and what changes have been made.

Follow these steps to get the notification through Gmail:

Step 1: Go to Tools > Notification rules.

Step 2: A drop menu will appear, when you want to be notified and selected from the options; when the users submit the form or when changes are made.

Step 3: From the same menu bar, how you want to be notified; Email – daily, Email – right away.

Step 4: Click on “Save” after selecting suitable options.

How to organize data in Google Sheets

Google sheet is an amazing tool that can store and manage huge data. It means more data you have it is difficult to find data without the filter. Google Sheet has a solution for this problem, several filters are available in Google Sheets that allow hiding data and selecting data you want to see.

How to data hide in Google Sheets?

You can hide entire columns in rows in Google Sheets. This can be helpful when to want to restrict data view for sharing or at a given time you want to control the amount of data for viewing. To hide a column, click on the column you want to hide then select “Hide Column”. On the previous and next columns, two arrows will display when you hide a column. To full view click these to restore the column.

In Google Sheets how to unhide columns?

In Google Sheets, to unhide the columns – in the header bar of the column, find arrow icons that appear. One of the arrows hovers over, and an arrow with a white box framing will pop up. To unhide the column click again on the arrow.

How to columns & rows can freeze?

 Within your line of sight, as you scroll freezing will create a transient column or rows and retain data in that field. For instance, to label your columns if you use the first row, you can freeze that row so don’t need to remember which column you scroll.

To freeze a row, go to “View”, then select “Freeze” > “1 row” or rows as per your need.

How you can filter data in Google Sheets?

Apply a filter, if you like to see all of the data in a single column that fits a particular criterion. How the criteria apply to data, the filter allows for selection criteria. For instance, for all the values greater than 1000 you can filter your “Site Visits” column to display or have the word “Cat” filter your sheets for all the cells.

To filter a data, select “Data” then tap “Create a Filter”. In the column, click the funnel icon, then by numeric order, value, alpha or condition choose to filter. Data that satisfies your criteria, your sheet will only reveal.

How Google Sheets use offline

To use Google Sheets offline, to access spreadsheets offline follow these steps to turn on offline.

  • Internet must be connected.
  • Use the Google Chrome browser for this purpose but don’t use incognito mode.
  • Install and enable the Chrome extension of Google Docs offline.
  • Ensure that you have enough space to save all your files on your device.

Then,

  • Open Google and sign in to your Google account.
  • Go to the settings of Google Drive.
  • To edit offline, next to “Sync Google Docs, Slides, Sheets and Drawing files” check the box.
  • Learn how to install Sync and Backup to work offline files on your device.

With Devices and Apps sharing Spreadsheets

Although Google Drive and Google Sheets are built to share between other members or clients. You’ll notice that many times as internal documents, your spreadsheets created and sharing can be done after getting work completed.

 By taking advantage of helpful add-ons you can streamline spreadsheet workflows and real-time data.

Google Docs mobile Apps: To view and edit your spreadsheets, share links and add users to the file you can use Google Sheets mobile app. It’s not a replacement for a web app but you can use this for basic sharing and editing things.

Google Drive sync to your desktop: From your desktop Google Drive allows you to upload files to your online Drive? This will make it easy to share files with team members or collaborators, make files accessible and you can quickly import them into a spreadsheet or other documents.

Third-party tool: Automatically add data into your spreadsheet you can use a third-party tool like Zapier, send files in your Google Drive or alter you to changes in Sheets.

Advanced Tips of Google Sheets

After understanding the basics of Google Sheets now let’s go through the advanced tips for Google Sheets.

  1. Heatmaps and Conditional Formatting

Based on median criteria (median will display darken when value is greater and will appear lighter when values are less) Heatmaps work the same as they put in colour scale on cells. On the gradient, this helps you to view values and recognize areas of concern that grab your attention.

In the present condition, cell-based Conditional formatting will apply formatting automatically. A cell can select and highlight red if the values are below 100 or read “Error”.

  1. Macros

The steps you want to repeat on command in Google Sheets, the feature of Macros will record. For example, each month you work on new data, and each time you want to manipulate data in the same sequence then Macro will record this for you and Google then do the same work for you.

  1. Array Formulas

The function of Array formulas is to perform many calculations at once and on those calculations output value depends. Although, in other formulas, you have to perform every step separately like adding one-row values and then adding in the other following rows. Arrays can do calculations more efficiently and help you when large amounts of data need to be calculated.

Functions and Skills of Google Sheets you must know

Create a Pivot Table

  1. In your pivot table, for source data select the cells. The header will use in each column.
  2. At the top of the menu bar, click on Data > Pivot table. If the sheets are not open click the pivot table sheet.
  3. Click Add in the side panel next to “Rows” or “Columns” and then choose the value.

Note: Based on the data you selected, sometimes a pivot table will be recommended. Select a pivot table, by choosing the option “suggested”.

  1. Next to “Values”, in the side panel click “Add” you want to see over rows or columns then choose the value.
  2. Click the down arrow, next to what you want to change.

To remove identical

  1. Cell in which you want to remove the identical information, select it.
  2. Enter the function = UNIQUE
  3. Cells in which you want to pull data, select it.
  4. Parentheses close, then the function should look like this: =UNIQUE (A: A).

To Create a Drop-Down List

  1. Choose an option, next to “Criteria”
  2. Click on “Data” > “Data validation”.
  3. For cells in which you want to create a drop-down list, select that cell. From the range list: choose those cells that will include in the list.
  4. Items list: Enter items, separated by commas and no spaces.
  5. Down the arrow in the cells, uncheck the “Display in-cell button to show list” to remove the arrow.
  6. In the list, if you enter data in a cell that will not match an item, the warning will appear. Next “On invalid data”, Choose the “Reject input” if you want from the list people only enter those items.
  7. Click on “Save”. The dropdown list will show in the cell. Use conditional formatting to change the colour of the cell.

Make a Graph

  1. In your chart, select those cells you want to include. Add a header column or row, to put a label on the chart.
  2. Click “insert”, then on “Chart”.
  3. Under “Chart type”, click on “Data” in the side panel, and choose a chart.
  4. Click “Customize”, for editing the chart.

How the cells can merge?

  1. Select or highlight the cells.
  2. On the selected cells click right and select “Format Cell”.
  3. In the toolbar, click on the “Merge” button and then select the merge option according to your need.

                

How Google Sheets can download

For analyzing data with your file, if you want to use Excel or another tool you can easily download the file on your device. Go to the menu bar, “File” > “Download as”. Then choose the format you need.

                  

If you want to learn more about Google Sheets then go on with the free Google Sheets Cheat Sheet for the quickest way to learn that covers these outlines:

  • Spreadsheet data Customization
  • Protecting and Saving Information
  • Working on columns, rows and cells
  • How to use key functions
  • Within Spreadsheet sharing and collaboration

Formulas of Google Sheets that you want to know

Google Sheets formulas are commonly used to speed up your data analysis and calculations. When you put your data and need to do some rapid basic formulas and number crumbling to find the answers. Here are some formulas you must know:

  1. SUM

Within the selected range this will adds all the values

=SUM (range)

  1. AVERAGE

Within the range, this will calculate the average of the values.

=AVERAGE (range)

  1. FIND

Within a text, this will turn back the positions at which the first strand was found. It is a sensitive case formula.

FIND (search-for, text_to_search_, [starting_at]

  1. CONCATENATE

In one cell this will allows you to combine multiple cells values e.g. first name and last name.

=CONCATENATE (Value1, Value2)

  1. FILTER

From the source range, this formula turns a filtered version, rows or columns that meet the specified conditions returning only. This will help when aligning data by month or year.

FILTER (range, condition 1, [condition 2])

  1. COUNTIF

Across a range, this formula returns a conditional count.

=COUNTIF (range, criterion)

  1. SPLIT

Around a specific character or string, this can divide the text and each fragment is set in a separate cell in the row. When you break the first name from last names use this, in the list of prospects or customers.

SPLIT (text, delimiter, [split_by_each])

  1. PROPER

Within cell this formula allows you to edit text, to case title so you don’t need each entry format manually.

=PROPER (text)

  1. VLOOKUP

VLOOKUP is a vertical lookup. For a key, this formula searches downrange the first column and turns it back in the row to find the value of a specific cell.

=VLOOKUP (search_key, range, index, [is_sorted].

  1. SUBSTITUTE

With the next text in the string, this formula replaces the existing text. For example, in the ad copy sheet, you can replace the product name with a new name.

=SUBSTITUTE (text_to_search, search_for, replace_with, [occurrence_number]).

Read More: https://www.vividads.com.au/blogs/marketing/top-best-affiliate-programs-that-pay-the-highest-commission-of-2022-that-will-boost-your-affiliate-income

Best Add-Ons for Google Sheets

In Google Sheets to work more efficiently, use add-ons. A few suggestions are mentioned below:

  1. DataEverywhere

Any files without exporting or importing, between Google Sheets local databases, Excel and Salesforce share data. It means you can automatically sync the data from Excel into Google Sheets without re-uploading the files in Google Sheets.

  1. Clearbit Sheets

Qualify leads and creates lists of targeted emails. If you are well aware of a tool like LinkedIn Sales Navigator then this add-on is a good option. It plays the same role, in your private spreadsheet only the data lives, it means you don’t need to Sign In every time on LinkedIn if you want to see the data of prospects or clients.

  1. Power Tools Add-On for Google Sheets

Routine operations streamline, like searching particular values and removing duplicates. If you are working on two same spreadsheets you can also compare sheets to check which one is more useful. You can pay for this tool and its starter cost 29.95 per year.

  1. TapClicks

In one spreadsheet, put data on social advertising and paid search and design it into a splendid reporting dashboard that you will show proudly to your director and team managers. This is also a paid tool for a monthly subscription of 29$ per month.

  1. SuperMetrics

For web analytics, social media, SEO and SEM, create a business reporting system. SuperMetrics can turn a huge amount of data into an assimilable form, it also has automatic refreshes, which means you don’t need to pull data manually whenever you want to see your performance abstract. 

  1. Google Apps Script

Beyond the basics, Google Apps Script can broaden the functionality of Google Sheets. With other Google apps, cloud scripting language permits you to create, read, and edit your Google Sheet by an integration. Advanced developers and users recommend this.

  1. XLMiner Analysis ToolPak

With functions to analyze statistics. As ToolPak analysis was originally a Microsoft Excel add-on, after being an Excel user this is an amazing tool if you are getting into Google Sheets.

 Find Best Google Sheets Templates

It is time-consuming to build a new spreadsheet, rather you can find pre-existing templates for Google Sheets that you can use and customize according to your need. For a rigorous analysis of data – on a spreadsheet ability to analyze a large amount of data is not enough, time saving is also a key factor that many people forget to consider. For Google Sheets, templates that are professionally created are time-saving and also increase the efficiency of individuals and teams to analyze data more successfully.

For your marketing efforts let’s explore some sites where you can find ready-to-go templates.

  1. Google Template Gallery

Within Google Sheets, this is Google Sheet free add-on allows you to access many different templates. You don’t need to download any external document or from a publisher make a copy you do not trust. For budgeting, project management and timekeeping templates can get from this tool. The only drawback is that the thumbnails are small and it is hard to look at templates previews.

  1. net

You can easily customize for your need and easily preview as Template.net has a variety of Google Sheets templates available in its library. This tool has no marketing-specific templates in its library while it offers many templates to track campaign ROI like the template for a social media marketing budget for marketers. Through this resource, spend some time finding the most suitable templates that you can use.

  1. HubSpot

For professionals who want to create spreadsheets fast, a library of HubSpot for Google Sheets templates is a rich source. You can access templates for any type of document in this library including marketing dashboard, competitive analysis, the budget of the business, expense report, and many more. You have to personalize the field and plug in numbers. If you are looking for a separate template for each document HubSpot library is an excellent option.

  

You can also download free template kits from HubSpot. For the high-function marketing team, the below list contains designed templates of Google Sheets.

  • Template for Email Marketing Planning: This template o email marketing planning will help you to increase ROI schedule your campaigns, conceptualize, refine, and give you optimized results.
  • Bundle of Free Google Sheets Template: Look through a complete library and one-by-one download the templates, for the creation process of the spreadsheet download this starter kit to begin.
  • Free Templates for Editorial Calendar: For starting your planning for publication calendar, you don’t need paid software. To get started you can use this template. With Google Calendar it can sync so no post is accidentally passed over or goes unpublished.
  • Templates for Free Marketing Budget: For tracking your spending on paid advertising, content, product marketing and much more these customizable marketing budget templates are ideal.
  1. BlockSpring

This is one of the most unique of all of the resources mentioned above. Because these templates are not plain where you can pick and add data. To pull data into your spreadsheet templates of BlockSpring are associated with third-party services. Templates of BlockSpring can do it for you, rather than link your spreadsheet manually to the third-party services. For instance, let’s suppose, the title of the template “Get content ideas on a topic using ‘Skyscraper’ analysis” on your spreadsheet gets you the list of content ideas by using data from Moz and Bing and in this you don’t need to use Bing or Moz. In the suitable field, you just have to put the topic and the spreadsheet will occupy on its own.

You have to take some extra steps to work with its templates, which is the only drawback f using templates of BlockSpring. On Google Sheets, you have to download the add-on of BlockSpring and follow the instructions to use the template.

Google Sheets templates that work best for you, here are some recommendations to try out:

  • For Scheduling Smartsheet Google Sheets templates: It offers you free templates that can help you to plan better your posts on the blog and get a result like the template of the project schedule.
  • Best Google Sheets Templates of HubSpot’s Roundup: In the template library of HubSpot you didn’t find what you looking for in one of the template kits? For every need this post curates also within Google Sheets, you also learn where to find the best templates.
  • For Marketing Buffer Google Sheets Templates: For Google Sheets, Buffer has many different “report cards” that you can track on different social media platforms like Facebook, Instagram, and Twitter your marketing efforts.

Wrapping Up

This guide will help you to start your work with Google Sheets and leverage it for your marketing efforts. Getting updated is the most important thing on your G suite applications since they can update you on daily basis. Every person is use Google Sheets to keep their record for professional or for personal use. Whether it’s an account manager who needs to create an annual report or a teacher who needs to keep a record of every student's performance or an individual who needs to create a to-do list. We tried our best to cover all the basic and advanced features of Google Sheets as well as the free template of Google Sheets.

Back to blog

Leave a comment

Please note, comments need to be approved before they are published.