Alright, let’s cut to the chase. Everyone knows that Excel tricks make PPC twice as fast, and everyone knows the go-to Excel formulas for V-lookups and pivot tables. Here are a few next-level PPC Excel tricks to skyrocket your productivity and take your PPC management to the next level.
Too often I carefully craft my Vlookup or my Countifs, only to have the hideous “#N/A” result pop up for ineligible values. That’s usually fine, since those cells aren’t the ones I need anyway. But if I want a visually appealing table to share with a client, then Iferror is a quick way to clean up the clutter. Just preface your formula with =IFERROR(, and then end your formula with what you want the formula to display if an error occurs. In the example below, I’m using a dash “–” in place of the error values.
Everything in Excel is accessible through the ribbon at the top, which means it’s well worth the time to learn Hotkey shortcuts. Hitting the Alt key will open the menu you see below, where each ribbon section has an assigned letter. Hitting one of those letters will pop you into that section, where you will see more letters for the different available actions. For example, pressing H in the example below will display all the shortcuts for every option in the Home section, such as text alignment, text size, text formatting, etc.
Here are some of the most helpful hotkeys to get you started:
- Alt-O-C-A: Resize column widths
- Alt-A-T: Apply filters across the top of a data set
- Alt-H-9: Remove a decimal point
- Alt-A-S-S: Sort menu
- Alt-W-F-F: Freeze panes (will freeze everything to the left and above of the selected cell)
At Metric Theory, we have periodic Ad Copy Extravaganzas where we get together in groups and blitz brainstorm new ad copy for each other’s clients. We want the creativity to flow freely, while people throw out any and all ideas, and we definitely don’t want to squash creativity because we’re worried about proper capitalization! Instead of creating the perfect title case while you’re writing copy, you can finalize your messaging with the Proper function, which will implement a consistent title case across all text in a cell. Check out the example below.
DAY, MONTH, YEAR
When I’m stitching together disparate data sets, the dates will inevitably end up in different formats all over the sheet. Some are formatted MMDDYYYY, some with DDMMMYY, some with just MMYY. Some are numbers, and others are text. Instead of trying to manipulate all the formats to match, I simply use Excel functions to standardize the date by month and day in multiple columns. This makes it easy to sort and filter by date.
ALT= is a very quick way to sum up data without taking time to type the SUM function. Simply navigate to the first blank cell in a column, hit the ALT button and = at the same time, and then hit Enter. The data in the column above will automatically select & sum!
Now that you don’t have to waste time adding up data or clicking around in the ribbon, your PPC analysis will be faster than ever. If you want even more PPC Excel goodness, feel free to check out our blog on 3 advanced Excel formulas or this handy online guide.