Everyday Excel, Part 2

Everyday Excel, Part 2

This course is part of Everyday Excel Specialization

Instructor: Charlie Nuttelman

Access provided by SR University

53,666 already enrolled

5 modules

Gain insight into a topic and learn the fundamentals.

572 reviews

Intermediate level

Recommended experience

Flexible schedule

2 weeks at 10 hours a week

Learn at your own pace

94%

Most learners liked this course

5 modules

Gain insight into a topic and learn the fundamentals.

572 reviews

Intermediate level

Recommended experience

Flexible schedule

2 weeks at 10 hours a week

Learn at your own pace

94%

Most learners liked this course

What you'll learn

Dynamic array functions and formulas in Excel
A variety of lookup techniques including: two-way forward, one-way reverse, and two-way reverse lookups, as well as Top 5/Bottom 5 techniques
Advanced data management tools (filtering, conditional formatting, randomizing techniques)
How to create dashboards using Pivot Tables, Pivot Charts, and Power Query

Skills you'll gain

Tools you'll learn

Details to know

Shareable certificate

Add to your LinkedIn profile

Assessments

5 assignments

Taught in English

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

Learn more about Coursera for Business

logos of Petrobras, TATA, Danone, Capgemini, P&G and L'Oreal

Build your subject-matter expertise

This course is part of the Everyday Excel Specialization

When you enroll in this course, you'll also be enrolled in this Specialization.

Learn new concepts from industry experts
Gain a foundational understanding of a subject or tool
Develop job-relevant skills with hands-on projects
Earn a shareable career certificate

There are 5 modules in this course

"Everyday Excel, Part 2" is a continuation of the popular "Everyday Excel, Part 1". Building on concepts learned in the first course, you will continue to expand your knowledge of Excel, particularly with respect to the new (as of 2020) dynamic array functions and formulas. Dynamic array formulas create the foundation for "modern" Excel. This course is aimed at intermediate users, but even advanced users will pick up new skills and tools in Excel. By the end of this course, you will have the skills and tools to take on the project-based "Everyday Excel, Part 3 (Projects)".

This course is the second part of a three-part series and Specialization that focuses on teaching introductory through very advanced techniques and tools in Excel. In this course (Part 2), you will: 1) learn dynamic array functions and how to create dynamic array formulas to accomplish many productive tasks in Excel; 2) become affluent in a variety of lookups (two-way forward lookups, one-way reverse lookups, and two-way reverse lookups) and create Top 5/Bottom 5 lists; 3) learn numerous advanced data management techniques, including filtering for multiple criteria, conditional drop-down lists, reconciliation problems, creating repeating arrays of other arrays and sequences, and randomizing (with and without repetition); and 4) learn how to create professional dashboards using Pivot Tables, Pivot Charts, and Power Query. The course is organized into 5 Weeks (modules). To pass each module, you'll need to complete two programming assignments and a mastery quiz (the final module does not have programming assignments). Given the wide range in experience and abilities of learners, the goal of the course is to appeal to a wide audience. This course is meant to be fun and thought-provoking. I hope for you to at least several times in the course say to yourself, "Wow, I hadn't thought of that before!"

Module 1 of the course introduces dynamic arrays and dynamic array functions, which provide the foundation for "modern" Excel. You will learn what dynamic arrays and dynamic array functions are, and you'll be introduced to some of the more common dynamic array functions in Excel, including the SORT, SORTBY, UNIQUE, and FILTER functions. The FILTER function, in particular, is a function of great utility. Several examples highlight the FILTER function and its capabilities. You will end the module with two programming assignments and a quiz.

What's included

12 videos3 readings1 assignment2 programming assignments

12 videosTotal 71 minutes

Welcome to Part 2 of Everyday Excel!3 minutes
What Is an Array in Excel?8 minutes
What Are Dynamic Array Functions?8 minutes
Dynamic Array Formulas Don’t Work Inside Excel Tables 2 minutes
SORT and SORTBY11 minutes
The SORT/UNIQUE Combination5 minutes
Randomizing a List4 minutes
Sorting by a List5 minutes
The FILTER Function7 minutes
Example: Who Earned a Bonus?5 minutes
Example: A Filtered Checklist5 minutes
Example: Filtering for Snowy Days6 minutes

3 readingsTotal 12 minutes

Course Updates and Accessibility Support1 minute
Microsoft 365 is Required for This Course1 minute
The Power of Microsoft Excel10 minutes

1 assignmentTotal 20 minutes

Module 1 Quiz20 minutes

2 programming assignmentsTotal 120 minutes

Filtering and Sorting Area Codes60 minutes
Filtering and Sorting Error Codes60 minutes

Module 2 is focused on advanced dynamic array functions, essentially an extension of Module 1. You will learn all about the SEQUENCE, RANDARRAY, HSTACK, VSTACK, DROP, TAKE, CHOOSECOLS, CHOOSEROWS, TOCOL, TOROW, WRAPCOLS, WRAPROWS, BYCOL, and BYROW dynamic array functions. Starting in around 2020, these functions have revolutionized the way in which people utilize Excel. Several examples demonstrate how these functions can be used for real-world applications. Module 2 concludes with two programming assignments and a quiz. Good luck!

What's included

10 videos1 assignment2 programming assignments

10 videosTotal 79 minutes

The SEQUENCE and RANDARRAY Functions10 minutes
The HSTACK and VSTACK Functions8 minutes
The DROP and TAKE Functions8 minutes
The CHOOSECOLS and CHOOSEROWS Functions5 minutes
Examples: The CHOOSECOLS Function9 minutes
The TOCOL and TOROW Functions4 minutes
The WRAPCOLS and WRAPROWS Functions6 minutes
The BYCOL and BYROW Functions10 minutes
Example: Copy Machine Sales8 minutes
Example: Averaging Grades by Category12 minutes

1 assignmentTotal 20 minutes

Module 2 Quiz20 minutes

2 programming assignmentsTotal 120 minutes

Team Builder60 minutes
Bookstore Orders60 minutes

Excel is a wonderful tool for performing lookups related to large data sets. In Module 3, you will review lookup functions and learn how to use the FILTER function for multiple matches. You will also learn how to reference/extract an entire row or column of an array. You will then learn how to perform two-way forward lookups, reverse one-way (or constrained) lookups, and two-way reverse lookups, and how to create Top 5/Bottom 5 lists. Power Query is great for performing two-way reverse lookups. The module concludes with two programming assignments and a quiz. Have fun!

What's included

13 videos1 assignment2 programming assignments

13 videosTotal 119 minutes

Review of Lookup Functions13 minutes
Using the FILTER Function for Multiple Matches5 minutes
Referencing an Entire Row or Column of an Array11 minutes
Two-Way Forward Lookups, Part 18 minutes
Two-Way Forward Lookups, Part 210 minutes
Top 3 or Bottom 37 minutes
Example: Top 5 Hottest and Coldest Days10 minutes
Reverse Lookups13 minutes
Example: Max Sales Lookup10 minutes
Two-Way Reverse Lookups, Part 19 minutes
Two-Way Reverse Lookups, Part 25 minutes
Example: Yearly Max and Top 5 Using TOCOL14 minutes
Example: Yearly Max and Top 5 Using Power Query6 minutes

1 assignmentTotal 20 minutes

Module 3 Quiz20 minutes

2 programming assignmentsTotal 120 minutes

Order ID and Customer Lookup60 minutes
Cheese Consumption60 minutes

This is perhaps the instructor's favorite module of the entire course. In Module 4, you will learn advanced filtering techniques to filter data for multiple criteria. Next, you will explore advanced conditional formatting tools and techniques, including advanced conditional drop-down lists, and you will learn how to perform reconciliation calculations (useful in the finance industry). Finally, you will learn all about randomizing data, both with and without repeats, and you will learn how to create arrays of repeating arrays and sequences as well as randomizing blocks of similar items. There are plenty of worked examples to reinforce your learning and to give you ideas of how you can apply this material to your own career or life. Module 4 concludes with two programming assignments and a quiz. Hope you enjoy!

What's included

16 videos1 assignment2 programming assignments

16 videosTotal 147 minutes

Filtering for Multiple Criteria9 minutes
Example: Filtering Company Transactions8 minutes
Example: Filtering Job Applicants for Multiple Criteria13 minutes
Example: Identifying Temperature Excursions13 minutes
Conditional Counting or Summing in the Same Column6 minutes
Conditional Formatting for Two-Way Lookups8 minutes
Conditional Drop-Down Lists (Data Validation)9 minutes
Example: Dinner Sign-Up11 minutes
Comparing Lists7 minutes
Who Hasn't Signed Up?8 minutes
Randomizing: Repeats Allowed15 minutes
Randomizing: Repeats Not Allowed8 minutes
Repeating Arrays and Sequences15 minutes
Randomizing Blocks of Similar Items6 minutes
Example: Randomized Schedule Maker7 minutes
Bike Inventory Assignment Preview and Hints6 minutes

1 assignmentTotal 20 minutes

Module 4 Quiz20 minutes

2 programming assignmentsTotal 120 minutes

Bike Inventory60 minutes
Transaction Audit60 minutes

Module 5, which is the final module of the course, will take you through the development of a real-world, professional dashboard using data from the web. You will use Pivot Tables and Pivot Charts, as well as Power Query. In contrast to all other modules of the course, there are no programming assignments in Module 5. However, there is one final quiz that you must take in order to pass the course and earn your Course Certificate. Have fun, and best of luck!

What's included

6 videos1 reading1 assignment

6 videosTotal 62 minutes

Introduction to the World Development Indicators Dashboard5 minutes
Step 1: Data Retrieval5 minutes
Step 2: Data Processing11 minutes
Step 3: Electricity Data vs. Time16 minutes
Step 4: Electricity and Energy Breakdown for a Specific Year18 minutes
Step 5: Top 5 Electric Power Consumption and Energy Use9 minutes

1 readingTotal 1 minute

Web Link to World Development Indicators Data1 minute

1 assignmentTotal 20 minutes

Module 5 Quiz20 minutes

Earn a career certificate

Add this credential to your LinkedIn profile, resume, or CV. Share it on social media and in your performance review.

Instructor

Instructor ratings

(191 ratings)

Charlie Nuttelman

University of Colorado Boulder

10 Courses482,714 learners

Offered by

University of Colorado Boulder

Why people choose Coursera for their career

Felipe M.

Learner since 2018

"To be able to take courses at my own pace and rhythm has been an amazing experience. I can learn whenever it fits my schedule and mood."

Jennifer J.

Learner since 2020

"I directly applied the concepts and skills I learned from my courses to an exciting new project at work."

Larry W.

Learner since 2021

"When I need courses on topics that my university doesn't offer, Coursera is one of the best places to go."

Chaitanya A.

"Learning isn't just about being better at your job: it's so much more than that. Coursera allows me to learn without limits."

Learner reviews

5 stars
84.09%
4 stars
11.18%
3 stars
2.27%
2 stars
0.17%
1 star
2.27%

Showing 3 of 572

Reviewed on Aug 14, 2020

Charlie is an AMAZING instructor - I learned a lot, and I wouldn't have if it weren't for his teaching style!

Reviewed on Oct 3, 2020

sir charlie explains things very well , the assignments are good . the course was nice , hope to get more examples regarding business scenarios too.Nice course

Reviewed on Nov 18, 2020

I have a great time during this course. I suggest everyone join this course, it helps for your future life. Thanks Charlie.

View more reviews