Table of Contents: Advanced Spreadsheet Design
Lost? No problem! Here's a complete table of contents for this course:
Get started here - we'll go over what you'll learn in this course and how to navigate through sections and lessons.
Overview of prerequisites
Before diving into our course, there are a couple of important prerequisite lessons we'll need to learn. Don't worry - they're minimal!
Tables are a data construct built in to Excel that allow us to manipulate similar ranges of data quickly and easily.
Excel has two types of cell references: Relative and absolute. Relative references shift as you copy and paste cells. Absolute references do not.
INDEX MATCH, one of Excel's most useful functions, is an improved alternative to VLOOKUP. Using INDEX MATCH is easy — find out how with our tutorial!
Excel's SUMIF function allows you to take the sum of a column or row of data conditional upon a particular criteria row.
What is a model?
Let's familiarize ourselves with a couple of terms that we'll be using throughout this course.
What is a 'model'?
Take a deeper dive into the term 'Excel model' with a real-world example of what a model is and why it's useful.
Why flexibility is important
Why is flexibility in our spreadsheets and models so important? Let's take a look at the advantages of proper spreadsheet design.
Structuring the spreadsheet
Outputs and inputs
Every flexible Excel spreadsheet or model has three primary components: outputs, baselines, and assumptions. Let's examine all three of these pieces and how they work together.
Before diving into Excel, it's useful to create a diagram of your spreadsheet: a basic sketch of how all of its individual pieces will interact together.
Now that you've got a sense for how to diagram a model, let's give it a try! Can you turn these instructions from your manager into a basic model diagram?
Creating the spreadsheet
Once we're in Excel, the first step of creating a flexible spreadsheet is to lay down a 'wireframe': a blank outline of the structure of our data.
Let's try our hand at wireframing our own model based on instructions given to us by our manager. This wireframe will serve as the baseline for the rest of our work!
Before inserting formulas and values into our wireframe, we'll need to clean whatever source data we've been given to ensure that it's properly formatted for analysis.
Now that you've learned how to format data properly, let's try our hand at formatting a sheet of messy source data sent to us by our manager.
Wiring the sheet
Our wireframe is complete, and our source data is clean and properly formatted. Now, it's time for the good part: let's complete our model by 'wiring' up our blank cells!
Try your hand at wiring the spreadsheet we've diagrammed and wireframed. Can you choose the proper formulas and functions to fill our blank cells?
Adjustments and analysis
Goal seek allows us to automatically adjust the input or assumption cells of a model to achieve a desired result.
Can you use goal seek to help your manager understand what model inputs are required to achieve a desired output?
Models aren't just about input and output. Since they often rely upon a set of varied assumptions, it's also helpful to know how changes in those assumptions will affect the final output.
Can you use sensitivity analysis tables to get a sense for how the output of our model will change when a few key assumptions are modified?
This marks the conclusion of our course! Check in here to review what we've learned.