Working in Search advertising means spending an extended amount of time on Excel to create an account structure, Keywords lists, external and internal reports, analyse your data to make the right decisions to optimise your account. Mastering Excel will help you save time on all data-related tasks so you can spend more time working on Strategy.

1 — Drop the mouse and use your keyboard!

Shortcuts are the key to fast and efficient work as we deal with a lot of data and some tasks are repetitive like organizing, filtering, deleting. Using shortcuts will increase your speed up to 3x on Excel (we actually tested that).

Everyone working in Search should have already mastered the CTRL + C, CTRL + V and CTRL + H (yes, find and replace) so here are some of my favourite shortcuts:

These shortcuts will save you loads of time for your PPC work in Excel.

Tip: You finally have an excuse to hang out with the accountants in your company: they are a real mine of knowledge when It comes to Excel. They can teach you some really good tricks — ours are awesome!

2- Replace the Compact Pivot Tables by a Tabular form

Pivot tables are created by default in a Compact form but creating it in a Tabular form will help you look at your data as a combined Raw data while easing the formatting if you need to create a report.

Tip: Repeat all items & Remove Subtotals

3 — Replace the Pivot table by a SUMIFS formula:

The Pivot table is the basic of data analysis, but when you report to your client on an Excel document, the format of the pivot table is not client facing despite being practical and easy to use. The SUMIFS formula will summarize the data in a defined data range if the data matches your selected criteria.
If we want to know the total clicks for the Campaign promoting Apple on Mobile Device and on the Search network, the formula will be:

= SUMIFS(E:E,A:A,”Apples”,B:B,”Mobile”,C:C,”Search”) … the result will be 142,001
Tip: Create a drop down menu in the cells where the criteria for the SUMIFS will be matched (such as Devices, Month, Product…) for an interactive experience on Excel.

4- VLOOKUP & CONCATENATE:

You should probably learn how to use a VLOOKUP and a CONCATENATE (for instance to generate unique IDs for campaign items) on your first week of working in Search Advertising. How about you take it to the next level? You can try combining those two formulas when there are different items you want to look up (for example when looking for a specific date AND campaign AND device):

= VLOOKUP(CONCATENATE(A3,B3),M:P,4)

Where A3,B3 link to your criteria and M:P the array of values needed.

This will return the values you need, linked to criteria you can specify on the fly.

Tip: Personalize some formulas and combine your most common formulas instead of having a formula calculated from another cell with a formula. It’s a bit of a mind trick at the beginning but once you get the logic you’ll be amazed at the possibilities!

5 — Retrieve your campaigns:

Using “Text to column” under the Data tab can be very helpful when delimiting the different parts of your campaign name or to remove the UTM parameter of your URLs to analyse campaign performances. You can then easily create a pivot table and compare if the campaigns in English performed better than your Arabic campaigns for example.

Tip: To avoid the manual task of the text to column when searching for specific terms in your campaign (Brand vs. Generic), automate it in your raw data with a FIND formula

I hope you found some of these tips useful. Don’t hesitate to share your favourite Excel PPC tricks in the comments section!