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.

Get and Transform (Power Query)

Often the first steps when analysing data are to import the data and combine different datasets together. In Excel, you can use Get and Transform, previously known as Power Query, to help with this. In this module, you will learn how to import data from various sources and the different ways to combine datasets depending on your requirements.

Transforming data in the Query Editor

Once your data is imported and combined, you then move on to transforming it. A common operation is to pivot data between wide and long formats. You can group data and split a column into multiple columns. Power Query has a few extra options that a normal PivotTable doesn't have.

Power Pivot and the Data Model

An Excel workbook can handle up to 1 million rows, which sounds like a lot but sometimes you have more data than that. The Data Model in Excel is only limited by the amount of memory your computer has. You can also define database-like relationships between tables. Then you can visualise your data using Power Pivot and cube functions, and create PivotTables.

Visualising Data with Power BI

We are moving out of Excel with this module. Power BI is Microsoft's Business Intelligence tool. You can put into practice the skills that you have learned in Power Query, M, and DAX, to create dynamic and interactive reports and dashboards in Power BI. Once you have the report looking how you want, share it with others.

Good to know

Know what's good

, what to watch for

, 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

Master excel for powerful data analysis

Learners say this course provides a largely positive introduction to Microsoft Excel's data analysis power tools. Led by expert instructors, the course covers topics including Power Query, Power Pivot, and Power BI. Through engaging assignments and well-structured lessons, students will develop their skills in data cleaning, manipulation, and visualization. The course is well-received for its hands-on approach and the thoroughness of its content.

Exposure to Power BI provides a valuable introduction to a widely-used data analysis tool.

"Awesome course one can do at their own pace."

"Great course to be familiar with power tools."

"I am surely going to recommend Macquarie University's Excel courses to my peers."

Varied and valuable exercises, challenges, videos, and practice files enhance understanding.

"The lessons were well-structured and easy to understand, and the practical exercises allowed me to apply what I learned."

"The work along Excel sheets and the challenge exercises provide enough hands-on practice and confidence."

"Amazing course. You will have the opportunity to learn POWER BI as well as its constituent tools, Power Query and Power Pivot."

Instructors are highly knowledgeable and skilled at teaching the material.

"I love this program. It's concise and informative."

"Great course by Nicky, she really shines over here."

"The combination of visual classes and practical exercises allows you to firm up the learning."

Coursework focuses on real-world application of skills, making it immediately useful.

"This course is excellent. There are a few issues, but in the forum, you can find the answers. Exercises are perfectly prepared to understand all three tools."

"I got to practice a lot of powerful skills in Excel and Power BI."

"I even started applying these principles to my work."

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