Udemy
    •  
    •  
    •  
    •  
    •  
    •  
    •  
    •  
Subscribe
Turn what you know into an opportunity and reach millions around the world.
Learn More
Your cart is empty.
Keep shopping
Dynamic Array Functions in Excel
Rating: 4.2 out of 5(3 ratings)
23 students

Dynamic Array Functions in Excel

Learn Dynamic Array Functions to simplify, automate, and transform your Excel data analysis with practical examples.
Created bySimon Sez IT
Last updated 11/2024
English

What you'll learn

  • Identify and apply the UNIQUE function to extract distinct values from data.
  • Use the SORT and FILTER functions to organize and display data based on specific criteria.
  • Create sequences and generate random data sets using SEQUENCE and RANDARRAY functions.
  • Perform advanced searches with XLOOKUP and XMATCH to find and retrieve specific data efficiently.

Course content

1 section • 25 lectures • 2h 18m total length

  • Course Introduction2:17
  • WATCH ME: Essential Information for a Successful Training Experience2:11
  • DOWNLOAD ME: Course Files0:24
  • DOWNLOAD ME: Exercise Files0:24
  • What are Dynamic Arrays?2:13
  • Introduction to Spills and Arrays10:47
  • Extract and Count Unique Entries6:13
  • Unique vs Distinct3:21

    Explain the difference between distinct and unique in Excel's dynamic array functions: distinct returns each value once, while unique returns values that appear exactly once, demonstrated with country data.

  • Extract Unique Values with Multiple Criteria3:10
  • Extract Unique Values by Column4:16
  • The SORT Function8:53
  • The SORT Function - Horizontal SORT6:05
  • The SORTBY Function6:09
  • The SORTBY Function - Horizontal SORT3:05

    Learn to perform a horizontal sort with the sort by function, using an order array to rearrange columns into the sequence: title, first name, last name, department, salary.

  • Simple SEQUENCE and Unstacking Records10:09
  • The FILTER Function6:51

    Use the dynamic array filter function in Excel 2021 to filter an exam results table by exam, date, and grade, with data validation and unique and sort lists.

  • FILTER with Logic: + Operator (OR)4:48
  • FILTER with Logic: * Operator (AND)5:39
  • FILTER with Logic: = Operator (Both or Neither)4:12
  • FILTER with Logic: - Operator (One or the Other)4:49
  • Using RANDARRAY and RANDBETWEEN to Randomize Data9:43
  • Using XLOOKUP to Perform Complex Lookups12:21
  • Using XMATCH6:18
  • Course Exercise12:55
  • Course Conclusion0:50

    Congratulations on completing the course on dynamic array functions in Excel. Keep practicing and exploring to unlock exciting possibilities with Excel.

  • Section Quiz

Requirements

  • Basic Excel skills.
  • Beginner to intermediate Excel user.
  • Access to Microsoft Excel 365 or Excel 2021.

Description

**This course includes downloadable course instructor files and exercise files to work with and follow along.**


Harness the power of dynamic arrays to revolutionize your data analysis in this course, "Dynamic Array Functions in Excel." This course will cover new dynamic array functions in Excel to automate and simplify your work.


Discover how to extract unique values from a dataset using UNIQUE. Effortlessly sort data with SORT and customize sorting with SORTBY. Apply specific criteria to filter data through FILTER and create numeric sequences effortlessly with SEQUENCE. Utilize RANDARRAY and RANDBETWEEN to inject random data into your spreadsheets. Replace traditional lookup functions with the more efficient XLOOKUP and XMATCH.


By the end of this course, you should be able to adapt and streamline data processing, enhance analytical capabilities, and inject efficiency into your Excel tasks. This course will give you practical examples to apply immediately, making your Excel projects more dynamic, efficient, and impactful.


After taking this course, students will be able to:

  • Identify and apply the UNIQUE function to extract distinct values from data.

  • Use the SORT and FILTER functions to organize and display data based on specific criteria.

  • Create sequences and generate random data sets using SEQUENCE and RANDARRAY functions.

  • Perform advanced searches with XLOOKUP and XMATCH to efficiently find and retrieve specific data.


This course includes:


  1. 2 hours and 15 minutes of video tutorials

  2. 22 individual video lectures

  3. Course and Exercise Files to follow along

  4. Certificate of completion

Who this course is for:

  • Students and learners who want to improve their Excel skills for projects, assignments, or data analysis tasks.
  • People working in offices, especially in roles like data analysis, finance, or administration, who deal with organizing and processing large amounts of data.
  • Job Seekers: Individuals looking to boost their resume by learning modern Excel features that are in demand.

Report abuse