A few months ago, I wrote a post about how to use Excel to streamline your PPC efforts. Since then, I have gotten a few questions on how to build an Excel template. Now, there is no set way to go about creating an Excel template, as they can vary in size and purpose depending on what you build them for. What this post is meant to do is really teach you the basic steps to take when building an Excel tool.
Here are my four best tips for building Excel templates for PPC. (Head’s up: At the end, I’ll provide a free download: a very simple Excel tool to get you started.)
#1. Skip to the End
As weird as it sounds, the best way to start is to know how you want it to end. You should come up with an idea of what you want your tool to produce. Do you want your tool to produce a table? Calculate stats? Update bids?
Additionally, in order to achieve your desired output, you need to figure out what information you need to input. If you’re trying to build a tool, that likely means that you tried doing it manually before and it was very time consuming. So figure out what information you needed when you were doing it manually.
#2. Build While Testing
This is honestly the best way to build a tool and this is how I built my first tools when I was still learning Excel. The easiest way to build an Excel tool is to start with the information you want to be able to input into the tool, and then build the tool using the sample data so you can check the results your tool is producing as you are building it. The reason I recommend doing this is because the most important part of building a tool in Excel is determining how you’re gonna have Excel pull the information out of whatever you input and move it and work with it. Using data that looks exactly like what you want to ultimately be able to put in the tool seems like an obvious solution.
Building a tool with Excel is like teaching a baby to walk; you need to teach a baby to go left foot, right foot, left foot, right foot. In both of these, you need to break down the activity into its most basic steps. Similarly, you need to walk Excel through each individual step including where to look for the information, and what to do with it. If you’re not sure how to do this, try and think about the steps you take when you’re doing the task the long way.
#3. Make Use of Resources
You don’t need to go in blind when building a tool. Chances are if you’re looking to build a tool to do something, someone else has tried to build a tool to do the same thing. So be sure to make use of available resources like Google if you ever hit a roadblock. You can look up formulas for doing various things and often just copy and paste the formulas after replacing a few cell references.
Additionally, as corny as it sounds, you should get an Excel formula book for reference. This can be invaluable especially when you’re just starting out with Excel. When I was starting out, I used the Excel 2010 Bible.
#4. Practice, Practice, Practice
Honestly, the first tool is the hardest, because that is likely when your knowledge of formulas is at its most limited. When I was starting out, I only knew SUM and IF. The more practice you get, whether it’s in your day to day work or through building tools, you’ll slowly build your repertoire of formulas and you’ll recognize when you could use one in a particular instance. As you learn more formulas, your templates can naturally get a little more complex as you realize the greater possibilities of what you can do with Excel.
Free Download: Character Counting Tool for PPC
To get you started, I have included a small tool that I built for writing AdWords ad copy. It’s very basic, but what it will do is count the number of characters in the headline, description lines, and display URLs and turn red if any of the entries are too long. This tool accounts for the length differences that may be associated with Dynamic Keyword Insertion as well in the headlines. Feel free to use this or tweak it to make your own modified tool. Click here to download the tool.