Xlessentials Eap M2

The What, Why, and How of Excel Preview 04:25

In this lesson, we'll take a high level look at Excel and review a few real world examples to understand why Excel is so popular and how it is used in industry to solve problems.

Excel Components and File Extensions Preview 02:36

Learn how workbooks, worksheets, ranges, and cells relate to one another and about the most common file extensions for Excel.

Excel's User Interface: The Ribbon Preview 04:55

Learn about Excel's ribbon.

Excel's User Interface: Quick Access and Formula Toolbars Preview 04:06

Learn about the Quick Access and Formula toolbars.

Excel's User Interface: The Worksheet Preview 03:58

Learn how to add/remove, move/copy, rename and navigate worksheets.

Introduction to Formulas and Functions Preview 02:57

Learn the order of operations and structure of formulas and functions.

Formulas and Functions

Show off your newly acquired knowledge of formulas and functions!

Order of Operations and Formulas Preview 06:48

Learn how to write simple formulas and how to apply the order of operations in Excel.

Using Excel's Built in Functions Preview 13:18

Learn how to write formulas using Excel's built in functions. We'll review how to use function tool tips, discuss the different types of arguments, and demonstrate how to provide functions hard-typed values, as well as, values using references.

Understanding Cell References Preview 12:02

Learn why cell references are like street addresses and understand the impact copy/paste, insert, delete, and move operations have on relative and absolute cell references.

Cell References

Test your skills on cell references!

Module 2 Overview Preview 02:44

Provides an overview of the material covered in Module 2: Process and Transform Data

Module 2.1 Overview Preview 01:34

Get an overview of the material covered in Module 2.1 - Performing Data Aggregation. The workbooks used in this module are attached as resources.

Simple Aggregation Functions Preview 12:00

Learn how to perform simple data aggregations and make use of data with the sum, count, counta, min, max, and average functions.

Simple Aggregation Functions

Show off your knowledge of the simple aggregation functions!

Single Criteria Conditional Aggregation: Static Criteria Preview 08:13

Learn how to use the countif, sumif, and averageif functions to aggregate data conditionally (i.e., data that meets a specific criteria).

Single Criteria Conditional Aggregation: Dynamic Criteria Preview 08:59

Using the countif, sumif, and averageif functions, build a sales report that dynamically aggregates car sales data to display metrics about a day's sales.

Single Criteria Conditional Aggregation: >, <, and <> Conditions Preview 10:18

Expand your data aggregation capabilities by learning how to test greater than, less than, and not equal to test conditions.

Single Criteria Conditional Aggregation: Combining Aggregations Preview 03:39

Build a management reporting tool that shows the percentage complete for a selected project using the single criteria conditional aggregation functions.

Single Criteria Conditional Aggregation Functions

Show off your knowledge of the single criteria conditional aggregation functions.

Multiple Criteria Conditional Aggregation Preview 10:11

Get an overview of the multiple criteria conditional aggregation functions and build a dynamic table using the countifs function that powers a dynamic chart.

Multiple Criteria Conditional Aggregation: AND Testing Preview 04:40

Get more practice using the averageifs function to aggregate data on multiple conditions when all criteria need to be true ("and testing"). We'll see how the multiple criteria conditional aggregation functions can be used on both vertical and horizontal data sets.

Multiple Criteria Conditional Aggregation: OR Testing Preview 04:13

Learn how to creatively perform "OR testing" by combining two or more single criteria conditional aggregation functions together.

Multiple Criteria Conditional Aggregation: Using Arrays for OR Testing Preview 06:42

Review an array formula cheat sheet to get a small taste of array formulas and then learn how to combine arrays with the single criteria conditional aggregation functions to perform or testing.

Multiple Criteria Conditional Aggregation: More on OR Testing Preview 08:17

Work an additional problem where you get to use array formulas to perform OR testing on a single range. Then expand your OR testing capabilities by learning how to perform OR testing on criteria that applies to more than one row or column.

Multiple Criteria Conditional Aggregation Functions

Show off your knowledge of the multiple criteria conditional aggregation functions!

Bonus: Using Array Multiplication to Aggregate Data from Many Columns Preview 05:03

Review an example of how array multiplication can be used to aggregate the data from many columns at once.

Database Aggregation Functions: Overview Preview 03:39

Obtain a high level overview of the 6 database aggregation functions we will be covering in this course. (DCOUNT, DCOUNTA, DSUM, DMIN, DMAX, and DAVERAGE)

Database Aggregation Functions: Building a Reporting Tool Preview 10:03

Learn how to build a dynamic reporting tool, using the database aggregation functions, that allows you to quickly answer questions about a data set.

Database Aggregations: Advanced Searches Preview 05:30

Learn how to limit data on a range of values and learn how to use Excel's wildcards ("?" and "*") to increase your database aggregation capabilities.

Database Aggregation Functions

Show off your knowledge of the multiple criteria conditional aggregation functions!

Performing Data Aggregation: Wrap Up Preview 03:05

Recap the key concepts you learned in Module 2.1.

Module 2.2 Overview Preview 01:17

Get an overview of the material covered in Module 2.2 - Performing Logical Operations. The workbooks used in this module are attached as resources.

Logical Operators Preview 08:11

Learn how to answer questions about your data by using the AND / OR functions to evaluate a series of logical tests or values.

Conditional Functions Overview Preview 03:41

Learn how to use the if and iferror functions to conditionally control the outcome of formula.

More on Conditional Functions: The If Function Preview 07:44

Use the if function to build a tool that reports on the adherence to a monthly budget. Also, learn how to expand the power of the if function by combining it with the and / or functions.

Handling more than two Outcomes: Nesting If Functions Preview 06:29

Learn how if functions can be nested together to handle situations that require more than two possible outcomes.

Simple Error Handling with the Iferror Function Preview 02:52

Learn how to make your tools more robust by using the iferror function to gracefully handle errors.

Logical Value Functions: True, False, and Not Preview 03:02

Learn how to use the not function to inverse logical values.

Logical Operations

Show off your knowledge of logical operations in Excel.

Performing Logical Operations: Wrap Up Preview 01:42

Recap the key concepts you learned in Module 2.2.

Module 2.3 Overview Preview 02:41

Get an overview of the material covered in Module 2.3 - Mastering Lookups and References. The workbooks used in this module are attached as resources.

Choose Function: An Overview and Simple Example Preview 03:35

Get an overview of the choose function and understand what makes it unique among its lookup counterparts.

Choose Function: Making Other Functions Dynamic Preview 08:54

Learn how to leverage the choose function to provide range inputs to other functions dynamically based on a user input. As a bonus, learn another method (combo box) to create a drop down menu in Excel and learn why this style of drop down complements the choose function so well.

Choose Function: Transforming Quality Scores and Selecting a Forecast Model Preview 07:00

Learn how the choose function handles decimal values and how to use that knowledge to transform quantitative values into qualitative ones. Also, learn how the choose function can be used to build dynamic tools by allowing users to select a calculation to perform.

Choose Function: Nesting Functions Inside Choose Preview 05:42

Learn how nesting functions within the choose function can be helpful. You will learn how to return the quarter given a date and you will learn how to dynamically perform different calculations based on user input.

The Choose Function

Show off your knowledge of the choose function.

Vlookup and Hlookup: An Overview Preview 03:41

Learn about Excel's most popular lookup functions - vlookup and hlookup. We'll discuss the difference between vlookup and hlookup and review an example of how they work.

Vlookup: Key Concepts Preview 10:09

Learn some very important concepts about the vlookup function by working a simple example. Here you will learn why its important to lock down the range for the table you are searching, what causes the vlookup function to return the value not found and reference errors, some common reasons why those errors can be misleading, and what happens when the table you are searching contains duplicate values.

Vlookup: Adding Meaning to Classifications Preview 06:30

Work a more realistic example and learn how to use the vlookup function to make a report meaningful by translating a set of classification codes into their descriptions.

Vlookup: Making it Unique Preview 06:50

Learn a tactic to help you lookup information even when you don't have a unique field.

Vlookup: Understanding Approximate Match Preview 05:26

Learn how to use vlookup's approximate match by building a tool to calculate the commission percentage due based on a multi-tier sales structure.

Vlookup: A Federal Income Tax Calculator Preview 08:07

Put your knowledge of the approximate match to use and build a federal income tax calculator.

Vlookup: Searching Multiple Tables Preview 02:53

Learn how the choose function can be combined with the vlookup function to let you dynamically lookup information from different tables.

Hlookup: More of the Same, Kind of Preview 06:39

Learn how to use the hlookup function. Recognize that everything you just learned about vlookup applies to hlookup as well

The Vlookup and Hlookup Functions

Show off your knowledge about the vlookup and hlookup functions.

Index and Match: An Overview Preview 03:31

Learn about Excel's most powerful lookup functions - index and match.

Match: Finding the Position Preview 04:16

Learn how to use the match function to return the relative position of an item from a list.

Match: Understanding the Approximate Match Preview 05:56

Learn how the greater than and less than approximate match types work with the match function.

Index: A Special Case with 1D Arrays Preview 06:39

Learn how to use the index function to lookup information from a one-dimensional array (data in a single column or single row) and learn a time saving tip for copying and pasting values inside of formulas.

Index: Getting Values from a Table Preview 05:20

Learn how to use the index function to retrieve data from a table.

Index: Getting Values from Multiple Tables Preview 03:01

Learn how to use the other version of the index function to lookup information from more than one table.

Index: Returning a Reference Instead of a Value Preview 06:29

Learn how to use the index function to return a reference instead of a value. Also, learn how the index function can be combined with the counta function to construct a dynamic range that can save you time.

The Index and Match Functions

Show off your knowledge about the index and match functions.

Index and Match: Benefits of Index / Match Lookup Preview 04:44

Learn how the index and match functions can be combined together to create a powerful lookup machine and review the benefits that this type of lookup has over the vlookup and hlookup functions.

Index and Match: Left lookups and Reference Stability Preview 06:41

Learn how an index / match lookup can be used to perform left lookups and how it is resilient to columns being inserted into the lookup range.

Index and Match: Building a Dynamic Order Report Preview 08:46

Learn how to perform a truly dynamic lookup by using the match function to supply both a row and column number to the index function.

Index and Match: Mastering the Approximate Lookup Preview 04:06

Learn how to perform an approximate lookup using the index and match functions.

Index and Match: Putting the Approximate Match to Use Preview 06:59

Learn how the approximate index / match lookup can be used to help you solve a common problem in business. Here, we'll build a tool to find the discount for a product that has price breaks based on the quantity a customer orders.

Index and Match: The Power of Offsetting Lookups Preview 04:07

Learn how to perform an index / match lookup that offsets from the located value as we revisit our sales commission problem.

Index and Match: Searching Multiple Tables - Revisited Preview 07:50

Learn how to perform an index / match lookup that searches multiple tables.

Index and Match: Finding the First Occurrence Preview 05:22

Learn a tactic for how to find the first occurrence of a tested condition in a list by using an index / match lookup in conjunction with an array formula.

Index and Match: Returning the Entire Row or Column Preview 06:21

Learn how to perform dynamic data aggregation by returning the entire row or column when performing an index / match lookup.

Combining the Index and Match Functions

Show off your knowledge about how the index and match functions can be combined to perform a powerful lookup.

Defined Names: Overview and Simple Example Preview 07:30

Learn what defined names are, as well as, why and how you would use them.

Defined Names: More than Constants - Storing Ranges and Calculations Preview 05:50

Learn how to use defined names to store ranges and calculations on ranges.

Defined Names

Show off your knowledge about defined names!

Offset: Understanding the Offset Function Preview 06:19

Learn how the offset function works and how it can be used to return a value, as well as, a reference.

Offset: Creating a Dynamic Dropdown List Preview 08:23

Learn how to use the offset and counta functions to create dropdown lists that automatically update.

Offset: Creating a Dynamic Calculation Preview 04:19

Learn how to perform dynamic calculations by using the offset function to provide a dynamic reference to other functions.

Offset: Another Dynamic Calculation Preview 05:34

Get more practice performing dynamic calculations by using the offset function to provide a dynamic reference to other functions.

Offset: Dynamic Charting Preview 05:13

Learn how to combine the offset and match functions to build a dynamic table that is the foundation for a dynamic chart.

The Offset Function

Show off your knowledge about the offset function!

Row and Column: An Overview Preview 05:27

Learn how to use the row and column functions to return the row and column numbers of references. Also, learn how to use the indirect function to indirectly point to a reference that is typed into a cell.

Row and Column: Finding the Last Row and Automatic Numbering Preview 05:45

Learn how to find the last row or column in a range and how to automatically number rows and columns by using the row and column functions in conjunction with multi-cell array formulas.

Rows and Columns: An Overview Preview 03:47

Learn how to use the rows and columns functions to return the total number of rows or columns in a reference.  Also, learn how to use the indirect function to indirectly point to a reference that is typed into a cell.

Rows and Columns: Cleaning Up Bad Data Preview 03:51

Learn how to combine the rows and index functions to help you clean up a data set by selecting the values from every other row.

Row, Column, Rows, and Columns

Show off your knowledge about the row, column, rows, and columns functions!

Lookups, References, and Arrays: Returning More than the First Occurrence Preview 13:09

Learn how to build a tool that enables you to lookup information from a table that contains duplicates and return the requested information for each occurrence by combining the index, row, and small functions together in an array formula.

Lookups, References, and Arrays: Managing Schedules - Part 1 Preview 07:35

We'll start building a tool here that helps manage the enrollment and scheduling for a series of training events. Learn how to combine the countif, index, and match functions together to dynamically count based on user input.

Lookups, References, and Arrays: Managing Schedules - Part 2 Preview 10:52

We'll continue building a tool that helps manage the enrollment and scheduling for a series of training events. Learn how to combine the index, match, row, and small functions together in an array formula to lookup information from a table that contains duplicates and return the requested data for each occurrence that meets the lookup criteria.

Lookups, References, and Arrays: Managing Schedules - Part 3 Preview 06:14

We'll finish building a tool that helps manage the enrollment and scheduling for a series of training events. Practice using vlookup along with an index / match lookup to pull information from a table and perform a calculation on it.

Lookups, References, and Arrays: Searching Data to Return Records Preview 14:24

Learn how to use the index, match, row, small, and if functions together in an array formula to apply filters to a dataset and return the matching records.

Address: An Overview Preview 04:26

Learn how to use the address function to return a reference to the cell at the intersection of a given row and column.

Address: Finding the Last Cell of A Dynamic List Preview 02:54

Learn how to combine the address function with the row, column, counta, and offset functions to return the last cell in a dynamic list.

Address: Getting Data From Templated Worksheets Quickly Preview 05:38

Learn how to save time by combining the address and indirect functions together to access information from different worksheets quickly. Also, get a brief introduction to how Excel's Autofill functionality can save you time.

Transpose: An Overview Preview 04:09

Learn how to use the transpose function to change the orientation of a range of data. Also, learn the difference between the transpose function and the copy and paste special transpose option.

Transpose: Transposing Data Inside Array Formulas Preview 04:17

Learn how to use the transpose function with array formulas.

Hyperlink: An Overview Preview 02:42

Get an overview of the hyperlink function an learn how to create a link to a web page in a workbook.

Hyperlink: Workbook and File Navigation Preview 07:13

Learn how to use the hyperlink function to create links to different locations within a worksheet or workbook and to link to external workbooks and files. Also, learn how to use relative file paths with the hyperlink function.

Address, Transpose, and Hyperlink Functions

Show off your knowledge about the address, transpose, and hyperlink functions!

Mastering Lookups and References: Wrap up Preview 01:27

Recap the key concepts you learned in Module 2.3.

Module 2.4 Overview Preview 01:31

Get an overview of the material covered in Module 2.4 - Working with Informationals. The workbooks used in this module are attached as resources.

Value Testers: An Overview Preview 08:28

Get an overview of the istext, isnontext, isnumber, and isblank functions, learn how they handle different types of cell values, and understand how formatting changes impact these functions. Also, learn a trick for using relative references when the ranges you are referencing need to move in a transposed direction.

Value Testers: Special Treatment for Blanks Preview 06:00

Learn how to use the isblank and if functions together to handle blank cells conditionally. Also, understand the difference between weighted averages vs the average function.

Value Testers: Conditionally Controlling Action Based on Input Data Type Preview 04:48

Learn how to combine the isnumber and if functions together to build a tool that decides what calculation to perform based on the data type of a user provided value.

Error Testers: An Overview Preview 03:19

Learn how the iserror, iserr, and isna functions handle each of Excel's error messages.

Error Testers: Enhancing Lookup Functions Preview 07:38

Learn how to combine the isna, if, and text functions together with the lookup functions to extend their capability to perform lookups when the underlying data types don't match.

Error Testers: Preventing Errors from Bubbling Up Preview 04:44

Learn how to use the iserror and if functions together in an array formula to pre-process data containing errors before it is provided to other functions to prevent errors from bubbling up in subsequent calculations.

Core Informationals: An Overview Preview 06:07

Get an overview of the cell, info, and type functions and learn how the cell function can be used to prevent errors from occurring.

Core Informationals: Formatting Details and File Names Preview 04:20

Learn how to use the cell function with the format and filename parameters to return the formatting details of a cell and the directory that a workbook is saved in.

Core Informationals: Last Active Cell and Environment Information Preview 03:26

Learn how the cell function works when a reference is not specified and learn how to use the info function to return information about the Excel environment. Also, understand the importance of recognizing that you have many options available when designing solutions in Excel and that it's your job as an Excel Wizard to evaluate the pros and cons of each option before selecting an implementation.

Core Informationals: The Type Function Preview 02:55

Learn how to use the type function to identify the type of data stored in a cell and learn how to combine it with the if function to prevent errors in calculations.

Informationals

Show off your knowledge of the informational functions!

Working with Informationals: Wrap Up Preview 01:17

Recap the key concepts you learned in Module 2.4.

Module 2.5 Overview Preview 01:57

Get an overview of the material covered in Module 2.5 - Working with Dates and Times. The workbooks used in this module are attached as resources.

Date and Time Foundations: Serial Numbers Preview 05:13

Understand how serial numbers are used to represent dates and times in Excel. Also, learn that the key to simple date and time calculations is how Excel stores date-time values.

Date and Time Foundations: Format vs. Value Preview 04:55

Learn the difference between the values stored in Excel and their formats (presentations).

Date and Time Foundations: Matching Units Preview 07:58

Get an understanding of why its important to make sure units match before you perform calculations as we calculate the average time it takes for a company to assemble a product. Also, learn how to differentiate between formatting differences and actual unit differences for dates.

Date Information Functions: An Overview Preview 03:25

Get an overview of the year, month, day, hour, minute, second, weekday, and weeknum functions.

Date and Time Foundations

Show off your knowledge about date-time values!

Date Information Functions: Breaking Date-Time Values Down Preview 02:30

Learn how to use the year, month, day, hour, minute, and second functions to deconstruct date-time values into their individual components.

Date Information Functions: Aggregating Data on Different Time Periods - Part 1 Preview 09:31

Deconstruct dates into their individual components to analyze a monthly sales data set by different time periods (month, year, and quarter). Also, learn how date-time values are sorted in Excel.

Date Information Functions: Aggregating Data on Different Time Periods - Part 2 Preview 05:41

We'll finish aggregating our monthly sales data set using the date information functions. We'll see how we can combine the month and choose functions together to determine the quarter that a date belongs to.

Date Information Functions: Analyzing Inbound Work for Staffing - Part 1 Preview 07:40

We'll start building a staffing tool that combines the date information functions with  the countifs function to calculate the average number of emails that a company gets by weekday and hour. Here you'll learn how to break a date-time data set down into 1 hour intervals by day of the week, which is a common task when planning for staffing.

Date Information Functions: Analyzing Inbound Work for Staffing - Part 2 Preview 08:06

We'll finish building a staffing tool that combines the date information functions with  the countifs function to calculate the average number of emails that a company gets by weekday and hour. Here you'll learn how to convert the counts we got in our prior lesson into daily averages.

Date Information Functions

Show off your knowledge about the date information functions!

Date Math: An Overview - Part 1 Preview 02:51

Get an overview of the edate, eomonth, and datedif functions.

Date Math: Forecasting Revenues Based on Sales Pipeline Preview 07:38

Learn how to use the edate and eomonth functions to perform simple date math as you build a tool to forecast monthly revenues based on a sales pipeline.

Date Math: Working with Strange Payment Terms Preview 02:21

Learn how to use the eomonth function to calculate the due date of invoices whose payment terms are "end of next month plus 15 days".

Date Math: Understanding the Datedif Function Preview 04:48

Gain an understanding of the lesser known datedif function and its different unit parameters.

Date Math: A Shortcut for Calculating Age Preview 03:31

Learn how to use the datedif function to calculate the age in terms of years, months, and days. 

Date Math: An Overview - Part 2 Preview 03:35

Get an overview of the workday and networkdays functions.

Date Math: Calculating Project Completion Dates - Consecutive Tasks Preview 02:55

Learn how to use the workday function to estimate when a list of consecutive tasks will be completed based on the duration of each task. Here you'll build a tool that takes weekends and scheduled holidays into consideration.

Date Math: Calculating Project Completion Dates - Dependent Tasks Preview 10:10

Learn how to combine the workday function along with the iferror and max functions and an index/match lookup to create a scheduling tool to estimate when a list of tasks will be completed based on the duration of each task. Here you'll build a tool that takes weekends and scheduled holidays into consideration, as well as, task dependencies.

Date Math: Analyzing Historical Duration Data Preview 04:44

Learn how to combine the networkdays and averageif functions to help you build a tool that can analyze the amount of time spent on different types of projects given their start and end dates.

Date Math: An Overview - Part 3 Preview 04:08

Get an overview of the days360 and yearfrac functions.

Date Math: Calculating the Accrued Interest on a Bond Preview 04:46

Learn how the days360 and yearfrac functions can be used to calculate the amount of interested earned on a corporate US Bond based on a 30/360 day basis.

Date Math Functions

Show off your knowledge about the date math functions!

Date Creation: An Overview Preview 02:57

Get an overview of the now, today, date, time, datevalue, and timevalue functions.

Date Creation: Dynamically Calculating Age Preview 04:22

Learn how to use the today function to dynamically calculate the age of items.

Date Creation: Dynamic Reports Preview 04:29

Learn how to use the today function to build reports that are self-sufficient and automatically report on the most recent data based on the current date.

Date Creation: Building Dates, Converting Text into Serial #s, & Handling Units Preview 08:08

Learn how to use the date and time functions to construct a timestamp from the individual components of a date-time value. Also, learn how to convert dates and times that are stored as text values into their serial numbers. Lastly, learn a trick for how to control the formatting of a date-time value that allows you to report back serial numbers when they total more than 24 hours, more than 60 minutes, or more than 60 seconds.

Date Creation Functions

Show off your knowledge about the date creation functions!

Working with Dates and Times: Wrap Up Preview 01:35

Recap the key concepts you learned in Module 2.5.

Module 2.6 Overview Preview 01:50

Get an overview of the material covered in Module 2.6 - Working with Strings. The workbooks used in this module are attached as resources.

String Basics: Strings and Data Entry Preview 06:56

Learn some basic concepts about strings and how they can be entered into Excel. Here you'll learn a trick for how to prevent Excel from automatically acting on data that you want to be treated as text.

String Information: An Overview - Part 1 Preview 01:25

Get an overview of the len and exact functions.

String Information: Cleaning up Mixed Data Preview 04:09

Learn how to use the len function to flag rows and differentiate the actual contents from their headers on a mixed data set that stores both headings and contents in a single column.

String Information: Testing String Equality when Case Matters Preview 03:30

Learn how to use the exact function to test the equality of strings when case matters.

String Information: An Overview - Part 2 Preview 01:42

Get an overview of the find and search functions.

String Information: Finding Information in a String Preview 04:51

Learn how to use the find and search functions to locate the starting position of a string within another string. Also, learn which function to use when case matters.

String Information: Case Sensitive Data Aggregation Preview 04:58

Learn how to expand your data aggregation capabilities to perform case sensitive data aggregations by combining the find function with the aggregation functions.

String Information Functions

Show off your knowledge about the string information functions!

String Manipulation: An Overview - Part 1 Preview 04:17

Get an overview of string concatenation, using both the concatenate function and the "&" sign to join strings together.

String Manipulation: String Concatenation of Function Outputs Preview 03:12

Learn how string concatenation can combine the results of functions with the results of other functions and strings into a single cell.

String Manipulation: String Concatenation of Function Inputs Preview 02:16

Learn how string concatenation can be used to expand the capabilities of the functions you already know how to use by modifying the inputs provided to them.

String Manipulation: Enhancing an Earlier Example Preview 06:02

Learn how to combine string concatenation with lookup functions to return information from more than one column at a time as we enhance the corporate training scheduler that we built earlier in the course.

String Manipulation: An Overview - Part 2 Preview 02:45

Get an overview of the right, left, mid, and trim functions.

String Manipulation: Building Dates from Numbers Preview 09:25

Learn how the string manipulation functions can help you build date-time values when you are provided with date values in a format that Excel doesn’t recognize as dates.

String Manipulation: Extracting Data based on Character Position Preview 05:04

Learn how to combine the left, mid, and len functions together to deconstruct a string stored in a single column into structured data stored in two separate columns.

String Manipulation: Cleaning up Leading and Trailing Spaces for Lookups Preview 04:03

Learn how to combine the trim function together with lookup functions to make your lookups more robust.

String Manipulation: An Overview - Part 3 Preview 03:26

Get an overview of the lower, upper, and proper functions and learn how they are used to transform the case of a string.

String Manipulation: An Overview - Part 4 Preview 02:42

Get an overview of the replace and substitute functions.

String Manipulation: Parse Strings and Remove Duplicates Preview 07:57

Learn how to combine the find and replace functions to parse a larger string into a smaller one. Also, learn how to use Excel's remove duplicate functionality to efficiently create a list of unique values.

String Manipulation: Replacing Specific Instances of Text Preview 05:44

Learn how to use the substitute function to replace a specific instance of text that's contained within a string.

String Manipulation: Keeping Content and Creating CSV's Preview 03:33

Learn how we can use the substitute function to make replacements and keep the original content for recording keeping purposes and learn how we can use the substitute function to transform a string into a csv list.

String Manipulation: An Overview - Part 5 Preview 03:17

Get an overview of the text, fixed, dollar, and value functions.

String Manipulation: Creating Static Formatted Text Strings Preview 10:34

Learn how to use the text function to transform a numeric value into a formatted string. We'll discuss how to customize the text format parameter to format the string as a date, time, number, or currency. We'll also introduce the "#", "?", "0", and "," operators and discuss how they help format numbers.

String Manipulation: Creating Dynamic Formatted Text Strings Preview 09:07

Learn how the text function can create dynamic formats by using a conditional formatting text string. Also, learn the similarities and differences between using the format dialog box to apply conditional formatting and using the text function to apply conditional formatting.

String Manipulation: Text Function Shortcuts and Creating Numerical Values Preview 04:04

Learn how to use the fixed and dollar functions to create text strings from numbers and learn how to use the value function to convert recognized text strings into numerical values.

String Manipulation: Automating Text Summaries in Reports Preview 09:12

Learn how to combine the fixed, dollar, and text functions with static strings to create a formula that automates the writing of text summaries in reports.

String Manipulation: Enhancing an Earlier Example - Take 2 Preview 05:20

Learn how to combine the text conversion and lookup functions to return formatted date information as we enhance the corporate training scheduler that we built earlier in the course.

String Manipulation Functions

Show off your knowledge about the string manipulation functions!

Working with Strings: Wrap Up Preview 01:42

Recap the key concepts you learned in Module 2.6.

Module 2.7 Overview Preview 01:13

Get an overview of the material covered in Module 2.7 - Working with Arrays. The workbooks used in this module are attached as resources.

Array Basics: Review the Array Cheat Sheet Preview 03:46

Learn the basic structure of an array in Excel along with some important rules about arrays.

Array Basics: Single-Cell vs Multi-Cell Array Formulas Preview 04:28

Learn the difference between single-cell and multi-cell array formulas and learn how to write them both.

Array Basics: Rules Review - Can't Change Part of an Array Preview 03:48

Understand how the rules we mentioned earlier work with array formulas in Excel.

Array Basics: Array Math Preview 03:28

Understand how same-sized arrays are multiplied and added. Learn the important fact that array math does not equal matrix math.

Array Basics: Array Math - A Special Case Preview 02:06

Learn the special array multiplication case of 1-D arrays whose dimensions are inverse.

Array Basics

Show off your knowledge about the basics of arrays!

Single-Cell Formulas: Expanding Functions by Passing them Arrays Preview 02:20

Learn how you can expand the value of the functions you've learned throughout the course by passing them arrays.

Single-Cell Formulas: Finding the Smallest Value Preview 04:04

Learn how to use array formulas to find the location of the smallest value in a list.

Single-Cell Formulas: Finding the Largest Value Preview 03:25

Learn how to use array formulas to find the location of the largest value in a list.

Single-Cell Formulas: A General Solution to Aggregating Every nth Value Preview 06:44

Learn how to use the mod function in a single-cell array formula to build a general solution that lets you aggregate the values on every nth row in a column.

Multi-Cell Formulas: Simple Calculations and Range Position Preview 05:16

Get some practice by enhancing a report to show two additional metrics using multi-cell array formulas. Also, learn that the position of the ranges used in a multi-cell formula don't matter.

Multi-Cell Formulas: Returning the Nth Largest Items from a List Preview 06:32

Learn how providing an array of values to the large function causes it to return an array of values representing the Nth largest items. Then output the values using a multi-cell array formula.

Multi-Cell Formulas: Returning the Nth Smallest Items from a List Preview 03:52

Learn how providing an array of values to the small function causes it to return an array of values representing the Nth smallest items. Then output the values using a multi-cell array formula.

Multi-Cell Formulas: Returning the Nth Items from a List when N is Large Preview 06:08

Learn how to use the row and indirect functions to quickly create a list of values that can be provided to the large or small functions to return an array of values representing the Nth largest or smallest items when N is large.

Array Constants: Entering Array Constants Preview 06:25

Learn different techniques for entering array constants as multi-cell array formulas.

Array Constants: Defined Name Array Constants Preview 02:48

Learn how to leverage the information you covered earlier about defined names to create named array constants and output them to ranges using multi-cell array formulas.

Array Constants: Storing Static Values and Making Formulas Readable Preview 06:12

Learn how defined name array constants can be helpful in storing static values such as performance targets and how they can be helpful in making formulas more readable.

Array Data Aggregation: Understanding Logical Tests with Arrays Preview 09:39

Learn how arrays can be used to test an entire range against a logical condition. Also, learn how the multiplication ("and" testing) and addition operators ("or" testing) can be used to combine the results of logical tests into a single array that flags the rows of data that meet the tested criteria. 

Array Data Aggregation: A General Solution for Maxif and Minif Preview 10:55

Learn how to apply your knowledge of testing logical conditions with arrays to build a general solution that lets you find the maximum and minimum values of a data set conditionally.

Array Data Aggregation: Translating Counting Problems into Array Formulas Preview 11:28

Learn how to count conditionally by translating problem statements into data aggregation array formulas. Also, gain an understanding of when to use the multiplication operator and when to use the addition operator. Lastly, learn how to check if your logical tests are mutually exclusive and gain an understanding of why that's important when your performing "or" testing.

Array Data Aggregation: Translating Aggregation Problems into Array Formulas Preview 08:58

Expand your knowledge of using array data aggregation to count conditionally, to more broadly encompass how to use arrays to perform any type of conditional aggregation on your data.

Array Data Aggregation: Summing up Nth Largest or Smallest Values Preview 03:54

Learn a general solution to summing up the nth largest or smallest values from a list.

Array Data Aggregation: Counting when there's a Single Logical Test Preview 02:38

Learn how to transform an array of TRUE / FALSE results into 1s and 0s by using Excel's unary (--) operator. Then, put this knowledge to use by writing a formula that leverages arrays to count when you are testing a single logical condition.

Array Data Aggregation: Building a Histogram - Part 1 Preview 07:44

In this lesson, we'll start building a histogram to show the distribution of incoming work across a day. Here we'll use arrays to start populating the data table that we will later use to plot our histogram.

Array Data Aggregation: Building a Histogram - Part 2 Preview 10:44

In this lesson, we'll finish building a histogram to show the distribution of incoming work across a day. Here we'll enhance our array data aggregation to be dynamic and we'll plot the results on a chart.

Array Data Aggregation: Aggregation Reference Sheet Overview Preview 03:20

Review a data aggregation reference tool that I built for you guys. Here you'll learn how this tool is structured and how it can be used to help you our in practice.

Arrays and Array Formulas

Show off your knowledge about arrays!

Working with Arrays: Wrap Up Preview 03:33

Recap the key concepts you learned in Module 2.7.