Excel Ranges

In Excel 2007, Microsoft have given much more prominence to Tables.  They are useful for formatting data, filtering and producing summary statistics, but they have another use - if you refer to a table (rather than a range) in a formula, they automatically expand when you add data! First, create your table (select a cell in the data, then 'Insert' tab, 'Tables' group, 'Table' command). Now, when writing a formula and wanting to include the table, just highlight it as you would when entering a range - rather than putting in the cell range, Excel will enter something like [...]

Isn't it irritating when you keep having to go back into wizards, dialog boxes and formulae to manually change data ranges? You can often get round this problem by using column references or tables. There are times when you want a range to expand itself based on the size of the data, and here's a way to do it. Because of the way this works, it's best to have your data on its own sheet, without anything else with it.  You also need to make sure there's always data in the first row and first column.  Put some data in and [...]