Using Excel for Content Marketing Reporting

October 18, 2017 / by CHARGE

Content marketing reporting, while a necessity, can often become a snooze-inducing mess of titles and numbers that are hard to understand. Especially if, like many offices, you use Excel as your standard reporting tool. We’ve been conditioned to see those rows and columns as a one-way ticket to boredom.

Want to get your boss (and team) to notice your good work? Stop getting lost in formulas and cells, and use Excel’s expansive capabilities to your advantage. Use these four tips to make your Excel documents more exciting and easier to interpret.


FORMATTING SPREADSHEETS AS TABLES

What it is: Before you begin adding bells and whistles to an Excel doc, you’ll want to format all of your data in a table. Though this adds a nice bit of visual style and coordination, it actually helps Excel understand that you view this data as one set. That means that when you add more data over time, it will automatically add all of the formatting and filtering you’ve already set. 

Use it for: All reporting. Especially useful in calendars or libraries you’ll update regularly. 

 

 

To do:

  1. Select cell A1
  2. Click Home > Format as Table > Pick A Style
  3. Excel will detect what cells you have data in, and automatically input that range. Make sure it’s correct, including the checkbox for whether you have headers, and click OK.

It’s as easy as 1, 2, 3, but it’s essential to a “living and breathing” document. This formatting also automatically adds a filter drop-down at the top of each column. This can help you clear out rows you don’t want to see all the time – without having to delete them. In your blog calendar, for example, choose to view only the blogs that haven’t been completed yet. Simply add a status column (if you don’t already have one), select the drop-down in the column’s header, and deselect any statuses you don’t want to see on a day-to-day basis. That data is always there, it is now hidden.


CONDITIONAL FORMATTING

What it is: Conditional formatting allows you to apply formatting like icons or colors based on the cell’s values. This doesn’t have to be plain numbers- you can also highlight certain words, dates or amounts.  

Use it for: Due dates. Turn past-due items red on your content calendar, so you can see what projects need attention. You can use the same function to highlight cells of projects that are due today, or in the near future.  Other uses: Detect duplicate values (i.e. you used that same idea three months ago); highlight your best- and worst-performing blogs so you know what to emulate or avoid.

 

 

To highlight your past-due dates:

  1. Highlight the cells you want to format.
  2. Click Home > Conditional Formatting > New Rule
  3. Choose Style: Classic from the dropdown menu
  4. Below the line divider, choose Use formula to determine which cells to format
  5. Enter the formula =A2<TODAY() and change the “A2” in the formula to the first cell in your column. For example, if your due date is in column J and you have a header in the first row, your formula would say =J2<TODAY() .
  6. Choose your formatting style in the Format With dropdown
  7. Click OK.


PROGRESS BARS

What it is: When you type a number in, a progress bar will appear in the cell, so you get an easy visual of a project’s progress. The best part? It might be the easiest tip in this blog.

Use it for: Track progress in your content calendar. This is a great visual for weekly check-in meetings with the boss and/or the team at large. Other uses: Visualize your budget; easily see engagement comparisons for content or social; track your progress toward your monthly or annual social. 

 

 

To add a percentage progress bar:

  1. Highlight the column or cell you’d like to see progress bars
  2. Choose Home > Conditional Formatting > Data Bars > More Rules...
  3. Under Minimum and Maximum, set Type: Number
  4. Set Minimum value to 0 and Maximum to 100.
  5. If you’d like to omit the number and just show the progress bar, check “Show data bar only.”
  6. Click OK.

Now, when you enter a number in that column or cell, a progress bar will automatically appear. Play around with colors and styles to make your sheet more visually appealing. 


VLOOKUP

What it is: VLOOKUP is a way to locate coordinating information in a large spreadsheet or across tabs within an excel document. If you have a large data set, you don’t have to look for one (or multiple) rows- Excel can find that row for you, and return another piece of data from that same row.

Use it for: Quickly pulling information from one report into another. Say you’re looking at a results report from an email campaign, but you want to know what topic each campaign had. If you have that information in another report, you can use a VLOOKUP to pull all that information in. Other uses: Assign unique identifiers and qualitative data to each piece of content, then use VLOOKUPS in your monthly reporting for powerhouse content marketing reporting.

 

 

To VLOOKUP information:

  1. Select the cell where you want to add the data.
  2. Enter “=VLOOKUP()” in the formula bar. Between the parentheses is where you’ll enter your arguments. An argument is a piece of data a formula needs to work. For example: 

Formula: =VLOOKUP(H2, A2:F50, 3, FALSE)

First argument: “lookup value” = the cell of the data you know. In this example, H2. Excel will only search the first column of your table for this information. If you want to look up data that’s in another column, simply select that whole column, then cut and insert it into the first column in your spreadsheet.

Second argument: “lookup table” = whole group of data you want to search, from the upper left cell to the bottom right cell. In this example, A2:F50. This can work across tabs as well; just start entering your formula in your current tab, then click on the tab with the table and highlight it. Once you are done, don’t click back. Just put in the comma and keep typing your formula.

Third argument: “column reference” = the number of the column you want to return. Excel will find your lookup value and then move over to the column reference to bring back your data. So if the data you want to find is in column C (as it is in the example), you’d enter 3, as C is the third column.   

Fourth argument: “range lookup” = Do you want to partial (TRUE) or exact match (FALSE) your lookup value. Basically, always use FALSE.

  1. If you want to look up one value, you’re all done!
  2. If you want to return results for a whole column, you need to make one alteration. In your formula, add a dollar sign in front of your lookup table, ex. =VLOOKUP(H2, $A$2:$F$50, 3, FALSE).
  3. Now, select the cell with your formula, and hover over the square in the bottom right corner. Your cursor should turn from a white plus sign to a black plus sign.
  4. Click and drag to copy the formula down all of the cells you want to fill in. The formula should automatically adapt to each new row, changing your lookup value as you change rows. So when you’re in row H, it looks up H2; when you drag it to row J, it changes to J2. Putting the dollar signs into the table range keeps the table range from changing along with the lookup value.

While these four tricks only skim the surface of options Excel offers for content marketing, sometimes it’s the simplest changes that can make the biggest impact.

Content marketing reporting doesn't have to be simple letters and numbers, so make it easier to report and visualize your efforts with these features.

For more content marketing reporting tips, contact us.

Contact Us