50+ Google Sheets Tips & Tricks (according to 85 marketers)

Learn the simple to advanced Google Sheets tips & tricks that marketers use to speed up their marketing, SEO and daily work tasks.

If you have a marketing task to do (or any online job-related task for that matter), there’s probably a tool that will help you complete it.

And chances are, it’s a spreadsheet.

Why spreadsheets?

They are versatile. They are free. And everyone can learn to use them.

But that’s just the start…

Google Sheets Tips to Supercharge Your Output

Google Sheets are a powerful tool in your marketing utility belt.

Knowing just a few spreadsheet tricks will make you more efficient + you’ll look around 32% cooler when you show off to your marketing colleagues. 😎

So to find these tips & tricks, I asked marketers (85 of them in fact) how they used Google Sheets.

Here are their tips:

1. Save and collaboration functionality

As an agency of 30+ collaborators on any given task, the built-in cloud-based save and collaboration functionality of Sheets is a major pro; and the ability to never have to worry about ‘clicking the save button’ is extremely convenient—but what if you’re somewhere that doesn’t have Internet connectivity?

Never fear, you can use the ‘make available offline’ option through the settings. Now you can still be productive, dare I say, without an internet connection!

Vincent Bucciachio @ Socially Infused

2. Label everything

Sounds simple but: label everything!

There’s nothing worse than going back to a spreadsheet in a few months time and only half remembering what each value actually means. Sometimes I even add a note explaining how I calculated certain things, just to make sure I never forget.

Alice Corner @ Venngage

3. Use CLEAN & TRIM to tidy your data

If you’re cutting and pasting data from a lot of different sources, and you end up with data that’s messy and has whitespace throughout, you can use CLEAN to remove non-printable/unicode characters and TRIM to remove whitespace.

Cherie @ UpLead

4. Fetch any Image from its URL

Adding images to your Google Sheets is simple. Just key in =IMAGE(“URL”) and replace the “URL” with your image URL, and you’re done!

Md Mohsin Ansari @ Troop Messenger

How to quickly grab images from a URL…

Adding images to your Google Sheets is pretty straightforward, just use this formula:

=IMAGE(“URL”)

Here’s it in action:

5. Solver and/or Goal Seek

Google released a powerful add-on last year called Goal Seek for Sheets.

This is extremely useful for marketing as it allows you to input a value or output you’d like to achieve such as 50,000 revenue and the add-on will then figure out the other values that will get you there.

As an example it might calculate you need a further 20,000 website visitors, or a specific conversion rate increase. It’s an extremely powerful add-on that can save hours of manual work.

Daniel Cuttridge @ Pathtorch

Both are highly under-rated and under-used, but incredibly powerful tools to find solutions in your models.

Goal Seek is the simpler one to start with and it’s useful for forecasting and understanding the nuisances between different ‘what if’ scenarios.

Jack Huang @ Truly Experiences

6. Explore data auto-magically

In the lower right corner, you have a green button “Explore”:

You can use this to research the data in your sheet more thoroughly and efficiently. For example, it creates charts automatically. Easy to visualize trends 🙂

Iris De Geest @ Survey Anyplace

7. Create custom internal processes

Google Sheets power a significant part of our internal processes. Once you learn how to use them, you understand that you can do pretty much anything with them.

Recently, we had to audit a massive website with over 7,000 pages from 8 different Post Sitemaps. As you can imagine, auditing so many pages is not something you can do manually.

This is why we built a Google Sheets template that allowed us to automate the biggest and most tedious part of the process.

We judged pages against 4 metrics:

1) Revenue Generated (for the last 12 months)

2) Organic Traffic (for the last 12 months)

3) Organic Keywords

4) Backlinks

We also took into account the number of Internal Links, but for a different reason.

Based on our criteria, we assigned an Action to each of the pages included in the Content Audit. The Actions were:

• Leave as is (200)
• Delete (404)
• Redirect or Merge (301)
• Manual Review

I’m sure that we couldn’t have managed to perform this audit the way we did if it wasn’t for Google Sheets.

If you’re building processes for your agency—which you should—Google Sheets is one of the must-haves in your technology stack.

Georgios Chasiotis @ MINUTTIA

8. Use conditional formatting to help visualize data

I’m of the mindset that data should be made as accessible and easily understood as possible. That’s why I like to use the conditional formatting function in order to highlight important data points for my team. It really depends on what type of data we’re looking at.

For example, when collecting customer feedback I could highlight positive responses in green, and negative in red. This type of visualization helps to drive home certain trends, particularly for the more visual team members.

Nikola Baldikov @ Brosix

Use conditional formatting for a quicker overview of your data…

The biggest thing I’ve found helpful is sheets is using conditional formatting and assigning colors to items, whether it be an person’s name, or the status of a task. Colors reveal a lot about the data more quickly.

Brendan Hufford @ 100 Days of SEO

Use conditional formatting to help with editorial calendars…

My favourite Google sheets tip is the conditional formatting tool using different colours.

As a B2B and SaaS content marketing agency owner, this feature makes it easier for me to plan our editorial calendar more effectively and efficiently.

This is because it makes it easy for everyone on the team to know the task they’ve been specifically assigned.

Not only that, as the team leader, I’m able to monitor the progress of all the tasks across board.

AbdulGaniy Shehu @ Winsome Writer Services

9. Filter out junk

In order to clean large lists from junk that I do not need, lets say I have a big list of URL’s that I don’t want to all kinds of foreign languages in there, I can use a formula that will mark me all the domains that I do not want, like: .ru .fr .de .en Etc. You name it!

I use the next formula:

=SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$6,A2)))>0

Then I get TRUE or FALSE values, then I filter out all the TRUE (because they have the values that I didn’t want on my list), to get a clean list.

Matan @ Photographer Touch

10. Learn keyboard shortcuts

Learn shortcuts while you work and get rid of the mouse.

Print out a list of the shortcuts and put them above your monitor on the wall. Before you ever do anything using the mouse, look up and find which shortcut to use. Once you’ve learned the shortcut, use a highlighter to cross it out – this will help you more easily find the remaining shortcuts.

In a few weeks you’ll know all of them, not need to use your mouse, and be twice as fast in everything you use google sheets for. This is the first thing I teach new hires at LawnStarter.

Ryan Farley @ Lawn Starter

Here’s some specific Google Sheets shortcuts to help:

Control + D or ⌘ + D to fill in an entire column to a certain cell, instead of trying to drag it all the way down. It took me way too long to figure this out and now I use it every single day.

Ryan McCready @ Venngage

You can switch between sheets with “Alt + Up/down arrow”

Dale Cudmore @ QuestionDB

This is super lazy but I’ve found hardly no one does it!

If you use a lot of sheets within one Google Spreadsheet, you can easily switch between them with a simple keyboard shortcut.

• On Macs it’s: Option + Down arrow to move right and Option + Up arrow to move left.
• For Windows: it’s Ctrl + Shift + PageUp or PageDown to switch between sheets.

Simple and super helpful when constantly switching between sheets!

Joel Widmer @ Fluxe Digital Marketing

11. Track (& organize) guest post submissions

As a guest contributor, I use Google sheets to keep track of guest post submissions. I always put down comments next to the blog name, but the best tip to analyze the spreadsheet and find what I need is to fill colors.

For example, I use green color to mark blogs that have accepted my piece, yellow for pending approval, and red for rejected websites. It helps to scan the file and find blogs. That’s easy, but it works well for me 🙂

Hugh Beaulac @ MC2 Bid 4 Papers

Google Sheets can be the real-life savior for organizing your marketing efforts.

Here, at TeamBuzz, we use it for tracking our outreach efforts. We keep records of the blogs we’ve pitched for guest posting as it makes it very easy to spot which ones should be contacted next or followed up with.

This really improved the flow of our outreach activities. The filtering option was what helped us stay on top of our pitching game. We just used the right names for the filters, such as “Pitched”, “Follow-Up”, “Published” and got to work. It was an easy improvement, but a very helpful one!

Arek Ponski @ TeamBuzz

12. Combine with Zapier to track guest post opportunities

Other than typing “sheet.new” in the URL bar (which I use almost every day) — I receive lots of cold emails about guest post opportunities on our blogs, so I created a Zapier workflow to help me go through it.

It creates a new row in a google spreadsheet whenever I receive an email with certain keywords such as “guest post” or “writing for your blog” so it allows me to bulk evaluate several opportunities at once.

Raul Galera @ Candy Bar

13. Create a new spreadsheet by typing sheets dot new

(this was a popular one!)

Introducing a ✨ .new ✨ time-saving trick for users. Type any of these .new domains to instantly create Docs, Sheets, Slides, Sites or Forms ↓ pic.twitter.com/erMTHOsdyH

— Google Docs (@googledocs) October 25, 2018

I’m surprised a lot of people don’t know about this, but if you type “sheets.new” in your browser and hit enter you’ll end up creating a brand new Google Sheet. This also works for Google Docs & Slides too! Plus, it’s satisfying in a weird way.

The true testament to my laziness is using this shortcut every time I want to create a new doc, sheet or a slide. (I’m too lazy to even type sheets.google.com in my browser)

Aditya Sheth @ Venngage

Taking sheets.new even further…

I’m obsessed with Google’s handy shortlinks.

For Google Sheets, all you have to do is type “sheet.new” (or “sheets.new” or “spreadsheet.new”) in your browser to open up a new spreadsheet.

I’ve taken this one step further, turning these shortlinks into clickable buttons on my Chrome bookmarks bar to save even more time when launching new spreadsheets!

Maddy Osman @ The Blogsmith

14. Track backlinks

This comes in useful when we’re building links for clients who already have an extensive backlink profile or have a person in-house also building links via outreach.

We set up a spreadsheet when we start building target lists of websites we’re going to reach out to and incorporate a VLookup function.

We’ll have a sheet showing links they already have, or the list of the in-house link builder, and if we add a site already on either of those, the cell will turn red.

That’s then an indication to myself and my team NOT to reach out to that website. Stops up from continually reaching out and getting links from the same sites, or confusing matters by clashing with the other guys outreach.

Amit Raj @ Amit Digital Marketing

15. Guest post prospecting + project tracking

We use Google Sheets for prospecting websites (for guest posting) and for project tracking. Our database of prospected websites is huge, and it wouldn’t work well without a few things: filter views being the first one.

As we all collaborate on one main sheet, we need to be flexible in using it. Filter views enable everyone to have a personalized view with differently organized data, without corrupting anyone else’s workflow.

Every user has its personal filter view saved to use it as they want, which means one person can prospect new blogs while others can use filter views to filter websites by project, niche, etc.

Also, there are a few upgrades that we use: trim to root formula and duplicate detection.

Here’s the trim to root formula that will trim the URL in the B2 field and return the trimmed URL to another field:

=trim(REGEXEXTRACT(REGEXREPLACE(REGEXREPLACE(B2,"https?://",""),"^(w{3}\.)?","")&"/","([^/?]+)"))

Why we use it?

To automate and speed up the data entry. When manually prospecting websites – you will likely land on one of their blog articles. With this formula, you can just paste the page you landed on in the B2 field, and this formula will automatically insert the website’s shortened homepage URL in the A2 field.

However, to be truly productive with your prospecting, you need to make sure you are not going through the same websites again.

We have a system for this but also a neat visual trick – we use conditional formatting which automatically colors the data if it already exists in the database. That way, the person adding new websites to the list will not waste her time if the website turns out green. She will delete it and move on.

Marina @ Fortis Agency

16. Make use of of pivot tables

The use of pivot tables has allowed us to create streamlined reports and uncover trends and tactics that otherwise would be impossible to identify via the use of traditional reporting tools.

Nick Dimitriou @ Moosend

Combine keyword research with pivot tables…

This tip applies to keyword research against competitors.

When exporting a list of keywords into a CSV from your keyword tool of choice, include the URL the keyword is ranking for in the export. (Popular tools such as Ahrefs or SEMrush both allow you to do this), you should also include metrics like volume, traffic and keyword difficulty.

When importing this data initially, it is likely that you’ll have to clean it up a bit. Pick and choose the columns you want to keep and analyze, and remove the rest. Once you have a clean spreadsheet, select all and create a pivot table.

For your “Row Labels”, add the “URL” column as the top label, and have the “Keyword” column under that. As the “Value”, include the metrics you want to analyze. Again these are usually things like Volume, Keyword Difficulty and Traffic. Sort all of this in descending order.

What this will give you is collapsible page by page breakdown of the competitors site, with the top ranking keywords driving organic traffic/volume along with with the difficulty of each.

Steven Orechow @ Trunksly

Find internal linking opportunities with pivot tables…

Although a well known one, I still believe pivot tables to be underused.

I’ve worked in several marketing teams, specifically in house SEO resources that don’t tap into the power of pivot tables. I particularly love using pivot tables to analyse internal linking opportunities and creating on-site ‘anchor text’ clouds, as well as to see key pages that may be missing out on vital link authority because of a lack of internal links.

That’s just the tip of the iceberg, they really can be used to analyse all sorts of data and figures perfect for marketing teams.

Charlie Morley @ Movement SEO

17. Automate reporting (with Supermetrics)

A big time saver for our team is adding Supermetrics for Google Sheets, it just makes it so easy to report and monitor key metrics for our clients. Using tools like Supermetrics are a big time saver.

We also use many of the Google sheets provided with The Blueprint Training which save our team many hours for specific SEO tasks. I highly recommend SEO team look into the course and resources provided.

James Norquay @ Prosperity Media

Use the Supermetircs Google Sheets add on. It is the glue that binds everything together. My reporting has transformed since I made the choice to pick it up.

Ronan Kelleher @ Local Rank Tank

Quickly find top performing articles with Supermetrics…

When evaluating the performance of articles on the site, to quickly ascertain the top-performing articles (versus other articles on the site) factoring in a number of metrics from sessions to time on page etc, we connected Google Sheets to Google Analytics via SuperMetrics.

And then made use of RANK.EQ to grade individual URLs based on their performance into which percentile they were – and then based on traffic v performance metrics to classify URLs that were great, high traffic but low behaviour metrics, and those not performing well at all so we can make quicker decisions around were to deploy effort, and take learnings of what works for future content.

Daniel Taylor @ Dan Taylor

18. Use REGEX formulas to manipulate data

Google Sheets supports three main REGEX formula types:

• REGEXEXTRACT
• REGEXREPLACE
• REGEXMATCH

Using these you can artfully manipulate just about any data you have to get it to do what you want. Oftentimes a quick REGEX formula can save hours and upon hours of manual work.

Matthew Kay @ Matthew Kay

19. Get fancy with your formulas

Some nice formula work from Yuri here:

=sum(INDIRECT("A1:"&ADDRESS(ROW()-1,COLUMN(),4)))

Sum everything in column above this cell, works even if you add new rows later. Useful if you really want the total to be displayed below the values. Without this, the sum() function will not “expand” to include the new rows and simply using something like B2:B will result in circular dependency. So use this instead.

=query(arrayformula(iferror(if(match(B3:B,A3:A,0)>0,""),B3:B)),"select Col1 where Col1<>'' order by Col1")

Check if items in column already appear in another column. For example, you have a big list of past clients and a smaller list of clients from this month. You want to only see clients that are NEW, i.e. that bought this month for the first time.

=unique(query({C3:C;D3:D;E3:E}, "where Col1 <>''"))

Get unique items from multiple columns. Pretty much does what is says. Thanks to query() you get a column with unique values where multiple columns where used as source. Can get really big if you have lots of columns.

Yuri B @ Yuri Burchenya

20. Extracting custom data (aka the data you actually need)

Extract domain name from email…

=index(split(A1,"@"),2) Get domain name from list of emails.

Justas Markus @ GetFound XL

Extract names of websites in another column…

I use REGEXEXTRACT formula to extract names of websites in another column in case I have a lot of long URL addresses in my sheet. This would be the formula for getting a home page from a long URL address.

If you need to change cell F2 use this formula in another cell:

=REGEXEXTRACT(F2,"^(?:https?:\/\/)?(?:[^@\n]+@)?(?:www\.)?([^:\/\n]+)")

Angelina Harper @ Really Simple Systems

Turn full names into first + last…

=SPLIT(text, delimiter) – sometimes you need to take full name and split it to first name and last name. This is how! =SPLIT(A1, ” “)

Steve @ PageDart

21. Personalise email outreach

I do a lot of outreach using Google sheets. To personalize my emails further, I use an API called Gender API (which has a google sheets add on) to tell me the gender of the contacts. Using this I segment my list and craft a different message for each gender!

Shaurya @ Attention Always

Personalize cold emails…

Whenever you are running a cold email outreach campaign you could either personalize a short note for each recipient in a Google Sheets column along with other prospect data prior to uploading the list into your preferred cold email tool, so that you can simple insert a custom field instead of manually customizing each individual message.

Nolan Clemmons @ Clemmons

22. Google Translate for international clients/websites

The Google Translate formula can be a huge help when working with international clients and websites.

From an SEO perspective, I find it to be great when translating large amounts of metadata into a templated approach across a range of languages. I would, of course, always have sign off from a native or fluent speaker before pushing live, but it is an efficient way to make language changes at scale.

Charlie Morley @ Movement SEO

Using the =GoogleTranslate function when you’re sense checking the work of a freelancer doing keyword translations for you.

I do a lot of work for clients abroad and, although it the most grammatically correct, being able to quickly scan down a translated version of their work adds another level of QA to the work and helps me sleep at night. Any that don’t make sense I simply check with them.

Andrew Chadwick @ Digital Quokka

The Google Translate function – for an international client, I was tasked with finding the international version URLs of all their English blog posts to properly implement hreflang for the site. I simply stripped the slug from the full URL, translated it using google translate, then did a VLOOKUP to match them, or manually match them if the translation was a bit off.

Saved me a lot of time considering there were over 200 blog posts, with at least 5 international URL equivalents.

Patrick Wareing @ Patrick Wareing

23. Share copies of your sheets

Add /copy at the end of a document that you want to share globally (public doc) and you want to make it available for users to download.

David Kaufmann @ SEO Alive

24. Easily collaborate with your team

When working in teams of a lot of people on the same sheets (we are 6 people at our agency), we love using the “filter view” option so everyone can have their own view and use each filter independently without affecting what others see.

Alan Silvestri @ Growth Gorilla

When working in a Sheets document with multiple collaborators, I enjoy adding contact info with assignments to the relevant people. This allows for rapid task list management and also provides public accountability for roles and responsibilities.

Riley Adams, CPA @ Young and the Invested

Using them for collaborating with freelancers! At Genbook, we work closely with freelancers on various projects. And while the communication is regular, often times some bits and bobs get missed because the information wasn’t shared as a comment via the spreadsheet.

Not anymore! However, by being able to send an email right through the comment section by using (+) and email simplifies it for all. Freelancers have all the info in one place, get notified instantly, and they can action the missing bits almost immediately. Win-win for us and them!

Taru Bhargava @ Genbook

25. All hail the VLOOKUP

Vlookup is a godsend when it comes to bringing multiple data sources together into one sheet.

Wyatt @ Bibium

Combining multiple data sources…

VLOOKUPS are one of my most used formulas.

Initially they can be a bit daunting to learn. But once you’ve got the hang of them, you’ll find yourself using them all the time.

You can use them to pluck out the data you need from bulky exports or combine data from different sources.

One thing I like to do is:

• Export top performing pages from Ahrefs
• Pull in Search Console data at the URL-level
• Using the humble VLOOKUP you can then combine this data.
• The end results: URLs + top ranking keyword + CTRs. Nice

Jake @ JSVXC

26. Use IMPORTRANGE to combine documents

If you need just one sheet’s data from a massive sheets doc, the importrange function is great.

Wyatt @ Underdog Coffee

It was too long in google sheets before I figured out the IMPORTRANGE function. For marketing reporting we would have someone move data from one spreadsheet (like internal marketing team reporting) to another (like client facing reports) all the time.

With IMPORTRANGE you can connect any spreadsheet anywhere in google docs. Saved us hours every month on duplicate reporting. Build it once and import it anywhere. That was a game changer

Brice Gump @ Major Impact Media

27. Automatically detect + translate

Automatic detect language and translate with:

=GOOGLETRANSLATE(A2, DETECTLANGUAGE(A2), "en")

This works great once you imported e.g. title or h1 into a sheet with =INDEX(IF(A1=””, “”, IMPORTXML(A1,”//title”)),1,1)

Peter @ Peter Thaleikis

28. Create your own templates

If you use a Google Apps Gmail account (not a regular, plain Gmail address), you can set up templates in your own template gallery from your favourite Sheets.

You just have to add any Google Sheet to the Template Gallery and you can open them when starting a new project. This is a good way to keep things organize and keep the “master” template up-to-date instead of searching for the latest versions in Google Drive all the time.

Combine it with the “sheet.new” command in the browsers URL field and you can save even more time when creating new Sheets.

Gabor Papp @ The Pitch

29. Connect your sheets to GA

A really helpful hack I’ve come across is connecting Google Analytics with Google Sheets. You can do it using the Google Analytics Spreadsheet Add-On.

Makes understanding and reporting on data super easy. Also, it’s a huge time saver. You don’t have to export reports to Google Sheets on a weekly basis just to pore over your data/results. You also don’t have to spend hours of your day inputting data into larger spreadsheets to track data!

Cécilien Dambon @ Venngage

30. Get GSC data in your spreadsheets

Use sheets Addon Search Analytics for Sheets to create daily, monthly back-up archive of important data from Google Search Console. This can be easily configured to run automatically creating a quick and easy back-up in a nice spreadsheet.

Stephen Sumner @ Optimise Agency

Download and install search analytics for sheets Google sheet extension for automatic Google Search Console backups!

Blake @ Inbounda

31. Convert spaces to hyphens in a new column

When dealing with a large number of contacts, it is easy to mix up individuals with two last names and individuals with a middle name. You can use Google sheets to convert spaces to hyphens in a new column, so you never lose a double last name.

Enoma @ Enoma

32. Scrape sites with IMPORTXML

Using IMPORTXML function to scrape sites when I don’t have screaming frog available (such as if I am working off a chromebook or if I simply can’t be bothered to run a crawl).

The following are useful xpath functions:

• Title Tags: //title/text()
• Meta Descriptions: //meta[@name=’description’]/@content
• Canonical Tags: //link[@rel=’canonical’]/@href
• H1 Heading(s): //h1/text()
• H2 Heading(s): //h2/text()

Andrew Chadwick @ Digital Quokka

33. Create a calendar for content & social posts

Plan keyword research content…

I use Google Sheets as my keyword planner and content calendar. It’s a great place to centralized all of your content marketing planning, broken out into buying consideration stages.

Levi Olmstead @ 2nd Kitchen

Create social media marketing calendars…

We use Google Sheets to manage our social media marketing calendars with clients. The beauty is in the collaboration.

We set up a calendar within the sheet, share the sheet with all relevant parties, and then use the sheet to collaborate. We create a section for new content ideas, what our competitors are doing, and ‘Metrics that Matter’.

The clients LOVE it and it creates a sense of organization for our social media marketing that we have never had before.

Robb Fahrion @ Flying V Group

34. Make use of tabs

Use tabs to show the data processing steps you have taken as part of your workflow.

For example, one tab for all prospects, the next tab for all prospects minus low quality email scores, and another tab for other layers of filtering.

This way you will end up with a sheet that is easy to read and less mistakes compared to trying to filter everything on one sheet.

Benjamin Boman @ Benjamin Boman

35. Use the Query function (Google Sheets secret weapon)

My favourite thing about Google sheets is its Query functionality, it is so powerful.

Syntax: =QUERY(data, query, [headers])

We have used this to build out all of your Digital PR worksheets which include things like editorial calendars all the way through to keeping on top of brand mentions and tracking manual outreach.

Then using the Google Sheets Query we are able to build out a master tracking template importing separate client sheets all into one.

We achieved this by combining the query function with importrange function. On top of this we can use the query part of the query function to identify the Col’s we only want to import meaning we only have data we truly need.

For example:

=QUERY(IMPORTRANGE("sheet_url","'sheet_tab_name'!A1:E"),"Select Col1,Col2,Col3,Col5 where (Col4 ='Yes') AND (Col5 is not null)")

On top of this we have the ability to also only import data when conditions are meet. As you can see in the query above the row would only import if Col4 string is equal to “Yes”. We took this a step further to then added an extra condition with the AND operator to say if Col5 doesn’t have any text in it don’t import the row.

This has been great for aggregating data into one sheet where we can then analyse all of the data in one place.

Elliott Davidson @ Contrast Digital

Data analysis with Query + SQL…

Learn to use the Query function.

If you do a lot of data analysis, then the Query function can really help to organise your raw data in a format that is easy to understand.

If you understand SQL, then you’re already more than half way there. In the past I’ve used the Query function to do things like create ‘top ten’ tables, automatically sort data by highest to lowest and group various data together.

There are other ways to do most of the things that can be done with the Query function, but the Query function just makes it so much easier and faster.

Rashed Khan @ Pepper Agency

36. Make your spreadsheets look good

Make your spreadsheet easy on the eye…

Visualization is key: so change column lengths, colors, freeze rows, etc. so that you can easily digest the information in the sheet.

Trevor Sookraj @ Trevor Sookraj

37. Remove duplicate data

Set yourself up to be able to filter and organize the data however you want. I utilize filtering and the “Remove Duplicates” add-on. This gives me the ability to filter and remove any unneeded data quickly.

Liam Barnes @ Directive Consulting

Add your company logo to your sheets…

I would always recommend to add your company logo if you are creating a spreadsheets to share with clients or customers. It will help you to improve your brand awareness 🙂

Bogdan @ Visme

38. Connect Sheets with Google Forms

Here, at Digital Olympus, Google Sheets are one of our favourite and most useful tools (thanks God it’s free, too). We use it for several things, but it helps us the most with organizing our content, as well as, conducting outreach activities.

One cool tip that saved us plenty of time when preparing roundup articles was the feature that allows one to connect Google Sheets with Google Forms.

When creating a form with questions to be answered by the experts, we choose to save the gathered responses in Google Sheets. It makes it really easy to keep all the information neatly organized and in result, speeds up the process of putting together an expert roundup. I’d recommend it to any blog editor who wants to save time!

Kas Szatylowicz @ Digital Olympus

39. Filter out negative keywords

It’s called negative keywords cleaner as I have programmed it by myself. It looks like this:

=filter({{Aux!C2:C},{Aux!E2:E}},Aux!D2:D = true) & =unique(filter('Master Negative Keywords'!A:A,'Master Negative Keywords'!A:A <> ""))

In short – I know it’s a bit complex right?

Basically what that does it’a removing all the negative domains and URL’s from my TAB1. In TAB1 I have the 100 URL’s taken from the Google by given country for a specific term. I can update all the domains in real time by updating the TAB2 and TAB3 will show show me clean data.

That means I can have the best results for the outreach for a specific keyword in seconds.

Milosz @ Milosz Krasinski

40. PR reporting with Mention.com, Sheets & Zapier

Working with a smaller company who had an active in-house PR person, but not the budgets for the larger reporting tools – so we connected Mention.com to Google Sheets via a Zapier connection.

Then using VLOOKUPs and COUNTIFs had it present data in such a way that you could run off a report very quickly – or in the case of the “dashboard” we built on one sheet report (at board level) the activities performed and the results achieved in minutes – rather than a couple of hours as was previously taking te person.

Dan Taylor @ Salt Agency

41. Visualize recent drops / increases

Export Organic Keywords -> Movements data from Ahrefs and create a Google Sheet and use that data to create a pivot table to visualize recent drops / increases.

Gives you a great overview when troubleshooting algorithm updates since Ahrefs doesn’t include seasonal data in their estimated traffic.

Axel Hansson @ Viseo

42. Spy on your competitors

To keep tabs on what content your competitors are publishing, use IMPORTFEED or IMPORTXML to grab the RSS feed of their CMS.

e.g. =IMPORTFEED("https://blog.competitor.com/rss.xml")

Ryan Shevin @ Tranzact

43. Master Google Sheets functions

I truly enjoy working with Google sheets, which is awesome because my work is tightly connected with Google docs.

So, learning all about the shortcuts is a must for people like me. Google sheet shortcuts gradually save my time and allow me to focus on more vital issues. My favorite commands though are:

• Ctrl plus semicolon key (for inserting the current date)
• Ctrl plus pageUp/pageDown (for moving between the sheets)
• Ctrl plus Shift plus V (for pasting plain text only)
• and lots more which I constantly use.

But the best tip while working with Google sheets that I could share with you is using functions. They are truly amazing and help produce impressive results.

For instance, I usually use the =REGEXEXTRACT function to get the domain from a URL and this one =VLOOKUP to find the data (if any – true/false) in another sheet. And of course, the =UNIQUE formula.

There is an easy way to indicate the duplicates – using the UNIQUE function. Basically, the UNIQUE formula looks at a range and pulls out all the unique values from it.

It’s beneficial for those who want to speed up their workflow working with a considerable amount of data.

For example, when I have an extended range of prospective opportunities generated from different sources, it sets aside repeated data which effectively slows things down.

So, if it weren’t for the UNIQUE function, I would probably spend much more time on reviewing opportunities (keywords, websites, emails) than I do now. And the better part about this function is that it works for both numbers and text.

Oksana Chyketa @ Albacross

44. Create a simple checklist

Create a Checklist by adding a Checkbox because I love Checkboxes 🙂

If you’re using Google Sheets to coordinate a project, then you can use Google’s new checkbox feature to sharpen up your to-do lists.

Click on “Insert”, then “Checkbox”, and you’re all set! This is so useful tool for tracking tasks.

Jitendra Vaswani @ Bloggers Ideas

45. Push data into Google Data Studio

Pulling data from Google Sheets into Google Data studio has been the biggest hack for me. I love living in spreadsheets but when I’m presenting data cross-departmentally, it needs to be less crowded and more visual.

Shannon Maloney @ SoapBox

46. Use the Sparkline function

My favorite tip on the spreadsheet is the Sparkline function. It’s quite often for me to deal with rows and rows of numbers, sometimes it’s quite hard to decipher what’s even going on there.

Creating a chart is overkill in the Spreadsheet, while all I need is just to take a quick view of the bulky data in the rows.

With the Sparkline function, it gives me a visual line chart (just like a snapshot), which is tiny (they can fit into a single cell). And of course, it’s also dynamic, so if I change any data, then the visual is going to as well.

This Sparkline function is extremely versatile for keeping pace with my marketing goals. Now it’s much easier to take a quick look at the progress my business has made. Such us:

• Follower growth on social media
• Traffic growth
• Clicks from social accounts

Tips: All you have to do is to type in equal sparkline, then select the cell you want to include in that data. Press enter, and there you have it. For example, if your data is in range A1 to A10, the formula would be:

=sparkline(A1:A10)

Andre Oentoro @ Breadnbeyond

47. Use CONCATENATE to track UTMs

One of my favorite and most used Google Sheets tips is to use the Concatenate formula to build an automated UTM tracking template.

It’s as simple as listing the parameters of the UTM link (like utm_campaign=, utm_source=, etc.) and then use =concatenate in a cell to join the strings into a single link.

You can even collapse (hide) the rows with the parameter tags to get them out of the way and just leave out the rows that you need to enter when generating a UTM link.

It’s a very neat way to build UTM links without bothering with writing every single parameter tag manually. The spreadsheet, also helps you keep track of all of the UTMs that you’ve created in the past.

Kalo Yankulov @ Encharge

Using =IMPORTXML to pull data from YouTube into sheets to run ROI calcs. on influencer marketing.

Gareth Harry @ GR Digital

48. Fix up keyword cannibalization

The best Google Sheets tip I have for marketing is the following:

Use this sheet template from Ahrefs:

We put our Ahrefs data in this sheet and it will automatically find pages which are competing with each other. In other words, we prevent keyword cannibalization. If you already use Ahrefs, it’s a no-brainer to use this sheet template at least once per month.

Mile Živković @ Chanty

Checkout the full keyword cannibalization post over at Ahrefs.

49. Save time (& money) with mail merge when doing outreach

Mail merge to save time and money when outreaching.

I usually do it in Word and Excel but you can save time and avoid using an expensive outreach tool by using the mail merge feature, it basically allows you to use a template and add “merge fields” (fields that will change depending on the information on the sheet) by combining Word/Excel or Google Docs/Sheets.

(You will need the Mail Merge with Attachments addon)

Basically, you will use the sheet to provide the different names/attachments/links and then create a template tagging those dynamic fields. You can then run the mail merge on your list of contacts and you will be set, saving loads of money in tools like Buzzstream and such.

If you struggle there are loads of tutorials of how to configure it.

Juan Mora @ Juan Mora Romero

50. Use data validation + conditional formatting in content calendars

At Tidio, we use Google Sheets for our content calendar. In order for the whole team to stay up-to-date with what’s going on with our blog and guest posts, we keep an open Google Sheet where everyone can contribute their articles ideas, as well as, check the writing progress.

One of the coolest Google Sheet feature that we use is simply “data validation” and “conditional formatting” which saves us plenty of time.

We simply create a list of elements, such as “Pitched”, “In Progress”, “Published” etc, and assign a different colour for each of them.

When updating the sheets, instead of manually typing the status change, we just choose it from the drop-down menu. Subsequently, each cell is coloured with its assigned colour (for example: “Published” is green) which makes it easy to track the progress.

Tytus Golas @ Tidio

51. Monitor Tweets to find questions to answer

I’m a Zapier consultant and I have a zap setup that adds tweets with the word “Zapier” in, as a new row on a Google Sheet. Then, every morning over coffee, I use this to find questions to answer and interesting conversations to jump into. I’ve won clients before doing this.

Andrew Davison @ Luhhu

52. Use (pre-made) Google Sheets templates

And finally (+ a shameless self plug here), go check out all the awesome Google Sheets templates that are available.

There are lots of generous marketers who have made spreadsheet templates to help speed up tedious jobs like tracking analytics, planning content marketing or doing keyword research.

Phew. That’s it.

Now go forth and use ALL the Sheets tips!