Ever copy and pasted data from the web only to find the formatting was a real mess? Learn a few tips and tricks for removing hyperlinks, clearing formatting, and filtering out data you don't need.
- [Lecturer] Have you ever copied and pasted data…into an Excel spreadsheet…and the formatting's all messed up, like this?…We'll go through how to avoid this issue…and get your data into the right format for analysis.…So, follow along, go to Exercise File 1.2,…Fixing Data Formatting Issues.…Now, take a look at this data.…This is a copy and paste from Wikipedia,…and you can see we have a couple of different issues.…There are hyperlinks everywhere.…The formatting is inconsistent…and some cells are bigger than others.…We can fix all of these issues just…by knowing a couple of tips and tricks.…
First, we want to get rid of these images, right here.…So, if we click into one of the images, hit Command + A,…it's going to select all the images for you.…Then, we're going to hit Delete and those are gone.…The next thing we can do is deal with the hyperlinks.…So, to do this, we just type a one into any cell,…copy that cell, and, then, let's just fix the hyperlinks…in Column I, and then right-click,…Paste Special, and then Multiply.…
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