Excel for Marketers - Why do you hate Excel (#Measurefest 2013)
Download as pptx, pdf56 likes30,101 views
A deck of actionable Excel tips and tricks for Marketers - Presented by Russell McAthy @therustybear at #measurefest in October 2013
More Related Content
What's hot (18)
Similar to Excel for Marketers - Why do you hate Excel (#Measurefest 2013) (20)
More from Russell McAthy (6)
Recently uploaded (20)
Excel for Marketers - Why do you hate Excel (#Measurefest 2013)
- 1. Why do you hate Excel? Russell McAthy MeasureFest Oct 2013
- 2. Is it? • Most people are self taught / taught by someone self taught • Its made by Microsoft and its “cool to hate them” • It’s a “work” application • You don’t know what you don’t know • What version do you use? • There are 8+ ways to sum two cells together!
- 3. YES – 8+ • A1 + B1 • SUM(A1,B1) • SUM(A1:B1) • SUBTOTAL(A1:B1) • SUMIF… • SUMIFS… • SUM(OFFSET… • SUMPRODUCT And these are the ones that I could remember..
- 4. Errors are no fun DIV/0 N/A #REF !
- 5. The Issue: Spreadsheets are only useful in context… How do I give you takeaways that will be actionable when you go back to your office.. So here is 32+ Tips for Excel for Marketers! I’ve snuck in some extra tips at the end of the deck so check it out on slideshare – links will be tweeted out after the event!
- 6. My Goal: Save everyone 5 mins a week… ~800 of you That’s 67 hours a week That’s 3.5k hours a year At £500 an hour… That’s £1.7 MILLION You’re welcome!
- 7. In no particular order…
- 8. Stop Merging Cells Use Center Across Selection
- 9. Calculating Growth Simple formula to remember growth % calculation NEW / OLD - 1 Then format the cell as a % (percentage) Eg. 546 Sales This year vs 410 sales last year 546 / 410 – 1 =
- 10. Calculating Growth Simple formula to remember growth % calculation NEW / OLD - 1 Then format the cell as a % (percentage) Eg. 546 Sales This year vs 410 sales last year 546 / 410 – 1 =
- 11. Remove Gridlines Don’t Select all cells and change to white!
- 12. Text to Columns Great for breaking out URL’s into folders
- 13. Text to Columns Great for breaking out URL’s into folders
- 14. Text to Columns Great for breaking out URL’s into folders
- 15. Shortcut Keys Shortcut Action CTRL + X Cut CTRL + C Copy CTRL + V Paste CTRL+arrow direction Moves curser to end of data Holding SHIFT Highlights Holding SHIFT + CTRL + arrow direction Highlights to end of data CTRL + A Highlights full section F2 Edits current cell
- 16. Shortcut Keys (more) Shortcut Action (hold) ALT then press E then S… Paste Special (as above) then V then Enter Paste Special - Values (as above) then T then Enter Paste Special - Formats (as above) then F then Enter Paste Special - Formulas CTRL + 1 Format Cell CTRL + ; (semi colon) Inserts todays date ALT + = Inserts SUM for column
- 17. Use Tables If you’re using basic data – use tables
- 18. Pivot Tables are MAGIC If you don’t use them already – learn! Its easy and makes data manipulative
- 19. Pivot Tables are MAGIC If you don’t use them already – learn! Its easy and makes data manipulative
- 20. Pivot: Calculated Fields Don’t do averages of averages! – Perfect for CVR
- 21. Pivot: Calculated Fields Don’t do averages of averages! – Perfect for CVR
- 22. Formula Transposing When you want to change the direction of a formula!
- 23. Formula Transposing When you want to change the direction of a formula!
- 24. Lets look at some formulas that might be useful
- 25. Vlookup “I have a table of data that I want to look up a value in” = VLOOKUP ( A10 , D1:E10 , 2 , FALSE ) “The thing I want to look up” “the list of things I’m looking it up against” “in the lookup table – what column do we want to bring back” “I don’t want excel to give me the closest answer – but the accurate answer”
- 26. Vlookup “I have a table of data that I want to look up a value in” = VLOOKUP ( A10 , D1:E10 , 2 , FALSE ) “The thing I want to look up” “the list of things I’m looking it up against” “in the lookup table – what column do we want to bring back” “I don’t want excel to give me the closest answer – but the accurate answer”
- 27. Vlookup “I have a table of data that I want to look up a value in” = VLOOKUP ( A10 , D1:E10 , 2 , FALSE ) “The thing I want to look up” “the list of things I’m looking it up against” “in the lookup table – what column do we want to bring back” “I don’t want excel to give me the closest answer – but the accurate answer”
- 28. …you can also do =HLOOKUP if the data is formatted Horizontally rather than Vertically
- 29. IF “If something is true, then do this…otherwise do that” = IF ( C3=“PPC” , “Paid Media” , “Other” ) “Is this true?” “If its true – do this” “If its false – do this”
- 30. IF “If something is true, then do this…otherwise do that” = IF ( C3=“PPC” , “Paid Media” , “Other” ) “Is this true?” “If its true – do this” “If its false – do this”
- 31. SUMIF “Can I add up all values that match certain criteria” = SUMIF ( A1:A40 , D4 , B1:B40 ) “The list of thinks I’m looking up against” “the thing I’m looking up” “the list of things to add when it matches”
- 32. SUMIF “Can I add up all values that match certain criteria” = SUMIF ( A1:A40 , D4 , B1:B40 ) “The list of thinks I’m looking up against” “the thing I’m looking up” “the list of things to add when it matches”
- 33. SUMIF “Can I add up all values that match certain criteria” = SUMIF ( A1:A40 , D4 , B1:B40 ) “The list of thinks I’m looking up against” “the thing I’m looking up” “the list of things to add when it matches”
- 34. …you can also do a COUNTIF – which counts the number of values that match a criteria
- 35. SUMIFS “Can I add up all values that match certain MULTIPLE criteria” = SUMIFS ( A1:A40 , B1:B40 , D1, C1:C40 , E1 ) “The things I want to add up” “the ranges of data to look up against” “the things I’m looking up”
- 36. Yes… that’s right it’s the other fubbing way round from SUMIF…
- 37. Ok…there might be a few occasions that excel is annoying…
- 38. SUMIFS “Can I add up all values that match certain MULTIPLE criteria” = SUMIFS ( A1:A40 , B1:B40 , D1, C1:C40 , E1 ) “The list of thinks I’m looking up against” “the ranges of data to look up against” “the things I’m looking up”
- 39. SUMIFS “Can I add up all values that match certain MULTIPLE criteria” = SUMIFS ( A1:A40 , B1:B40 , D1, C1:C40 , E1 ) “The list of thinks I’m looking up against” “the ranges of data to look up against” “the things I’m looking up”
- 40. MATCH “How far down my data can I find this..” = MATCH ( “A1005” , B3:38 , 0) “The thing I’m looking for” “the range of data to look up in” “I want it to be an exact match”
- 41. MATCH “How far down my data can I find this..” = MATCH ( “A1005” , B3:38 , 0) 1 2 3 4 5 “The thing I’m looking for” “the range of data to look up in” “I want it to be an exact match”
- 42. OFFSET “I want the value that is 30 rows down, 40 columns across” = OFFSET ( A1 , 30 , 40 ) “Where do I want to start” “how many rows” “how many columns”
- 43. So…. how do we use this?
- 44. …you combine a MATCH and an OFFSET
- 45. MATCH + OFFSET “I want to see the number of sales when sales code is A1005” = OFFSET ( Reference , MATCH() , MATCH() )
- 46. MATCH + OFFSET “I want to see the number of sales when sales code is A1005” = OFFSET ( Reference , MATCH() , MATCH() )
- 48. Thanks for listening If you have any questions please contact me below @therustybear @stream20 www.russellmcathy.com www.stream20.com
- 49. …the stuff I probably didn’t have time to go through
- 50. Conditional Formatting Visually represent trends / Highlight variances
- 51. Conditional Formatting Visually represent trends / Highlight variances
- 52. Calculation Options When working in a complex excel document – control when it calculates!
- 53. Calculation Options When working in a complex excel document – control when it calculates!
- 54. Addons to Excel Sometimes Excel is not enough! • Powerpivot (if you’re using not using excel 2013) • SEO Tools • Excellent Analytics • Adobe Report Builder • Geoflow – looks exciting • ExTweets – pulls tweets in • SEOGadget for Excel
- 55. AND or/and OR “I want both things to match” , “I want one of these things to match” = AND ( C3=“PPC” , c4=“PPC” ) = OR ( C3=“Social” , c4=“PPC” )
- 56. Using AND or OR in a IF allows you to match multiple criteria…
- 57. String Functions Function What it looks like What it does LEFT =LEFT(A1,3) Returns the first 3 characters RIGHT =RIGHT(A1,3) Returns the last 3 characters =MID(A1,3,3) Returns 3 characters starting at 3 characters in TRIM =TRIM(A1) Removes blank characters (spaces) from cell values LEN =LEN(A1) How long is the string CONCAT or & =CONCAT(“fish”,”cake”) or “fish”&”cake” Joins two things together MID
- 58. Handy Date Functions If A1 is todays date…. What you want Formula Show the day of the week in 3 characters eg. Mon/Tue =TEXT(A1,”ddd”) First date of the week =A1-WEEKDAY(A1)+1 First date of the year =DATE(1,MONTH(A1),YEAR(A1)) Taking into consideration working days =WORKDAY(A1,7,0) Translates date value into week day number =WEEKDAY(A1,2) *be careful with weekdays – the number after the comma refers to the day the week starts with (2 is Monday to Sunday)
- 59. Other Functions Function What it looks like What it does IFERROR =IFERROR(A1/B1,0) Instead of error message returns a 0 UPPER =UPPER(A1) Changes the text value in A1 to uppercase LOWER =LOWER(A1) Changes the text value in A1 to lowercase PROPER =PROPER(A1) Changes the text value in A1 to capitalisation at the start of each word. MAX =MAX(A1:A10) Provides the maximum value in the range =AVERAGE(A1:A10) Calculates the mean average of the range of cells AVERAGE
- 60. Excel does a lot more… If you have any questions please contact me below @therustybear @stream20 www.russellmcathy.com www.stream20.com