Nearly every technical profession has some tool for wireframing, prototyping and down-and-dirty work.
For data analysts, that’s Excel. So I find it humorous when the “tech bros” of the world imbibe the centrality of prototyping in the minimum viable product world but then dump on Excel:
Don’t listen to the haters: it’s a valid slice of the data analytics stack and the perfect way to learn data analytics. And that’s what this checklist is for.
Sign up below for the checklist and access to my resource library: 👇
If you’re already subscribed, you’ll find this resource in the learning-guides-and-checklists folder of the library.
By the end of this 30-day checklist, you should be able to conduct basic exploratory and confirmatory data analysis in Excel. Now this is no short order, considering many seasoned researchers can’t even really explain what a p-value is, and that is really a linchpin of the whole operation. That said, by scouring the best blog posts, videos and more of the web you’ll be in great shape not only to conduct rigorous data analysis in Excel, but to explain it to yourself (and others).
You’ll learn, among other things, how to:
- Summarize variables appropriately given their type
- Visualize relationships and trends as part of exploratory data analysis
- Build and interpret inferential statistics
- The relationship between correlation and causation
- Use linear regression in real-life business scenarios
Prerequisites
This checklist is more about the data analysis and less about the data cleaning. I know, I know… data cleaning is important! I know that. In fact, I know it’s so important that I isolated each task to give it its proper due.
The Excel topis you should be familiar with include the following:
- Absolute, relative, and mixed cell references
- Conditional logic and conditional aggregation (
IF()statements,SUMIF()/SUMIFS(), and so forth) - Combining data sources (
VLOOKUP(),INDEX()/MATCH(), and so forth) - Sorting, filtering, and aggregating data with PivotTables
- Basic plotting (bar charts, line charts, and so forth)
If you would like more practice with these topics before moving on, I suggest Excel 2019 Bible by Michael Alexander et al.
Get the checklist here 👇
I hope that this checklist shows you how handy Excel can be for data analysis, and how handy statistics is for data analysis. More so than perhaps anything else, statistics lends data analysts such a clever way of thinking that even if you don’t get to directly implement a hypothesis test in your work, you will carry the thought process forward.
That said, I also hope that this encourages you to think more robustly about your quantitative claims, trying to back them up with statistical testing — and you’ll be in good shape to do it, as you’ll have visited some of the best online resources on the topic.
Want more analytics in Excel and beyond?
If I achieved the goal of this checklist, you’re excited to learn more about analytics and more. In that case, check out my book Advancing into Analytics. Part I of the book should be a breeze with the knowledge you have coming in.
At that point, you’ll be in great shape to pick up R and Python for more advanced analytics. The book explains how and why to make this jump.
You can learn more about the book, including how to read for FREE, here.
Leave a Reply