Excel Fundamentals for Data Analysis

Macquarie University via Coursera

Go to class Write review

Details

Go to class

Provider

Coursera
Pricing

Free Online Course (Audit)
Languages

English
Certificate

Paid Certificate Available
Duration & workload

15 hours 4 minutes
Sessions

On-Demand
Level

Intermediate
Subtitles

Arabic, French, Portuguese, Italian, German, Russian, English, Spanish, Thai, Indonesian, Kazakh, Hindi, Swedish, Korean, Greek, Chinese, Ukrainian, Japanese, Polish, Dutch, Turkish, Hungarian, Bengali, Pashto, Urdu, Azerbaijani, Farsi

Found in

Part of

Excel Skills for Data Analytics and Visualization

Overview

Class Central Tips

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.

Syllabus

Welcome and critical information
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.

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.

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.

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.

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.

Final assessment

This is the final assessment of the course and it is worth 25% of your total grade. As this is the final assessment, it requires you to demonstrate the learning objectives from each of the weeks — and thus requires you to demonstrate all the learning objectives of the entire course.

Taught by

Dr Prashan S. M. Karunaratne, FHEA

Reviews

4.8 rating, based on 6 Class Central reviews

4.8 rating at Coursera based on 3635 ratings

Start your review of Excel Fundamentals for Data Analysis

Suparn Padma Patra @suparn

I really enjoyed this course, spent ~3 hours a week for 5 weeks and found it to be easy. Positive things I found in the course: - In the beginning of the course, both the instructors used to talk about the week syllabus and high level overview. - E…

I really enjoyed this course, spent ~3 hours a week for 5 weeks and found it to be easy.

Positive things I found in the course:
- In the beginning of the course, both the instructors used to talk about the week syllabus and high level overview.
- Every week contains a toolbox section which has a pdf containing shortcuts, concepts in nutshell and many more. It really acts as a quick reference guide.
- Concepts are explained nicely by both the instructors.
- On an average length of videos is ~10minutes followed by a quiz of ~5 questions (which I personally prefer)
- Course covers topics which are useful for to speed up process and I felt it to be for beginners only (But coursera course page shows intermediate level)

Negative parts (happened to me, might not have happended to other)
- For week four, excel sheet upon which qxam was based was missing for me.
- I'm a macOS user and some functionality is not available.
- Since I mostly use Google sheets, this course personally didn't helped me much. But could be super useful for Microsoft Excel users.

Useful topics includes:
- Data cleaning
- Working with dates and numbers
- Conversion to tables and working with it.
Rui Ma @ruima

This is the first course I learned in a study group with other three members from Class Central. Memories about our happy biweekly virtual meetings, anxiety of quizzes and assignments, excitement of receiving the certificate are still vivid in my…

This is the first course I learned in a study group with other three members from Class Central. Memories about our happy biweekly virtual meetings, anxiety of quizzes and assignments, excitement of receiving the certificate are still vivid in my mind. Hope we can play games together during virtual meetings someday ^^.

Positive "peer-pressure" plays an important role in motivating me to persist in learning and finally complete the whole course, although I don't think dropping out of an online course halfway is a serious problem as today we have more freedom to modify our online learning journey.

The course is taught with common excel cases, which we will probably have in day-to-day work. Videos are well made and presented, making the whole learning process more intuitively. Doing hands-on assignments helps me improve a lot rather than only watching videos.

However, there are some differences between Excel on Mac and on Windows, or Excel on Mac lacks some functionalities. For example, there is no Name Manager on Mac Excel and filter by percentage is unavailable. Besides, shortcuts are also slightly different.
Dhawal Shah @dhawal

I did this course as part of a internal study group at Class Central. We were four people in four different countries: India, United States, Australia, and China. As a programmer for almost a decade, this course wasn't too difficult, yet informativ…

I did this course as part of a internal study group at Class Central. We were four people in four different countries: India, United States, Australia, and China.

As a programmer for almost a decade, this course wasn't too difficult, yet informative. Each week it took me 2-3 hours to complete and I became familiar with a few Excel features (like Tables) that I could apply immediately to my personal/work life.

Each week the course provides you with an excel sheets with some data and you can work with them while following the videos. Each week there are fives videos, followed by a quiz, a weekly practice exam and a final graded quiz.

At the end of the course there is also a final exam. The support in the forums was non-existent and we found that for some people in the study group the excel sheet in the final exam was not available to download. There were similar complains on the forums that weren't being answered.

This course expects you to have Excel on a Windows. As a Mac user, things were different and/or didn't exist in Excel on Mac.
Mifta Uddin @mifta

Most important program. Data and analytics show how people in APAC experience your brand across platforms. Gain a better understanding of the performance of each channel in your marketing mix, and learn from marketing leaders who are delivering results.
Pat Bowden @pat

Quite a challenging course, but very useful for learning about some of the many features of Excel. I took longer than the "recommended" 15 hours/5 weeks and I reset my deadlines without penalty. This course helped me create a household budget spreadsheet (as a personal project).
Anonymous

This Course is quite good, espcailly with Practice will give you permante. But if there are a deeper questions linked to real life after each video, it will be better.