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.