Dear students! 🎓
As a beginner data analyst, you probably find Excel to be your first (or even best) friend before you move on to more scalable software solutions like Tableau or Power BI or start coding yourself in Python or R. With the new GenAI add-ons that come out every day, the time to get up and running in the data field becomes shorter and shorter. However, I don’t think Excel will go anywhere anytime soon, even if your day-to-day job is in a codebase.
If you are new to Excel, welcome! I put this article together at the request of my students a few years ago since many find the vast array of Excel functions overwhelming, especially since most of my courses and workshops go at a very fast pace. So, what you will find below are the majority of essential functions you will need as a data analyst, whether you work in finance, real estate, marketing, operations, HR, or retail. These functions will provide you with the foundation to analyze data efficiently and effectively, preparing you for more advanced tools and techniques in the future.
You don’t need to code for every task
Excel is still a valuable tool for data analysts for several reasons. Firstly, it is user-friendly and easy to learn. Many people already have a basic understanding of how to use Excel, which makes it easy to share and be productive in a more collaborative environment (it is definitely easier to teach someone how to operate with Git repositories). Secondly, Excel’s flexibility and wide range of built-in functions allow for extensive data manipulation and analysis without requiring deep technical knowledge (e.g., Regression Analysis or Solver). This makes it a great starting point for beginners who need to quickly analyze data and present findings without using more complex programming languages. Additionally, Excel also provides strong data visualization tools, such as charts and pivot tables, which let users create insightful and interactive visual representations of their data. Finally, Excel’s ability to integrate smoothly with other software and data sources keeps it relevant in any data analyst’s toolkit, despite advancements in technology.
I believe, going forward Excel will continue to serve as a user-friendly interface for simple data analysis tasks, while programming languages like Python and R are the workhorses in the back handling the more advanced and complex tasks. Programming Tools are without a question the most powerful tools for data analysis, specifically with their extensive libraries for data manipulation, machine learning, and statistical analysis, as well as the ability to handle large-scale datasets. However, Excel will remain in many legacy solutions for a while and will continue to be the most accessible option for most people.
Video Library: Excel Functions
I have compiled a list of 32 functions, ranging from basic (e.g., sum) to intermediate (e.g., pivot table), for users to learn. Additionally, I have included VBA as a very valuable tool for pro-users, but I suggest not jumping into Visual Basic too early since it can be very confusing if you have no coding background. It can be a good transition platform before entering the world of R or Python, but most people skip VBA since it is only really helpful if you are planning to stick with Excel or other MS products. Please note that the functions in this list are general and widely applicable, rather than tailored to specific industries. For example, IRR (Internal Rate of Return) is commonly used in finance for cash flow analysis, YIELD (Yield on Securities) is used for calculating the yield on financial securities, and PMT (Payment) is used for loan payment calculations.
🖱️👆Click on the functions below to access the 📽️ video tutorials. Please find the dataset for the videos 📥 HERE.
Description and Use Cases
For those wanting to expand their skills beyond the basics, I recommend exploring additional resources such as online courses, Excel-focused books, or professional certification programs. Websites like Coursera, edX, and LinkedIn Learning offer comprehensive courses that cover Excel’s advanced functions and integrations.
In my experience, learning by doing works best. Practice applying these skills to real-world scenarios to reinforce your understanding. Work on case studies or participate in data analysis competitions to gain hands-on experience. You can find datasets online from sources like Kaggle or government databases. For a personal touch, download your bank statement or Amazon purchases. Can you spot trends in your expenses or areas to cut for savings?
