Connect with us

MARKETING

The Ultimate Guide to Google Sheets

Published

on

The Ultimate Guide to Google Sheets


Google Sheets is one of the best tools you can use to store data, track performance metrics, and create dashboards and reports.

But while it’s a wondrous tool with countless features and functionalities, it can be hard to grasp for beginners — especially if you need to create a marketing report or dashboard, fast.

Not to worry. If you’re looking to get started with Google Sheets, you’re in the right place. In this guide, we’ll be looking at the benefits of Google Sheets and walking you through some of the basics so you can get up to speed quickly and efficiently.

Benefits of Google Sheets for Marketing

Saying that marketers are data-centric people is an understatement. Between return on investment, cost per acquisition, and more, they’re always looking at different metrics and reporting on progress.

It can be tricky to keep all of those numbers straight, though. That’s where spreadsheets come in handy. With a well-organized spreadsheet, marketers can quickly analyze robust data sets and make smarter, more informed decisions about their efforts.

Originally introduced by Microsoft in 1987, Excel was the go-to tool for years for building spreadsheets. But when collaborative, cloud-based tool Google Sheets was introduced in 2012, many users made the switch over to this new (free!) resource.

Let’s talk about the benefits of using Google Sheets for marketing purposes before we discuss more traditional options (like a standard Excel spreadsheet).

  • It’s cloud-based. Cloud-based spreadsheets are automatically saved, stored, and maintained via a remote server. That means that you can access it online from anywhere, any time. Plus: Google Sheets is also available offline.
  • It’s free. No expensive software required — Google Sheets is always 100% free.
  • It works across devices. Modern marketers switch between devices throughout the day — and Google Sheets accommodates that behavior. You can access and edit Google Sheet data from a wide variety of devices (like your smartphone, tablet, and computer).
  • Add-ons are available. We’ll get into the specifics of add-ons for Google Sheets a bit later, but the increased functionality and flexibility of these additional features makes this spreadsheet tool even more versatile and powerful.

You might be wondering: What’s the difference between Google Sheets and Excel? The short answer is: price, collaboration, and flexibility.

While Excel requires a software subscription (which costs about $130), Google Sheets is free for all Google account holders. In terms of collaboration, Excel requires you to save and send a new version when edits are made, while Google Sheets automatically updates a single shared file for all team members. And lastly, because Google Sheets has the ability to accommodate many different integrations, it’s often more flexible than a standalone software solution like Excel.

Advertisement

Common Google Sheets Terms to Know

Next, let’s get familiar with some of the language and common terms used when referencing data within Google Sheets.

  • Column: Columns are vertical cell sets.
  • Row: Rows are horizontal cell sets.
  • Cell: Cells are the single data points within a Google Sheet.
  • Range: Range refers to a selection of cells across a column, row, or both.
  • Function: Function refers to built-in operations for the Google Sheet that can be used to quickly calculate values, to manipulate data, and more.
  • Formula: Formulas combine functions, rows, cells, columns, and ranges to generate a particular end result.

How to Use Google Sheets

Now we’re ready to dive into the more advanced how-to’s and lessons on using Google Sheets.

Understanding the Google Sheets Toolbar

One of the very first things to do is master the icons located in the Google Sheets toolbar. Doing so will help ensure you’re getting the most out of its abilities and that you’re saving time with shortcuts. Take a look at the screenshot below to identify some of the basic icons and their purposes within the toolbar.

google-sheets-toolbar

Image Source

How to Create a New Spreadsheet

When you’re ready to create a new spreadsheet, you’ll just need to follow a few quick steps to get started. Open Google Sheets and click “File” > “New” > “Spreadsheet”.

create-new-google-sheet

Once you create your new spreadsheet, you can start adding data. If you want to work with an existing spreadsheet from Excel instead, you’ll need to import that data into your new Google Sheet.

google-sheets-import-file

From there, your file will be uploaded and automatically converted into a Google Sheet.

Sharing and Protecting Your Data

Once your Google Sheet is set up and populated, you’ll want to think about how to share and protect your data. Data is automatically saved, so you don’t need to worry about that part. You can select who you share your file with and whether they have edit, comment, or view permissions. You can also protect specific data within your sheet so that those who have access can only edit certain cells.

To share a file:

  1. Go to “File” > “Share” (or just click the blue Share button in the top right corner)
  2. Enter the email address of who you’ll be sharing the spreadsheet with
  3. Assign permission levels (such as “View only” or “Can edit”)
  4. Click “Advanced” and select any additional privacy conditions needed

To protect data:

  1. Navigate to “Data” > “Protected Sheets and Ranges”
  2. Choose “Range” or “Sheet” (to protect an entire sheet)
  3. Select the data you’d like to protect
  4. Click “Set Permissions”
  5. Choose to show an error warning to anyone who attempts to edit or customize editing permissions for certain people.

Organizing Data in Google Sheets

Google Sheets is a powerful tool that can hold a ton of data. That means the more data you have, the harder it becomes to find it — that’s when filtering comes in handy. Google Sheets has several filters that allow you to select the data you want to see and hide what you don’t.

How to Hide Data

Google allows you to hide entire rows or columns. This becomes useful when you want to restrict views for sharing, or when you’d like to control the amount of data you’re viewing at any given time. To hide a column, right-click the column you want to hide, then select “Hide Column”. When you hide a column, two arrows will appear on the previous and subsequent columns. Click these to restore the column to full view.=

Advertisement

How to Freeze Rows and Columns

Freezing will create a floating row or column as you scroll and keeps the data in those fields within your line of sight. For example, if you use the first row to label your columns, you can freeze that row so you don’t have to remember what each column is as you scroll.

To freeze a row, select “View” > “Freeze” > “1 row” (or up to X rows).google sheets hack: freeze row

How to Filter Data

What if you’d like to view all of the data in a single column that fits a certain criteria? Apply a filter. Filters allow you to select a criteria and how that criteria applies to your data. For example, you can filter your ‘Site Visits’ column to show all values greater than 1000, or filter your sheet for all cells that have the word “cat”.

To set up a filter, select “Data” > “Create a Filter.” Click the funnel icon that appears in the column, then choose to filter by condition, value, or alpha or numeric order. Your sheet will only display data that satisfies your criteria.

google sheets hack: creating filters

Learn about other filtering options below.

How to Setup and Use Google Sheets Offline

If you need to use Google Sheets offline, just follow the steps outlined here to turn on offline access to your spreadsheets.

Advertisement

Keep in mind:

  • You must be connected to the internet.
  • You’ll need to use the Google Chrome browser (don’t use incognito mode)
  • You’ll need to install and enable the Google Docs Offline Chrome extension.
  • Make sure you have enough free space on your device to save all your files.

From there:

  1. Open Chrome and make sure you’re signed in.
  2. Go to drive.google.com/drive/settings.
  3. Check the box next to “Sync Google Docs, Sheets, Slides & Drawings files to this computer so that you can edit offline.”
  4. To work on files offline from a computer, learn how to install Backup and Sync.

Advanced Google Sheets Tips

Now that you understand the basics of Google Sheets, let’s get into some advanced tips so you can start using Sheets like a pro. These tips are helpful to have in your back pocket … even if you don’t need them just yet.

Conditional Formatting and Heatmaps

Conditional formatting will automatically apply formatting to a cell based on a preset condition. For example, you can select to highlight a cell in red if the value entered is below 100 or reads “Error”.

Heatmaps work similarly in that they apply a color scale to cells based on a median criteria (values greater than the median will appear darker while values lesser will appear lighter). This helps you view values on a gradient and identifies areas of concern on which to focus your attention.

google sheets hack: creating conditional formatting

Array Formulas

Array formulas can perform multiple calculations at once and output a value based on those calculations. Whereas most formulas require you to perform each step independently (e.g. sum one row then add it to the sum of the subsequent row), arrays make calculations much more efficient. This helps when working with large amounts of data.

Macros

The Macros feature records a series of steps that you’d like Google Sheets to repeat on command. For instance, if you work with a new data set each month, and need to manipulate that data in the same sequence each time, then you can record a Macro, and have Google do the work for you.

Advertisement

 

10 Must-Know Google Sheets Formulas

When you’ve entered your data and you need to do some quick number-crunching, basic formulas can help you find the answers you need. Here are a few commonly used Google Sheets formulas to speed up your data analysis.

1. SUM

This formula adds all values within a selected range.

=SUM(range)

2. AVERAGE

This formula calculates the average of values within a range.

=AVERAGE(range)

Advertisement

3. FILTER

This formula returns a filtered version of the source range, returning only rows or columns which meet the specified conditions. This is helpful when segmenting data by year or month.

FILTER(range, condition1, [condition2])

4. FIND

This formula returns the position at which a string is first found within the text. The Find formula is case sensitive.

FIND(search_for, text_to_search, [starting_at])

5. COUNTIF

This formula returns a conditional count across a range.

Advertisement

=COUNTIF(range, criterion)

6. CONCATENATE

This formula allows you to combine values of multiple cells into one cell, e.g., first and last name.

=CONCATENATE(Value1, “ ”,Value2)

7. VLOOKUP

This stands for vertical lookup. This formula searches down the first column of a range for a key and returns the value of a specific cell in the row found.

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

Learn how to do a VLOOKUP in less than a minute.

Advertisement

8. SPLIT

This formula divides text around a specified character or string, and puts each fragment into a separate cell in the row. Use this when you want to break up first names from last names in a list of customers or prospects.

=SPLIT(text, delimiter, [split_by_each])

9. SUBSTITUTE

This formula replaces existing text with new text in a string. For example: You could replace a product name with a new one in a sheet of ad copy.

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

10. PROPER

This formula edits text within a cell to title case so you don’t have to format each entry manually.

Advertisement

=PROPER (text)

For more formulas you can use in Google Sheets, use the search tool on the spreadsheet functions list and find the one you need.

Google Sheets Functions & Skills You Should Know

How to Create a Pivot Table

  1. Select the cells with source data you want to use in your pivot table. Note: Each column will need a header.
  2. In the menu at the top, click “Data” > “Pivot table.” Click the pivot table sheet (if it’s not already open).
  3. In the side panel, next to “Rows” or “Columns,” click “Add” and then choose a value. Note: Sometimes you will see recommended pivot tables based on the data you’ve selected. To add a pivot table, under “Suggested,” select a pivot table.
  4. In the side panel, next to “Values,” click “Add”, then choose the value you want to see over your rows or columns. You can change how your data is listed, sorted, summarized, or filtered.
  5. Next to what you want to change, click the down arrow.
google-sheets-pivot-table

How to Remove Duplicates

  1. Select the cell in which you’d like to remove the duplicated info.
  2. Enter the function =UNIQUE(
  3. Select the cells you would like to pull data from
  4. Close the parentheses. Your function should look something like this: =UNIQUE(A:A)

How to Create a Drop-Down List

  1. Next to “Criteria,” choose an option:
  2. Click “Data” > “Data validation”.
  3. Select the cell or cells in which you want to create a drop-down list. List from a range: Choose the cells that will be included in the list.
  4. List of items: Enter items, separated by commas and no spaces.
  5. The cells will have a down arrow. To remove the arrow, uncheck “Display in-cell button to show list”.
  6. If you enter data in a cell that doesn’t match an item on the list, you’ll see a warning. If you want people to only enter items from the list, choose “Reject input” next to “On invalid data”.
  7. Click “Save”. The cells will show a drop-down list. To change the color of a cell based on the option, use conditional formatting (refer to section above).

How to Make a Graph

  1. Select the cells you want to include in your chart. To label your chart, add a header row or column.
  2. Click “Insert” > “Chart”.
  3. In the side panel, click “Data” > under “Chart type,” choose a chart.
  4. To edit your chart, click “Customize.”
google-sheets-create-chart

How to Merge Cells

  1. Highlight or select a range of cells.
  2. Right-click on the highlighted cells and select “Format Cells”.
  3. Click the “Merge” button in the toolbar, then select the merge option you need.
google-sheets-merge-cells

How to Download Google Sheets

If you want to use Excel or another tool for analyzing data sets with your file, you can easily export the file. Go to “File” > “Download as”. Then decide which format you need.

download-google-sheets

If you need more help getting started, the quickest way to get a crash course in using Google Sheets is to access the free Google Sheets Cheat Sheet, which outlines the basics on:

  • Customizing spreadsheet data
  • Working with rows, columns, and cells
  • Sharing/collaborating within your spreadsheet
  • Saving and protecting your information
  • Utilizing key functions

You can also access the Tips Library for more advice on using Google Sheets.

Where to Find the Best Google Sheets Templates

Building new spreadsheets from scratch can be extremely time-consuming, so don’t reinvent the wheel if you don’t have to. Instead, look for pre-existing Google Sheets templates that you can customize and use for your own efforts. Let’s look at a few different places where you can find ready-to-use templates for your marketing efforts.

1. HubSpot

where to find google sheets templates: hubspot

HubSpot’s library of Google Sheets templates is a gold mine of resources for the professional who needs to set up a spreadsheet quickly. In this library, you’ll find a template for any type of document you might need, including a business budget, competitive analysis, expense report, marketing dashboard, and more. You only have to plug in the numbers and personalize the fields. This option is a great fit if you’re looking for an individual template.

Otherwise, you might enjoy downloading HubSpot’s free template kits. The below bundles contain Google Sheets templates designed to supercharge a high-functioning marketing team.

  • Free Google Sheets Template Bundle: Don’t want to look through an entire library and download templates one-by-one? Download this starter kit to jumpstart your spreadsheet creation process.
  • Free Marketing Budget Templates: These customizable marketing budget templates are ideal for tracking your spending on product marketing, content, paid advertising, and more.
  • Free Editorial Calendar Templates: You don’t need expensive software to start planning your publication calendar. Use these templates to get started. They also sync with Google Calendar so that no post goes unpublished or accidentally passed over.
  • Email Marketing Planning Template: This email marketing planning template helps you conceptualize, refine, and schedule your campaigns to increase ROI and optimize your results.

2. Google Template Gallery

where to find google sheets templates: google template gallery add-on

This free Google Sheets add-on gives you access to dozens of different templates right within Google Sheets. There’s no need to download an external document or make a copy from a publisher you might not trust. In this tool, you can access templates for project management, timekeeping, and budgeting. The only downside is that it’s hard to look at the previews of the templates, because the thumbnails are quite small.

3. Template.net

where to find google sheets templates: template.net

Template.net has a vast library of Google Sheets templates that you can easily preview and customize for your needs. While the library is not as marketing-specific, it still offers plenty for marketers to track their campaigns’ ROI, such as this social media marketing budget template. We recommend spending some time digging through this resource to find a template that works for you.

4. Blockspring

where to find google sheets templates: blockspring

Out of all the resources in this list, this one is the most unique. Why? Because these aren’t plain old templates where you can plug in the numbers and go. Blockspring’s templates are connected with third-party services to pull data into your spreadsheet. Instead of you having to manually link your spreadsheet to these third-party services, Blockspring’s templates do it for you.

Advertisement

For example, the template titled “Get content ideas on a topic using ‘Skyscraper’ analysis” uses data from Moz and Bing to get you a list of content ideas right on the spreadsheet — without you having to actually use either Moz or Bing. You only have to place the topic into the appropriate field, and the spreadsheet will populate on its own.

The only downside to using Blockspring’s templates is that you have to take a few extra steps for them to work. You have to download the Blockspring add-on onto Google Sheets, and then follow a few simple instructions to get the template up and running.

Haven’t found the Google Sheets templates that work best for you? Check out a few roundups we recommend:

The Best Google Sheets Add-Ons

To boost your efficiency in Google Sheets, consider using add-ons. Here are a few suggestions:

TapClicks

Pull your paid search and social advertising data into one spreadsheet, and turn them into beautiful reporting dashboards you’ll be proud to show your team managers and directors. This is a paid tool that starts at $29/month.

Google Apps Script

Google Apps Scripts offers a way to extend the functionality of your Google Sheets workspace beyond the basics. The cloud-scripting language allows you to create, edit, and read your Google Sheet through an integration with other Google apps. Recommended for advanced users and developers. Check out the documentation here.

DataEverywhere

Share data between Excel, local databases, Salesforce, and Google Sheets, without importing or exporting any files. That means that if you’re using Excel and want to automatically sync the data with Google Sheets, you can do so without needing to re-upload the document into Sheets.

XLMiner Analysis ToolPak

Do statistical analysis with functions. Because Analysis ToolPak was originally a Microsoft Excel add-on, this is a great tool for you if you’re getting into Google Sheets after being an Excel user.

Advertisement

Power Tools Add-On for Google Sheets

Streamline routine operations, such as clearing out duplicates and searching for specific values. You can also compare sheets, which is highly useful if you’re working off of two similar spreadsheets. This is a paid tool that starts at $29.95/year.

SuperMetrics

Create a business reporting system for SEM, SEO, web analytics, and social media. Not only does SuperMetrics turn a large amount of data into a more digestible format, but it also carries out automatic refreshes — so there’s no need to manually pull in the data every time you want to see a summary of your performance.

Clearbit Sheets

Create targeted email lists and qualify leads quickly. This add-on is a great option if you’re already familiar with a tool such as LinkedIn Sales Navigator. It fulfills a similar role, only the data lives on your own private spreadsheet, so there’s no need to sign in to LinkedIn every time you want to see prospect data.

Google Sheets is a Must-Have for Every Team

This quick guide to Google Sheets should help you get a running start as you leverage it for your marketing efforts. From more efficient tracking of different metrics to better planning around your content to easier collaboration with your team, this tool can help you make smarter, more data-backed choices that drive meaningful results for your brand.

Editor’s note: This post was originally published in October 2018 and has been updated for comprehensiveness.

business google sheets templates



Source link

MARKETING

6 martech contract gotchas you need to be aware of

Published

on

6 martech contract gotchas you need to be aware of

Having worked at several organizations and dealt with many more vendors, I’ve seen my share of client-vendor relationships and their associated “gotchas.” 

Contracts are complex for a reason. That’s why martech practitioners are wise to lean on lawyers and buyers during the procurement process. They typically notice terms that could undoubtedly catch business stakeholders off guard.

Remember, all relationships end. It is important to look for thorny issues that can wreak havoc on future plans.

I’ve seen and heard of my share of contract gotchas. Here are some generalizations to look out for.

1. Data

So, you have a great data vendor. You use them to buy contacts and information as well as to enrich what data you’ve already got. 

When you decide to churn from the vendor, does your contract allow you to keep and use the data you’ve pulled into your CRM or other systems after the relationship ends? 

You had better check.

Advertisement

2. Funds

There are many reasons why you would want to give funds in advance to a vendor. Perhaps it pays for search ads or allows your representatives to send gifts to prospective and current customers. 

When you change vendors, will they return unused funds? That may not be a big deal for small sums of money. 

Further, while annoying, processing fees aren’t unheard of. But what happens when a lot of cash is left in the system? 

You had better make sure that you can get that back.

3. Service-level agreements (SLAs)

Your business is important, and your projects are a big deal. Yet, that doesn’t necessarily mean that you’ll get a prompt response to a question or action when something wrong happens. 

That’s where SLAs come in. 

It’s how your vendor tells you they will respond to questions and issues. A higher price point typically will get a client a better SLA that requires the vendor to respond and act more quickly — and more of the time to boot (i.e., 24/7 service vs. standard business hours). 

Make sure that an SLA meets your expectations. 

Advertisement

Further, remember that most of the time, you get what you pay for. So, if you want a better SLA, you may have to pay for it.


Get the daily newsletter digital marketers rely on.


4. Poaching

Clients and vendors alike are always looking for quality people to employ. Sometimes they find them on the other side of the client-vendor relationship. 

Are you OK with them poaching one of your team members? 

Advertisement

If not, this should be discussed and put into writing during the contract negotiation phase, a renewal, or at any time if it is that important.

 I have dealt with organizations that are against anti-poaching clauses to the point that a requirement to have one is a dealbreaker. Sometimes senior leadership or board members are adamant about an individual’s freedom to work where they please — even if one of their organization’s employees departs to work for a customer or vendor. 

5. Freebies

It is not unheard of for vendors to offer their customers freebies. Perhaps they offer a smaller line item to help justify a price increase during a renewal. 

Maybe the company is developing a new product and offers it in its nascent/immature/young stage to customers as a deal sweetener or a way to collect feedback and develop champions for it. 

Will that freemium offer carry over during the next renewal? Your account executive or customer success manager may say it will and even spell that out in an email. 

Then, time goes by. People on both sides of the relationship change or forget details. Company policies change. That said, the wording in a contract or master service agreement won’t change. 

Make sure the terms of freebies or other good deals are put into legally sound writing.

Read next: 24 questions to ask ABM vendors before signing the contract

Advertisement

6. Pricing factors

There are many ways vendors can price out their offerings. For instance, a data broker could charge by the contact engaged by a customer. But what exactly does that mean? 

If a customer buys a contact’s information, that makes sense as counting as one contact. 

What happens if the customer, later on, wants to enrich that contact with updated information? Does that count as a second contact credit used? 

Reasonable minds could justify the affirmative and negative to this question. So, evaluating a pricing factor or how it is measured upfront is vital to determine if that makes sense to your organization. 

Don’t let contract gotchas catch you off-guard 

The above are just a few examples of martech contract gotchas martech practitioners encounter. There is no universal way to address them. Each organization will want to address them differently. The key is to watch for them and work with your colleagues to determine what’s best in that specific situation. Just don’t get caught off-guard.


Opinions expressed in this article are those of the guest author and not necessarily MarTech. Staff authors are listed here.


Advertisement

About The Author

Steve Petersen is a marketing technology manager at Zuora. He spent nearly 8.5 years at Western Governors University, holding many martech related roles with the last being marketing technology manager. Prior to WGU, he worked as a strategist at the Washington, DC digital shop The Brick Factory, where he worked closely with trade associations, non-profits, major brands, and advocacy campaigns. Petersen holds a Master of Information Management from the University of Maryland and a Bachelor of Arts in International Relations from Brigham Young University. He’s also a Certified ScrumMaster. Petersen lives in the Salt Lake City, UT area.

Petersen represents his own views, not those of his current or former employers.

Source link

Advertisement
Continue Reading

DON'T MISS ANY IMPORTANT NEWS!
Subscribe To our Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

Trending

Entireweb
en_USEnglish