This course is part of the Excel Skills for Data Analytics and Visualization Specialization

4.8
stars
3,073 ratings

Nicky Bull +1 more instructor

121,302 already enrolled

Offered By

Part of the Finance, Analysis and Modeling MasterTrack® Certificate
Learn More

Excel Skills for Data Analytics and Visualization SpecializationMacquarie University

About this Course

318,167 recent views

As data becomes the modern currency, so the ability to analyse the data quickly and accurately has become of paramount importance. Excel with its extraordinarily broad range of features and capabilities is one of the most widely used programs for doing this. In the first course of our Excel Skills for Data Analysis and Visualization Specialization, you will learn the fundamentals of Excel for data analysis. When you have completed the course, you will be able to use a range of Excel tools and functions to clean and prepare data for analysis; automate data analysis with the help of Named Ranges and Tables; and use logical and lookup functions to transform, link and categorise data.
This course will enable you to build a strong foundation in the fundamentals, helping you to be more efficient in your day-to-day and developing the necessary skills to work with the more advanced techniques used in later courses. To make the content easy to relate to and to personalize the learning experience, we are going to follow Zara's journey through the course. Who is Zara? Well, she is no-one and everyone. You will find that Zara's trials and tribulations sound familiar, and together with Zara, you will develop your Excel skills along the way — and, importantly, have some fun doing it.

The Excel Skills for Data Analytics and Visualization courses are the sequel to one of most successful specializations on Coursera, Excel Skills for Business, which has attracted hundreds of thousands of learners and top ratings. Transform your skills, your confidence, and your opportunities by adding this new set of skills to your repertoire.

Flexible deadlines

Reset deadlines in accordance to your schedule.

Shareable Certificate

Earn a Certificate upon completion

100% online

Start instantly and learn at your own schedule.

Course 1 of 3 in the

Excel Skills for Data Analytics and Visualization Specialization

Intermediate Level

Approx. 15 hours to complete

English

Could your company benefit from training employees on in-demand skills?

Try Coursera for Business

What you will learn

Use Excel tools and functions to clean and prepare data for analysis.
Use Named Ranges and Tables to automate your analysis.
Understand the different types of data in Excel and use appropriate functions to work with them.
Use logical and lookup functions to transform, link and categorise data.

Skills you will gain

Data Analysis
Microsoft Excel
Data Cleansing

Flexible deadlines

Reset deadlines in accordance to your schedule.

Shareable Certificate

Earn a Certificate upon completion

100% online

Start instantly and learn at your own schedule.

Course 1 of 3 in the

Excel Skills for Data Analytics and Visualization Specialization

Intermediate Level

Approx. 15 hours to complete

English

Could your company benefit from training employees on in-demand skills?

Try Coursera for Business

Instructors

Instructor rating

4.85/5 (1,231 Ratings)

Nicky Bull

Lecturer and Lead Content Designer

Macquarie Business School

1,065,394 Learners

20 Courses

Dr Prashan S. M. Karunaratne

Senior Lecturer - Teaching & Leadership | FHEA

Department of Actuarial Studies and Business Analytics, Macquarie Business School

1,069,288 Learners

23 Courses

Offered by

Macquarie University

Start working towards your Master's degree

This course is part of the 100% online Finance, Analysis and Modeling MasterTrack® Certificate from Macquarie University.

MasterTrack Certificates are portions of Master’s programs that have been split into online modules, so you can earn a high quality university-issued career credential at a breakthrough price in a flexible, interactive format. If you enroll in the program, your coursework will count towards your MasterTrack Certificate.

Learn More

See how employees at top companies are mastering in-demand skills

Learn more about Coursera for Business

Syllabus - What you will learn from this course

Content Rating96%(14,309 ratings)

Week1

Week 1

1 hour to complete

Welcome and critical information

1 hour to complete

1 video (Total 3 min), 3 readings

3 hours to complete

Cleaning and manipulating text

By the end of this module, you will be an expert in Excel's Text functions. This module discusses ways you can extract information and manipulate data to fulfil specific business requirements. You will develop techniques for working confidently with these Excel functions and unlock the full power of Excel by manipulating and cleaning text data. This is really important because you cannot properly analyse data if it is invalid or in an unsuitable format. You will begin with the mechanics of creating a function, specifying multiple arguments, and then you will look at how to work with text in functions. You will also learn how to use multiple functions in a single formula — a technique known as nesting. The topics you will be learning this week: Functions for Combining Text; Functions for Splitting Text; Combining Text Functions; Cleaning Data & Changing Case; and Removing and replacing unwanted characters.

3 hours to complete

6 videos (Total 30 min), 4 readings, 6 quizzes

6 videos

Week 1 Introduction5mExcel functions for combining text values6mFunctions that split text data3mCombining text functions6mCleaning data and changing case4mRemoving and replacing text characters4m

4 readings

Read me before you start: Quizzes and Navigation10mDownload the Week 1 workbooks5mWeek 1 Practice Challenge20mWeek 1 Toolbox10m

6 practice exercises

Excel functions for combining text values10mFunctions that split text data10mCombining text functions10mCleaning data and changing case10mRemoving and replacing text characters10mCleaning and manipulating text: Test your skills30m

Week2

Week 2

3 hours to complete

Working with numbers and dates

By the end of this module, you will be an expert in Excel's Date functions. This module discusses ways that you can extract information and manipulate data to fulfil specific business requirements. You will also

be looking at functions for converting data types: such as text to numbers, dates to text, and then both numbers and text to dates. You will learn to solve the problem of data not being in the correct format, and you will learn how to perform arithmetic operations with dates like you would with numbers, as well as performing calculations with dates. You will also learn some really great functions specifically for solving day-to-day tricky date problems that are required in business such as the WORKDAY, EDATE and EOMONTH functions. The topics you will be learning this week: Converting Data Types; Understanding dates and basic date functions; Generating valid dates; Calculating days between two dates; and Calculating dates from a given date.

3 hours to complete

6 videos (Total 37 min), 3 readings, 6 quizzes

6 videos

Week 2 Introduction3mConverting Data with VALUE and TEXT6mUnderstanding dates and basic date functions7mGenerating Valid Dates using the DATE function6mCalculations with Dates and using DAYS, NETWORKDAYS and WORKDAY8mMore sophisticated date calculations with EOMONTH and EDATE5m

3 readings

Download the Week 2 workbooks10mWeek 2 Practice Challenge20mWeek 2 Toolbox10m

6 practice exercises

Converting Data with VALUE and TEXT10mUnderstanding dates and basic date functions10mGenerating Valid Dates using the DATE function10mCalculations with Dates and using DAYS, NETWORKDAYS and WORKDAY10mMore sophisticated date calculations with EOMONTH and EDATE10mWorking with numbers and dates: Test your skills30m

Week3

Week 3

3 hours to complete

Defined Names for working more effectively with data

In this module, you will learn about different types of cell referencing: relative cell referencing, absolute cell referencing, and mixed cell referencing. You will then learn about a great Excel functionality, Named Ranges — which is another way of referencing a cell or a range of cells by giving them a sensible name. You will see that named ranges make our formulas much easier to understand and you will also see how it makes them much easier to create — using tools such as Define Name and Create from Selection. You will also learn about Excel's Name Box and the Name Manager. You will learn some exciting Excel functions that allow you to aggregate and summarise data — such as SUMIFS and COUNTIFS, and much more. The topics you will be learning this week: Cell referencing and naming; Creating named ranges; Managing named ranges; Calculations with named ranges; and Automating processes with named ranges.

3 hours to complete

6 videos (Total 36 min), 3 readings, 6 quizzes

6 videos

Week 3 Introduction5mCell Referencing and Naming6mDefined Names and Create from Selection4mManaging Names5mCalculations with Named Ranges6mAutomating Data Validation with Named Ranges6m

3 readings

Download the Week 3 workbooks10mWeek 3 Practice Challenge20mWeek 3 Toolbox10m

6 practice exercises

Cell Referencing and Naming10mDefined Names and Create from Selection10mManaging Names10mCalculations with Named Ranges10mAutomating Data Validation with Named Ranges10mDefined Names for working more effectively with data: Test your skills30m

Week4

Week 4

3 hours to complete

Tables for automating data manipulation

In this module, you will learn that a table in Excel is more like a database of records and fields which contains a set of related information — such as a table of inventory data. You will learn how to create, format, and manage tables, and you will learn how to easily format, select, sort, and filter in a table. You will also learn about the Total Row where you can just pick from a set of automated summarisation functions. You will learn about structured referencing — where you can continue to work with the data in a table, but the formulas look slightly different because tables use a different way to reference cells. You will also learn how to automatically extend tables whenever new data is added. The topics you will be learning this week: Creating, naming and removing tables; Formatting and selecting in tables; Sorting and filtering tables; Performing calculations with structured references; and Automating processes with tables.

3 hours to complete

6 videos (Total 42 min), 3 readings, 6 quizzes

6 videos

Week 4 Introduction5mIntroduction to Tables6mCustomising Tables 5mSorting and Filtering Tables8mTable Calculations and Structured References9mAutomation with Tables7m

3 readings

Download the Week 4 workbooks10mWeek 4 Practice Challenge20mWeek 4 Toolbox10m

6 practice exercises

Introduction to Tables10mCustomising Tables10mSorting and Filtering Tables10mTable Calculations and Structured References10mAutomation with Tables10mTables for automating data manipulation: Test your skills30m

Week5

Week 5

3 hours to complete

Logical and lookup functions

You will learn two great Excel skills in this week’s module — first, you will learn about conditional logic, and second, you will learn about automating lookups. The two of these tools together will push your data analytics to the next level! Excel has several logical functions and this module explores some of them, such as AND, OR, and IF. You will start by learning the concept of conditional logic, and how conditional logic works in Excel formulas. You will then learn to conduct logic tests and use conditional operations. For more complex logic tree scenarios, you will learn how to use nested IF functions to evaluate data. You will then learn about lookup functions — these are widely used in data analytics for connecting data sets as well as error checking. You will learn about one of the most widely used lookup functions in business — VLOOKUP, as well as other lookup functions such as INDEX and MATCH, as well as Excel newest lookup function — XLOOKUP. The topics you will be learning this week: Performing logical operations with IF; Applying advanced logical operations; Categorising data with VLOOKUP; Matching data with VLOOKUP and XLOOKUP; and Advanced data matching with INDEX and MATCH.

3 hours to complete

6 videos (Total 40 min), 3 readings, 6 quizzes

6 videos

Week 5 Introduction6mApply logical operations to data using IF6mNested IF, AND and OR6mCategorising data with VLOOKUP5mMatching data with VLOOKUP and XLOOKUP7mPower Lookups with INDEX and MATCH9m

3 readings

Download the Week 5 workbooks10mWeek 5 Practice Challenge20mWeek 5 Toolbox10m

6 practice exercises

Apply logical operations to data using IF10mNested IF, AND and OR10mCategorising data with VLOOKUP10mMatching data with VLOOKUP and XLOOKUP10mPower Lookups with INDEX and MATCH10mLogical and lookup functions: Test your skills30m

1 hour to complete

Final assessment

1 hour to complete

Reviews

4.8

823 reviews

5 stars
81.87%
4 stars
15.73%
3 stars
1.58%
2 stars
0.25%
1 star
0.54%

TOP REVIEWS FROM EXCEL FUNDAMENTALS FOR DATA ANALYSIS

by MAJan 16, 2023

I thank the lecturers, Dr. Prashan and Professor Nikki. I learned the basics in an excellent and strong manner. Indeed, a strong foundation that encouraged me to complete the specialization.

by FVJul 17, 2020

Well done. A small thing, though: in the quizzes, the grading could be made more fault-tolerant. Example: an answer such as AND(y,x) instead of AND(x,y) is deemed wrong, which borders on the nasty.

by GGMar 26, 2022

Thank you for this course! It was a lot of fun and easy to follow. I loved the assignements when not only that weeks knowledge was tested but also something randomly from previous weeks.

by YYAug 13, 2021

I cannot tell you how grateful I am for these amazing practical Excel courses you have gathered. Thank you Nicky, thank you Prashan. You explain everything so clearly and perfectly.

View all reviews

About the Excel Skills for Data Analytics and Visualization Specialization

As data becomes the modern currency, so the ability to quickly and accurately analyse data has become of paramount importance. Therefore, data analytics and visualization are two of the most sought after skills for high paying jobs with strong future growth prospects. According to an IBM report, the Excel tools for data analytics and visualization are among the top 10 competencies projected to show double-digit growth in their demand. This course will help you develop your analytical and visualization skills so that you not only improve your current work performance but also expand your future job prospects. For those in business and data analysis who want to master advanced Excel and beginner Power BI , that will add an asset to your employability portfolio.

Upon completing this specialization, you will be able to bring data to life using advanced Excel functions, creative visualizations, and powerful automation features. These courses will equip you with a comprehensive set of tools for transforming, linking, and analysing data. You will master a broad range of charts and create stunning interactive dashboards. Finally, you will explore a new dimension in Excel with PowerPivot, Get and Transform, and DAX. Harnessing the power of an underlying database engine, we will remove the 1,048,576 row limitation, completely automate data transformation, create data models to effectively link data, and open the gateway to Power Business Intelligence.

Excel Skills for Data Analytics and Visualization

Frequently Asked Questions

When will I have access to the lectures and assignments?
Access to lectures and assignments depends on your type of enrollment. If you take a course in audit mode, you will be able to see most course materials for free. To access graded assignments and to earn a Certificate, you will need to purchase the Certificate experience, during or after your audit. If you don't see the audit option:
The course may not offer an audit option. You can try a Free Trial instead, or apply for Financial Aid.
The course may offer 'Full Course, No Certificate' instead. This option lets you see all course materials, submit required assessments, and get a final grade. This also means that you will not be able to purchase a Certificate experience.
What will I get if I subscribe to this Specialization?
When you enroll in the course, you get access to all of the courses in the Specialization, and you earn a certificate when you complete the work. Your electronic Certificate will be added to your Accomplishments page - from there, you can print your Certificate or add it to your LinkedIn profile. If you only want to read and view the course content, you can audit the course for free.
Is financial aid available?
Yes. In select learning programs, you can apply for financial aid or a scholarship if you can’t afford the enrollment fee. If fin aid or scholarship is available for your learning program selection, you’ll find a link to apply on the description page.

More questions? Visit the Learner Help Center.

Build employee skills, drive business results

Discover Coursera for Business

Excel Fundamentals for Data Analysis

About this Course

What you will learn

Skills you will gain