Learn a foolproof technique for taking a series of steps and combining them into one formula to clean up your spreadsheets.
- [Instructor] Have you ever stumbled upon an Excel formula…so complex that you couldn't…imagine how it was constructed?…Well actually there's a foolproof way…to build complicated formulas.…Let's see how it works.…If you'd like to follow along,…go to exercise file 4.4,…building complex formulas the easy way.…So the example we have here is a series of six steps…that you need to use to calculate…how long to run an AB test.…And what we're going to do is…we're going to condense all of these steps down into one step.…So the way to do this is you click into the last step.…
And we can see that it's referencing cell D8…but also the result of step five.…And all we need to do is copy the formula from step five…all the way up to the equals…and then go command+C, hit escape.…Go back to step six, click in…and where it references cell G12,…which is step five,…I'm going to double click and then go command+V and hit enter.…Now we've basically just copied and pasted…but look what this allows us to do.…
If we select this and then hit delete,…
Michael begins with the basics, showing how to work with marketing data sources and explaining basic formulas for analysis. Next, he walks through how to build a data-driven marketing plan, including mapping out your marketing funnel, reviewing volume vs. cost tradeoff, and running A/B testing. Michael shows how to analyze a campaign's performance, working with PivotTables, charts, and dashboards. Finally, he demonstrates how to set up marketing campaigns in bulk, including manipulating text for ad copy generation and automating the most boring, repetitive tasks with macros.
- Cleaning up data formatting
- Using formulas for analysis
- Building a data-driven marketing plan
- Top-down vs. bottom-up marketing plans
- Running A/B tests
- Analyzing campaign performance
- Plotting data visually
- Building report templates and dashboards
- Building bulksheets for bulk campaigns on AdWords and Facebook