5 Hidden Excel Secrets Every Digital Marketer Should Know

When it comes to optimizing accounts at scale, making individual campaign adjustments within large accounts can be time-consuming, tedious, and generally inefficient. To circumvent this, many advertisers use Microsoft Excel to handle day-to-day optimizations in bulk, as Google Ads and other platforms make it easy to upload changes from spreadsheets. Excel has an almost overwhelming amount of functionality, and it offers a number of hidden or lesser-known capabilities that can streamline your workflow and save you time – if you know where to find them.

1. “New Window”

If you’ve ever had to work between data sets on different sheets of the same workbook, you know how frustrating and tedious it can be to constantly navigate back and forth between tabs. Fortunately, Excel has a tucked-away functionality to improve this experience for dual-monitor users. Navigating to the View tab and selecting “New Window” will open a separate instance of the current workbook, which functions independently of the first instance, but updates in real time as changes are made in either. This allows you to view two sheets of the same workbook simultaneously, which saves time when working with reference tabs and raw data inputs.

2. Column Auto-SUM

When creating a Total row at the bottom of a table or data set, you’ve likely used a SUM formula to add all of the data within a column. Excel has a nifty feature that automatically simplifies this process – selecting the cell directly beneath the last cell of a column, holding the “Alt” key, and pressing “=” will automatically sum the above contents of the column.

3. Custom Lists

If you’ve ever dragged a date cell down and had it autofill a chronological list of additional dates, you’ve seen Excel’s List functionality in action. One hidden feature that Excel offers is the ability to create your own custom lists, which can save significant time if there are list criteria you frequently utilize (in the example above, I’ve created a custom state abbreviation list). Once a list is created, entering a component of that list into a cell and dragging it down will autofill those cells with the sequential list entries.  To create your own Custom Lists, navigate to File > Options > Advanced > Edit Custom Lists, and add in any list criteria you may need.

4. “Show in Tabular Form”

When working with multiple Rows criteria in a PivotTable, having these criteria share a column can make things messy when trying to filter, or when copying this data to a new tab where you would like to manipulate the data further. Using the “Show in Tabular Form” functionality under the PivotTable “Design” tab, you can separate these criteria into multiple columns for better filtering and readability. Additionally, you can use the “Repeat All Item Labels” functionality beneath it to – you guessed it – repeat all of the item labels in the first column for added filtering ability.



5. What-If Analysis

Excel’s “What-If Analysis” is a handy tool for quickly setting goals and determining what KPI changes you will need to make to achieve specific results. By navigating to Data > What-If Analysis > Goal Seek, you can input the cell where you would like to see a desired result in the “Set Cell” field, the result in question (in my example below, a higher target ROAS) in the “To Value” field, and the cell you would like to alter to achieve this result in the “By Changing Cell” field, and Excel will adjust the associated cell values to achieve the desired result in your “Set Cell”. This functionality only works if the “Set Cell” contains a formula that ties it to the “To Value” cell, so it is best used in tables where you have formulas in place to calculate your goal metrics.

Excel is one of the most popular pieces of business software on the market, yet it houses a substantial catalog of features that go unutilized by the majority of users. Managing campaign optimizations in Excel can be a more efficient alternative to making adjustments in interfaces and software editors, and using the tips above can save you time and spare you from a few headaches in your day-to-day optimization routines.


Want to geek out with us about excel? Drop a comment below or connect with our Team.

LINKEDIN

Posted on June 5, 2019 in Tanner Brown

Leave a reply

Your email address will not be published.

Back to Top