Efficiency and time management are the factors that evaluate the work of a PPC specialist.

Excel gives a lot of opportunities for PPC specialists to analyse, evaluate, record and show data in a well-organized way.

Excel charts allow you to manage campaigns more effectively and do routine work much faster.

Excel functions almost eliminate the need for you to do calculations manually, which saves from careless mistakes or loose calculations.

A lot of PPC specialists use Google Sheets or Data Studio to make reports. However, Excel is still an indispensable free service for making mass changes in campaigns.

You can correctly export or import data from Google Analytics or Facebook Ads right into Excel. When we export a campaign, we receive a CSV file containing thousands of lines. It might take ages to edit it manually: to change bids, ads or some specific URLs. However, you might spend just several minutes doing it if you know how to use Excel functions in a proper way.  

Even such basic functions as filters, AutoSum, data sorting facilitate your work when you need to make the simplest calculations or analysis.

For example, Text-to-Columns feature allows you to split text entries into separate spaces, which will help you create unique lists of negative keywords or split UTM from URL as fast as possible.

You’ll need formulas to make more complicated calculations. Here are just some of them which will make your life easier.

=LEN counts characters in a cell

A short but convenient formula which is almost indispensable if you make ads for loading them into Editor or Commander. Excel LEN function allows you to visualise how many characters every title or description field contains. It also prevents you from exceeding the limit. If you add conditional cell formatting, you’ll be able to clearly see it.

A bonus tip: if you use dynamic keyword insertion, Excel counts {Keyword:…} characters as a phrase part. To do it correctly, use the following formula:

= LEN (A2) -10 * COUNTIF (A2, ”* {KeyWord: *} * »).

=SUMIF adds all numbers in a range of cells based on one criteria

It is an indispensable function when you need to calculate the total cost of goods of a specific price range. Clients usually give this sales data in one extensive chart covering the whole sales period since the very beginning. On the one hand, it’s convenient to have all the necessary information in one place, but on the other hand, while making reports, we often need to calculate sales per month to understand how efficiently SEM works. It’s where we use SUMIF function.

Another, even more complicated example of using SUMIF formula, is essential if you need to quickly calculate the number of leads, clicks or any other data from a specific device or marketing channel.

=CONCATENATE

This formula joins two or more text strings into one string. It’s eminently suitable if you need to arrange all the UTM-parameters into a full URL. Apart from that, this function application is limited only by your imagination.   

=SUBSTITUTE replaces text in a given string by matching. It’s helpful if you need to put a broad match modifier before every single keyword.

=TRIM is a quick way to remove extra spaces.

The Excel TRIM function strips extra spaces from text, leaving only a single space between words and no space characters at the start or end of the text. It’s suitable for bulk editing when, for example, texts are copied from websites or customers send unsorted lists of buyers.

=PROPER Capitalises the first letter in each word

This function might come in handy for editing data received from text messages, emails, etc. Lots of users don’t capitalise their names and surnames while subscribing to newsletters, whereas other might use capital letters only.

=NETWORKDAYS Gets the number of working days between two dates.

This function calculates the number of working days between two dates. We use it in the process of B2B campaigns planning and project timing.

=VLOOKUP Looks up a value in a table by matching on the first column

You might consider this function not to be really useful at the first sight. However, you are going to change your mind when you have to work with a huge number of keywords or items. You can easily look up and retrieve data from a specific column in a table. The key point is that if item numbers or names change, these data points will be automatically changed in the basic data of the formula while replacing them with new ones.

ONE MORE HANDY TIP

The AutoSum shortcut ALT+= (ALT and = at the same time) allows you to automatically sum rows and/or columns.

CTRL + SHIFT + 4 inserts today’s date, CTRL + SHIFT + 6 inserts current time.

Excel gives plenty of opportunities for PPC specialists allowing them to analyse, evaluate, record and show data in a well-organized way. The above mentioned Excel functions are a good start for basic calculations or changes. They will make campaigns management and reporting more efficient. Your efficiency will significantly improve even if you start using just a few Excel functions from the list above.