Excel Power Tools for Data Analysis from Coursera

Welcome to Excel Power Tools for Data Analysis. In this four-week course, we introduce Power Query, Power Pivot and Power BI, three power tools for transforming, analysing and presenting data.

Excel's ease and flexibility have long made it a tool of choice for doing data analysis, but it does have some inherent limitations: for one, truly "big" data simply does not fit in a spreadsheet and for another, the process of importing and cleaning data can be a repetitive, time-consuming and error-prone. Over the last few years, Microsoft have worked on transforming the end-to-end experience for analysts, and Excel has undergone a major upgrade with the inclusion of Power Query and Power Pivot.

In this course, we will learn how to use Power Query to automate the process of importing and preparing data for analysis. We will see how Power Pivot revolutionises the actual analysis process by providing us with an analytical database inside the Excel workbook, capable of storing millions of rows, and a powerful modelling language called DAX which allows us to perform advanced analytics on our data. We will finish off by venturing out of Excel and introducing Power BI, which also uses the Power Query and Power BI architecture but allows us to create stunning interactive reports and dashboards.

This is the third course in our Specialization on Data Analytics and Visualization. The previous courses: Excel Fundamentals for Data Analysis and Data Visualization in Excel, cover data preparation, cleaning, visualisation, and creating dashboards. To get the most out of this course we would recommend you do the previous courses or have experience with these topics. In this course we focus on Excel Power Tools, join us for this exciting journey.

Please note that Power Query, Power Pivot and Power BI Desktop are only available on the Windows platform, so Mac users will require Bootcamp running Windows or a Virtual machine with a Window O/S. While Power Query is available as an add-in Excel 2010 and 2013, the tools have changed significantly, and this course has only been designed and tested for Excel 2016 and later. For an optimal experience, we recommend Office 365.

What's inside

Syllabus

Welcome and critical information

Welcome to Excel Power Tools for Data Analysis. In this course, you will learn about importing and transforming data with Power Query, working with huge datasets in Power Pivot, and creating interactive reports with Power BI. This introductory material will help orient you into the course. We encourage you to think about your goals for the course and share them with your fellow learners.

Traffic lights

Read about what's good

what should give you pause

and possible dealbreakers

Provides an introduction to data transformation and analysis using Power Query, Power Pivot, and Power BI

Covers essential concepts like importing data, creating data models, and visualizing data for analysis

Taught by industry experts with extensive experience in data analytics

Suitable for individuals seeking to enhance their data analysis skills for data-driven decision-making

Does not cover advanced topics like machine learning or artificial intelligence

Requires access to Windows platform and Microsoft Office, which may not be accessible to all learners

Reviews summary

Mastering excel data tools

According to students, this course provides an incredibly well-structured and highly practical approach to mastering Excel Power Tools for data analysis. Many praise the instructor's clear explanations and engaging delivery, finding the content directly applicable to real-world tasks and instrumental in automating data processes. While offering a strong foundation, some learners with less experience found the pace challenging, suggesting a need for prior intermediate Excel skills. A significant number of reviewers also noted that the course requires a Windows operating system, which presented a considerable hurdle for Mac users. The introduction to DAX is generally well-received, though a few wished for more in-depth coverage.

DAX section is a good introduction but some desired more depth.

"Power Pivot and DAX were introduced well, but I felt the DAX section could have gone a bit deeper."

"Solid introduction... Power Pivot was good, but DAX needs more examples. Power BI part was a quick overview."

"The DAX part was a bit challenging, but manageable."

Course provides highly practical skills directly applicable to real work.

"The hands-on examples are practical and directly applicable to real-world data analysis tasks."

"The content is highly relevant for anyone working with large datasets in Excel... It's a game-changer for my daily tasks."

"This course provided a strong foundation and practical skills immediately. It has truly enhanced my data analysis capabilities."

"I learned so much about handling large datasets and automating reports. The content is very current and well-organized."

The instructor's clarity and engaging style are highly praised.

"This course is incredibly well-structured and the instructor, Michael, is truly excellent. He explains complex topics..."

"The instructor's delivery is engaging, and the pace is just right."

"Absolutely brilliant... The explanations are top-notch, and the practice files are excellent."

"The instructor is superb. I learned so much about handling large datasets and automating reports. ...instructor's passion for the subject shines through."

Minor note that Power BI visuals may not be the absolute latest.

"My only minor feedback is that sometimes the visuals were a tiny bit outdated compared to the absolute latest Power BI updates, but still very functional."

Course assumes prior Excel knowledge; may be challenging for beginners.

"The course is okay, but definitely not for beginners. I struggled a bit with the pace and the jump into DAX."

"For someone already familiar with basic Excel, this is a great step up."

"If you don't have basic Excel skills, this will be tough. It's not a beginner-friendly course."

Course requires Windows, causing significant issues for Mac users.

"Mac users, be warned, you absolutely need a Windows environment for this, which was a hassle for me initially."

"The Mac compatibility issue was also a huge problem for me, wasting a lot of time setting up a VM."

"The main issue for me was the specific software requirements – it's definitely for Windows users, and older Excel versions won't work well."

"The practical side was let down by the Mac compatibility. The course description mentioned it, but I underestimated the difficulty."

Activities

Be better prepared before your course. Deepen your understanding during and after it. Supplement your coursework and achieve mastery of the topics covered in Excel Power Tools for Data Analysis with these activities:

Review Relational Databases

Show steps

Review foundational database concepts that will be used in this course.

Browse courses on Relational Databases

Show steps

Review the basics of database normalization
Practice writing SQL queries to create, read, update, and delete data

Learn Power Query M

Show steps

Learn the basics of Power Query M to enhance your data transformation skills.

Browse courses on Data Transformation

Show steps

Complete an online tutorial on Power Query M
Practice using Power Query M to transform data in Excel

Create a Data Model in Power Pivot

Show steps

Practice creating a data model in Power Pivot to gain hands-on experience in data analysis.

Browse courses on Data Modeling

Show steps

Import data into Power Pivot
Create relationships between tables
Create measures using DAX

Two other activities

Expand to see all activities and additional details

Show all five activities

Design an Interactive Report in Power BI

Show steps

Demonstrate your understanding of data visualization by creating an interactive report in Power BI.

Browse courses on Data Visualization

Show steps

Connect Power BI to a data source
Create visualizations and charts
Add interactivity and filters

Mentor Junior Data Analysts

Show steps

Enhance your understanding of data analysis concepts by sharing your knowledge with others.

Browse courses on Mentoring

Show steps

Identify junior data analysts who could benefit from your guidance
Provide regular support and guidance on data analysis topics

Career center

Learners who complete Excel Power Tools for Data Analysis will develop knowledge and skills that may be useful to these careers:

Data Analyst

Data Analysts gather, cleanse, analyze, and interpret data. Professionals in this career field are responsible for using data insights to solve business problems. This course can help provide the foundation necessary to build a successful career as a Data Analyst. It teaches the skills and knowledge that are essential to this field, including how to import and transform data with Power Query, work with large datasets in Power Pivot, and create interactive reports with Power BI.

See salaries and explore the career path for Data Analyst

Business Intelligence Analyst

Business Intelligence Analysts use data to provide insights into business operations and make recommendations. This course can help provide a strong foundation for a career as a Business Intelligence Analyst. It teaches learners how to use Power Query, Power Pivot, and Power BI to analyze data and create reports and dashboards.

See salaries and explore the career path for Business Intelligence Analyst

Data Scientist

Data Scientists use scientific methods and statistical techniques to extract knowledge from data. This course can be a helpful resource for aspiring Data Scientists. It provides a solid foundation in data analysis, including how to import and transform data, work with large datasets, and create interactive reports.

See salaries and explore the career path for Data Scientist

Data Engineer

Data Engineers design and build the systems and infrastructure that are used to store and process data. This course can provide valuable skills for Data Engineers, including how to use Power Query to import and transform data, Power Pivot to work with large datasets, and Power BI to create interactive dashboards.

See salaries and explore the career path for Data Engineer

Database Administrator

Database Administrators maintain and manage databases. This course may be useful for aspiring Database Administrators as it provides a foundation in data management, including how to import and transform data, work with large datasets, and create reports.

See salaries and explore the career path for Database Administrator

Market Research Analyst

Market Research Analysts gather and analyze data about markets and customers to provide insights that can be used to make business decisions. This course can provide a helpful foundation for a career as a Market Research Analyst. It teaches the skills necessary to import and transform data, work with large datasets, and create interactive reports.

See salaries and explore the career path for Market Research Analyst

Operations Research Analyst

Operations Research Analysts use mathematical and analytical techniques to solve business problems. This course can be a useful resource for Operations Research Analysts as it provides a foundation in data analysis, including how to import and transform data, work with large datasets, and create interactive reports.

See salaries and explore the career path for Operations Research Analyst

Financial Analyst

Financial Analysts use data to analyze financial performance and make investment recommendations. This course can provide a helpful foundation for a career as a Financial Analyst. It teaches how to import and transform data, work with large datasets, and create interactive reports.

See salaries and explore the career path for Financial Analyst

Quantitative Analyst

Quantitative Analysts use mathematical and statistical techniques to analyze data for investment purposes. This course can provide a helpful foundation for a career as a Quantitative Analyst. It teaches how to import and transform data, work with large datasets, and create interactive reports.

See salaries and explore the career path for Quantitative Analyst

Actuary

Actuaries use mathematical and statistical techniques to assess risk and uncertainty. This course can provide a helpful foundation for a career as an Actuary. It teaches how to import and transform data, work with large datasets, and create interactive reports.

See salaries and explore the career path for Actuary

Statistician

Statisticians use mathematical and statistical techniques to collect, analyze, and interpret data. This course can provide a helpful foundation for a career as a Statistician. It teaches how to import and transform data, work with large datasets, and create interactive reports.

See salaries and explore the career path for Statistician

Data Journalist

Data Journalists use data to tell stories and communicate information. This course can provide a helpful foundation for a career as a Data Journalist. It teaches how to import and transform data, work with large datasets, and create interactive reports.

See salaries and explore the career path for Data Journalist

Information Security Analyst

Information Security Analysts protect computer systems and networks from unauthorized access and use. This course may be useful for Information Security Analysts as it provides a foundation in data analysis, including how to import and transform data, work with large datasets, and create reports.

See salaries and explore the career path for Information Security Analyst

Software Engineer

Software Engineers design, develop, and maintain software applications. This course may be useful for Software Engineers as it provides a foundation in data analysis, including how to import and transform data, work with large datasets, and create reports.

See salaries and explore the career path for Software Engineer

Web Developer

Web Developers design and develop websites. This course may be useful for Web Developers as it provides a foundation in data analysis, including how to import and transform data, work with large datasets, and create reports.

See salaries and explore the career path for Web Developer