LF logo
by learnformula
search
Log in
search
Courses/Office Productivity/MS Excel

Advanced Excel Function Mastery: Unlock Powerful Data Tools

Refine your skills with advanced functions, dynamic arrays, and practical techniques.

Created byAssemble You
4.9
(43 reviews)
BeginnerUpdated Dec 2, 2024
Advanced Excel Function Mastery: Unlock Powerful Data Tools

What You'll Learn

check_circleDiscover the application of Excel's LARGE and SMALL functions to efficiently analyse data while incorporating advanced techniques like XLOOKUP for seamless data association
check_circleDiscover the SUMIFS function in Excel and how to proficiently conduct data analysis with both single and multiple criteria
check_circleLearn to efficiently manipulate and present data using the TOCOL and TOROW functions, to enhance your spreadsheet skills and stay ahead in data analysis
check_circleLearn to utilise the LAMBDA function in Excel to create custom functions, simplifying complex formulas and enhancing efficiency in data analysis.
check_circleLearn how to efficiently count and sum cells based on their background fill colour in Microsoft Excel using the SUBTOTAL function and filters
check_circleLearn how to use advanced VLOOKUP techniques to efficiently extract and display multiple columns of data from a table in a single formula, leveraging dynamic array-like functionality to simplify and streamline your data lookups
check_circleLearn how to use the SORT and SORTBY functions in Excel to organise data dynamically. Discover how to apply these functions to sort by multiple criteria, update your sorted data automatically, and output your results anywhere in your workbook. Gain insights into the differences between SORT and SORTBY, and enhance your data management skills with these powerful tools available in Excel 365 and Excel 2021
check_circleLearn how to use the CHOOSECOLS function in Microsoft 365 to extract and display specific columns from a larger dataset, enabling more focused data analysis and streamlined data management.
check_circleLearn how to use the PivotBy function in Excel to create dynamic, pivot table-style reports with formula-driven data analysis, including real-time updates and customisation options
check_circleDiscover how to effectively use the IMAGE function in Microsoft 365 Excel to import company logos into your spreadsheet

About This Course

Elevate your Excel skills with this comprehensive lesson.

Explore hidden gems like the LARGE, SMALL, TOCOL, and TOROW functions for refined data manipulation and analysis.

Master the versatility of SUMIFS for handling complex conditions, unlock the power of LAMBDA for creating custom functions, and learn advanced VLOOKUP techniques for dynamic arrays.

We’ll also show you how to COUNT and SUM cells by background color without VBA. Packed with practical examples and step-by-step guides, this lesson equips you to tackle advanced Excel challenges with ease.

This program includes the following lessons:

  • The LARGE and SMALL Functions in Excel
  • STOP Using SUMIF in Excel
  • New Functions TOCOL and TOROW in Excel
  • A Simple LAMBDA in Excel
  • COUNT and SUM by Cell Color in Excel
  • Return Multiple Columns with VLOOKUP
  • SORT vs. SORTBY in Excel
  • Pick Columns from a Dataset with CHOOSECOLS in Excel
  • Build One Pivot Table from Multiple Sources with Power Pivot in Excel
  • The IMAGE Function in Excel
  • The AND Function with Conditional Formatting in Excel
  • IF with Partial Match in Excel
  • GROUPBY in Excel

Your Instructor

Assemble You
Assemble You
menu_book12 courses
star1,030 reviews

Assemble You teaches the Power Skills you need to build the best you. Skills unique to humans. Leadership, Productivity, Wellbeing, Project Management, Communication, Intrapreneurship, Strategy, Systems, Ethics, and so much more. Each Assemble You course includes impactful video lessons, downloadable key-point infographics and ends with a short test. Become the best you with Assemble You.

Credit Information

Do these courses count toward my professional development requirements?

This portal is provided as a training and development resource for City of Markham employees. Every course is delivered by a qualified subject matter expert or learning organization, is quantifiable in hours, and is verifiable — you receive a documented certificate of completion for every course you finish, stored on LearnFormula indefinitely.

If you hold a professional designation (for example in engineering, accounting, human resources, or law), courses may be counted as professionally relevant, verifiable learning activities toward your continuing professional development. Individual practitioners are responsible for confirming that an activity meets the requirements of their professional body. For questions about the City of Markham's training and development policies, please speak with your people leader or Human Resources.

What Students Are Saying

4.9
Student's Choice
43 reviews

Frequently Asked Questions

We are a registered provider with 327+ associations and regulatory bodies worldwide. We operate across 29 global markets including Canada, the US, Australia, and the UK. Every course page clearly displays its specific accreditations. Upon completion, you receive a professional certificate that can be validated online. Our certificates include all necessary accreditation details, credit hours, and completion dates, and are formatted specifically to meet the submission requirements of most global regulatory bodies.