One of the cultural values at Buffer is to live smarter, not harder.
We like to think this extends into our workflows as well.
That being said, spreadsheets are not always easy. Finding or building the right ones and figuring out how best to use them can be time-consuming tasks. We’d love to help.
To give you a hand with managing spreadsheets in Excel and Google Sheets (and hopefully save a great deal of time), we’ve pulled together a list of essential spreadsheets, templates, formulas, and shortcuts that are handy for every marketer to have in their locker.
Let’s jump in…
10 ready-made marketing spreadsheets to boost your productivity
For many of the free spreadsheets linked below, you can download as an .xls file to use and customize in Excel or Google Docs. Google Doc users can also go to “File > Make a Copy …” to add the spreadsheet to their account, then edit.
1. A weekly social media report
Track your social media marketing with week-over-week data
This social media report is built in Google Sheets, and it works off of a data export from Buffer (though you can rig it to work with exports from other social media analytics tools as well).
The report will help you keep tabs on your engagement, top posts, and much more.
2. Waterfall chart
Monitor your progress to see if you’re on track
The waterfall spreadsheet template is extremely versatile for keeping pace with your goals. We use it to track many of our OKRs here at Buffer. (Thanks to the HubSpot team for turning us onto waterfalls.)
Here’s an example of it in use to keep tabs on the number of comments we received per post throughout Q2:
This spreadsheet can be used to track your progress on most any metric. Here are a few ideas to get you going:
- Follower growth on social media
- Newsletter subscribers
- Traffic growth
- Clicks from your social accounts
3. Blog post traffic tracker
Know which posts are seeing the most traffic, and when
This is one of my favorite spreadsheets we use at Buffer and I’m excited to share it with you. The blog post traffic spreadsheet enables us to keep an eye on which pieces of content are hitting our traffic goals and it’s also really great to keep an eye on what topics are performing best, too.
4. Social media marketing baselines
Know right away which social media posts are on track and which are taking off
How can you tell if a certain number of clicks, reshares, or reach is good?
It can be a bit of a puzzle to see your social media results in context. That’s why we’ve gone about trying to set benchmarks and baselines for our social media marketing, using the above spreadsheet as our starting point.
Simply enter your social media data into the spreadsheet (it works natively with a Buffer data export).
Then the formulas do the rest, highlighting any update that goes above and beyond your average.
5. Social media audit spreadsheet
Easily track all your social profiles in one place
Performing a social media audit on a monthly basis can be a good habit. Once you get in a good flow, it might only take 15 minutes or less, and you’ll gain tons of benefits with branding, consistency, and perspective.
Here are some of the things we track in the audit spreadsheet:
- Profiles on all social networks
- Active / dormant
- Posting frequency
- Followers and growth
- Engagement and growth
6. Moz’s One Metric
See at-a-glance which pieces of content are performing best
We’ve used the Moz One Metric spreadsheet to track the performance of our blog posts and even re-engineered it to work with social media updates.
It’s a powerfully simple way to measure performance. Here is Moz’s explanation for why they built it:
We need a way to quickly sift through the noise and figure out which pieces of content were really successful, and which didn’t go over nearly as well.
It works by weighing three different points of data and standardizing to make a single score. The data points can be anything you choose. By default, they’re:
- Google Analytics traffic data
- On-page data (comments, thumbs up)
- Social shares
7. Google Analytics heatmap
Find out when your readers visit your site (so you know when to publish/promote)
The folks at Seer Interactive set about to recreate a Google Analytics mobile dashboard look from a desktop spreadsheet. The results are pretty nifty: You can see the times when your site receives its most organic traffic, which might help you plan when to publish new posts or promote content.
To get started, Seer published step-by-step instructions for setting up the spreadsheet with your own Google Analytics data.
8. Social media calendar
Manage and plan your social media marketing content weeks in advance
We were grateful to partner with HubSpot in creating the above calendar template. One of our favorite features: It includes a sheet to store your best evergreen content and updates so that you can quickly grab something to share in a pinch.
9. Social media metrics dashboard
Visualize (and share) all your social media marketing growth from one place
This is the spreadsheet we use at Buffer to track the performance of our social media marketing. It allows us to chart week-over-week growth and month-over-month growth, with sheets for the snapshot overview and each month’s performance.
10. Quotes to share
Easy-to-grab, inspiring quotes to share on social media
Some of our most highly engaged social media content is quotes. And when we’re looking for some fresh inspiration, we often turn to this spreadsheet. (Likewise, when we find some quotable inspiration, we add it to the sheet.)
5 incredibly handy spreadsheet formulas
1. Tidy up spacing
Have you ever started working on a spreadsheet with some odd spacing going on? A few rogue spaces throughout a sheet can make it difficult to work with the data. Thankfully, there’s a nice, simple formula to help you remove unwanted spaces.
The Trim function works across both Google Sheets and Excel. To use it, simply type the following formula into the Formula Bar:
=TRIM("Your Text Here")
Here’s an example, to remove the unwanted spaces before a name in our spreadsheet. For this, we used the formula: TRIM(“Kevan Lee”)
The following formula can help you to split values within your Google Sheets spreadsheet based on any given character (or delimiter) within the cell. In the below example we used the character ” ” (space) to split first names and surnames from the values listed in Column A.
The formula you need to do this is:
And in the above example, we used “A2″ as the string and ” ” (space) as the delimiter, making our formula:
=split(A2, " ")
If you’re using Excel, this handy guide from Microsoft will help you achieve the same outcome.
3. Percentage difference between two numbers
Back at the start of Q2 2016, we set a goal to boost the traffic to our new posts by 30 percent in the first 30 days after publishing. To measure this, I created a spreadsheet, and in one column had the target traffic for each post with the actual traffic in another and the percentage difference between the two figures displayed in a third column. This formula helped to me see whether we hit the goal on not at a glance, and how far over or under we were.
Here’s a snippet of the spreadsheet (you can see the % difference in the green / red figures in the furthest right column):
To work out the percentage difference you need to use the following formula:
=(-1) * (Cell 1-Cell 2)/Cell 2
For example, if we have a target of 3,315 page views and achieve 4,147 page views, this formula will tell you that you were 25.10 percent over your target:
Note: Ensure the cells you’re using this formula for are formatted as percentages to ensure this one works.
Autosum can be a really neat time saver in Excel. To use it, simply select an empty cell to the right or below the cells you want to sum, and type Alt + = (or Command + Shift + T on Mac). Excel will then estimate the range of cells you’re trying to combine and in one step give you the total.
Note: If Excel’s estimation is a little off, you can edit the range of cells include in the sum within the formula bar.
In Google Sheets, Autosum works a little differently. First, you need to select the range of cells you wish to add up, then click the Functions button and select the SUM option. Google Sheets will then automatically add the sum of your selected cells directly in the cell below (or to the right if you’re combining data from rows).
Here’s that workflow in action:
5. Add up the sum of cells matching certain criteria
If you wanted to discover the page views on your blog generated by posts written a certain author or count only data from users in a specific cohort, it could take a while to figure these out manually. This is where the SUMIF function comes into play.
SUMIF allows you to add up cells that meet a certain criteria. Here’s how a SUMIF works:
=SUMIF (range, criteria, [sum_range])
- =SUMIF: tells the formula it’ll be summing only cells that match the specified critera
- Range: the range of cells you’re going to add up
- Criteria: the criteria used to determine which cells to add
- Sum Range: The cells to add together
Here’s an example showing how we can breakdown page views generated by post type on the Buffer Social blog using a SUMIF:
In order to calculate the number of page views ‘News’ posts generated we used the formula:
This formula sums the amounts in column D (range) when a value in column B (sum range) contains “News” (critera).
More on the SUMIF function:
9 time-saving shortcuts and tips by spreadsheet masters
1. Add borders to cells
When I work with spreadsheets, I love to use borders to help me break up the data and make a sheet easier to understand. Both Excel and Google Sheets have a button to add borders, but they also have some super-handy shortcuts:
- Apply top border: PC: Alt + Shift + 1 | Mac: Option + Shift + 1
- Apply right border: PC: Alt + Shift + 2 | Mac: Option + Shift + 2
- Apply bottom border: PC: Alt + Shift + 3 | Mac: Option + Shift + 3
- Apply left border: PC: Alt + Shift + 4 | Mac: Option + Shift + 4
- Remove borders: PC: Alt + Shift + 6 | Mac: Option + Shift + 6
- Apply outer border: PC: Alt + Shift + 7 | Mac: Option + Shift + 7
PC and Mac (substitute Alt for the Option key on Mac):
2. Format numbers as currency
If you have same data you’d like to quickly turn into currency, there’s a super quick solution to help you out. This shortcut can be particularly useful when you’re working with budgets, revenues or even salaries.
To use this trick, simply highlight the cells you wish to update and press Control + Shift + 4. Thankfully, this shortcut is universal across Excel, Google Sheets, Mac and PC.
3. Format as a percentage
Much like formatting numbers as currency you can also format numbers as a percentage using a neat shortcut. To do this, simply select the cells you’d like to show as percentages and press Control + Shift + 5.
4. Copy formatting
Formatting spreadsheets to your liking can take quite some time. To help speed this process up, you can use the Paint Format button to copy and paste formatting from one bunch or cells to another.
To do this, highlight the formatting you’d like to copy, then click on the paint brush icon (on both Excel and Google Sheets) and then select the area you’d like to apply the formatting to and click the paint brush again. Your styling will now be applied to those cells.
5. Start a new line in the same cell
Adding multiple lines of text within the same cell is often a puzzle for spreadsheet users. I can’t even recall how many times I experimented and tried to figure this out before someone was gracious enough to teach me this amazingly simple keyboard command.
Here’s the answer, to add a new line of text in the same cell holding Alt + Enter on PC or Control + Option + Return on Mac, will add a new line inside a cell on both Google Sheets and Excel.
6. Insert date and time
Almost every spreadsheet will have a column for the date or time and the following shortcuts work across Excel, Google Sheets, Mac and PC:
To add the date, use Control + ;
To enter the current time, use Control + Shift + ;
7. Fill down / Fill right
These shortcuts allow you to quickly copy data from the cell above or the cell to the left, without having to copy and paste. In Excel, to copy a value from the cell above, use Control + D. To copy data from the cell to the left, use Control + R.
Google Sheets works a little differently here, but you can still use a shortcut to fill cells to the right and below. To do this on Google Sheets:
- Highlight the cells you’d like to fill with the top or furthest left cell being the one you’d like to copy
- Press Command + D
8. Show formulas
Occasionally, it can be useful to see all the formulas within your sheet, and what’s even better is being able to do this without having to manually click on each cell to see the formula behind the data. By holding Control + ‘ (on both Mac and PC and Google Sheets and Excel), you can display all formulas within your spreadsheet at once.
This shortcut is particularly useful to help ensure you’re using consistent formulas throughout your sheet.
9. Insert rows and columns
To insert a row above or column into a spreadsheet, you first need to select an entire row or column. The, on Excel, use Control + Shift and + (on Mac: Control + I) to insert a row or column (columns will be added to the right of the selected column).
On Google Sheets, the command is a little different:
Here are the shortcuts for Mac:
And on PC:
Over to you: What are your spreadsheet tips?
Thanks for reading! I hope you picked up one or two new tips and tricks for your spreadsheets here. Now, I’d love to open the floor up to you and ask for your favorite spreadsheet hacks.
What formulas do you use regularly? Any shortcuts you couldn’t be without? Feel free to leave a comment below. I’m excited to join the conversation and learn from you too.