In this episode of Deep in the Sheets, Megan Demarais, AIMCLEAR SEM Account Manager, recounts 15 Excel hacks that increase PPC workflow. Here’s what you can expect to learn:
- Automatically sum
- Network days function (determine how many weekdays in a particular month)
- “CTRL” “PLUS”
- Repeating a formula in multiple cells
- Copying patterns
- Tabbing between worksheets
- Trim function
- Transposing (changes rows to columns and vice versa)
- Format painter
- LEN function
- Proper function (easily capitalize the first letter of every word)
- Lower function
- Concatenate
- Conditional formatting
- Pivot tables
Watch the video to learn how to use Excel to increase PPC workflow step-by-step!
Read on for the full transcript:
Hi! I’m Megan. In this episode of Deep in the Sheets, we’ll look at fifteen Excel hacks to increase PPC workflow. [MUSIC PLAYS] Number 1, automatically sum. Quickly add up an entire row or a column in two steps. One: Click into the cell or column you’d like to be adding. Next: Hit “ALT,” “plus,” “equals.” Boom! Number 2, networkdays function. Using the networkdays function is a fast and easy way to determine how many weekdays are in the month you’d like to look at. Simply hit “equals,” “NETWORKDAYS,” add a comma in-between the two months, and there you go. This is very effective if you or your client do not wish to run your budget on weekends. Number 3, “CTRL” “PLUS.” When you’re hundreds or thousands of rows deep in data, getting back to the beginning or end can be a lot of mindless scrolling. But it doesn’t have to be. All you have to do is hit “CTRL” “UP” to go to the beginning, or “CTRL” “DOWN” to go to the very end. Use this in combination with “SHIFT” to highlight an entire row or column. Number 4, repeating a formula in multiple cells. First, type in the formula you’d like to copy. Hit “ENTER.” Next, put your cursor into the cell you’d like to copy from. Drag it to the right-hand corner box once the plus sign shows up. Double-click. And easy. It copies all of the formulas into all of the cells. Well that was easy! Number 5, copying patterns. All you have to do is highlight the pattern you’d like to copy, and drag down as far as needed. Easy peasy! Number 6, tabbing between worksheets. Simply click “CTRL,” “PAGE UP” to move left, or “CTRL,” “PAGE DOWN” to tab right. Number 7, trim function. The trim function removes all spacing between text except for single spaces between words. So right here we have an unnatural space before the word “Brand.” We’ll go in here, hit “EQUALS,” “TRIM,” close it out, double-click, and it has taken away all spacing before the word “Brand.” Number 8, transposing. The transposing function will change rows to columns, and vice versa. To do this, highlight the table, copy it, and use the “Paste Special” function. In here, just click transpose, and there you go. Data in a different view! Number 9, format painter. If you’ve ever spent more than ten minutes trying to format a document, simply use the Excel format painter to duplicate cells. Simply copy the cells or rows that you would like to duplicate the formatting from, double-click the format painter, and easy as that. Copy all the formatting into any cells you desire. Number ten, LEN function. Avoid manually counting each character by highlighting by highlighting next to the cell you’d like to count, hit “EQUALS,” “L-E-N,” parenthesis, the cell you would like to character count, close it off, and hit “ENTER.” Drag down the formula by hitting the plus sign and dragging down. Number 11, proper function. Easily capitalize the first letter of every word by clicking into the cell, typing in “EQUALS,” “Proper,” and voilà ! Number 12, lower function. Conversely, type into the cell “EQUALS,” “Lower,” and it will take all of your title-case letters into lower-case. Number thirteen, concatenate. The concatenate formula combines data from two or more cells into one cell. It’s very useful in tracking URL parameters or building out URLs. To do this, “EQUALS,” and then simply add the ampersand between all cells you would like to merge. There you go! Number 14, conditional formatting. Conditional formatting allows you to automatically change the format of a cell based on the parameters or values you’ve defined. Let’s say we wanna take a look at the conversions within the campaigns, as to which one is performing the best based on color alone. Highlight the conversions that you would like to look at. Convert—conditional formatting, you can do this based on data bars, based on color scales, and based on icon sets. And last, but not least, number 15, pivot tables. In this session, we’re just going to teach you how to create a pivot table, as we have a whole episode of Deep in the Sheetsdedicated to pivot tables coming down the works. So, to create a pivot table, we’re going to highlight all our data in our table, hit “Insert,” “Pivot table,” and you have the option to choose “new worksheet,” or “existing worksheet.” From here, you can play around, and drag and drop which data you would like to parse. With these easy tips, you can increase your PPC workflow!
