This question came in from me!

I love everything Excel has to offer: formulae, facilities such as Power Query and, of course, the VBA programming language.  But, what am I keen on above all of this?  Effective file structure.  Yes, how you set up your file - in terms of the number of sheets and the role of each – really does change everything.  The truth is you might not need VBA or other advanced tools if the file is put together in a logical and coherent way.

I felt the benefit of clear and consistent file structure when making this video.  What’s the context here?  Well, it’s something close to my heart – the Excel (VBA) for Football Traders content navigator.  Don’t know about the content navigator?  Where have you been?!  It collates all the information about the community in one place and is an essential for any community member.  I’ve uploaded the file alongside the video and the latest version is available here.  Make sure you check it out!

What do I mean by ‘consistent’ in this case?  Well, it’s simple.  The file comprises one sheet per video series (‘Heatmaps’ is an example of a video series).  And, the layout of each sheet is exactly the same.  Right down to the number of rows and columns above and to the left of the main data on each sheet.  Moreover, the sheets are formatted in the same way – that’s only an aesthetic thing, but does create a sense of order!

The file structure and sheet layout would help me with one specific task: creating a summary sheet for the whole file.  I don’t want community members to have to click through all the sheets to understand what’s on offer.  It would be great for the main information to be summarised in one place.  So, a ‘summary’ sheet is needed: but how to do it in a way what isn’t too onerous / time-consuming?

Key to the mechanism is what some would consider a controversial choice: Excel’s INDIRECT formula.  Yes, it’s a ‘volatile’ formula: so, too many INDIRECT formulae can slow your spreadsheet down.  But, used judiciously, it delivers a unique benefit, as I demonstrate in the video, and it won’t necessarily slow things down.  And, it’s all possible because of a disciplined approach to file structure and individual sheet layout.

I hope you’ll enjoy this video which demonstrates what really matters in spreadsheet design and development.

Topics And Techniques

• Planning a spreadsheet layout

• How to quickly format a spreadsheet

• Keyboard shortcuts for formatting a spreadsheet

• Tips for column widths

• The unique contribution of INDIRECT

• The concept of ‘writing a reference in a cell’

• VBA to list all sheet names in the file

• Excel UPPER formula with INDIRECT

• Excel COUNTA formula with INDIRECT

• Excel SUM formula with INDIRECT

• How to quickly format a date or time cell