Advanced Data Manipulation in Excel
4.2 (11 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
83 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Advanced Data Manipulation in Excel to your Wishlist.

Add to Wishlist

Advanced Data Manipulation in Excel

Power Techniques for Spreadsheet Agility and Modeling
4.2 (11 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
83 students enrolled
Last updated 12/2016
English
Curiosity Sale
Current price: $10 Original price: $160 Discount: 94% off
30-Day Money-Back Guarantee
Includes:
  • 2 hours on-demand video
  • 1 Supplemental Resource
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Employ the “Must-Know” techniques for data manipulation using Excel
  • Manipulate and process text and date information using a variety of Excel’s built-in text and date manipulation functions and data tools
  • Work with arrays and ranges using array functions, array formulas, named ranges, and dynamic named ranges to streamline your development
  • Connect to and import data from a range of external data sources
  • Leverage the additional utilities available for manipulating data in Excel’s Data Ribbon
View Curriculum
Requirements
  • Proficiency entering data and formulas in Excel
  • Familiarity with common Excel functions and exposure to nested functions
  • Exposure to creating and working with PivotTables
Description

This course covers advanced data manipulation techniques for spreadsheet analysis and modeling.  These are “power techniques” which, once internalized, can enable you to manipulate data and information with speed and agility in your spreadsheets.

The major content areas of this course are:

  1. Lookup Functions and Absolute/Relative/Mixed Reference - The Must-Know Techniques for Data Manipulation in Excel
  2. Text and Date Manipulation
  3. Arrays and Ranges
  4. Working With External Data Sources (Optional Material)
  5. The Excel Data Ribbon (Optional Material)

For this course, we have hand-selected some of the most important data manipulation skills needed for analysis and modeling using Excel, and we present them to you using realistic business-oriented examples.  Each of the key concepts has an exercise in Excel for you to gain immediate practice implement it within an example business context, complete with solutions and solution walk-through videos for you to check your work. If you are currently working in or seeking a role in finance, accounting, planning, forecasting, procurement, supply chain management, or other business operations functions, and you wish to continue building your abilities to manipulate data and build spreadsheets, then this is the course for you!

This course covers advanced techniques.  In order to understand the material in this course, students will need to have proficiency entering in data and formulas in Excel, and have some experience using Excel functions.  Students will also benefit from having some exposure to nested functions and creating and working with Pivot Tables.  If you are not familiar with these topics, please consider our preceding course, Fundamentals of Data Manipulation and Visualization in Excel.

Including the lecture content and the exercises, this course should take about 5-8 hours to complete.

Who is the target audience?
  • We recommend this course for Intermediate or Advanced Excel users who understand the fundamentals of how to manipulate data in Excel, but wish to strengthen their skills even further.
Students Who Viewed This Course Also Viewed
Curriculum For This Course
23 Lectures
02:09:58
+
Introduction
3 Lectures 10:43

I give an overview of the course, and describe what we will cover.

Preview 04:55

Strategic Management Solutions Managing Director Chris Fry welcomes students to our course, and discusses how the data manipulation skills taught in this course form an important step in the spreadsheet modeling process.

Welcome and Context
04:44

I describe the course exercise spreadsheet and how to work through it.

Preview 01:04
+
The Must-Know Techniques of Data Manipulation in Excel
4 Lectures 22:49

I’ll talk about Excel’s three types of cell references: Absolute, Relative, and Mixed. We’ll discuss the uses for these different reference types.

Must Know Technique #1 Absolute and Relative Reference
05:42

Excel has several lookup functions, including the infamous vlookup function. I’ll give an overview of the primary ones (vlookup, index and match) and demonstrate how to use them using an example.

Must Know Technique #2 Lookup Functions
10:11

I'll walk through our solution for Excersice 1-1.

Exercise 1-1 Solution
03:01

Here, I'll explain the Index-Match combination and ways in which it compares to vlookup.

The Index-Match Combination
03:55
+
Text and Date Manipulation
3 Lectures 15:01

In this lecture, I'll go through some common text manipulation functions as well as a situation where they really come in handy: multikey lookups.

Text Manipulation
06:51

In this lecture, I demonstrate how to manipulate dates using various Excel functions.

Date Manipulation
02:24

I'll walk through our solution for Excersice 1-2.

Exercise 1-2 Solution
05:46
+
Arrays and Ranges
10 Lectures 55:03

I discuss arrays, and the concept of Array-Input functions.  We will review several Array-Input functions including COUNTIF(), SUMIF(), AVERAGEIF(), COUNTIFS(), SUMIFS(), and AVERAGEIFS(). 

Preview 05:23

I'll walk through our solution for Excersice 1-3.

Exercise 1-3 Solution
08:03

I describe how to transpose data both through using Paste Special and the TRANSPOSE() function.

Transposing Data
02:20

I'll walk through our solution for Excersice 1-4.

Exercise 1-4 Solution
01:54

I talk about how to make and manage named ranges in spreadsheets and show how they can make your spreadsheets more readable.

Named Ranges
03:55

In this optional lecture, I go through the more advanced concept of creating dynamic named ranges, named ranges that automatically update if data is changed or added to the range they refer to.

(Optional) Dynamic Named Ranges
09:12

I discuss and demonstrate some Array concepts in Excel including Array Functions, Array Formulas, and Array Constants.

Array Functions and Array Formulas
07:21

I'll walk through our solution for Excersice 1-5.

Exercise 1-5 Solution
02:55

In this optional lecture, we'll go through the mathematical theory behind matrix multiplication and how to employ it in Excel using MMULT.

(Optional) The MMULT Function
04:33

I'll walk through our solution for Excersice 1-6A and 1-6B.

(Optional) Exercise 1-6A and 1-6B Solutions
09:27
+
Working With External Data
3 Lectures 26:22

This optional material will show you some of the primary ways in which you can connect to external data and bring that data into Excel.

(Optional) Working With External Data
10:48

Now that we've discussed various options for accessing external data, I'll go through the rest of the tools offered by Excel's Data ribbon.

(Optional) Additional Utilities: The Data Ribbon
12:19

I conclude our course and talk about some of our recommendations for implementing the skills you've learned in this course and share your thoughts on your experience.

Course Wrap Up and Next Steps
03:15
About the Instructor
Strategic Management Solutions
4.0 Average rating
28 Reviews
138 Students
2 Courses
Business analytics for strategic advantage

Strategic Management Solutions is a management consulting firm specializing in applications of business analytics and data science to critical business problems in the areas of forecasting, supply chain strategy, product management, and pricing. We also provide a range of additional consulting, training, and software development services. Utilizing an analytical, data-driven approach, we advise companies of all sizes on issues of strategic importance in today’s increasingly complex and information-rich business environment.