Using Microsoft Excel for PR
Whether you work in a PR agency or do it for your sole proprietorship, tracking, sorting, and pulling information together to learn from collected data can be a headache. There are a few simple tricks in Microsoft Excel that really streamline the process. Here are a few to look over.
Concatenate Formula
You can generate more keywords using the concatenate formula. This is a great trick and saves a lot of time so you don’t have to try and figure out a bunch of keywords that could be used in social media posts for clients. If you are working with 25 products and need to identify the SEO relevance for each, then once you’ve run the formula for one of the products, it’s easy to switch out the product for another and hit enter again. These are the steps:
Across the top of the spreadsheet columns starting in column 2, there will be each of the keywords you want to search – (i.e., sale, offer, reviews, discounts, etc.). Below that line of keywords, in the second column, you’ll list each product in a separate row in that column. In the third column, next to your first product, type in =$B3&,” “,C$2&, which is the simplified version of the formula. The dollar sign in the formula makes it possible to drag the formula to other cells making it easy to run the keyword volumization search to each of the products in turn. You should also note there is a single space between the two quote marks.
When you click on the small box on the right lower corner of the cell and drag it right until all the terms are covered, it fills the cells in that you just dragged across. Now double click that little box again, and it fills in all the rows too. Now go to Google Adwords to run the search volume for each of the new keywords. This allows you to populate new keywords immediately.
Using Keywords To Make a Pivot Table
So, you’ve already generated the keywords and their search volume, but now you need to know which items are most popular. Use your original spreadsheet and organize it so it shows the products that have the most action. Then add a new column – call it Groups and use the first column that has been blank so far for this. List the keyword in the Groups column prior to each product in rank order.
Now select all the cells with the data you want and hit the Insert tab at the top of the screen. Click on Pivot Table and accept. This starts the process of creating a pivot table. You’ll get a new screen – in the box that says row labels, add Group and Keyword and in the values box add in the value field setting of your choice and then choose Sum. Choose the first group value and sort from the largest to the smallest, then choose the first keyword value and again, sort from largest to smallest.
Right click on the first group with your mouse and collapse the entire field. Now your spreadsheet is sorted by most searched no matter what the keyword. This helps to prioritize content in campaigns, but it can be used in many other ways. Let your imagination run wild! This process allows you to identify new keywords from Adwords, find new opportunities, or even new clients, or to identify the most popular products using a combination of keywords and product names. This is useful to help clients know which items to stock or even what will generate the most interest in a sale or promotion.
