TRIMRANGE function First Last State Age

Walter Brown NM 55
#NAME? #NAME? ### ### Aya Shizuoka OR 27
#NAME? #NAME? ### ### Janice Drexel WY 41
#NAME? #NAME? ### ### Steve Malloy UT 37
#NAME? #NAME? ### ### Timothy Sanders TX 50
#NAME? #NAME? ### ### Jordan Smith MN 31
#NAME? #NAME? ### ### Jason Chang GA 40
#NAME? #NAME? ### ### Emily Harris CO 38
#NAME? #NAME? ### ### Yuki Nomura ID 35
#NAME? #NAME? ### ### Kelly Grady TX 29
#NAME? #NAME? ### ### Walter Brown NM 55
#NAME? #NAME? ### ###
#NAME? #NAME? ### ###
Read a full explanation here

Example of cutting a very

large range down to a much
smaller size.
ID Address Price Beds Baths Size (sf) Status Rows
1 5335 Lake Rd $535,680 3 1 2,003 For Sale ID
2 4349 Cedar Ln $423,910 1 1 1,207 For Sale Price
3 2573 Maple Ave $355,068 4 4 3,454 Sold
4 3773 Lake Dr $473,365 4 4 3,455 Sold
5 1659 Main Ave $349,562 1 2 1,041 For Sale
6 2680 Lake Ave $388,774 2 2 1,535 For Sale
7 2386 Oak Rd $628,305 2 3 1,831 For Sale
8 2636 Oak St $790,649 4 1 2,646 Sold
9 2095 Hill Dr $783,209 4 3 2,871 Sale Pending
10 2441 Main Ave $229,702 3 2 2,389 For Sale
 10
6
$388,774 Read a full explanation here

Typical ranges used in two

simple formulas. These will
not change when data is
added to the table. Try
copying and pasting data from
Sheet8.
ID Address Price Beds Baths Size (sf) Status Rows
1 5335 Lake Rd $535,680 3 1 2,003 For Sale ID
2 4349 Cedar Ln $423,910 1 1 1,207 For Sale Price
3 2573 Maple Ave $355,068 4 4 3,454 Sold
4 3773 Lake Dr $473,365 4 4 3,455 Sold
5 1659 Main Ave $349,562 1 2 1,041 For Sale
6 2680 Lake Ave $388,774 2 2 1,535 For Sale
7 2386 Oak Rd $628,305 2 3 1,831 For Sale
8 2636 Oak St $790,649 4 1 2,646 Sold
9 2095 Hill Dr $783,209 4 3 2,871 Sale Pending
10 2441 Main Ave $229,702 3 2 2,389 For Sale
11 1632 Lake Dr 341303 3 1 2158 Sale Pending
12 3390 Park St 250047 1 1 1229 For Sale
13 4752 Maple Ln 674974 4 2 2605 For Sale
14 2132 Hill Dr 283345 3 2 2137 Sale Pending
15 2812 Hill Ln 537564 4 5 3270 For Sale
16 1780 Lake Dr 738721 2 1 1641 Sold
17 4273 Pine Dr 433522 1 2 1295 For Sale
18 5952 Elm Rd 789259 4 3 3212 For Sale
19 3507 Pine St 673609 4 4 3494 Sold
20 5713 Maple Ln 466637 3 2 2259 Sale Pending
21 4416 Main Rd 726907 4 2 2725 Sold
22 2600 Valley Rd 534687 1 1 978 Sold
23 5046 Cedar St 249637 4 2 3002 Sale Pending
24 1264 Hill Dr 277338 3 2 2494 For Sale
25 3575 Hill St 595292 4 4 3106 Sale Pending
26 4608 Valley Rd 308863 1 1 923 For Sale
27 1194 Elm St 602582 4 4 3139 For Sale
28 5177 Maple St 639768 3 2 2184 Sold
29 2678 Cedar Ln 638094 2 3 1931 For Sale
30 2726 Cedar Dr 435139 4 3 3190 Sale Pending
31 1529 Lake St 684397 2 2 1818 Sold
32 2557 Oak St 364104 2 1 1705 For Sale
33 4389 Lake Ln 327208 4 5 3519 For Sale
34 5658 Main Dr 633559 4 2 2650 Sale Pending
35 4023 Cedar St 619993 1 1 1009 Sold
36 5416 Maple Rd 705151 2 1 1296 Sale Pending
37 5275 Cedar Dr 758887 3 3 2354 Sold
38 2223 Main St 541979 3 4 2951 For Sale
39 1755 Oak Rd 505141 2 1 1561 For Sale
40 1919 Oak St 453599 1 1 1080 For Sale
41 5685 Valley Dr 363125 4 2 2683 Sold
42 3357 Lake Dr 610911 2 1 1431 For Sale
43 5942 Pine Dr 411025 1 2 1373 Sale Pending
44 1439 Park Ln 557273 1 1 1018 For Sale
45 1523 Hill Dr 515513 2 2 1881 Sold
46 3624 Elm Ave 389378 3 4 2928 For Sale
47 1140 Valley Ln 424645 2 3 1770 For Sale
48 1008 Park Ave 799813 2 2 1817 For Sale
49 3000 Maple Rd 514130 2 3 2006 For Sale
50 1506 Elm St 564013 1 2 1265 Sale Pending
51 1208 Hill Dr 425555 2 1 1708 For Sale
52 1010 Maple Rd 679568 1 1 898 Sold
53 1521 Maple Ln 257265 2 2 1827 For Sale
54 1235 Hill Ave 350743 3 2 2177 For Sale
55 1583 Cedar Ln 525850 3 3 2739 Sale Pending
56 2935 Oak Rd 770316 1 2 1163 For Sale
57 5047 Oak Dr 654299 1 1 796 Sale Pending
58 2227 Maple St 627513 3 3 2497 SOld
59 3920 Hill Dr 353164 4 4 3183 Sale Pending
60 2490 Pine Dr 215489 3 4 2484 Sold
61 4198 Hill Ln 251223 2 3 1846 Sale Pending
62 2280 Park Ave 449056 4 1 2652 For Sale
63 1938 Lake St 423316 1 1 766 Sale Pending
64 1165 Valley Dr 708873 3 3 2412 For Sale
65 2302 Main St 440628 4 3 2958 For Sale
66 1785 Cedar Ave 692722 3 1 1877 For Sale
67 5598 Elm Rd 407539 1 2 1026 For Sale
68 5606 Valley Dr 675773 3 3 2729 Sold
69 2365 Valley St 758315 4 3 3063 Sold
70 1622 Hill Ave 376554 4 2 3037 Sold
71 2273 Cedar Ave 269643 3 2 2059 For Sale
72 1000 Valley Dr 591775 1 2 1396 For Sale
73 2706 Hill Rd 630511 1 2 1372 Sale Pending
74 1177 Cedar Ave 631976 2 1 1482 Sold
75 4600 Elm Ave 354543 4 4 3219 For Sale
76 4789 Lake Rd 539234 3 1 2152 For Sale
77 3895 Lake Rd 333450 3 3 2668 Sold
78 2599 Cedar Ln 737081 4 3 3090 For Sale
79 3092 Oak Dr 540942 1 1 894 Sold
80 2527 Lake Ave 240497 2 2 1558 Sold
81 1753 Elm St 233875 3 2 2223 Sold
82 3490 Park Dr 521329 2 1 1637 For Sale
83 3938 Main Ave 463164 1 2 1419 For Sale
84 4669 Main St 326436 3 4 2899 For Sale
85 5921 Maple Dr 680857 3 1 2092 Sale Pending
86 1416 Oak Ln 387418 4 1 2334 Sold
 87 1059 Valley Rd 660014 4 5 3672 Sold
88 5986 Oak Rd 359583 2 2 1940 Sale Pending
89 3687 Elm Ln 757681 3 2 2379 Sold
90 1240 Main Ave 428235 4 3 2884 Sold
91 1761 Elm St 532801 2 3 1843 For Sale
92 3858 Valley Ln 368932 4 3 3003 Sold
93 3947 Cedar Ln 488372 1 2 1174 For Sale
94 1536 Hill Dr 744819 1 2 1023 Sold
95 4570 Main Dr 766861 2 2 1831 Sold
96 2739 Elm Ln 454745 1 1 1240 Sale Pending
97 1519 Maple Ave 506100 3 3 2258 Sold
98 4049 Cedar Rd 677321 4 2 2732 Sold
99 2249 Valley Rd 429427 4 4 3168 For Sale
100 4828 Cedar St 786517 2 3 1928 For Sale
 100
12
$250,047 Read a full explanation here

Using TRIMRANGE to make

both formulas use dynamic
ranges.
ID Address Price Beds Baths Size (sf) Status Rows
1 5335 Lake Rd $535,680 3 1 2,003 For Sale ID
2 4349 Cedar Ln $423,910 1 1 1,207 For Sale Price
3 2573 Maple Ave $355,068 4 4 3,454 Sold
4 3773 Lake Dr $473,365 4 4 3,455 Sold
5 1659 Main Ave $349,562 1 2 1,041 For Sale
6 2680 Lake Ave $388,774 2 2 1,535 For Sale
7 2386 Oak Rd $628,305 2 3 1,831 For Sale
8 2636 Oak St $790,649 4 1 2,646 Sold
9 2095 Hill Dr $783,209 4 3 2,871 Sale Pending
10 2441 Main Ave $229,702 3 2 2,389 For Sale
11 1632 Lake Dr $341,303 3 1 2,158 Sale Pending
12 3390 Park St $250,047 1 1 1,229 For Sale
13 4752 Maple Ln $674,974 4 2 2,605 For Sale
14 2132 Hill Dr $283,345 3 2 2,137 Sale Pending
15 2812 Hill Ln $537,564 4 5 3,270 For Sale
16 1780 Lake Dr $738,721 2 1 1,641 Sold
17 4273 Pine Dr $433,522 1 2 1,295 For Sale
18 5952 Elm Rd $789,259 4 3 3,212 For Sale
19 3507 Pine St $673,609 4 4 3,494 Sold
20 5713 Maple Ln $466,637 3 2 2,259 Sale Pending
21 4416 Main Rd $726,907 4 2 2,725 Sold
22 2600 Valley Rd $534,687 1 1 978 Sold
23 5046 Cedar St $249,637 4 2 3,002 Sale Pending
24 1264 Hill Dr $277,338 3 2 2,494 For Sale
25 3575 Hill St $595,292 4 4 3,106 Sale Pending
26 4608 Valley Rd $308,863 1 1 923 For Sale
27 1194 Elm St $602,582 4 4 3,139 For Sale
28 5177 Maple St $639,768 3 2 2,184 Sold
29 2678 Cedar Ln $638,094 2 3 1,931 For Sale
30 2726 Cedar Dr $435,139 4 3 3,190 Sale Pending
31 1529 Lake St $684,397 2 2 1,818 Sold
32 2557 Oak St $364,104 2 1 1,705 For Sale
33 4389 Lake Ln $327,208 4 5 3,519 For Sale
34 5658 Main Dr $633,559 4 2 2,650 Sale Pending
35 4023 Cedar St $619,993 1 1 1,009 Sold
36 5416 Maple Rd $705,151 2 1 1,296 Sale Pending
37 5275 Cedar Dr $758,887 3 3 2,354 Sold
38 2223 Main St $541,979 3 4 2,951 For Sale
39 1755 Oak Rd $505,141 2 1 1,561 For Sale
40 1919 Oak St $453,599 1 1 1,080 For Sale
41 5685 Valley Dr $363,125 4 2 2,683 Sold
42 3357 Lake Dr $610,911 2 1 1,431 For Sale
43 5942 Pine Dr $411,025 1 2 1,373 Sale Pending
44 1439 Park Ln $557,273 1 1 1,018 For Sale
45 1523 Hill Dr $515,513 2 2 1,881 Sold
46 3624 Elm Ave $389,378 3 4 2,928 For Sale
47 1140 Valley Ln $424,645 2 3 1,770 For Sale
48 1008 Park Ave $799,813 2 2 1,817 For Sale
49 3000 Maple Rd $514,130 2 3 2,006 For Sale
50 1506 Elm St $564,013 1 2 1,265 Sale Pending
51 1208 Hill Dr $425,555 2 1 1,708 For Sale
52 1010 Maple Rd $679,568 1 1 898 Sold
53 1521 Maple Ln $257,265 2 2 1,827 For Sale
54 1235 Hill Ave $350,743 3 2 2,177 For Sale
55 1583 Cedar Ln $525,850 3 3 2,739 Sale Pending
56 2935 Oak Rd $770,316 1 2 1,163 For Sale
57 5047 Oak Dr $654,299 1 1 796 Sale Pending
58 2227 Maple St $627,513 3 3 2,497 SOld
59 3920 Hill Dr $353,164 4 4 3,183 Sale Pending
60 2490 Pine Dr $215,489 3 4 2,484 Sold
61 4198 Hill Ln $251,223 2 3 1,846 Sale Pending
62 2280 Park Ave $449,056 4 1 2,652 For Sale
63 1938 Lake St $423,316 1 1 766 Sale Pending
64 1165 Valley Dr $708,873 3 3 2,412 For Sale
65 2302 Main St $440,628 4 3 2,958 For Sale
66 1785 Cedar Ave $692,722 3 1 1,877 For Sale
67 5598 Elm Rd $407,539 1 2 1,026 For Sale
68 5606 Valley Dr $675,773 3 3 2,729 Sold
69 2365 Valley St $758,315 4 3 3,063 Sold
70 1622 Hill Ave $376,554 4 2 3,037 Sold
71 2273 Cedar Ave $269,643 3 2 2,059 For Sale
72 1000 Valley Dr $591,775 1 2 1,396 For Sale
73 2706 Hill Rd $630,511 1 2 1,372 Sale Pending
74 1177 Cedar Ave $631,976 2 1 1,482 Sold
75 4600 Elm Ave $354,543 4 4 3,219 For Sale
76 4789 Lake Rd $539,234 3 1 2,152 For Sale
77 3895 Lake Rd $333,450 3 3 2,668 Sold
78 2599 Cedar Ln $737,081 4 3 3,090 For Sale
79 3092 Oak Dr $540,942 1 1 894 Sold
80 2527 Lake Ave $240,497 2 2 1,558 Sold
81 1753 Elm St $233,875 3 2 2,223 Sold
82 3490 Park Dr $521,329 2 1 1,637 For Sale
83 3938 Main Ave $463,164 1 2 1,419 For Sale
84 4669 Main St $326,436 3 4 2,899 For Sale
85 5921 Maple Dr $680,857 3 1 2,092 Sale Pending
86 1416 Oak Ln $387,418 4 1 2,334 Sold
 87 1059 Valley Rd $660,014 4 5 3,672 Sold
88 5986 Oak Rd $359,583 2 2 1,940 Sale Pending
89 3687 Elm Ln $757,681 3 2 2,379 Sold
90 1240 Main Ave $428,235 4 3 2,884 Sold
91 1761 Elm St $532,801 2 3 1,843 For Sale
92 3858 Valley Ln $368,932 4 3 3,003 Sold
93 3947 Cedar Ln $488,372 1 2 1,174 For Sale
94 1536 Hill Dr $744,819 1 2 1,023 Sold
95 4570 Main Dr $766,861 2 2 1,831 Sold
96 2739 Elm Ln $454,745 1 1 1,240 Sale Pending
97 1519 Maple Ave $506,100 3 3 2,258 Sold
98 4049 Cedar Rd $677,321 4 2 2,732 Sold
99 2249 Valley Rd $429,427 4 4 3,168 For Sale
100 4828 Cedar St $786,517 2 3 1,928 For Sale
 without named range

100 100
12
$250,047 Read a full explanation here $250,047

Using a dynamic named range

called "data" inside both
formulas. Open the Name
Manager to see how it is
defined.
TRIMRANGE to create a dropdown menu

Product Color Products Colors

Product A Red Product A Red
Product B Green Product B Blue Read a full explanation here
Product C Product C Green
White
Black
Using a TRIMRANGE to
populate two dropdown menu
based on values in column E
and G.
 raw formulas

#NAME? #NAME?
#NAME? #NAME?
Read a full explanation here #NAME? #NAME?
#NAME?
#NAME?

Using a TRIMRANGE to
populate two dropdown menus
based on values in column E
and G.
TRIMRANGE to anchor other formulas

Text LEN 1
Bird 4
Cat 3 Read a full explanation here
Coyote 6
Dog 3
Elephant 8
Fox 3 A normal dynamic array formula
will not expand to include new
Mink 4 data.
Moose 5
Otter 5
Jellyfish 9
Monkey 6
Zebra
TRIMRANGE to anchor other formulas

Text LEN 1 LEN 2

Bird 4 #NAME?
Cat 3 #NAME? Read a full explanation here
Coyote 6 #NAME?
Dog 3 #NAME?
Elephant 8 #NAME? Using TRIMRANGE to "anchor"
Fox 3 #NAME? a formula to the data in column
B.
Mink 4 #NAME?
Moose 5 #NAME?
Otter 5 #NAME?
Jellyfish 9 #NAME?
Monkey 6 #NAME?
Zebra #NAME?
More data - copy and paste from here

11 1632 Lake Dr 341303 3 1 2158 Sale Pending

12 3390 Park St 250047 1 1 1229 For Sale
13 4752 Maple Ln 674974 4 2 2605 For Sale
14 2132 Hill Dr 283345 3 2 2137 Sale Pending
15 2812 Hill Ln 537564 4 5 3270 For Sale
16 1780 Lake Dr 738721 2 1 1641 Sold
17 4273 Pine Dr 433522 1 2 1295 For Sale
18 5952 Elm Rd 789259 4 3 3212 For Sale
19 3507 Pine St 673609 4 4 3494 Sold
20 5713 Maple Ln 466637 3 2 2259 Sale Pending
21 4416 Main Rd 726907 4 2 2725 Sold
22 2600 Valley Rd 534687 1 1 978 Sold
23 5046 Cedar St 249637 4 2 3002 Sale Pending
24 1264 Hill Dr 277338 3 2 2494 For Sale
25 3575 Hill St 595292 4 4 3106 Sale Pending
26 4608 Valley Rd 308863 1 1 923 For Sale
27 1194 Elm St 602582 4 4 3139 For Sale
28 5177 Maple St 639768 3 2 2184 Sold
29 2678 Cedar Ln 638094 2 3 1931 For Sale
30 2726 Cedar Dr 435139 4 3 3190 Sale Pending
31 1529 Lake St 684397 2 2 1818 Sold
32 2557 Oak St 364104 2 1 1705 For Sale
33 4389 Lake Ln 327208 4 5 3519 For Sale
34 5658 Main Dr 633559 4 2 2650 Sale Pending
35 4023 Cedar St 619993 1 1 1009 Sold
36 5416 Maple Rd 705151 2 1 1296 Sale Pending
37 5275 Cedar Dr 758887 3 3 2354 Sold
38 2223 Main St 541979 3 4 2951 For Sale
39 1755 Oak Rd 505141 2 1 1561 For Sale
40 1919 Oak St 453599 1 1 1080 For Sale
41 5685 Valley Dr 363125 4 2 2683 Sold
42 3357 Lake Dr 610911 2 1 1431 For Sale
43 5942 Pine Dr 411025 1 2 1373 Sale Pending
44 1439 Park Ln 557273 1 1 1018 For Sale
45 1523 Hill Dr 515513 2 2 1881 Sold
46 3624 Elm Ave 389378 3 4 2928 For Sale
47 1140 Valley Ln 424645 2 3 1770 For Sale
48 1008 Park Ave 799813 2 2 1817 For Sale
49 3000 Maple Rd 514130 2 3 2006 For Sale
50 1506 Elm St 564013 1 2 1265 Sale Pending
51 1208 Hill Dr 425555 2 1 1708 For Sale
52 1010 Maple Rd 679568 1 1 898 Sold
53 1521 Maple Ln 257265 2 2 1827 For Sale
54 1235 Hill Ave 350743 3 2 2177 For Sale
55 1583 Cedar Ln 525850 3 3 2739 Sale Pending
56 2935 Oak Rd 770316 1 2 1163 For Sale
57 5047 Oak Dr 654299 1 1 796 Sale Pending
58 2227 Maple St 627513 3 3 2497 SOld
59 3920 Hill Dr 353164 4 4 3183 Sale Pending
60 2490 Pine Dr 215489 3 4 2484 Sold
61 4198 Hill Ln 251223 2 3 1846 Sale Pending
62 2280 Park Ave 449056 4 1 2652 For Sale
63 1938 Lake St 423316 1 1 766 Sale Pending
64 1165 Valley Dr 708873 3 3 2412 For Sale
65 2302 Main St 440628 4 3 2958 For Sale
66 1785 Cedar Ave 692722 3 1 1877 For Sale
67 5598 Elm Rd 407539 1 2 1026 For Sale
68 5606 Valley Dr 675773 3 3 2729 Sold
69 2365 Valley St 758315 4 3 3063 Sold
70 1622 Hill Ave 376554 4 2 3037 Sold
71 2273 Cedar Ave 269643 3 2 2059 For Sale
72 1000 Valley Dr 591775 1 2 1396 For Sale
73 2706 Hill Rd 630511 1 2 1372 Sale Pending
74 1177 Cedar Ave 631976 2 1 1482 Sold
75 4600 Elm Ave 354543 4 4 3219 For Sale
76 4789 Lake Rd 539234 3 1 2152 For Sale
77 3895 Lake Rd 333450 3 3 2668 Sold
78 2599 Cedar Ln 737081 4 3 3090 For Sale
79 3092 Oak Dr 540942 1 1 894 Sold
80 2527 Lake Ave 240497 2 2 1558 Sold
81 1753 Elm St 233875 3 2 2223 Sold
82 3490 Park Dr 521329 2 1 1637 For Sale
83 3938 Main Ave 463164 1 2 1419 For Sale
84 4669 Main St 326436 3 4 2899 For Sale
85 5921 Maple Dr 680857 3 1 2092 Sale Pending
86 1416 Oak Ln 387418 4 1 2334 Sold
87 1059 Valley Rd 660014 4 5 3672 Sold
88 5986 Oak Rd 359583 2 2 1940 Sale Pending
89 3687 Elm Ln 757681 3 2 2379 Sold
90 1240 Main Ave 428235 4 3 2884 Sold
91 1761 Elm St 532801 2 3 1843 For Sale
92 3858 Valley Ln 368932 4 3 3003 Sold
93 3947 Cedar Ln 488372 1 2 1174 For Sale
94 1536 Hill Dr 744819 1 2 1023 Sold
95 4570 Main Dr 766861 2 2 1831 Sold
96 2739 Elm Ln 454745 1 1 1240 Sale Pending
 97 1519 Maple Ave 506100 3 3 2258 Sold
98 4049 Cedar Rd 677321 4 2 2732 Sold
99 2249 Valley Rd 429427 4 4 3168 For Sale
100 4828 Cedar St 786517 2 3 1928 For Sale
Read a full explanation here

Extra data you can copy and

paste into Sheet2.
TRIMRANGE function

Product 1-Feb 1-Mar 1-Apr 1-May 1-Jun

Product A 1200 2500 2425 1525 1375
Product B 800 2200 1900 2475 675 Read a full explanation her
Product C 1575 775 1400 1375 1700
Product D 550 2025 850 1750
Product E 950 2075 1675 900 750 Example of a data that ca
be used to create an Exce
Product F 1600 2100 2150 500 2050 Table. But TRIMRANGE w
Product G 1725 1375 2275 1100 750 work fine.
Product H 2375 625 500 575
Product I 1000 1675 2475 2225
Product J 1975 775 2300 1125
Product K 1425 2300
Product L 1800 1150 1900
Read a full explanation here

Example of a data that cannot

be used to create an Excel
Table. But TRIMRANGE will
work fine.