10 Excel Tips for Managing Content at Scale

A super-nerdy, not-at-all sexy tutorial on using Microsoft Excel to manage content more efficiently and look smarter for your boss.

Back when I started my career, I wrote computer tutorials. I mean, stuff for total newbies: “Open the File menu, select Save As, type in the file name, and hit Enter.” That’s what audiences needed, and I got paid to deliver it with a bit of chitchat.

I have no desire to write such explicit tutorials anymore, but for a while now, I’ve been wanting to compile a list of my go-to Microsoft Excel tools for content management. This is because I’ve noticed a gap where colleagues sometimes don’t know a few simple techniques that will help them do their jobs more efficiently. Yet when I’ve sent team members to get Excel training, the resources they found weren’t focused properly.

To manage content, we don’t need a lot of fancy footwork. We just need a few basics, used in an effective way. Here are my favorites.

1. Create a functional file

As content managers – or really for anyone who works in digital marketing – we often need to send Excel files to our colleagues. I’m still surprised that not everyone bothers to do a little cleanup first. It takes just two steps to turn an Excel data dump into a workable document.

First, apply a Filter to the top row of your file. Then, Freeze Panes for the top row of the file and the first column or two. Now, whomever you send the file to can actually use it. Your boss probably won’t notice if you do this – but they may notice if you don’t.

2. Create a readable file

This is part two of creating a functional file. First, make your column widths appropriate for the content that’s most important. For instance, if this is a metrics report, make sure the column is wide enough for your largest number. (Ablebits.com has a solid tutorial on changing column widths.)  Next, use Wrap Text formatting on any columns with notes that are important but a bit long.

Spend a little time fiddling with the sizing and format. Don’t add any extra rows or columns for looks, but make sure the rows and columns you have can be easily scanned. You might need to Hide some columns too. As a manager, when I get a file that’s been tidied up like this, it heightens my trust in the accuracy and thought that’s gone into the reporting, as well as the Excel skills of the person who has sent it.

When I get a file that’s well formatted, it heightens my trust in the accuracy and thought that’s gone into the reporting.

3. Turn folder structures into URLs

I often work with content reports that have a core part of each URL but are missing a critical part at the beginning or end that turns the data into a URL. In these scenarios, Concatenate is my buddy. It’s not nearly as fancy as it sounds – “concatenate” just means to link things together. 

Let’s say you have a column with data that looks like this: “/technology/services/product1.” But it’s missing the front part of the URL, say “www.myco.com.” You can faff around with copy and paste each time you want to look at a web page. Or, to turn the data into a URL, use Concatenate, or simply CONCAT.

If the file data is in cell B2, and your website is “www.myco.com,” the function might look like this: =CONCAT("www.myco.com",B2). Then Excel will display the full URL, or “www.myco.com/technology/services/product1.”

4. Turn formulas into data

Once you’ve learned to concatenate, the next thing you’ll discover is that sometimes you want to lose the formula and keep just the results. Otherwise, you may end up with errors when you’re working with your spreadsheet.

You can do this with a special pasting feature called – wait for it – Paste Special. I’ll walk you through this because it’s quick. Select and copy the whole column with your formula. Add an empty column to the right. Then, with your cursor in that column, select Paste Special > Values Only. Now that you have the actual data, you can even erase the column with your formulas.

5. Trim extraneous stuff off the ends of URLs

While we’re talking about URLs, let’s take things in the opposite direction: sometimes you’ll get a file where the URLs have extra characters at the end. These extra characters might include both tracking information (starting with a “?”), and anchor link information (starting with a “#”). I’ve seen this most often during a content migration, when someone has sent me an Excel file that they’ve painstakingly created after reviewing the live site and their social media feeds for their most important content. From a content review standpoint, anything in the URL after a pound sign or question mark is generally unimportant and makes the data sloppy.

There are a few different ways to clean this up, but a Find and Replace with a wildcard character (*) is what I find quickest. Just save the Excel first in case you make a mistake.

First, select the column with the URLs, and CTRL-F for “#*”. This will Find anything that has a pound sign with extra characters after it. If you leave the Replace field blank, you can zap out all the anchor link information out of the cells.

Next, do the same thing for data after the “?” in the URL. You’ll just need to add a tilda before the question mark, for Excel to locate question marks. Your Find field will look like this: “~?*”.

6. Weed out the duplicates

This is a good time for our next tip: eliminating duplicates. This is a magic way of weeding out work and is one of the most satisfying Excel things ever. (I said this article was nerdy.)

In the example above, suppose someone has sent you 100 pages that require attention, but 30 of them were just variations on the same URL. Now you’ve cleaned up the URLs and want to just focus on the actual pages that need attention.

Select the column with the URLs, and then look under Data for a tool called Remove Duplicates. Depending on how complex the data is and how comfortable you are with Excel, you may want to do a bit more research before proceeding. In that case, I recommend this Simpilearn tutorial on removing duplicates.

7. Make it easy for people to record decisions

Content management tends to be a collaborative activity, where sometimes you need feedback from a lot of people to decide on the next steps. In these situations, I’ve found it’s best to give everyone a narrow set of options, and force them to apply those options in a consistent way.

Let’s say you’ve pulled a report to do a content audit. For every line in the Excel document, someone needs to make a decision. If you leave the “Decision” column as an open field, no matter what you ask people to do, you’ll end up with a messy response set. This can range from simple character variations such as “Keep” and “keep,” to more nuanced variations like “Keep” vs. “Needed,” to people putting in way more details than needed.

For a long time, I thought that drop-down lists were too much bother. Once I actually began using them, though, I realized how much time they saved in gathering and acting on decisions. 

Once I began using drop-down lists, I realized how much time they save when acting on decisions. 

Create a new worksheet in your Excel document marked “Dropdowns” or similar, and come up with as short a list of options as possible. In the case of a content audit, it may just be Archive, Keep or Update. Then select the column where you’d like the list to appear and look for Data, Data Validation, List. Excel-easy.com breaks down data validation if you get stuck. 

8. Turn your results sideways

Not long ago, I was working with a spreadsheet that ran wide instead of long. I think it was survey results from a Google Form. The answers ran on and on across the columns. The stronger opinions were about a question, the more people answered the question, the more they had to say in comments, and the further right I had to scroll. Meanwhile, my question set was not actually that long.

I didn’t even know what to search for. Was there a way to turn my results sideways? It turns out that there is. It’s called Transpose, and it’s located under Paste Special. This nifty setting simply flips your columns and rows. Suddenly my data set made sense, and I could begin to analyze the response set. 

9. Just learn VLOOKUP already

I’ve saved the hardest two items for last – but VLOOKUP is an absolute must for every content manager. VLOOKUP is used when you need to “look up” information in one Excel worksheet, and pull it into another worksheet.

For instance, suppose you have a reference list that includes all your stakeholders organized by the CMS folder they manage. And suppose you have a new metrics report full of URLs but no stakeholders. You can add a column to your new content report and use VLOOKUP to add in all the content managers.

This is not an easy function to get right – the spreadsheet has to be ordered just so, and it may take a bit of tweaking to get it to work perfectly. But being able to use VLOOKUP will help you in ways you can’t even imagine until you need them.

I won’t try to explain how to use VLOOKUP. Check out this video from a Microsoft alum or this tutorial from Excel Easy.

10. Pivot and make sense of it all

Last but not least are the perfectly imperative pivot tables and charts. A pivot table can take a complex, detailed set of data and extract trends and summary data. And a pivot chart can present that same information in a more visual way. What boss won’t be impressed by that? (I certainly am! I love when someone sends me a nice pivot table.)

Let’s say you have a content report that has thousands of URLs with a page type and stakeholder for each one. Scrolling through the report, it’s easy to see why you’re busy – but harder to define exactly why. A pivot chart will let you count exactly how many of each page type you have, and from which stakeholders. 

Again I’m not going to get into the nitty-gritty, but I like this pivot table tutorial from Hubspot. 

11. What's missing?

That’s my top 10. Tell me what I need to know, and we can be content Excel nerds together.