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:
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,.
Including the lecture content and the exercises, this course should take about 5-8 hours to complete.
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.
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.
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.
I'll walk through our solution for Excersice 1-1.
Here, I'll explain the Index-Match combination and ways in which it compares to vlookup.
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.
In this lecture, I demonstrate how to manipulate dates using various Excel functions.
I'll walk through our solution for Excersice 1-2.
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().
I'll walk through our solution for Excersice 1-3.
I describe how to transpose data both through using Paste Special and the TRANSPOSE() function.
I'll walk through our solution for Excersice 1-4.
I talk about how to make and manage named ranges in spreadsheets and show how they can make your spreadsheets more readable.
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.
I discuss and demonstrate some Array concepts in Excel including Array Functions, Array Formulas, and Array Constants.
I'll walk through our solution for Excersice 1-5.
In this optional lecture, we'll go through the mathematical theory behind matrix multiplication and how to employ it in Excel using MMULT.
I'll walk through our solution for Excersice 1-6A and 1-6B.
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.
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.
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.
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.