The Secret To Smooth Formula-Building In Excel (EXCEL MODELLING FOR BEGINNERS!)

As Excel Analysts, we know an Excel model comprises input, process and output cells, and expresses real-world relationships with Excel formulae. This is the essence of an Excel model and, as we learned yesterday on 30 Day Excel Analyst, not all Excel files are models! In fact, very few are.

DOWNLOAD THE RESOURCES HERE

Watch The Video HERE

But, real-world relationships are complex - which means the formula-building phase of Excel model construction (that attempts to express those real-world relationships) is often tricky. I’ve got into a tangle many times trying to build Excel formulae, and I’m sure you have too. In this session, I walk you through how to conceptualise and plan an Excel model based on real-world situations I am engaged in all the time. It’s the approach I use to make Excel formula-building smooth. Yes, it doesn’t NEED to be stressful and long formulae are not required …

Welcome back to Day 14 of 30 Day Excel Analyst – transforming your analytical skillset in 30 days.

I love the management science truism ‘Structure Determines Behaviour’. An employee’s experience in an organsiation, the argument goes, is primarily shaped by the organisation’s structure. Escalating a matter in your day-to-day work, or getting help with a time-consuming task can be stressful; but, these things are done more easily if, for example, your boss or superiors are available or you have colleagues around you ready to help. But, helpful bosses and colleagues only exist as far as the organisation’s hierarchy allows. This is why structure, the argument goes, not something more immediate such as the content of your job, determines behaviour.

Obviously, I’ve applied this argument to spreadsheets. Since, your experience of Excel, and specifically how stressful it is, is determined to a great extent by the layout and structure of the file (or files) you’re working with.

If you’re limited to a single column for a complex calculation, for example, stress levels are likely to rise as you grapple with a long and complicated Excel formula. On the other hand, suppose you have multiple columns with helpful column headers expressing the calculation step-by-step. That means steady-and-systematic formula-building and less stress for you, not to mention a spreadsheet that’s more efficient AND easier to understand and debug. It’s a route to preserving your sanity as an Excel Analyst. (Spreadsheet) structure determines (Programmer) behaviour! Welcome back to 30 Day Excel Analyst …