When managing multiple Pay Per Click campaigns, Excel is often an important tool when you need to pull large amounts of data together to analyse and send to a client. When using Excel on a daily basis, you will pick up a number of different functions to make your life easier. For any budding excel analysts or seasoned users, here are 5 tips you may or may not be familiar with to save you time on a daily basis using Excel.
1. Date Formulas
There is not a lot worse than the sinking feeling when you send a report and realise you’ve made a simple clerical error, for example not updating or entering the wrong date. You can overcome this with the TODAY formula, as your date will update each time you open the spread sheet which can be useful when opening a daily summary you may send onto a client.
Insert: =TODAY()
Also:
Use the TEXT formula next to your date formula to be given the day that the date was/will be. This can be extremely useful when trying to spot weekly trends or calculate the working days in a month for budgeting without having to track back using a calendar.
Insert:=TEXT(CELL, “dddd”)
2. Keeping To The Ad Text Limit
When writing Ads, it so often seems all the best Headlines reach 26 characters. Rather than struggling during the upload of your ads, you can check as you write with a combination of the =Len formula and a little conditional formatting.
Using the =Len formula, you can count how many characters are used including whitespace, and show this is in an adjacent cell.
Insert: =LEN(CELL)
Combine this with conditional formatting rules and you can produce a visual aid to make it easy to see where you have gone over the text limits.
3. Concatenation
A bread and butter formula, but so useful and time saving that it’s still worth a mention. This tool can be essential when building large Keyword lists for a new campaign. Simply fill in your text that you want before, after or either side of the current text and refer to the relevant cell to pull two or more strings of text together.
4. The Pivot Table
The Pivot table can be such a strong tool in Excel, especially when pulling together keywords of varying match type. This tool enables you to accumulate all figures for a particular keyword into one group for example. Recent versions of Excel have helped to make this feature much easier than it has been in the past, simply drag and drop your fields into the wizard boxes then copy and paste the results.
There are hundreds of tutorials on YouTube that can help you to manipulate data using this tool. As this can become extremely complicated with variations of data sets with differing formats.
5. The VLOOKUP
The ultimate reporting and editing formula, the VLOOKUP helps quickly compare data, match up metrics for a specific term or even check a term is present in a campaign. Essentially it matches the values of a cell on another sheet to a specific value on your current sheet.
Using the Function Wizard you will need to fill in:
Lookup_value – The cell value you are wishing to look up in another corresponding table
Table_Array – The table range in which you are looking up the value
Col_Index_Num – The number of columns you wish to travel across when the initial value is matched
Range_Lookup – True Or False – Use False to actually show the data in the sheet
There are many more tools that can help you in Excel, let us know some of your essential formulas that you use on a daily basis to help streamline your campaigns.