Your cart is empty.
Keep shopping
Teach on Udemy
Turn what you know into an opportunity and reach millions around the world.
Learn MoreExcel Data Analysis Bootcamp for Finance Professionals
Rating: 4.3 out of 54.3(14 ratings)
36 students
What you'll learn
- Everything you need to start using Excel like a pro.
- Master Excel essentials: cell references, named ranges, tables, Auto-fill, Flash-fill, Text-to-columns, dynamic arrays, data validation and drop-down lists.
- Get comfortable with formulas and functions such as SUM, AVERAGE, COUNT, COUNTA, MAX, MIN, UPPER, LOWER, PROPER, TRIM, CONCAT, TEXTJOIN and more.
- Feel at ease creating formulas that search and fill data automatically with VLOOKUP, INDEX, MATCH, XLOOKUP.
- The secrets of Excel dates and times: DATE, TIME, DAYS, DAYS360, WORKDAY, EDATE, CHOOSE, TEXT and other must-know functions for working with Excel dates.
- Create custom Excel functions with LET and LAMBDA.
Explore related topics
Course content
11 sections • 111 lectures • 14h 56m total length
- Introduction1:19
Develop practical Excel skills through a start-to-finish bootcamp for finance professionals. Complete exercises before viewing solutions and ask questions to tailor the learning to your goals.
- Which Excel?2:31
In this video, we discuss which version of Microsoft Office and Excel you should use to make the most of the course. In order to get the best experience throughout the course, I recommend Microsoft 365 subscription for Windows. On a Mac computer, Microsoft 365 subscription is more limited. Your experience may also be limited if you have a non-subscription version of Microsoft Office, such as MS Office 2016, MS Office 2019, MS Office 2021, etc.
- Huge Favor0:22
- About This Section0:34
This section is a curated collection of basic Excel tutorials. Even if you are a complete beginner, these lessons will help you get started with Microsoft Excel quickly. The section covers everything you need to understand Excel's basic and most important concepts. It is going to be a great way to get you ready to absorb the rest of the course.
- Basic Excel Concepts and the User Interface12:35
This tutorial is an overview of Excel user interface and its elements . Such as the ribbon commands and command groups. It explains the functionality of the name box and formula bar, as well as how to navigate between sheets in a workbook using shortcuts and renaming sheets.
The concept of the active cell along with various techniques for selecting cells, columns, and rows within Excel.
Useful tips for adjusting column width and row height to display values properly, as well as accessing commands using shortcuts and hotkeys.
- User Interface Quiz
- Creating Worksheets and Entering Data11:15
This video teaches the ins and outs of data entry and navigation within Excel worksheets. It guides you through the basics of cell selection and the nuances of entering data efficiently.
Discover how to maneuver across your spreadsheet with ease, using keyboard shortcuts like Enter to shift down, Shift + Enter to shift up, Tab to move right, and Shift + Tab to move left.
Moreover, the lesson will delve into more advanced navigation tips such as using Control + arrow keys to jump to the edge of data ranges or to the beginning or end of the Excel sheet, and expanding selection with the Shift key.
How to select non-contiguous ranges of cells, handle the edit mode efficiently, and utilize the formula bar for edits. I will teach you the shortcuts and methods for editing existing content, including double-clicking, using the formula bar, and the F2 key.
By the end of this lesson, you will have grasped how to enter and edit your data in Excel with confidence, leading to a more streamlined and productive workflow.
- Entering Data Quiz
- The AutoFill Tool9:14
Master the autofill tool and fill handle to copy cells and create fill series. Use dates, weekdays, and built-in lists with auto fill options and shortcuts like ctrl+; and ctrl+enter.
- The Flash-Fill Tool12:33
In this lesson you learn how to fill data from an example with the Flash Fill tool using the fill handle, smart tag, ribbon, or keyboard shortcut (Ctrl+E).
Flash Fill works best with consistent data patterns, allowing users to easily extract first names, last names, state abbreviations, and other data from a data set.
You will be amazed by the simplicity, versatility and usefulness of the Flash Fill tool for data extraction and transformation in Excel.
- Flash Fill Tool Quiz
- Text-to-columns10:01
In this video, you discover how to split spreadsheet columns with the Text-to-Columns feature—a legacy tool that remains as useful and relevant as ever.
Effortlessly split a single column of data into multiple columns, whether your data is delimited by characters such as spaces, commas, or tabs, or organized by fixed width.
Furthermore, the lecture guides you through the nuances of data formatting, teaching you how to prevent Excel from misinterpreting your data, such as mistaking numerical strings for dates or inadvertently stripping away leading zeros from zip codes.
You'll learn how to dictate the format for each column—whether it be text, date, or general—and gain insight into how to maintain the format consistency that's critical for data accuracy.
We will also compare the Text-to-Columns wizard with Excel's Flash Fill feature, offering insights into when and why to use one tool over the other.
- Text to Columns Quiz
- Introducing Excel Formulas and Functions (Part 1)5:28
Your first brush with Excel formulas and functions. It provides a brief overview of Excel functions, hinting at their more advanced and exciting nature.
It explains a shortcut (Control + Tilde) to toggle between displaying the results and the formulas in the cells.
The instructor highlights the benefits of using cell references, allowing for dynamic calculations when input values change.
- Introducing Excel Formulas and Functions (Part 2)9:18
Here we explore various functions such as SUM, AVERAGE, COUNT, COUNTA, COUNTBLANK, MAX, and MIN.
We learn different methods of inserting functions in Excel, including typing, using the FX button, and accessing functions from the formulas tab on the ribbon.
The structure of a function, including the function name, opening and closing parentheses for arguments, and the potential need for multiple arguments, is explained, along with a brief mention of the helpful prompt available while entering function arguments.
- Introduction to Formulas and Functions Quiz.
- Excel A1-Style Cell References10:03
Learn about cell references and why they are important for creating robust Excel formulas.
You'll discover how to craft formulas that accurately calculate data. You will also understand the difference between relative and absolute references. I will also introduce convenient shortcuts, like using the F4 key to toggle references.
- Cell References Quiz.
- Excel Named Ranges10:20
Learn to reference cells with named ranges in Excel, including A1 style references, create and manage names, and reuse them in formulas with F3 or paste names.
- Excel Names Quiz
- Excel Tables and Structured References13:58
Excel tables are not just for formatting; they are structured containers that automatically expand and provide structured references for easy data management.
Tables can be named, renamed, and referenced using structured references, which adapt automatically if the table or column names are changed.
Learn useful shortcuts for selecting entire rows and columns within tables, as well as tips for applying and clearing formatting in Excel tables.
The importance of structured references within Excel tables for effective data analysis and manipulation.
- Excel Tables Quiz
- Array Formulas and Dynamic Arrays8:32
An Excel formula can return either a single value or a list of values.
Dynamic arrays are the results of a formula that returns a list of values.
In Excel, dynamic arrays are indicated by a blue outline with a formula in one cell and results in a range of cells.
The dynamic array can expand dynamically based on changes in the data set it references, ensuring the results are always up to date.
When referencing a dynamic array, use the hashtag symbol (#) to ensure the reference remains compliant even if the array's size or structure changes.
- Dynamic Arrays Quiz
- Concatenating Text Strings7:40
This lesson teaches you several approaches to combine or concatenate text strings in Excel.
The first approach is using the concatenation operator, represented by the ampersand sign (&).
Another method is using the CONCAT function, which requires providing arguments as text strings separated by commas.
The CONCAT is newer and simpler than the older CONCATENATE function.
The TEXTJOIN function offers a more sophisticated method for joining text. It allows you to specify delimiters and to handle empty cells gracefully.
- Concatenating Text Strings Quiz
- Text Functions4:04
In this lesson, you're going to unlock the power of Excel's Text functions to manipulate and improve the presentation of your data. Discover the wizardry of UPPER, LOWER, PROPER, and TRIM functions and learn how to effortlessly convert text strings to your desired case format or clean up unnecessary spaces with ease. Whether you're dealing with a list of names that need capitalization or a dataset littered with extra spaces, these functions will become your go-to tools for quick and efficient text manipulation. By the end of this lesson, you'll not only understand how to apply each function individually, but also how to combine them to achieve even more dynamic results.
You'll see firsthand how to use the PROPER function to capitalize names correctly, the LOWER function to standardize text to all lowercase, and the UPPER function to emphasize key information. Plus, the TRIM function will become your secret weapon, cleaning up strings by removing leading, trailing, and excessive in-between spaces. By mastering these functions, you'll enhance the readability and quality of your data, and with the practical examples provided, you'll be able to immediately implement what you've learned.
- Text Functions Quiz
- Excel-Formatting Basics11:19
In this tutorial, you will discover valuable tips and tricks for enhancing Excel spreadsheet formatting skills. It shows how to use the Format Cells dialog box (Ctrl+1) and the Format Painter tool to apply and copy formatting to cells and ranges.
This tutorial showcases the power of shortcuts, such as Ctrl+B for bold and Ctrl+Y for repeating your last formatting action.
- Excel Formatting Basics Quiz
- Drop-down Lists with Data Validation15:57
Learn to build drop-down lists in Excel using data validation, with static range references, dynamic arrays (unique and sort), tables, and text join techniques for robust, auto-updating lists.
- Conditional Summary Functions (SUMIFS AVERAGEIFS COUNTIFS)7:53
Learn to use sumifs, averageifs, and countifs to calculate totals, averages, and counts based on donor and badge of honor criteria, with error handling via iferror.
- Excel-Basics Crash Course Homework (ASSIGNMENT)1:22
Engage in a practical homework assignment that guides you to download a file with multiple sheets, read the screenshot instructions, and replicate the shown details in the correct ranges.
- Excel-Basics Crash Course Homework (SOLUTION)25:37
Excel basics crash course homework guides finance professionals through formatting, borders, and currency formats. Learn autofill, text to columns, tables, data validation, and conditional summaries with sumifs, countifs, and averageifs.
- Keyboard Shortcuts Quiz
- VLOOKUP Tutorial (Approximate Match)8:21
This video lesson is a VLOOKUP tutorial. It explains how to use the VLOOKUP function with an approximate match argument. It's important when you must return results based on thresholds even if the exact value is missing from the table array.
- VLOOKUP Tutorial (Exact Match)7:12
This is part two of the VLOOKUP function tutorial instructions. the lesson explains how to use VLOOKUP with the exact match, FALSE, argument.
- MATCH Function Tutorial4:29
Excel's MATCH function returns the relative position of a value. It works for a vertical or a horizontal range. There are three arguments for the MATCH function: lookup value, lookup array, and match type. I illustrate with specific examples how to use the MATCH function.
- INDEX Function Tutorial3:50
This video lesson teaches you how to use the INDEX function in Excel. We start by addressing the basics: What exactly is the INDEX function, and what it does? Through this tutorial, you'll see how the INDEX function is useful for extracting values based on relative positions within a range By the end of this tutorial, you'll have a clear grasp of the INDEX function's fundamentals.
- MATCH and INDEX (Exact Match)7:28
In this lesson, we're diving into the MATCH and INDEX functions. These two are a dynamic duo of data lookup. The VLOOKUP has so many limitations. The MATCH with INDEX give you a much more flexible and efficient approach to lookup calculations. The tutorial gives you everything you need to understand how to take advantage of them.
- MATCH and INDEX (Approximate Match)4:08
In this lesson, you continue to learn how to us MATCH and INDEX functions. It focuses on use cases when you need an approximate match similar to VLOOKUP formulas.
- XLOOKUP Tutorial (Exact Match)6:29
Now, let's embrace the future of lookups with the modern and versatile XLOOKUP function! It is a superstar of lookups and combines VLOOKUP, MATCH and INDEX, IFERROR, in one into one powerful, user-friendly formula. Whether you're dealing with vertical or horizontal data, XLOOKUP adapts seamlessly. This XLOOKUP tutorial shows how to use this valuable function tool in Excel.
- XLOOKUP Tutorial (Approximate Match)2:53
In this video, you continue to learn how to use the XLOOKUP function. This time, with the approximate match option.
- Excel Lookups Practice (ASSIGNMENT)1:35
Practice lookups by building three tables: projects, pms, and exp codes, then apply vlookup, index and match, and xlookup with exact match and unknown for errors.
- Excel Lookups Practice (SOLUTION)19:44
Learn to build and use Excel lookup techniques, including VLOOKUP, MATCH with INDEX, and XLOOKUP, with error handling via IFERROR, plus table creation and conditional formatting to highlight unknowns.
- Lookup and Reference Functions Quiz
- Download Example Files for the Dates and Times Sections0:02
- Overview of Excel Dates-and-Times System12:47
Explore how Excel treats dates as numbers and times as decimals, switch between 1900 and 1904 date systems, and interpret formats like general and date.
- Entering Dates and Times in Excel Spreadsheets4:18
Enter dates in Excel with ctrl+semicolon for today and use the fill handle to create sequential dates, weekdays, months, or years via fill series and smart tag.
- Regional Settings on Windows5:08
Learn how Excel mirrors your Windows regional settings to format dates, switch between short and long date formats, and adjust date, time, and number formats via control panel or settings.
- Regional Settings on Mac3:27
Adjust System Preferences, language and region, on macOS to set the default short date to a four-digit year, then restart Excel to apply the change.
- Playing with Random Date Values8:50
Explore formatting date values in Excel by generating random dates with rand_array between a start date and today, then apply short and long date formats using the dialect box.
- Playing with Random Time Values5:28
Learn how Excel treats times as decimals, distinguish time data from date data, and apply time formats via the format cells dialog and standard or custom formatting codes.
- Playing with Random Date-and-Time Values9:01
Create and display random date and time values in Excel using custom formatting. Learn to use formatting codes and shortcuts to show four-digit year, two-digit month/day, and hours, minutes, seconds.
- Formatting Dates (Part I)9:57
Format dates in Excel for finance by applying short and long formats, building custom codes (m/d, yyyy, mmm), and using Ctrl+1 to edit format cells while preserving the date value.
- Formatting Dates (Part II)9:08
Explore advanced date formatting in Excel, including custom codes for weekday, month names, and day suffixes; learn escaping and alignment tricks for polished date displays.
- Formatting Time Values7:40
Explore how Excel formats times with shortcuts and custom codes, applying hh:mm, hh:mm:ss, and 24-hour formats, plus time fractions.
- Formatting Date-and-Time Values7:38
Format date and time values in excel with custom formats (ctrl+1), display date only, time only, or both; use dd/mm/yy, am/pm, 24-hour clocks, and note the underlying decimal includes time.
- Formatting for Elapsed Time8:03
Learn how to format elapsed time in Excel, converting hours into minutes and displaying durations correctly. Use custom codes like bracketed M to show minutes and seconds and sum durations.
- The DATE and the TIME Functions6:36
Explore the date function and the time function, using year, month, day, or hour, minute, second to produce date and time values, format with custom options, and combine via addition.
- Date & Time Calculations5:34
Extract year, month, day, weekday (with optional return type), week or ISO week number from dates, and hour, minute, second from time values; all results are numbers for further calculations.
- The TEXT Function6:50
Learn to extract values from dates and timestamps using the text function and formatting codes to produce text strings for filtering and pivot table analysis.
- Separating Date from Time6:48
Explore separating date and time in Excel timestamps using trunc and int to extract date, and mod or subtraction for time, with date-only and time-only formats.
- The WORKDAY and WORKDAY.INTL Functions7:35
Learn to use workday and workday.international to calculate the next workday by excluding weekends and holidays. Build holiday lists in tables and adapt for different weekend systems.
- The DATEDIF Function4:24
Explore the unofficial but useful Excel date_diff function to compute age, days, and months using today and units like y, m, and d.
- YEARFRAC Function2:45
Compare date_diff and year_frac to compute years between dates, using year_frac with optional bases (actual, 360, European), then convert decimals with int for an exact year difference.
- The DAYS and DAYS360 Functions3:10
Compare dateDif with dates in order and the days function using end date and start date, then explore days360 with 360-day year, 30-day months, and US versus European methods.
- The NETWORKDAYS and NETWORKDAYS.INTL Functions5:12
Learn how to use networkdays and networkdays.intl to calculate working days between dates, excluding weekends and holidays, with sample holiday tables for US, French, and Israeli calendars.
- The EDATE Function2:33
Learn how to use the edate function to calculate dates after or before a given number of months from a start date, with examples showing positive and negative month shifts.
- The CHOOSE Function10:00
Explore how the Excel choose function uses an index to pick from a list of values, enabling month-based, quarter, and fiscal quarter calculations from dates.
- Calculating the Fiscal Year of a Date7:31
Learn to determine a fiscal year from a date by extracting year and month and applying conditional logic for different fiscal start months.
- Working with Dates and Times Stored as Text5:55
Learn how to convert dates and times stored as text into true numbers using datevalue and timevalue, format the results, and extract year, month, day, and weekday.
- Introduction to the LET Function4:09
Explore how the Excel let function declares a variable, assigns a value, and uses it in downstream calculations with cell references, improving robustness of formulas.
- The LET Function Example (Parsing a String)8:29
Learn how to parse a string with left, mid, and right functions and simplify formulas using the let function to declare a variable and reuse it across the calculation.
- The LET Function (Microsoft Support Example)7:49
Explore how the let function in Excel streamlines formulas by declaring criteria and range variables, then applying filter with if and isblank to display hyphens for blanks.
- The LET Function (Fiscal Year Calculation Example)5:44
Apply the let function to fiscal year calculation in Excel, declaring variables for timestamp, last month, current month, and year to simplify the if logic and automate year results.
- LAMBDA Function (Introduction)4:29
Learn how the lambda function creates parameter placeholders in Excel to build custom calculations, using X and Y to compute expressions, enabling new in-spreadsheet custom functions.
- Custom AGE function with LAMBDA and DATEDIF4:50
Explore creating a custom age function in Excel using lambda and datedif, computing age from a birth date and naming the function for workbook-wide reuse.
- Custom Fiscal Year Function with LAMBDA6:43
Create a custom fiscal year function with lambda in Excel, using let and if, and name it fyear to compute year from a date with timestamp and last fiscal month.
- Calculate Loan Payments (PMT Function Tutorial)4:38
Compute monthly loan payments with Excel's PMT function by converting the annual rate to a monthly rate, using term in months, and the loan principal (PV) for what-if analyses.
- Excel Goal-Seek Tutorial5:34
Use Excel's what-if analysis and goal seek to reverse-calculate loan inputs for a target payment. Navigate the Data tab and Forecast group tools to adjust principal, rate, and term.
- Excel Data-Table Tutorial10:10
Explore Excel data tables in what-if analysis to model scenarios with one-variable and two-variable data tables. Learn how row and column input cells feed calculations and display results.
- Excel Scenario-Manager Tutorial5:41
Use Excel's scenario manager to create, edit, and apply multiple what-if loan scenarios by changing input cells, then merge and summarize them for comparison.
- Excel Charts Basics14:59
learn to visualize data with Excel charts by understanding data series and categories, grouping data, inserting charts, and using the formatting panel to customize chart elements on Windows and Mac.
- Chart Elements8:11
Explore Excel chart elements, including chart title, axes, data series, legend, and data labels, then customize with add chart element, format panel, and linking axis titles to cells.
- The Select-Data Tool9:07
Learn to use the Excel select data tool to plot non-contiguous ranges, adjust data series, switch rows and columns, and handle hidden or empty cells for precise chart visualization.
- Combination Charts (PC)5:34
Learn how to create combination charts in Excel to compare off-scale data using primary and secondary axes, mixing column and line series for revenue and gross margin.
- Combination Charts (Mac)8:52
Master creating and managing combination charts on Mac in Excel, including converting one series to a line, enabling a secondary axis, and labeling data with industry values from cells.
- Excel Charts Practice (ASSIGNMENT)2:01
Open the practice file, review each tab’s dataset and screenshot, and recreate the chart type shown for every chart using stacked column, line, bar, pie, Pareto, and combo charts.
- Excel Charts Practice (PC SOLUTIONS)34:31
Learn to create diverse Excel charts: stacked column, line, bar, pie, Pareto, and combo; link chart titles to cells, add data tables, axis titles, and data labels for finance analysis.
- Excel Charts Practice (MAC SOLUTION)29:10
Master creating and styling multiple Excel charts on Mac, from column and line to pie, Pareto, and combo charts; learn chart elements, axis titles linked to cells, and data labels.
- Pivot Tables - Getting Started13:35
Discover how to create pivot tables from good, tabular data, using the four drop zones—rows, columns, values, and filters—and choose layouts like compact, outline, or tabular.
- Pivot Table Design Tab Totals and Subtotals12:38
Create a pivot table from a named table source, design with region, state, and category, and manage grand totals and subtotals in various layouts.
- Pivot Tables Summarization Functions9:03
Learn to build and customize pivot tables from a transactions data source, show quantities by category, and use summarize values by options like sum, count, average, max, and min.
- Pivot Charts5:09
Create a pivot chart from pivot table data by adding a calculated revenue column, then build and refresh the pivot table and chart, and explore chart fields.
- Pivot Tables Show Values As Options8:25
Explore pivot tables and learn to present data as cross tab reports using show values as options, including percent of grand total, percent of column total, percent of row total, and running totals.
- Pivot Tables Refresh2:00
Excel pivot tables copy data into a pivot cache as a snapshot, so changes in the data source require a refresh to update the report.
- Pivot Tables - Grouping Dates6:04
Learn how pivot tables automatically group dates into years, quarters, and months, then customize groupings such as days or 10-day ranges for row or column labels.
- Pivot Tables - Grouping Numbers3:42
Learn to group numbers in pivot tables within the Excel data analysis bootcamp for finance professionals by creating a pivot table, using group field, and counting ranges for charts.
- Pivot Tables - Grouping Selection4:39
Learn how to group data in pivot tables by selection, combining multiple items such as regions into a single sales territory.
- Pivot Tables - Filters Area5:03
Explore pivot table filters, balance the filters area and report filters, learn legacy filters and filter context, and preview slicers as a more versatile filtering method.
- Pivot Tables - Slicers and Timelines9:54
Learn to build pivot tables with slicers and timelines to interactively filter finance data by region and state, summarizing revenue with sum and price with average.
- Pivot Tables - Practice Lab (ASSIGNMENT)2:13
Practice creating pivot tables and charts from a transactions data set, reproducing top five reports for regions, states, and categories with slicers and styling.
- Pivot Tables - Practice Lab (SOLUTION)22:01
Create and customize pivot tables to analyze revenue by region, state, and category, applying top 5 filters, styling, charts, data labels, and timeline slicers across multiple pivot tables.
- Download Power Query Exercise Files0:01
- Power Query Introduction 119:48
Explore power query in excel to connect to a csv, transform data with query steps, adjust data types, split columns, fix errors, and load clean data for pivot tables.
- Power Query Introduction 216:25
Learn to clean and transform a messy CSV dataset using Power Query, connecting to a CSV file, unpivoting data, and loading into a pivot table for analysis.
- Power Query-Remove Rows6:56
Connect to a tab-delimited file in Power Query, transform data, use remove rows options—top, bottom, blank, and duplicates—and keep range of rows to load results into sheets.
- Power Query-Split Columns9:37
Learn to split columns in Power Query from an Excel file using delimiters (leftmost, rightmost, custom comma space) and by number of characters, including splitting into rows.
- Power Query-Pivot and Unpivot11:58
Master Power Query pivot and unpivot in Excel, transforming tables and sheets into cross-tab and tabular formats, while connecting to external workbooks and applying advanced options.
- Power Query-The Format Tool7:00
Master the Power Query format tool by applying transform steps—trim, uppercase, lowercase, split by delimiter, merge columns, and add prefix and suffix—while understanding add vs replace column behavior.
- Power Query-The Extract Tool4:14
Learn to use the extract tool in Power Query to create new text columns. Explore extracting text before or after delimiters and between delimiters, with first or last characters.
- Power Query-Merge Columns5:07
Master merging columns in Power Query by selecting fields in sequence with non-contiguous selection and a separator, creating merged columns that reflect order while removing nulls and changing types.
- Power Query-Custom Columns7:10
Master power query to add custom columns, including a customizable index and conditional columns. Create a revenue column by multiplying unit price by quantity, and explore column from example.
- Power Query-Dates and Times7:45
Explore dates and times in Power Query by combining date and time, converting types, and extracting year, month, day, and week with add column and transform tools.
- Append Data (Part 1)12:34
Combine data sets with Power Query by appending, create a master data set with source context, and analyze sales by salesperson via a pivot table.
- Append Data (Part 2)12:49
Append data from multiple sheets by creating and transforming queries, stacking into a master data set, and labeling sources while handling date and currency types.
- Connect Power Query to a Folder8:48
Connect Power Query to a folder of state sales csv files, then combine them into a master dataset using a sample file to apply transformations across all files.
- Merge Queries (JOINS)15:07
Explore merging data in Power Query by performing inner, left, right, full outer joins to combine enrolled and attended datasets, including anti-joins and walk-ins.
- Merge Queries (VLOOKUP)7:19
Learn how to use Power Query to replicate vlookup with exact match by merging orders, customers, and employees using left outer join and unique identifiers.
Requirements
- No prior Excel knowledge needed. This course teaches everything you need to know.
- You have access to a computer with Microsoft Office suite installed so you can follow the course and practice.
- You are willing to put time to acquire serious data-analysis skills using Excel.
- You want to shine above the rest with your expertise of Excel tools.
Description
Dear Aspiring Excel Wizard,
Are you ready to elevate your Excel skills to new heights?
If you answer YES, I invite you to embark on a learning journey with me into the wonderful world of Excel. If you join this course, I practically guarantee that your skills will jump to a higher orbit almost overnight.
I am so confident because I meticulously designed it for people like you who are serious about their skills and aspire to master Excel as quickly and as deeply as humanly possible.
That’s why I believe this comprehensive course is going to be your gateway to mastery of one of the most sought-after skills in today’s job market.
This is not one of those regular courses, which are a dime a dozen. No way!
It is a transformative learning experience that helps you build your skills step by step, nourishes your mind with fresh knowledge and creative ideas and gently leads you towards mastery. So you can transform yourself into a true Excel wizard.
Here’s what you are getting with this course:
Lifetime access to the entire training so you can hone your skills anytime you choose.
Excel-basics crash course: Where it all begins. A smooth journey through the fundamentals, paving the way for mastery. This is where you build a strong understanding of the most important Excel topics. Such as user-interface tools, Flash-fill, Auto-fill, Text-to-columns, cell references, tables, named ranges, dynamic arrays.
How to create drop-down lists with data validation tools in Excel.
Formulas and Functions: The backbone of Excel.
Lookup and reference tutorials: VLOOKUP, INDEX, MATCH, XLOOKUP.
Concatenation: Formulas to join text strings with CONCAT, TEXTJOIN or the ampersand operator.
How to transform text values with UPPER, LOWER, PROPER, TRIM functions.
The basics of professional formatting of spreadsheets.
Date and Time Mastery: A comprehensive training on how to work with dates and times in Excel. From formatting to calculations. Master date and time functions: DATE, TIME, DATEDIF, WORKDAY, WORKDAY.INTL, YEARFRAC, NETWORKDAYS, NETWORKDAYS.INTL, DAYS, DAYS360.
How to calculate fiscal years based on dates.
How to create custom functions with LET and LAMBDA.
Excel what-if analysis tools: Goal seek, Data Table, Scenario Manager.
Data Visualization: A picture is worth a thousand numbers. Bring your data to life. Learn to present data with Excel charts that impress, captivate and inform.
Pivot Tables: Unravel the secrets of summarizing and analyzing large data sets effortlessly. This is where data bows to you, revealing its hidden gems through your newfound mastery.
Practical homework (Lab) assignments with solution walk-throughs.
And more...
Who this course is for:
- Everyone who wants to master Excel and stand out from the crowd.
- Savvy business and data analysts.
- Banking and finance professionals.
- Future, current and former MBA students.
- Real-estate professionals.