Data has become a key component of business success in today’s world. The ability to quickly and accurately analyze large volumes of data has become essential in almost every industry.
Introduction
Today, I will share my recent experience acquiring new skills through the specialization “Excel Skills for Data Analytics and Visualization” offered by Macquarie University on Coursera. This path has been the deepening of a journey that began with Excel Skills for Business.
This specialization has allowed me to delve into the use of Excel for data analysis, data cleansing, visualization (DataViz), use of big data, and introduction to Power BI.
The importance of data analysis
We live in an era when data is crucial for driving decisions and strategies. Proficiency in quickly and accurately interpreting vast data sets is now a prerequisite in many professional fields. Skills in using Excel tools for data analysis and visualization are among the most in-demand and fastest-growing skills in the job market. Thanks to my recent specialization, I can increase the effectiveness of my current work and open up new professional horizons.
The specialization
This specialization has allowed me to delve into the use of Excel for data analysis, data cleansing, visualization (DataViz), use of big data, and introduction to Power BI.
In particular, I was able to:
- Use Excel tools and functions to clean and prepare data for analysis;
- Use “Named Ranges” and “Tables” to automate my analysis;
- Understand the different types of data in Excel and use the appropriate functions to work with them;
- Use logic and search functions to transform, connect, and categorize data.
The project applied
I had the opportunity to work on an applied project, which allowed me to leverage Excel tools to shape data, create valuable visualizations, and prepare dashboards and reports to share results. I learned how to create a data workflow to automate my analysis and make the results flexible and reproducible.
The training course
The specialization consists of three courses, each structured in several weeks of learning:
- Course 1: Excel Fundamentals for Data Analysis
The first course in the major introduced me to the fundamentals of data analysis in Excel. I learned how to use Excel tools and functions to clean and prepare data for analysis. I also developed an understanding of the different types of data in Excel and how to use the appropriate procedures to work with them. - Course 2: Data Visualization in Excel
The second course allowed me to experience Excel’s data visualization power. I have had the opportunity to explore a wide range of charts and create interactive dashboards that make the data immediately understandable and engaging. - Course 3: Excel Power Tools for Data Analysis
In the last course, I had the opportunity to delve into the world of Excel Power Tools dedicated to data analysis. Thanks to tools like PowerPivot, Get and Transform, and DAX, I could use a more sophisticated database engine and overcome Excel’s classic limit of 1,048,576 rows. This allowed me to automate data transformation processes, build more robust data models, and immerse myself in the world of “Power Business Intelligence.”
Course 1: Excel Fundamentals for Data Analysis
The first course in the “Excel Skills for Data Analytics and Visualization” specialization is “Excel Fundamentals for Data Analysis.” This four-week course is dedicated to introducing the essential tools of Excel for data analysis.
In the course, you will gain skills in using various Excel tools and functions to clean and prepare data for analysis. “Named Ranges” and “Tables” to automate data analysis are also introduced. Finally, you learn to use logic and search functions to transform, link and categorize data.
The course’s pedagogical approach follows the path of a character named Zara, with whom we tackle typical problems and solutions using Excel for data analysis.
Week 1: Text cleaning and manipulation
In the first week, you will acquire skills in Excel text functions. You learn techniques for extracting information and manipulating data to meet specific business requirements. You develop the ability to work confidently with these Excel functions and unlock the full potential of Excel by manipulating and cleaning text data.
Week 2: Working with numbers and dates
In the second week, you become proficient in Excel’s date functions. You learn functions to convert data types, such as text to numbers and dates to text, and you know arithmetic operations with dates as you would with numbers. Specific functions are also studied to solve daily problems with the required dates in the company, such as WORKDAY, EDATE, and EOMONTH functions.
Week 3: Named Ranges to work more efficiently with data
In week three, we studied the different types of cell references: relative reference, absolute reference, and cross-reference. You learn to use Named Ranges, another way of referring to a cell or set of cells by giving it a meaningful name. You also learn Excel functions that allow you to aggregate and summarize data, such as SUMIFS and COUNTIFS.
Week 4: Tables for automating data manipulation.
In the fourth and final week, you learn about the use of tables in Excel, similar to a database of records and fields containing related information. You learn how to create, format, and manage tables and extend tables whenever new data are added automatically.
In summary, the first course allows you to build a solid foundation on the fundamentals of Excel for data analysis. The skills acquired in this course will enable you to work with the more advanced techniques used in subsequent courses in the specialization.
Course 2: Data Visualization in Excel
The second course in the specialization “Excel Skills for Data Analytics and Visualization” is titled “Data Visualization in Excel.” This five-week course focuses on data visualization techniques using Excel.
In the course, Excel visualization tools are explored through practical case studies. You learn to create dynamic visualizations using conditional formatting, charting techniques, specialized and custom charts, pivot tables, and interactive dashboards.
This course follows the challenges of Rohan, an environmental analyst, as he navigates through creating visualizations that show trends, forecasts, breakdowns, and comparisons for a wide range of ecological datasets.
Week 1: Dynamic displays with conditional formatting, custom number formatting, sparklines, and macros.
In the first week, you explore different data visualization tools that do not include graphs. You learn the use of conditional formatting, including custom conditional formatting using formulas, drop-down lists, and macros. You also learn sparklines, shapes, and custom number formatting.
Week 2: Charting techniques
In the second week, you explore various options for visualizing data using graphs. Standard charts such as line charts, pie charts, and scatter charts are studied, as well as more specialized charts such as area charts, donut charts, and bubble charts.
Week 3: Specialized charts
In week three, you explore several innovative and creative charts, many new to Excel. These include hierarchy, waterfall, funnel, and geospatial graphs.
Week 4: Creating an interactive dashboard using Pivot Charts and Slicers.
In week four, you learn to use pivot tables to summarize data. You also learn to customize, group, sort, and filter pivot data. Finally, we explore Pivot Charts, which help visualize data in pivot tables.
Week 5: Completing the dashboard with creative visualizations and dynamic graphics
In the fifth and final week, we explore how to bring together all the tools and knowledge gained to create aesthetically pleasing, interactive, and informative dashboards. You learn how to incorporate themes, macros, and hyperlinks and use slicers to make dashboards interactive.
In summary, the second course in the specialization provides a broad overview of the data visualization tools offered by Excel. At the course’s end, students can create dynamic, customized, and interactive data visualizations using various techniques and tools.
Course 3: Excel Power Tools for Data Analysis
The third and final course in the “Excel Skills for Data Analytics and Visualization” specialization is “Excel Power Tools for Data Analysis.” This four-week course introduces three powerful Excel tools: Power Query, Power Pivot, and Power BI, for data transformation, analysis, and presentation.
In this course, you will learn how to use Power Query to automate importing and preparing data for analysis. You will see how Power Pivot revolutionizes the analysis process by providing an analytical database within the Excel worksheet, capable of storing millions of rows, and a powerful modeling language called DAX that enables advanced data analysis. Finally, you will venture outside Excel to introduce Power BI, which also uses the Power Query and Power BI architecture, but allows you to create beautiful interactive reports and dashboards.
Week 1: Presentation; Get and Transform (Power Query)
In the first week, you will be introduced to the course and will introduce Power Query. You will learn how to import data from various sources and the different ways to combine data sets according to your needs.
Week 2: Data transformation in the Query Editor
Once the data have been imported and combined, you will move on to their transformation in the second week. You will learn everyday operations such as pivoting data between wide and long formats, grouping data, and splitting a column into multiple columns.
Week 3: Power Pivot and the Data Model.
In week three, you will learn how to work with the Data Model in Excel, which is limited only by the amount of memory on your computer. You can define database-type relationships between tables, visualize your data using Power Pivot and cube functions, and create Pivot Tables.
Week 4: Data visualization with Power BI
In the fourth and final week, you will move outside Excel to work with Power BI, Microsoft’s business intelligence tool. You can practice the skills learned in Power Query, M, and DAX to create dynamic and interactive reports and dashboards in Power BI.
In summary, the third and final course in the specialization provides a comprehensive overview of Excel’s powerful tools for data analysis. Upon completing the course, students can use Power Query, Power Pivot, and Power BI to automate the data import and preparation process, perform advanced analysis, and create interactive reports and dashboards.
Conclusion
In conclusion, this specialization has enabled me to acquire new skills to analyze and visualize data more effectively, thereby increasing my ability to provide quality service to my clients. I was already an experienced Excel user, but this path allowed me to deepen my knowledge and discover new tools I had never used before.
I hope this experience inspires and motivates you to train and acquire new skills. Remember that learning is an ongoing process; you never stop growing professionally. Thank you for reading my article. If you have any questions, please feel free to contact me. I will be happy to respond to all of you.
Evaluation
- Course content: lectures’ quality, the topic’s relevance, and how well the course met your initial expectations.
- Clarity of instruction: how easy it was to understand the course material, whether the instructions were clear, and whether the teacher provided sufficient explanations.
- Practical applicability: if the skills acquired in the course will be helpful in my career or practical applications.
- Support and resources: additional learning resources, such as supplementary readings and support offered by tutors or fellow students.
- Difficulty level: the complexity of the course and how challenging it was for me to complete it; more stars indicate a more challenging course.
- Overall value: overall judgment of the quality of the course, considering both the cost and what I gained in terms of new skills acquired.
Image gallery
FAQ
If you are looking for an experienced and reliable Excel consultant to help you maximize the potential of this tool, please do not hesitate to contact me. They are available to discuss your needs and find the best solution for your business.
