My consulting work recently highlighted a stark contrast in different Excel models and the effort it takes to create or change them. I make some recommendations to make things easier for yourself at the end of this post.

The morning was taken with amending a model that had multiple data sources – including data from reports and query results. This model has over-rides, exceptions and exclusions built-in to it.

The afternoon was spent building a new reporting model based on a single data table that is created via Power Query.

AM

The morning session was a hard work because I had to always be thinking about the exceptions and exclusions and getting the right data out of the differently structured data sources.

At the end of the morning, the work output didn’t seem to match the amount of effort I put in.

It was tough to stay motivated and focused. I was tired when I finished.

PM

A salad for lunch and into the afternoon session. It was a breeze. Working with correctly structured tables in Excel makes life so easy. I was looking forward to this.

I look back on the time spent and think wow, look at all those reports and they are all driven by only a few formulas. (I also thought I’d better write a blog post about this)

I used helper cells; range names; table names and formulas to populate all the different report layouts because the data and report structures were consistent.

All the reports are automated to the extent that all the user has to do is paste in the latest Power Query results – reports done.

The Power Query results are in a separate file and these new report files needed to be standalone files  – so no links. A single paste is all that is required to compete the reports.

The Power Query results already drive a number of other reporting models.

Creating vs Amending

Of course part of the ease of the afternoon session was creating a model from scratch – I could create the structures, range names and helper cells to make my life easy.

But even changing well structured data is so much easier than amending models with exceptions, over-rides and different data structures.

A few recommendations to make creation and change easier

• Use Formatted Tables – auto expanding Tables are great
• Use range names in formulas – range names add structure to files (when used correctly)
• Use helper cells – any cells that simplify the final formulas are helper cells
• Use grouping to hide rows and columns used as helper cells – makes it so much easier to hide/unhide the workings ranges
• Centralise your logic – eg if a column is for actuals, capture that fact in a single row and refer back to that row in all the formulas in the column
• Use formulas/functions (EOMONTH and EDATE) to automate all the dates in your file, either with a single input cell or via the data in the file. Eg use the MAX function to find the last date in a table column.
• Use fixed/mixed references to make copying easy – remember pressing the F4 function key in the formula bar changes references easily (you can keep pressing F4 to change the reference)
• If you need to use a different formula in a range, change its fill colour and add a cell comment to explain the exception
• Use formulas to capture the sheet name and link to it to refer to that sheet – see how in this blog post