A report by Experian found 95% of C-level executives believe that data is an integral part of forming their business strategy. But regardless of whether you’re optimizing marketing campaigns or assessing your business’ finances, you’ll need a spreadsheet to house that data.
And you’ll need to know how to manipulate that data too.
To help you with that, we teamed up with the research team at Databox to ask marketing spreadsheet experts what their favorite spreadsheet functions are and how they use them.
Even though these functions exist in most spreadsheet programs, the majority of our respondents prefer Google Sheets over other tools:
No matter your tool of choice, you’re looking for a simplified way to organize, use or analyze the data in your spreadsheets… Right?
I get it.
Our poll also found 42.5% of spreadsheet managers are executives or business owners, and over 15% are creative marketers:
You’ve got more important things to do than spend your entire day in a spreadsheet.
That’s why we asked 30 experts to share their favorite Google Sheets or Excel functions. They’ll come in handy whether you’re reporting on sales, analyzing SEO performance—or anything in between.
According to PeopleForce’s Eugenia Evoyan, the concatenate function allows you “to join values of two cells together in a single cell.”
The team at PeopleForce put this into practice when they’re analyzing traffic sources for their website: “In digital campaigns it is necessary to know how much traffic comes from which campaign,” Evoyan explains. “You distinguish it with UTM, a piece of code attached to a custom URL in order to track a source, medium, and campaign name.”
Storing all of that data in a single spreadsheet is enough to send me into a panic.
But Evoyan uses the concatenate function by adding their domain name “in one cell and UTMs of campaigns in a column.” This usually ends up looking like “($A$1:$B2), where A1 is the cell with my target link and we fix it with $$ signs, and B2 is the UTM of campaigns.”
Daryl Burrows of Six & Flow is a fan of this function, too—only he uses concatenate to improve his PPC campaigns, since Burrows “always learnt to build PPC Search account at their base level in Excel and still find this the optimal way in terms of efficiency.”
Burrows continues: “We centre our account builds around the themes of the site navigation logically, and using concatenate to combine strings from research (i.e Google Keyword Planner, SEMRush, Ahrefs etc.) for naming conventions and keywords.”
Overall, Burrows claims this Excel function “make[s] the job a lot less time consuming!”
|
Related articles How to Become a Customer Data Wizard Using the ‘Transform Data’ Tool The Complete Guide to Data Cleaning How to Merge Duplicates in HubSpot and Salesforce and Keep them Syncing |
How much time do you spend counting cells in your spreadsheet? Take a leaf from Tamas Torok (of Coding Sans)’s book, and use the COUNTA function to “count only non-empty cells.”
Torok’s team regularly use the COUNTA function when they analyze survey data: “We published a survey with multiple choice questions. Among the options, there was "other" where participants could specify their answer.”
Torok explains this extra field meant “most of the answers were different and when creating the chart,” which caused problems because “I only wanted to know how many participants selected the "other" option.”
Luckily, Torok used the COUNTA function to solve this problem, and view survey results clearly.
“COUNTIFS is a function that lets you efficiently segment your data,” explains Daniel Lynch of Empathy First Media, “and get a count of the number of cells within a particular range that meet provided criteria.”
Lynch uses this function to “help monitor conversions or run quick reports on CSV file exports.”
“I recently exported a csv file from my Hubspot CRM and used this functionality to quantify particular traits that I merge with additional data from other marketing platforms,” Lynch summarizes.
You don’t need to spend hours manually segmenting the data in your spreadsheet. “FIND allows you to check if a cell contains the text you're looking for,” Ladder’s Michael Taylor explains.
“You first input the 'find_text' (the text to find), then 'within_text' (normally a reference to a cell), and finally the 'start_num' (default is to start looking from the beginning). It returns the character number the text appears at if found, or a #VALUE error if not found.”
The team at Ladder regularly use the FIND function—but have an advanced hack to maximize the function’s value.
“Where it really gets powerful is in combining it with LEFT, RIGHT and MID formulas to define rules for automatically categorizing your campaigns.”
Taylor continues: “For example at Ladder we use the naming convention LAD-a1-m1-c1 in our campaigns, with a1= audience 1, m1 = message 1 and c1 = creative 1. If you wanted to extract just the message ids (m1, m2, m3... m100, etc) from a spreadsheet you could write a formula like =MID(A1,FIND("-m",A1)+1,FIND("-",A1,FIND("-m",A1)+1)-FIND("-m",A1)-1), and drag it down from A1 cover all of the rows (assuming the campaign name is in column A:A).
“This looks complicated but all it does is find the start of the string "-m", pass that character number into another FIND as the start point to count to the next "-" and then MID extracts whatever id number is in the middle (m1, m2, m3... m100, whatever is there it works on arbitrary lengths).”
Using the IF formula can help automate your data analysis—not to mention, save time.
Tettra’s Kristen Craft explains: “This function lets you perform one action if a number meets some criteria and another action if the number doesn't meet the criteria.”
So, how do you use the IF function in-practice?
“I recently used this when calculating the variation in site traffic and the variation in new account signups,” Craft explains. “I wanted to determine if (during weeks when we had a smaller than normal number of new accounts) if it was because we'd had less traffic than usual. It's a fast, easy way to spot trends and compare different pieces of data.”
Alex Robinson’s team at Team Building Hero, however, used “IF statements in marketing project management.”
They have a spreadsheet containing the tasks each team member is expected to complete on a daily, weekly, monthly and quarterly basis.
“If the cell is blank nothing happens. If the team member enters an "x" next to a specific task then the system considers it complete and another cell automatically becomes a small trophy emoji (or a frown if it is incomplete).”
Robinson says: “The goal of this formula is to add a little joy to task management so team members are encouraged and excited to keep the sheet updated.”
...But it’s not just productivity and up-to-date sheets happening as a result. “Anecdotally, I've noticed more employees do fill this out now and we are growing and getting more done as a company!,” Robinson concludes.
When we asked Nextiva’s Yaniv Masjedi for his favorite Google Sheet function, Masjedi’s answer was simple: IMPORTRANGE, because “this Google Sheet function allows you to bring in data from other spreadsheets.”
...So much so, Masjedi thinks the function is “incredibly useful when you are working with multiple, specialized team members. Maybe one team member owns email, another calls, and yet another social media. Clearly, you can't have one spreadsheet with 73 tabs on it.”
Masjedi continues: “So, import range lets team members work independently–on their own spreadsheets–while still being able to work with others' data. You do need permission from a spreadsheet owner, but that's as simple as granting "Editing" privileges.”
The IMPORTRANGE function can be used for contacting sales prospects, too.
Here’s Agata Gruszka from Zety explaining how she uses it: “I extract our outreach contact lists from various sources and need to transfer them to one file in order to compare the data and possibly- remove the duplicates.”
“Importrange lets me do that within seconds, allowing me to keep the desired formatting.”
“During the SEO task, we marketers need to analyse competitor pages. The analysis can be checking their meta desc, title, KW presence etc,” writes Swaraj Sahu.
However, Sahu uses the IMPORTXML function: A tool that helps “turn Google Sheets into a scrapper.”
Sahu “used IMPORTXML to analyse competitor page title. For example, your A2 has a competitor page abc.com/keto-diet. Now, I want to scrape the page title. Putting this function on B2 will give you the same, =importxml("A2","//title").”
“"//title" is the XPath function to extract the title from abc.com/keto-diet. Now putting all top 10 results from the search engine in A2-A11 and copy the function from B2-B11,” Sahu explains.
“In a fraction of second, we have all the titles on our competitor at one place to analyze.”
The only downside here? Sahu thinks “you need to learn a little bit of XPath for working with IMPORTXML,” but tools such as “StackOverflow can be your friend.” Regardless of the tool you’re using, Sahu thinks “you can save 100s dollars monthly” by taking advantage of the IMPORTXML function in Google Sheets.
Sahu is right: 91% of C-level executives believe that preparing data for insight ultimately costs their business in terms of resource and efficiency, too.
Joe Flanagan of Suddora thinks INDEXMATCH is “a super lookup” because “you can look up two or more items rather than just one.”
“With products in a column and financial periods in a row you are able to search by both specific product and period to return the result, in this instance the sales of a product for a given period,” Flanagan explains.
Stop squinting your eyes, and zooming-in on your spreadsheet to manually count the characters included in each cell.
SyncShow’s Jasz Joseph recommends using the LEN function to do this for you—an Excel function their team uses to count “anything that has a maximum character count like title tags, meta descriptions, tweets, and social media bios.”
It’s always easier to interpret data when your spreadsheet looks neat and tidy.
That’s why Morgan of Online Optimism recommends the PROPER(TRIM) function: A tool that “helps to make text in the sheet look cleaner with proper grammar.”
The team at Online Optimism use this Excel function for their SEO campaigns because “with an SEM campaign, you often use keywords with different match types and search queries without proper grammar or spelling.”
Morgan also adds: “PROPER (TRIM) helps to make it easier to read for client facing documents.”
“Query allows you to treat your spreadsheet, including all tabs, as a database,” explains Diona Kidd of Knowmad Digital Marketing. “It allows you to write SELECT statements, as you would with a database, to pull data from one or more sheets and applying qualifiers (think filters) to the data you pull.”
Kidd explains that one of their clients uses the QUERY function to “record all leads into a spreadsheet including the lead source, the outcome and value of sale if it occurs.”
This helps Knowmads marketing teams because Kidd says: “Using a query statement, I can easily pull the leads attributed to organic search or paid advertising to sum the lead volume and value of sales per channel.”
Are you working inside spreadsheets with thousands of rows or columns? I don’t blame you if you grow frustrated by the time it takes to find specific data.
But Andrzej Muzaj of CrazyCall recommends the REGEXTRACT function: “It helps to extract particular type of string from more complex and longer text values. For instance, I can extract user's email address or ID from a longer, merged field.”
The CrazyCall team use an analytical tool which contains complex user IDs, which Muzaj says “is composed from many different text and number strings.”
But when Muzaj wanted to extract “only one part of it - user's website address,” the REGEXTRACT function came in handy: “I used the REGEXEXTRACT function to search them and put them out in a separate column, regardless of their length or position within the complex UserID string.”
“The family of REGEX formulas allow you to use regular expressions within traditional Google Sheets formulas,” explains Futurety’s Louis John Murray.
However, Murry uses REGEXMATCH specifically because the function “allows you to search a text string for specific keywords or phrases.”
“Where it differs from a traditional MATCH is in the ability to use wildcards and OR logic to exactly control how broadly or narrowly the formula will return positive matches for groups of keywords or phrases. By default it returns a TRUE or FALSE which allows it to be used as the conditional logic within IF statements,” Murray explains.
Chetaru’s Dean Bowes uses the REGEXMATCH function “to dig through a list of social media posts to find all of the posts using hashtags”, using the formula: “=IF(RegExMatch(A2,"#"),"True", "False").”
“The (RegExMatch(A2,"#") is searching for a # symbol, used in all hashtags. Combined with the "IF" formula it then gives a very clear True or False response. From there I can filter the columns and compare the performance of posts with or without hashtags.”
“This is a very simple use of the RegExMatch formula,” Bowes explains, “but it's saving a lot of time in having to manually read posts that might have hashtags anywhere in the body of a post.”
Bowes summarizes: “Regex itself is a useful skill to have a basic knowledge of, especially dealing with the data side of marketing.”
According to CIENCE’s Alexy, the SPLIT function “divides text around a specified character or string, and puts each fragment into a separate cell in the row.”
This Google Sheets function is useful because Alexy “often work[s] with email addresses,” and “sometimes I need to extract domain name from each email address. SPLIT function helps me a lot with this.”
Alexy continues: “I use "@" as a divider, so I can get a name of an email and a domain name in separate columns, which is really useful.”
You’ve likely heard of the SUM function. It’s a tool that does what it says on the tin: Displays the sum of a data set inside your spreadsheet (without the need for a calculator.)
“We like to use Google Sheets for various revenue/income reports,” writes Srish Agrawal of A1 Future Technologies. “The same template is used for each month, then we create a new sheet for each month.”
Agrawal uses the SUM function to provide his team “with the final numbers for the month.”
...And we all know how important that is to report accurately.
The SUMIF function “ function adds up all the numbers in a range of cells that meet specified criteria,” Growth Hackers’ Jonathan Aufray explains.
“For instance, you can use the SUMIFS function to add up all the leads generated for an e-book download coming from a specific traffic source. If you use Google Analytics and Data Studio in addition to spreadsheets, the SUMIFS feature can become very helpful,” Aufray continues.
Ryan Knoll’s team at Tidy Casa also use the SUMIF function—this time, to help keep an accurate record of “issues and related costs on service jobs.”
“Some problems are service provider related and others are issues are customer related. We've found customers from one marketing channel tend to be more costly than those from other channels,” Knoll explains.
However, with the SUMIF function, Knoll says: “We can look up which channel a customer came from and how much customers from that channel cost. This has been great for identifying which channels has the most profitable and best customers.”
“Basically, the UNIQUE formula looks at a range and pulls out all the unique values from it,” writes Oksana Chyketa of Albacross. “It’s beneficial for those who want to speed up their workflow working with a considerable amount of data.”
Chyketa uses it “when I have an extended range of prospective opportunities generated from different sources,” because “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.”
Plus, Chyketa says: “The better part about this function is that it works for both, numbers and texts”--making it a great workaround for filtering qualitative or quantitative data.
The most popular Google Sheets function was VLOOKUP—a function that “essentially pulls data from one area of a spreadsheet into another area of a spreadsheet,” as Junto’s Darin Evangelista explains.
Evangelista thinks “the VLOOKUP function is useful when looking for pages that are underperforming from a CTR perspective.”
“For example, I can export a large list of pages from Google Search Console and use the VLOOKUP function to compare a page's CTR with the average CTR for that SERP position (I keep this data in another spreadsheet and use VLOOKUP to pull it in). I then calculate how many more (or less) pageviews I could get if a page has an average CTR for its position.”
“Based on this information, a user can quickly identify the pages that would see the biggest traffic improvements if we go back and re-optimize title tags and meta descriptions to improve CTRs,” Evangelista summarizes.
ScienceSoft’s Liubou Zubarevich has a similar practice: “Using the VLOOKUP function, I added real clicks data into the first table and compared the values there.”
“The difference was surprisingly good and after some investigation I found that our page is displayed in a feature snippet for the first three keywords. This explains the higher number of real clicks.”
The VLOOKUP function has won praise for other marketing tactics, too.
Ross Simmonds, who heads up the team at Foundation Marketing, finds himself “constantly analyzing lists of data surrounding the URLs associated with various sites or the search results related to specific keywords. In many cases, I'm dealing with data sets from different tools and sometimes the way they showcase URL data is different.”
Simmonds says: “Some tools and services will deliver two columns, one for the www.sitename.com (host name) and another column for the /extension (landing page). Another tool might create an entire column of URLs that includes the https and another may remove https://www. Entirely.”
...As you can imagine, Simmonds says this can cause “MASSIVE headaches when trying to create reports that showcase an accurate representation of URL data from multiple sources.”
Luckily, VLOOKUP comes to the rescue for Simmonds’ team—“because it can be used to analyze an entire column to find an exact match, identify data associated with that match and place the right information next to the "URL" that I'm talking about.”
And if you’re collecting data to gain a top-level overview of your marketing campaigns, you might spend hours manually cross-referencing.
Josh Wardini of SEOTribunal regularly uses VLOOKUP “to cross reference data between Google Analytics, Search Console and SERP rankings.”
Wardini continues: “From cross referencing these we can get a timeline between changes in SERPs and the bump in traffic for specific niches. We use these timelines as a point of reference to understand when we can expect site changes to be reflected in the SERPs.”
There are several keyboard shortcuts compatible with Google Sheets that will make your life easier, too.
One of those is Shift, Command and Arrow—a function that according Growth Marketing’s Stacy Caprio, “allows you to highlight the entire filled in function of the worksheet in three button clicks as opposed to dragging your mouse over thousands of spreadsheet lines to highlight every active row and column.”
Caprio’s team use the shortcut when “bouncing between looking at data on the top of the spreadsheet to the bottom.”
However, they Caprio says: “I also find it helpful when copy pasting a whole section of data in seconds without spending minutes dragging the highlight bar over thousands of lines of data.”
“I use conditional formatting to change the format of numbers or text based on their value or "condition",” explains Scott MacDonell of Bizcounsel.
Each Monday, MacDonell scans the variance in his customer acquisition costs. “With conditional formatting, the other executives can immediately see if our lead and sign-up acquisition costs have improved or worsened.”
“At a glance, one can see if the week-over-week numbers moved in a positive direction (the font turns green) or a negative direction (the font turns red).”
The plan of action for MacDonnel’s team then changes depending on those colors: “If improved, I'm looking for more of that same strategy. If worsened, I'm looking to optimize in that week with our agency partners and sales conversion funnels.”
Mackenzie Thompson says the team at National Health Care Provider Solutions “have a company guidebook that's 200+ pages”—in which, they “use tables to show to score and track items.”
But when Thompson “needed to make an update to our scoring table in the scoring sheet, [...] the Google Doc automatically updated” when they made changes.
However, by embedding the table from your source and forcing the table to sync with any chances, Thompson says there’s “no more going back and forth when editing a file, just update the original, and the change will be made to all related files.”
BestCompany.com’s Alice Stevens recommends freezing your columns because “it so much easier to navigate, enter, and read mass amounts of data.”
Stevens used this function when “putting together a spreadsheet to view the results of a test I had run on several pages. I was analyzing dwell time, bounce rate, and click through rates. I was also looking to see if there were differences based on the kind of device used.”
Needless to say, that’s a lot of data to analyze—and potentially hundreds of cells to manage.
Stevens says: “The freeze function made it easier to be sure that I input the date accurately.”
As you can see from the examples above, Google Sheets (or Excel) comes in quite handy for helping marketers do their jobs. From improving paid ad results to optimizing pages for search traffic and formatting customer data, easily manipulating data in a spreadsheet makes marketers jobs possible and more efficient.
Hopefully, some of these examples give you ideas you can apply in your role. As a marketer, how are you using spreadsheets?
Insycle is a suite of tools for managing and working with data efficiently. Instead of exporting data to CSV and wrestling with VLOOKUP or other functions only to import the data back into the marketing platform, Insycle enables marketers to work directly on their data easily without downloading it first.
For example, you can proper case capitalize names in the entire database or any segment with just a click and avoid unprofessional email campaigns that can impact your brand reputation. Insycle offers more sophisticated data management features that help standardize inconsistent free-text fields like Title that make campaign segmentation difficult and reporting inaccurate. Two contacts that are listed as "VP Sales" and "Vice President of Sales" hold the same position but wouldn't be featured in the same campaign or report bucket.
From importing data in flexible ways, to cleansing and de-duplicating contacts and companies in HubSpot, Salesforce, Intercom and more apps, Insycle is a single solution to manage and work with data across your SaaS apps in a way that anyone, technical or not, can use. Set it up to run automatically to increase data quality and consistency and achieve better results for your Sales, Marketing, and Customer Success teams.
Ready to get started? Learn more here about how Insycle can help you manage and work with data more efficiently to achieve better results.