Dynamic Named ranges

Hi Sophie

Thanks for getting in touch. Thankfully since Excel 2007 there's a technique built in that avoids you having to use a complex formula to create a dynamic range. It's called "table formatting".

Select your range of data, then on the Home tab select "Format as Table". You have to pick a formatting style but you can remove this afterwards.

Even though it is labelled "Format as Table" it will give the table a name such as Table1, which you can change. If you base something on this name, such as a chart or PivotTable then it will dynamically grow with the data.

I hope this helps.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector