Everyday Excel, Part 2

University of Colorado Boulder via Coursera

Go to class Write review

Details

Go to class

Provider

Coursera
Pricing

Paid Course
Languages

English
Certificate

Certificate Available
Effort

21 hours 57 minutes
Sessions

Self-Paced
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

Everyday Excel

Overview

Google, IBM & Meta Certificates — All 10,000+ Courses at 40% Off

One annual plan covers every course and certificate on Coursera. 40% off for a limited time.

Get Full Access

"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!"

Syllabus

Dynamic Array Functions

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.

Advanced Dynamic Array Functions

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!

All About Lookups

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!

Advanced Data Management

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!

Case Study: Building a Dashboard Using Pivot Tables and Pivot Charts

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!