The Excel Sensitivity Table Trick You Probably Don’t Know (But Should!)

Here’s an Excel trick you probably don’t know about that makes a big difference and is super easy to implement.

Data sensitivity tables allow an end-user to synthesize outputs given changes in input variables. Even though data sensitivity tables have been around forever, I still find that at least 75% of Excel users aren’t familiar with them. Since most people don’t know how to use them, this is what I advise:

? Don’t keep the axis controls baked into the table.

? Pull them out to an external input cell so your end-user can influence them quickly and easily.

The trick I’ve been using and teaching for years involves connecting a form control spin button or scroll bar to each axis. That way, when you click on the button, the axis increases and decreases accordingly. You never need to touch the table.

The sensitivity table in this example represents the range of 6-month average royalties.

These objectives are achieved:

? The end-user has full control of the inputs,

? The end-user has full ability to analyze the output, and

? The end-user never needs to know how data sensitivity tables work to be able to utilize them for decision support.

Would you like this template? Message me, and I'll send it your way.

It's hard to imagine a world without Excel. It's a struggle to imagine finance without Excel. For many people, their jobs would be impossible without it.

Two years ago, when I started creating content, I wasn’t building with any particular goal in mind. I just began sharing what I knew.

But the more I created and shared, the more feedback I'd receive. It was through messages and sharing that I discovered the simplest of goals: Helping people get better at what they do and introducing them to new ideas they’d never thought of.

The combination of powerful tools, valuable resources and wonderful mentors can make all the difference. That's been the story of my entire career.

It's a special feeling to now be called a Microsoft MVP.

I'm still in awe that people spend their most valuable assets — time and attention — engaging with the videos, files, Excel tutorials, and business modeling walkthroughs that I put out there.

For that, I’m grateful and hope they serve you well in pursuit of the work-life you envision for yourself and your family.

Thank you for joining, watching, and sharing. It means a lot.

Live Virtual Programs

View All Upcoming Programs and Events

The encore to Carl's Level 1 Cash Flow Forecasting course, this course dives into data management, dynamic processes, and deeper thinking.

Program Type: Virtual

Date & Time: September 23 and 25 at 9 am - 1:30 pm CDT

CPE credits: 9

Learn More

Build sophisticated financial models that enable you to make strategic business decisions and optimize business outcomes.

Program Type: Virtual

Date & Time: October 18, 25 and November 1, 8, 16 at 9 am - 1 pm CDT

CPE credits: 20

Learn More

How can I help you?

• FP&A Learning: Professional Development Engagements and Individual Online Learning
• FP&A Inner Circle Mastermind: Community, Connection and Content for Financial Professionals
• The Finance Circle Community: Expert network, learning resources, discussion, Q&A, and more.
• Book A Call

Unlocking the full potential of Excel doesn’t have to be complicated. With simple tricks like the one I shared today, you can streamline your data analysis and give end-users more control with ease. For more FP&A insights, follow me on LinkedIn.

Until next time,

Carl