Interactive Reports Made Easy with Dynamic Arrays

Link Topic Comprehension

DAPivot Easy Pivot-Table-Style Reports with Select One…
Dynamic Arrays
PivotSelection Pivot-Style Report with Row & Column Select One…
Selection
InterestPaid Interest Paid with SEQUENCE Select One…
LoanAmort Loan Amortization Table (Interest, Select One…
Principal) with SEQUENCE
Calendar Dynamic Calendar with SEQUENCE Select One…
Function
ShrinkingDataVal Shrinking Data Validation - Hide Used Select One…
Items
SearchableDataVal Searchable Data Validation List Select One…

Challenge Challenge: Interactive KPI Selection Select One…

Report
Arrays

Your Notes
Easy Pivot-Table-Style Reports with Dynamic Arrays INDEX
Create a report for App by region - show actual, budget and % change.

Actual
Division Region App Sales Budget All Regions
Game Asia Arcade 4378 4159 #NAME? #NAME?
Game Asia Aviatrr 5254 4203 #NAME? #NAME?
Game Asia Baden 6130 6682 #NAME? #NAME?
Game Asia deRamblr 7443 7220 #NAME? #NAME?
Game Asia Fightrr 13135 15631 #NAME? #NAME?
Game Asia Twistrr 7443 7815 #NAME? #NAME?
Game Australia Arcade 15890 12712 #NAME? #NAME?
Game Australia Aviatrr 11917 14181 #NAME? #NAME?
Game Australia Baden 1490 1296 #NAME? #NAME?
Game Australia deRamblr 497 586 #NAME? #NAME?
Game Australia Fightrr 993 1122 #NAME? #NAME?
Game Australia Five Labs 2483 2111 #NAME? #NAME?
Game Australia Hackrr 2979 2919 #NAME? #NAME?
Game Australia Jellyfish 3972 4051 #NAME? #NAME?
Game Australia Kryptis 4469 5363 #NAME? #NAME?
Game Australia Perino 993 923 #NAME? #NAME?
Game Australia Pes 497 467 #NAME? #NAME?
Game Australia Twistrr 3476 3546 #NAME? #NAME?
Game Europe Arcade 9267 10101 #NAME? #NAME?
Game Europe Aviatrr 9731 9536 #NAME? #NAME?
Game Europe Baden 15754 12603 #NAME? #NAME?
Game Europe deRamblr 927 973 #NAME? #NAME?
Game Europe Fightrr 1390 1460 #NAME? #NAME?
Game Europe Five Labs 1853 1612 #NAME? #NAME?
Game Europe Hackrr 2317 2039 #NAME? #NAME?
Game Europe Jellyfish 927 769 #NAME? #NAME?
Game Europe Kryptis 927 751 #NAME? #NAME?
Game Europe Perino 463 505 #NAME? #NAME?
Game Europe Pes 463 375 #NAME? #NAME?
Game Europe Twistrr 2317 2780 #NAME? #NAME?
Game North America Arcade 5361 5575 #NAME? #NAME?
Game North America Aviatrr 5808 6156 #NAME? #NAME?
Game North America Baden 6701 6500 #NAME? #NAME?
Game North America deRamblr 7148 7934 #NAME? #NAME?
Game North America Fightrr 8042 6997 #NAME? #NAME?
Game North America Five Labs 447 496 #NAME? #NAME?
Game North America Hackrr 894 1073 #NAME? #NAME?
Game North America Jellyfish 1340 1501 #NAME? #NAME?
Game North America Kryptis 1787 1984 #NAME? #NAME?
Game North America Perino 1340 1099 #NAME? #NAME?
Game North America Pes 4468 5138
Game North America Twistrr 1340 1380
Game South America Arcade 5108 4240
Game South America Aviatrr 0 0
Game South America Baden 0 0
Game South America deRamblr 0 0
Game South America Fightrr 851 996
TRY THIS - Add a new APP to the
Game South America Five Labs 2554 3065 bottom of the table and see how
Game South America Hackrr 8514 9280 the report updates.
Game South America Jellyfish 0 0
Game South America Kryptis 0 0
Game South America Perino 0 0
Game South America Pes 12771 13410
Game South America Twistrr 12771 10855
ProductivityAsia Blend 0 0
ProductivityAsia Dasring 0 0
ProductivityAsia Didactic 0 0
ProductivityAsia Flowrrr 0 0
ProductivityAsia Halotot 3513 2951
ProductivityAsia Inkly 2928 3426
ProductivityAsia Kind Ape 1757 1634
ProductivityAsia Mirrrr 3513 3794
ProductivityAsia Pet Feed 0 0
ProductivityAsia Rehire 0 0
ProductivityAsia Right App 0 0
ProductivityAsia Silvrr 0 0
ProductivityAsia Sleops 878 948
ProductivityAsia Voltage 3513 2810
ProductivityAsia WenCaL 13174 13569
ProductivityAustralia Kind Ape 2342 2061
ProductivityAustralia Mirrrr 4977 5724
ProductivityAustralia Pet Feed 7612 7916
ProductivityAustralia Rehire 6734 6128
ProductivityAustralia Right App 1171 1124
ProductivityAustralia Silvrr 0 0
ProductivityAustralia Sleops 0 0
ProductivityAustralia Voltage 0 0
ProductivityAustralia WenCaL 6441 6055
ProductivityEurope Blend 911 1002
ProductivityEurope Dasring 2277 2004
ProductivityEurope Didactic 3188 3794
ProductivityEurope Flowrrr 3643 3570
ProductivityEurope Halotot 5465 4700
ProductivityEurope Inkly 0 0
ProductivityEurope Kind Ape 0 0
ProductivityEurope Mirrrr 1822 2077
ProductivityEurope Pet Feed 2732 3114
ProductivityEurope Rehire 3188 3666
ProductivityEurope Right App 5465 5738
ProductivityEurope Silvrr 4554 3825
ProductivityEurope Sleops 455 364
ProductivityEurope Voltage 911 1020
ProductivityEurope WenCaL 10930 12897
ProductivityNorth America Blend 1708 2033
ProductivityNorth America Dasring 683 601
ProductivityNorth America Didactic 683 642
ProductivityNorth America Flowrrr 683 751
ProductivityNorth America Halotot 6148 6271
ProductivityNorth America Inkly 1025 1210
ProductivityNorth America Kind Ape 683 608
ProductivityNorth America Mirrrr 1366 1448
ProductivityNorth America Pet Feed 1708 1520
ProductivityNorth America Rehire 6148 7255
ProductivityNorth America Right App 7514 7890
ProductivityNorth America Silvrr 683 669
ProductivityNorth America Sleops 342 339
ProductivityNorth America Voltage 3416 4031
ProductivityNorth America WenCaL 1366 1475
ProductivitySouth America Blend 1220 1098
ProductivitySouth America Dasring 2732 2896
ProductivitySouth America Didactic 9563 9276
ProductivitySouth America Right App 1025 1210
ProductivitySouth America Silvrr 0 0
ProductivitySouth America Sleops 683 765
ProductivitySouth America Voltage 12979 12719
ProductivitySouth America WenCaL 5465 6449
Utility Australia Accord 684 609
Utility Australia Amplefio 4104 4309
Utility Australia Atmos 0 0
Utility Australia Commuta 1710 1436
Utility Australia Infic 684 554
Utility Australia Minor Liar 684 711
Utility Australia Misty Wash 0 0
Utility Australia Mosquit 10259 10669
Utility Australia Motocyco 1026 934
Utility Australia Scrap 342 321
Utility Australia Strex 684 732
Utility Australia Tanox 6839 6292
Utility Australia Twenty20 7181 6607
Utility Europe Accord 2622 2491
Utility Europe Amplefio 2185 1814
Utility Europe Atmos 1748 1835
Utility Europe Commuta 5243 5610
Utility Europe Infic 1311 1337
Utility Europe Minor Liar 13109 15206
Utility Europe Misty Wash 12235 12357
Utility Europe Mosquit 874 1040
Utility Europe Motocyco 437 454
Utility Europe Scrap 437 376
Utility Europe Strex 0 0
Utility Europe Tanox 1311 1442
Utility Europe Twenty20 2185 2294
Utility North America Accord 0 0
Utility North America Amplefio 0 0
Utility North America Atmos 2210 2298
Utility North America Commuta 1768 1662
Utility North America Infic 1326 1551
Utility North America Minor Liar 9723 9917
Utility North America Misty Wash 0 0
Utility North America Mosquit 884 725
Utility North America Motocyco 11049 9834
Utility North America Scrap 884 831
Utility North America Strex 13259 12066
Utility North America Tanox 884 743
Utility North America Twenty20 2210 2387
Utility South America Accord 0 0
Utility South America Amplefio 0 0
Utility South America Atmos 0 0
Utility South America Commuta 4598 4368
Utility South America Infic 6687 6018
Utility South America Minor Liar 0 0
Utility South America Misty Wash 2508 2006
Utility South America Twenty20 7105 6395
 Actual Budget

#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?

#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
 % Change

#NAME? #NAME? #NAME? #NAME? #NAME? #NAME?

#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
Pivot-Style Report with Row & Column Selection INDEX

Select Row
Select Column

Division Region App Profit

Game Asia Arcade 3196 App Total
Game Asia Aviatrr 3573 #NAME?
Game Asia Baden 4904 #NAME?
Game Asia deRamblr 4689 #NAME?
Game Asia Fightrr 10377 #NAME?
Game Asia Twistrr 5657 #NAME?
Game Australia Arcade 11918 #NAME?
Game Australia Aviatrr 8461 #NAME?
Game Australia Baden 939 #NAME?
Game Australia deRamblr 353 #NAME?
Game Australia Fightrr 794 #NAME?
Game Australia Five Labs 1813 #NAME?
Game Australia Hackrr 2056 #NAME?
Game Australia Jellyfish 2661 #NAME?
Game Australia Kryptis 2726 #NAME?
Game Australia Perino 685 #NAME?
Game Australia Pes -383 #NAME?
Game Australia Twistrr 2607 #NAME?
Game Europe Arcade 6858 #NAME?
Game Europe Aviatrr 6422 #NAME?
Game Europe Baden 11500 #NAME?
Game Europe deRamblr 612 #NAME?
Game Europe Fightrr 959 #NAME?
Game Europe Five Labs 1482 #NAME?
Game Europe Hackrr 1390 #NAME?
Game Europe Jellyfish 695 #NAME?
Game Europe Kryptis 603 #NAME?
Game Europe Perino 361 #NAME?
Game Europe Pes 370 #NAME?
Game Europe Twistrr 1807 #NAME?
Game North Ameri Arcade 3270 #NAME?
Game North Ameri Aviatrr 3717 #NAME?
Game North Ameri Baden 4892 #NAME?
Game North Ameri deRamblr 4360 #NAME?
Game North Ameri Fightrr 5227 #NAME?
Game North Ameri Five Labs 317 #NAME?
Game North Ameri Hackrr 563 #NAME?
Game North Ameri Jellyfish 938 #NAME?
Game North Ameri Kryptis 1376 #NAME?
Game North Ameri Perino 1018 #NAME?
Game North Ameri Pes 2860 #NAME?
Game North Ameri Twistrr 1005
Game South Ameri Arcade 3576
Game South Ameri Aviatrr 0
Game South Ameri Baden 0
Game South Ameri deRamblr 0
Game South Ameri Fightrr 672
Game South Ameri Five Labs 1737
Game South Ameri Hackrr 6726
Game South Ameri Jellyfish 0
Game South Ameri Kryptis 0
Game South Ameri Perino 0
Game South Ameri Pes 8429
Game South Ameri Twistrr 7790
Productivity Asia Blend 0
Productivity Asia Dasring 0
Productivity Asia Didactic 0
Productivity Asia Flowrrr 0
Productivity Asia Halotot 2810
Productivity Asia Inkly 2196
Productivity Asia Kind Ape 1318
Productivity Asia Mirrrr 2178
Productivity Asia Pet Feed 0
Productivity Asia Rehire 0
Productivity Asia Right App 0
Productivity Asia Silvrr 0
Productivity Asia Sleops -571
Productivity Asia Voltage 2670
Productivity Asia WenCaL 10012
Productivity Australia Kind Ape 1663
Productivity Australia Mirrrr 3683
Productivity Australia Pet Feed 5405
Productivity Australia Rehire 4242
Productivity Australia Right App 726
Productivity Australia Silvrr 0
Productivity Australia Sleops 0
Productivity Australia Voltage 0
Productivity Australia WenCaL 3865
Productivity Europe Blend 574
Productivity Europe Dasring 1685
Productivity Europe Didactic 1913
Productivity Europe Flowrrr 2696
Productivity Europe Halotot 3552
Productivity Europe Inkly 0
Productivity Europe Kind Ape 0
Productivity Europe Mirrrr 1275
Productivity Europe Pet Feed 1912
Productivity Europe Rehire 2040
Productivity Europe Right App 3388
Productivity Europe Silvrr 3142
Productivity Europe Sleops 273
Productivity Europe Voltage 574
Productivity Europe WenCaL 7870
Productivity North Ameri Blend 1042
Productivity North Ameri Dasring 485
Productivity North Ameri Didactic 505
Productivity North Ameri Flowrrr 533
Productivity North Ameri Halotot 4365
Productivity North Ameri Inkly 718
Productivity North Ameri Kind Ape 417
Productivity North Ameri Mirrrr 1093
Productivity North Ameri Pet Feed 1230
Productivity North Ameri Rehire 4365
Productivity North Ameri Right App 6011
Productivity North Ameri Silvrr 417
Productivity North Ameri Sleops 253
Productivity North Ameri Voltage 2050
Productivity North Ameri WenCaL 984
Productivity South Ameri Blend 878
Productivity South Ameri Dasring 1721
Productivity South Ameri Didactic 7172
Productivity South Ameri Right App 646
Productivity South Ameri Silvrr 0
Productivity South Ameri Sleops 512
Productivity South Ameri Voltage 8047
Productivity South Ameri WenCaL 3716
Utility Australia Accord 527
Utility Australia Amplefio 3242
Utility Australia Atmos 0
Utility Australia Commuta 1300
Utility Australia Infic 431
Utility Australia Minor Liar 479
Utility Australia Misty Wash 0
Utility Australia Mosquit 7489
Utility Australia Motocyco 687
Utility Australia Scrap 260
Utility Australia Strex 410
Utility Australia Tanox 4719
Utility Australia Twenty20 5386
Utility Europe Accord 1888
Utility Europe Amplefio 1726
Utility Europe Atmos 1346
Utility Europe Commuta 3618
Utility Europe Infic 787
Utility Europe Minor Liar 8521
Utility Europe Misty Wash 8197
Utility Europe Mosquit 586
Utility Europe Motocyco 341
Utility Europe Scrap 350
Utility Europe Strex 0
Utility Europe Tanox 918
Utility Europe Twenty20 1726
Utility North Ameri Accord 0
Utility North Ameri Amplefio 0
Utility North Ameri Atmos 1348
Utility North Ameri Commuta 1414
Utility North Ameri Infic 902
Utility North Ameri Minor Liar 6612
Utility North Ameri Misty Wash 0
Utility North Ameri Mosquit 566
Utility North Ameri Motocyco 8729
Utility North Ameri Scrap 566
Utility North Ameri Strex 9812
Utility North Ameri Tanox 681
Utility North Ameri Twenty20 1702
Utility South Ameri Accord 0
Utility South Ameri Amplefio 0
Utility South Ameri Atmos 0
Utility South Ameri Commuta 2759
Utility South Ameri Infic 5082
Utility South Ameri Minor Liar 0
Utility South Ameri Misty Wash 1530
Utility South Ameri Twenty20 4476
 TRY THIS - Change the selection and
App see how the report adjusts. Conditional formatting has
Region been applied to get
automatic borders.
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? Click HERE to find out how
#NAME? #NAME? #NAME? #NAME? #NAME? INDEX & MATCH works.
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
al formatting has
ed to get
borders.

o find out how

TCH works.
Monthly Interest Paid with SEQUENCE INDEX
Data validation rule has been
applied to ensure min year=1
Input year 5 and max=10
Rate 6.50%
Value 150,000
Total Interest Paid #NAME? Monthly payment $2,934.92

Month / Years #NAME? #NAME? #NAME? #NAME? #NAME?

#NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
Loan Amortization Table (Interest, Principal) with SEQUENCE INDEX
Data validation rule has been
applied to ensure min year=1
Input year 2 and max=10
Rate 5.52%
Value 80,000 Interest & Principal amounts show
how the monthly payments are
Monthly payment $3,528.37 allocated each month.

Month # Interest Principal Balance

80,000.00
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
 By using the PV() function in Excel, we can get
the Balance to spill as well. This way we don't
have to use the IF function.
Thanks to Donald Robert Steele for the tip!

Month # Present Value Interest Principal Balance

#NAME? #NAME? #NAME? #NAME? #NAME?

#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME?
Dynamic Calendar with SEQUENCE Function INDEX Date List
Aug-24
Sep-24
August '20 Oct-24
S M T W T F S Nov-24
### ### ### ### ### ### ###
TRY THIS - Select another Dec-24
month from the drop-down.
### ### ### ### ### ### ### Jan-25
### ### ### ### ### ### ### NOTE - This was updated to Feb-25
### ### ### ### ### ### ### spill to 6 rows to account for Mar-25
### ### ### ### ### ### ### months such as August 2020 Apr-25
### ### ### ### ### ### ### May-25
Jun-25
Weekday function is Jul-25
adjusted to find which Aug-25
date applies to
Monday. Sep-25
Oct-25
August '20 Nov-25
M T W T F S S Dec-25
Conditional Formatting
### ### ### ### ### ### ### is used to grey out the Jan-26
### ### ### ### ### ### ### dates that don't belong Feb-26
### ### ### ### ### ### ### to the selected month. Mar-26
### ### ### ### ### ### ###
### ### ### ### ### ### ###
### ### ### ### ### ### ###
End of month function
EOMONTH is used to get a list
of the dates representing the
1st of the month. This is used
for the month drop-down
Shrinking Data Validation - Hide Used Items INDEX

Select Employee Division

Andre Cooper Utility
Ann Withers Productivity The list for employees and
divisions can be found in tab
SourceList1.

TRY THIS - A name appears only once. If

it's been previously selected, it doesn't
show in the drop-down list.
Source Data for Shrinking Data Validation
Data Preparation for Shrinking Data Validation (Tab SourceList)

Employee List
Name Division Count Used Names
Gary Miller Utility 0
James Willard Productivity 0
Richard Elliot Productivity 0
Robert Spear Productivity 0
Roger Mun Productivity 0
Paul Garza Productivity 0
Robert Marquez Productivity 0
Natalie Porter Productivity 0
Kim West Utility 0
Stevie Bridge Game 0
Andre Cooper Utility 1
Crystal Doyle Productivity 0
Robert Musser Productivity 0
Daniel Garrett Productivity 0
Ann Withers Productivity 1
Paul Hill Game 0
Corinna Schmidt Game 0
Ewan Thompson Game 0
Walter Miller Utility 0
Paul Wells Game 0
Betina Bauer Game 0
Daniela Schreiber Utility 0
Dan Ziegler Utility 0
Peter Ramsy Game 0
Wolfgang Ramjac Game 0
Robert Richardson Utility 0
Brigitte Bond Utility 0
Robert Blume Utility 0
Mike Saban Utility 0
Maria Tot Game 0
Lukas Hofer Game 0
0
0
0
0
0
0
0
0
0
0
 INDEX

Filter for 0 AND not empty Filter for Remaining names

#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
Searchable Data Validation List

My Name
My address Line 1
My address Line 2
My address Line 3

Invoice 15076
Date 8/7/2024
Payment terms 14 days net

WenCaL US
Gary Miller

TRY THIS - Type part of a name for

example "robert" - then click on the drop-
down to see names that include "robert".

Price in
Services Time Period VAT in %
EUR

Subtotal -
VAT -
Total -

Bank Info
Bank Name
IBAN: 2342456435
BIC: GIBARESCXX
 INDEX

a name for
n click on the drop-
t include "robert".
Source Data for Searchable Data Validation
Data Preparation for Searchable Data Validation (Tab SeachableDataVal)

Customer List
Name Company Search Name for text Match
Gary Miller WenCaL US #VALUE!
James Willard Blend #VALUE!
Richard Elliot Voltage #VALUE!
Robert Spear Inkly #VALUE!
Roger Mun Sleops #VALUE!
Paul Garza Kind Ape #VALUE!
Robert Marquez Pet Feed #VALUE!
Natalie Porter Right App Learning #VALUE!
Kim West Right App Play #VALUE!
Stevie Bridge Hackrr #VALUE!
Andre Cooper Perino Corp #VALUE!
Crystal Doyle Silvrr #VALUE!
Robert Musser Dasring #VALUE!
Daniel Garrett Rehire #VALUE!
Ann Withers Didactic #VALUE!
Paul Hill Fightrr #VALUE!
Corinna Schmidt Kryptis #VALUE!
Ewan Thompson Perino #VALUE!
Walter Miller Twistrr Clothes #VALUE!
Paul Wells Hackrr #VALUE!
Betina Bauer Pes #VALUE!
Daniela Schreiber Baden Paper #VALUE!
Dan Ziegler Baden Packaging #VALUE!
Peter Ramsy deRamblr #VALUE!
Wolfgang Ramjac Arcade #VALUE!
Robert Richardson WenCaL UK #VALUE!
Brigitte Bond Twistrr Productivity #VALUE!
Robert Blume Twistrr Games #VALUE!
Mike Saban Twistrr Utility #VALUE!
Maria Tot Twistrr Kids #VALUE!
Lukas Hofer Kryptis #VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
 INDEX

Complete Formula - FILTER

#NAME?
Challenge: Interactive KPI Selection Report

1 Create a drop-down list for Category to include Division, Region & APP (Reference A10 to C10)
Create a drop-down list for KPI to include Sales and Profit.
2 Create a dynamic report which shows sorted, unique values for the Category selected
and total values by category for the KPI selected.
3 Format the report to include light grey borders (Should only apply to cells with values)

Division Region App Sales Profit

Game Asia Arcade 4378 3196
Game Asia Aviatrr 5254 3573
Game Asia Baden 6130 4904
Game Asia deRamblr 7443 4689
Game Asia Fightrr 13135 10377
Game Asia Twistrr 7443 5657
Game Australia Arcade 15890 11918
Game Australia Aviatrr 11917 8461
Game Australia Baden 1490 939
Game Australia deRamblr 497 353
Game Australia Fightrr 993 794
Game Australia Five Labs 2483 1813
Game Australia Hackrr 2979 2056
Game Australia Jellyfish 3972 2661
Game Australia Kryptis 4469 2726
Game Australia Perino 993 685
Game Australia Pes 497 -383
Game Australia Twistrr 3476 2607
Game Europe Arcade 9267 6858
Game Europe Aviatrr 9731 6422
Game Europe Baden 15754 11500
Game Europe deRamblr 927 612
Game Europe Fightrr 1390 959
Game Europe Five Labs 1853 1482
Game Europe Hackrr 2317 1390
Game Europe Jellyfish 927 695
Game Europe Kryptis 927 603
Game Europe Perino 463 361
Game Europe Pes 463 370
Game Europe Twistrr 2317 1807
Game North America Arcade 5361 3270
Game North America Aviatrr 5808 3717
Game North America Baden 6701 4892
Game North America deRamblr 7148 4360
Game North America Fightrr 8042 5227
Game North America Five Labs 447 317
Game North America Hackrr 894 563
Game North America Jellyfish 1340 938
Game North America Kryptis 1787 1376
Game North America Perino 1340 1018
Game North America Pes 4468 2860
Game North America Twistrr 1340 1005
Game South America Arcade 5108 3576
Game South America Aviatrr 0 0
Game South America Baden 0 0
Game South America deRamblr 0 0
Game South America Fightrr 851 672
Game South America Five Labs 2554 1737
Game South America Hackrr 8514 6726
Game South America Jellyfish 0 0
Game South America Kryptis 0 0
Game South America Perino 0 0
Game South America Pes 12771 8429
Game South America Twistrr 12771 7790
Productivity Asia Blend 0 0
Productivity Asia Dasring 0 0
Productivity Asia Didactic 0 0
Productivity Asia Flowrrr 0 0
Productivity Asia Halotot 3513 2810
Productivity Asia Inkly 2928 2196
Productivity Asia Kind Ape 1757 1318
Productivity Asia Mirrrr 3513 2178
Productivity Asia Pet Feed 0 0
Productivity Asia Rehire 0 0
Productivity Asia Right App 0 0
Productivity Asia Silvrr 0 0
Productivity Asia Sleops 878 -571
Productivity Asia Voltage 3513 2670
Productivity Asia WenCaL 13174 10012
Productivity Australia Kind Ape 2342 1663
Productivity Australia Mirrrr 4977 3683
Productivity Australia Pet Feed 7612 5405
Productivity Australia Rehire 6734 4242
Productivity Australia Right App 1171 726
Productivity Australia Silvrr 0 0
Productivity Australia Sleops 0 0
Productivity Australia Voltage 0 0
Productivity Australia WenCaL 6441 3865
Productivity Europe Blend 911 574
Productivity Europe Dasring 2277 1685
Productivity Europe Didactic 3188 1913
Productivity Europe Flowrrr 3643 2696
Productivity Europe Halotot 5465 3552
Productivity Europe Inkly 0 0
Productivity Europe Kind Ape 0 0
Productivity Europe Mirrrr 1822 1275
Productivity Europe Pet Feed 2732 1912
Productivity Europe Rehire 3188 2040
Productivity Europe Right App 5465 3388
Productivity Europe Silvrr 4554 3142
Productivity Europe Sleops 455 273
Productivity Europe Voltage 911 574
Productivity Europe WenCaL 10930 7870
Productivity North America Blend 1708 1042
Productivity North America Dasring 683 485
Productivity North America Didactic 683 505
Productivity North America Flowrrr 683 533
Productivity North America Halotot 6148 4365
Productivity North America Inkly 1025 718
Productivity North America Kind Ape 683 417
Productivity North America Mirrrr 1366 1093
Productivity North America Pet Feed 1708 1230
Productivity North America Rehire 6148 4365
Productivity North America Right App 7514 6011
Productivity North America Silvrr 683 417
Productivity North America Sleops 342 253
Productivity North America Voltage 3416 2050
Productivity North America WenCaL 1366 984
Productivity South America Blend 1220 878
Productivity South America Dasring 2732 1721
Productivity South America Didactic 9563 7172
Productivity South America Right App 1025 646
Productivity South America Silvrr 0 0
Productivity South America Sleops 683 512
Productivity South America Voltage 12979 8047
Productivity South America WenCaL 5465 3716
Utility Australia Accord 684 527
Utility Australia Amplefio 4104 3242
Utility Australia Atmos 0 0
Utility Australia Commuta 1710 1300
Utility Australia Infic 684 431
Utility Australia Minor Liar 684 479
Utility Australia Misty Was 0 0
Utility Australia Mosquit 10259 7489
Utility Australia Motocyco 1026 687
Utility Australia Scrap 342 260
Utility Australia Strex 684 410
Utility Australia Tanox 6839 4719
Utility Australia Twenty20 7181 5386
Utility Europe Accord 2622 1888
Utility Europe Amplefio 2185 1726
Utility Europe Atmos 1748 1346
Utility Europe Commuta 5243 3618
Utility Europe Infic 1311 787
Utility Europe Minor Liar 13109 8521
Utility Europe Misty Was 12235 8197
Utility Europe Mosquit 874 586
Utility Europe Motocyco 437 341
Utility Europe Scrap 437 350
Utility Europe Strex 0 0
Utility Europe Tanox 1311 918
Utility Europe Twenty20 2185 1726
Utility North America Accord 0 0
Utility North America Amplefio 0 0
Utility North America Atmos 2210 1348
Utility North America Commuta 1768 1414
Utility North America Infic 1326 902
Utility North America Minor Liar 9723 6612
Utility North America Misty Was 0 0
Utility North America Mosquit 884 566
Utility North America Motocyco 11049 8729
Utility North America Scrap 884 566
Utility North America Strex 13259 9812
Utility North America Tanox 884 681
Utility North America Twenty20 2210 1702
Utility South America Accord 0 0
Utility South America Amplefio 0 0
Utility South America Atmos 0 0
Utility South America Commuta 4598 2759
Utility South America Infic 6687 5082
Utility South America Minor Liar 0 0
Utility South America Misty Was 2508 1530
Utility South America Twenty20 7105 4476
 INDEX TRUE
TRUE
Region & APP (Reference A10 to C10) ✘ 1. Done
TRUE
✘ 2. Done
es for the Category selected WELL DONE!
✘ 3. Done

ly apply to cells with values)

Add drop-down lists here

Select Category Region
Select KPI Sales

Region Sales
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME? Report to Go Here - double-check your
results. Hint take a look at lecture "Pivot-Style
#NAME? #NAME? Report with Row & Column Selection" if you
get stuck.
uble-check your
t lecture "Pivot-Style
mn Selection" if you