An Excel video course from Deskbright:
Advanced Spreadsheet Design
Building a Model with Flexible Inputs
If you're an intermediate-level Excel user, you likely know a ton of useful formulas and functions. You've probably experimented with the basics like
But if you're like most Excel users, something still isn't quite right. No matter how many formulas and functions you learn, your spreadsheets are still cluttered, hard to read, and difficult to navigate. Do you find that:
- Small changes to your spreadsheet can eat up a large amount of your time?
- Changing one small part of your spreadsheet breaks other cells in unpredictable ways?
- You often have to comb through your spreadsheets manually adjusting numbers and assumptions?
- Your spreadsheets are difficult for your manager to use?
- It's hard to re-learn your old spreadsheets after you open them up months later?
- Despite all your knowledge, you still spend more time in Excel than you should?
If you answered 'yes' to any of these questions, it's probably because you're not in on the secret that enables top-tier management consultants, financial analysts, and Fortune 500 employees around the world to be 10x more productive in Excel than their peers:
Spreadsheet design matters more than formulas and functions.
It may seem counterintuitive, but it's true: no matter how quick you are with
So what is good spreadsheet design, anyways? Simply put, it means designing spreadsheets that are flexible; easily updated; and easy to interpret. A well-designed spreadsheet:
- Can be updated quickly and easily, with minimal effort;
- Has clear inputs and outputs;
- Is easy for others in your office to use and interpret — without explanation; and
- Works with you — not against you.
And here's the kicker: knowing how to design a proper spreadsheet is an extremely valuable skill. Top-tier employers around the world are constantly struggling to find qualified analysts who know how to design spreadsheets right. And learning to do it can mean huge career payback for you.
Now, let's get to the good stuff:
In under 5 hours, this course will teach you to design the perfect spreadsheet.
Forget boring lectures, hard-to-understand articles, and ambiguous instructions. We've done away with all the unnecessary fluff and distilled the core of what you need to know into an engaging video course that can be completed in under five hours.
In it, you'll learn how to construct a model — a beautifully-designed spreadsheet that is easy to read, simple to update, and built to last. Here's what you'll explore:
- What a model is, and why proper spreadsheet design is important;
- How to plan your model by outlining outputs, baselines, and assumptions;
- The importance of flexible assumptions, and how to build them into your spreadsheet;
- How to 'wireframe' a spreadsheet before constructing it to save yourself time and energy;
- Formulas and functions needed to link up formulas in a completed spreadsheet;
- Excel's Goal Seek and Data Table functions for sensitivity analysis.
When you're done, your productivity will skyrocket as you begin to design spreadsheets the right way — the first time. Buy the course today and get started on the most important chapter of your Excel journey!
Not convinced? Try out a free trial of the course and get a sample lesson on us!
Hands-on exercises
At Deskbright, we believe in learning by doing. That's why all of our courses come with a set of hands-on challenges that let you practice your newly-learned skills on your own. Each exercise comes with a comprehensive answer key so that you can check your work and learn through experience.
Your satisfaction is guaranteed.
We offer a 100%, unconditional, 30-day money-back guarantee on all of our courses. If you aren't satisfied for any reason, you can let us know within 30 days of your purchase to get your money back — no questions asked. This means that there's absolutely zero risk on your end!
Want to know more?
No problem! Check out the frequently asked questions below. There's a ton more details on the course; what you'll learn; and your instructor to read through.
What are the requirements?
Microsoft Excel 2007 or later — Windows or Mac!
Who is the target audience?
This course is designed for the intermediate spreadsheet user who is already familiar with Excel's interface — and some formulas and functions — but who isn't quite sure how to use Excel to its fullest. Basic knowledge of features like formulas and arithmetic, INDEX MATCH, and PivotTables is helpful, but not required.
If you want to learn how to leverage the power of Excel to build beautiful models that save you hours of time, this course is perfect for you.
Although this course is recorded on a Windows computer, the formulas and functions we'll learn also work great on a Mac.
How long will it take?
You should be able to complete this course in 3-5 hours. We've saved you time by eliminating all the fluff and making sure you get the highest possible value out of this course per minute spent learning.
What will I learn?
Here's a full Table of Contents for this course:
Introduction
Introduction
Get started here - we'll go over what you'll learn in this course and how to navigate through sections and lessons.
Prerequisites
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!
Using Tables
Tables are a data construct built in to Excel that allow us to manipulate similar ranges of data quickly and easily.
Cell references
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
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!
SUMIF
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?
Terminology
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.
Spreadsheet diagrams
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.
Diagramming exercise
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
Wireframing
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.
Wireframing exercise
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!
Data cleaning
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.
Formatting exercise
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!
Wiring exercise
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
Goal seek allows us to automatically adjust the input or assumption cells of a model to achieve a desired result.
Goalseek exercise
Can you use goal seek to help your manager understand what model inputs are required to achieve a desired output?
Sensitivity analysis
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.
Sensitivity exercise
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?
Conclusion
Conclusion
This marks the conclusion of our course! Check in here to review what we've learned.
Your instructor
This course is taught by master Excel instructor Sam McIntire of Deskbright. Sam taught himself Excel after landing a job at a top management consultancy, and is passionate about sharing his spreadsheet knowledge with others.
With years of experience teaching students around the world advanced Excel skills, his easy-to-follow style is perfect for beginners to advanced learners alike. Sam:
- Has helped thousands of students learn the Excel formulas and functions they need to succeed at work;
- Is an experienced management consultant who learned Excel on-the-job at a top tier firm;
- Has been featured in top business publications like Fast Company, Business.com, and Forbes;
- Has received hundreds of positive reviews and 'thank you' e-mails from students whose work was transformed by his lessons.
In addition to teaching Excel, Sam has founded a number of businesses — most recently, Mosaic, a food company that helps busy professionals eat well.
Quoted in:
Customer reviews