Excel is an inherent part of every PPC manager’s day-to-day business. Generally, we have different options to select certain functions. One of these options involves keyboard shortcuts, which save valuable time. Today, I would like to introduce you to my ten favourite Excel shortcuts.
Excel filters – everyone knows about them, everyone uses them, and everyone applies different settings. You can choose to filter by numbers, certain text phrases, or even by colours. The fastest way I know to set a filter is to hit Ctrl + Shift + L. This way, you can add filters to all columns of your table or to the selected cell. You can remove the filter by pressing the same key combination again.
Just like filtering, sorting in Excel is a function that everyone uses frequently, but handles differently. Being the Excel-super-speed-fanatic that I am, I have chosen the following shortcut: Alt + 1 or Alt + 2 = Filter from A-Z [CUSTOM].
At this point, the shortcut won’t work yet because you first need to adjust your quick access tool bar. Click on the small arrow on the right-hand side of the tool bar or, alternatively, make a right-hand click on the tool bar itself to assign commands to positions. In my case, commands in positions one and two in this task bar are “Sort in ascending order” and “Sort in descending order”. You can access these commands by pressing Alt + 1 or Alt + 2. Now, you have a go!
3. Inserting values
“Insert value” is another option we frequently need. You can either choose to do it the hard way and opt for “Insert content > values”, or you can do it the easy way. Again, you will need to use the quick access tool bar to create your shortcut. For me, the command “Insert values” is in third place; you can access it by pressing Alt + 3. As you can see, the order of these commands can be changed according to your personal preferences.
4. Editing cells
Here is an alternative to double-clicking which has proved particularly useful when working on a laptop computer: F2. This button allows you to move into a cell, so to speak, in order to edit its contents directly. By pressing “Enter,” you can confirm your entry; by pressing “Escape”, you will cancel it.
5. Absolute and relative references
Absolute and relative references are marked by the “$”symbol and provide a way of controlling whether cell references change dynamically or not. F4 is the method of choice here. Press F4 a couple of times and see what happens. For this to work, you need to be in “Edit cell” mode (see paragraph 4).
6. Adding and deleting rows and columns
In order to add a new row simply mark a row and hit Ctrl + Plus. You can delete the row by pressing Ctrl + Minus. The same also applies for columns: mark the position of a column of your choice and press Ctrl + Plus to add a new column or Ctrl + Minus to delete it.
7. Navigating and selecting quickly
A typical PPC manager can spend what feels like hours every day going through piles and piles of data. Efficient Excel navigation is therefore a must! If you want to navigate through your Excel tables quickly and expediently, you should have a look into arrow keys. By pressing Ctrl + Arrow, you can skip to the end of your data selection. If you want to select this data, simply hold Shift at the same time.
8. Transferring formats
You probably all know the brush icon that allows you to transfer formats. Did you also know that you can double-click on it? When you do, it allows you to transfer the new format to more than one area in one go, which is very useful. Try it yourself! Press “Escape” in order to cancel.
9. Formatting as percentages
PPC managers are constantly calculating one index or another, such as click-through rates, conversion rates, and other percentages. In order to format numbers as percentages, press Ctrl + Shift + 5. My advice to remember this trick: the percentage symbol is always on the 5 on your keyboard anyway!
In addition to calculating percentages, we also often use currencies. A word of advice in this regard: Ctrl + Shift + 4. Just remember Ctrl+ Shift + the key with the dollar sign. This is your Excel shortcut to “Format numbers as currency”.
Are you still with me? Alright, let’s try it this way. First, download a keywords report from one of your AdWords accounts (at the very least, the report should contain the columns keywords, ad groups, clicks, impressions, and costs). The time period is irrelevant in this case, as you only need the data to practice your shortcuts.
Try to complete the following exercises without using your mouse!
- Set filters at the top row of your data table and filter by keywords in descending order.
- Add a column next to the impressions column and calculate the CTR (even if you already exported this data – it’s all about practice!)
- Format the CTR as percentages and copy the formula for all of your data sets – without using your mouse, of course!
- Insert the formula for your CTR as values.
- Format the costs as currency.
How did you go? As always, practice makes perfect. Start with your favourite shortcut and work your way through the others step by step. The best motivation to learn a new shortcut is to seek one out that solves previous disruptions to your workflow. It’s perfectly fine to get annoyed by delays at times – just look for the right Excel shortcut!
Do you know any other Excel shortcuts that one should know? I’m looking forward to your comments!