In this blog, I share steps I have taken to learn data analysis, including the list of books and resources that helped me along the way. Find out about why it’s good to know Excel, what are benefits of SQL and where you can find out more about machine learning.
Step 1: Learn How to Analyse Data with Excel
I had just graduated from university, and I quickly learned that I knew nothing. It was 2006, so London was in the middle of a financial boom. Like others, I sent my applications to several banks. Most of the replies indicated that I lacked qualifications or that there were better applicants out there.
After a desperate search, I had finally settled on a job offer from a consulting firm. On my first day, one of my new team members asked me to help her with Excel. It was a rainy day, and the prospect of filling out numerous spreadsheets for the foreseeable future made me depressed.
But I persevered. I decided that it was time to dust off my Excel knowledge, which I still remembered from my high school years. I also figured that most of the tasks such as creating dashboards, while tedious, could be easily automated or made efficient with creative use of formulas.
There are many online resources which can help you with your first steps in Excel, but if you are looking for one book, I highly recommend “Excel Hacks: 100 Industrial Strength Tips and Tools 1st Edition”: https://www.amazon.com/Excel-Hacks-Industrial-Strength-Tools-ebook/dp/B0043GXMPM
For a step-by-step tutorial on how to learn Excel, check out my blog Your First Steps in Excel – Beginner’s Crash Tutorial. Also, to see an example of what analysing data in Excel looks like, go to How to Analyse Data in Excel with Power Query and a Pivot Table.
Step 2: Automate Data Analysis with VBA
VBA is an Excel’s programming language, and it’s instrumental if you need to deal with many repetitive tasks in Excel. Those can be copying and pasting, moving data from one place to another or merging spreadsheets with thousands of different rows of data into one workbook.
The easiest way to test writing a VBA is to record a macro for a simple task like highlighting a cell. If you right-click on a sheet’s tab and select ‘View Code’, you will notice a simple script. That’s the VBA code.
The good news is that you don’t need to know too many commands to start writing scripts. If you have done some programming before, you will be familiar with if statements, loops, variables. In that respect, VBA is just like any other programming language.
To learn specific commands on how to, for instance, copy and paste values, you can simply record a macro. You can then copy the code to re-use it for your script.
To get a good start, I highly recommend checking out https://www.excel-easy.com/vba.html. Not only is it an excellent free overview of VBA, but also an essential reference point for Excel. A great learning resource, especially when you are starting a new job as a data analyst.
And to find examples of useful Excel macros and VBA code, visit https://zingurl.com/24-useful-excel-macro-examples-for-vba-beginners-ready-to-use/.
Step 3: Learn SQL to Extract and Aggregate Data
To become a good data analyst, you will need to deal with databases and extracting data. There is no better way to prepare yourself for that task than by learning SQL. Yes, there is a chance that you will never have to use it. Your company will present you with an easy to use interface from which you can extract data, so why bother?
The truth is that the majority of those interfaces take your request and translate it into an SQL query anyway. Learning and mastering SQL will give you immense satisfaction and make your data analyst life much easier. Also, you will not need to rely on your other developer colleagues in case you suddenly need to pull thousands of row of data and your interface crashes.
There are many books and resources out there, but I highly recommend “Learning SQL” book by O’Reilly: https://www.oreilly.com/library/view/learning-sql-3rd/9781492057604/. The material is concise, easy to read, and you will soon be able to put together “SELECT” and other SQL queries in your sleep.
Step 4: Diversify Your Data Analysis Skills
Because my degree was in social science, I knew that I needed to get some more analytical qualifications. One of my first roles involved working for a start-up. I was reporting to the chief financial officer, so my tasks involved dealing not only with numbers but also with finances. I had to learn how to create financial models in Excel or help my colleagues in the marketing department understand how user churn impacts overall profitability.
Going back to school was not an option for me, so I opted to take a Chartered Financial Analyst (CFA) qualification. Learning CFA is a great way to get to up to speed with such topics like financial analysis, economics or statistics. In other words, it teaches you how to use data in a more commercial setting.
It is one of the most challenging degrees, but the good news is that you don’t need to attend any classes. All you need to do is sign up, study on your own and show up at one of the examinations centres located around the world. And although there are three levels in total, just passing the first one will significantly enrich your knowledge.
For more info on CFA program, visit https://www.cfainstitute.org/
To learn more about financial modelling in practice, check out my tutorial How to Create a Cash Flow Forecast in Excel.
And to learn how you can use Excel to analyse customer behaviour, go to How to Use Cohort Analysis to Calculate Retention and Churn Rate in Excel.
Step 5: Learn to Visualise Data with Tableau
To become a good data analyst, you need to learn how to best articulate and present your data. While you can be good with handling data, your audience will need to grasp the numbers in an easy to follow way. The task can be challenging, but luckily there are tools out there to help you. And they are not just Excel graphs and PowerPoint slides.
Tableau is a powerful application, allowing you to extract data from different sources such as Excel spreadsheets or SQL databases. It can aggregate the data and create interactive dashboards. You can even write scripts and publish your dashboards so others can easily share them.
It may be a learning curve, but the benefit of being able to present your data to others is enormous. Tableau has a great online community that most likely will have an answer to any issue you are facing.
For free training videos in Tableau, go to https://www.tableau.com/learn/training/20203.
Step 6: Accelerate Data Analysis with Python
If you need to process vast quantities of data or script repetitive processes, Python is Data Analyst’s best friend. It opens new areas of possibilities combining programming and analytical skills you have learned over time.
I learned Python later in my career, and I wish I had done it much earlier. Consider merging Excels files. Yes, you can write a VBA code, but the whole process is likely to take a long time. With Python, all you need to do is write a few lines of scripts.
And that is just the beginning. Different modules available in Python allow you to merge, process, transform and visualise data. Python is an open-source project, and it enjoys a considerable level of support from the online community. Just head to Stackoverflow if you have a question or an issue and there is a high chance someone will have an answer for you ready.
There are a lot of resources out there, but the website I found most helpful was Dataquest. It is an online interactive course that takes you through a practical journey through Python. The best part is that its basic plan is completely free of charge.
If you are looking for a good book on Python, I recommend “Python for Data Analysis” by O’Reilly: https://www.oreilly.com/library/view/python-for-data/9781449323592/.
To learn how you can use Python to consolidate and transform Excel data, check out my tutorial How to Use Python and Pandas for Data Consolidation and Transformation.
Step 7: Keep Up with Machine Learning
Learning Python allows you to expand your data analytical horizons and learn more advances topics. One of them is machine learning. It is currently a hot topic, and it’s probably here to stay. It is good to have at least some familiarity with machine learning, plus learning about it can be fascinating!
The good news is that if you are a data analyst and have some understanding of statistics, it is not as difficult as it sounds.
Visit Kaggle.com to get involved in machine learning challenges and increase your practical knowledge. It is an excellent destination to develop your machine learning and data analytical skills. The website is also a great online source of data and Python scripts.
Step 8: Have Fun and Make Yourself Useful
To become a good data analyst, always try to stay involved and relevant. If you look around, you will always find someone who needs assistance with spreadsheets or data.
Helping others with their Excel or data questions is a chance to see how your analysis contribute to other projects. It is also an excellent way to get a different perspective and context when requests and tasks come your way.
Working as a data analyst can be extremely rewarding, and the good news is that you can learn it on your own. Make sure that you stay up to date with the recent developments. Learn as much as possible about Excel, SQL, machine learning, etc. As data analysis is continually evolving, having a solid technical knowledge will keep you on top of your game.
Remember that being a good data analyst is not just about writing an SQL script or creating a spreadsheet. You need to learn how to use data in a commercial aspect, so get involved in various projects. Taking on different roles will help you learn how you can utilise data in finance, marketing or sales.
The field of data analytics comes with countless opportunities. Enjoy the process of learning because working with data and spreadsheets can be fun!
Get in Touch
Hi, my name is Jacek and I love data. Hope you’ve enjoyed reading this tutorial as much as I enjoyed writing it! If you have any questions about data analysis in general or any topic in particular, don’t hesitate to get in touch.
If you would like to book one of my One-to-One Excel and Data Analysis Courses, click here. Or for more info about my Data Analysis and Business Insights Consulting services, visit this page.
Your First Steps in Excel – Beginner’s Crash Tutorial
How to Analyse Data in Excel with Power Query and a Pivot Table
How to Visualise Data in Tableau
How to Use Python and Pandas for Data Consolidation and Transformation
How to Build a Cash Flow Forecast in Excel
How to Use Cohort Analysis to Calculate Retention and Churn Rate in Excel
This is very informative. Thank you.