how to master excel.
*this is meant as a study guide that was created from lecture videos and is used to help you gain an understanding of how to master excel.
Sources of marketing data- Google Analytics traffic, Google Analytics page, Facebook campaign, Facebook ads, Google AdWords Campaign, Google Adwords Daily, Internal data, scraped data
Fixing Excel Data Formatting Issues
Remove images from cells- click image -> command + a to select all images -> delete
Remove hyperlinks- type 1 into any blank cell -> copy that cell -> click on column you want to remove hyperlinks -> right click, paste special -> operations: multiply
Keep only values from a data set- command + a -> copy whole table -> go into new cell area -> paste special -> paste: values
Get rid of unnecessary spaces- command + a -> copy whole table -> data tab -> filter -> sort by ascending
Get rid of number references- click on magnifying glass top right -> replace -> find what: type [*] -> replace all
Format numbers -> select whole column to format -> home tab -> change value to what is needed
*Good habit is to delete anything you do not need if you want to learn how to master excel.
Visit Microsoft Office to learn more about Excel Formatting Tips.
Navigating Large Data Sheets
Mac scrolling- Command + down arrow or up arrow
Command + A will select all the data
Command + shift + left arrow or right arrow
Double click columns to quickly resize them
Delete anything that you are not using
Using filters- select anywhere in the data -> data: filter -> use drop down arrows to filter columns
How to use formulas for analysis if you want to learn how to master excel.
Count formula to see how many pages- open cell -> =count( select rows to add )
Sum ( ) to count columns
Avg ( )
Determine average bounce rate- Insert new column -> add column name -> multiple bounce rate / pageviews
Exceljet has more tips on how to navigate excel
Common Excel Marketing Metrics
Cost, impressions, clicks, conversions..same concept
Calculate revenue- new cell -> =sum(select revenue column)
CPM- new cell -> =cost/impressions * 1000 (calculate cost and impressions both beforehand)
CPC- new cell -> =cost/clicks
CVR- new cell -> =conversions/clicks
ROI- new cell -> (revenue-cost) / cost
Avg position- new column -> average position * impressions to get weighted average impressions -> =sum(weighted average impressions) / impressions
Plotting the Volume vs, Cost Tradeoff
Select spend and conversions column -> insert scatter plot
Analysis with tables, totals and filters
Import Google Analytics data into Excel -> turn data into table by clicking on any cell -> insert tab -> table -> table:add a total row -> dropdown:sum
How to master excel when joining multiple datasets together
=vlookup (choose campaign, table array
=sumif formula
Segmenting your data with PivotTables
Ex. Import data from Google AdWords into Excel -> click anywhere on the data -> select pivot table -> create new worksheet -> drag in metrics that you want
Plotting your data visually with charts
Select columns of data -> insert tab -> select chart such as clustered bar chart -> format chart as necessary with proper font, title and number formatting
Automatically have chart update with data- click in data -> insert -> pivot table -> new worksheet -> build calculated field cpc take cost.
Your Mind Moves the Machine.