Avoid looking up all rows in a column to make spreadhseet faster

[rash799]

Hi,

My excel sheet has become slow and I'm looking for ways to speed it up. One thing I found was that my formulas look up all of a column (eg they look up A:A instead of A1:A200). I've done this so that the formulas don't need to be updated when more data is added but now that my spreadsheet is becoming very slow, I wonder if there's a formaula solution to make this faster by automatically calculating how far down the formula should look. I'm thinking on the lines of a cell which counts the total number of rows that are not empty and then the other formulas looking up that cell to find out how far down a column they should look. Alternatively if there's any other way to do this or if you have any other more general tips on speeding up a spreadhseet I'd love to hear them.

Thanks

[Tony Valko]

You can use dynamic ranges.

What type of data is it? Is it text? Numeric? Could be both? Something else?

Is the data entered in a contiguous range or are there empty cells within the range?

[james19]

You can use dynamic ranges.

What type of data is it? Is it text? Numeric? Could be both? Something else?

Is the data entered in a contiguous range or are there empty cells within the range?

It could be either.

[james19]

You can use dynamic ranges.

What type of data is it? Is it text? Numeric? Could be both? Something else?

Is the data entered in a contiguous range or are there empty cells within the range?

Oh, and it's a continous range with no gaps. :-)

[bebo021999]

To keep the formula simpler than using dynamic range solution:

If your current used range is A1:A200. Highlight the rows 202, 203 with background red colour.

Expand your range in formula to A1:A203.

Choose row 201 and insert more rows (should be ABOVE row 202) for inputting data.

[Jacc]

Use the Excel Table feature.
http://contextures.com/xlExcelTable01.html

[rash799]

Use the Excel Table feature.
http://contextures.com/xlExcelTable01.html

Hi Jacc,

This is great thanks

[Tony Valko]

Here is how to create some dynamic ranges.

Data Range

	A	B	C
1	Range1	Range2	Range3
2	Bill	83	text
3	Tom	65	10
4	Sue	91	10
5	Barb	22	20
6	Carl	99	text
7	Karen	89	text
8		54
9		84
10
11
12
13
14
15

Data Range

	E	F
2	Range1	=Sheet1!$A$2:INDEX(Sheet1!$A:$A, MATCH("zzzzz",Sheet1!$A:$A))
3	Range2	=Sheet1!$B$2:INDEX(Sheet1!$B:$B, MATCH(1E100,Sheet1!$B:$B))
4	Range3	=Sheet1!$C$2:INDEX(Sheet1!$C$2:$C$10000,COUNTA($C$2:$C$10000))

In Range3 use an end of range that you think is large enough to allow for future data addition.

Some example formulas...

Data Range

	E	F	G
2	Range1	6	=COUNTA(Range1)
3	Range2	5	=COUNTIF(Range2,">75")
4	Range3	3	=COUNTIF(Range3,"Text")
5	------	------