Drill Down to More Detailed Charts using Excel Lookups and Xcelsius Chart Drill Down

In this Post Jim Brogden explains how to link multiple charts to a single drill down using Excel Lookups and Xcelsius 2008 chart drill down feature.

Use the Drill Down feature in combination with Excel logic to pass information from one chart to many others.

Its Interactive


Click here to see the Blown up version.

1. The first step is to either add a Column Chart to an existing Xcelsius model or edit an existing one, set the values and labels to populate the chart then select the Drill Down tab.

2. Check the ‘Enable Drill Down’ box and select Row as the Insertion Type.  The Insertion Types available when enabling the Drill Down feature on a Column Chart are the following: Position, Value, Row, Column, Status List. The Position type returns the position in the order where the selected bar appears in the list. The Value type returns the value of the selected bar.  This example will be using the Row type, which will return the label of the selected bar.

3. After selecting Row as the Insertion Type, point to the Source Data (the same as the charts Labels), and choose a Destination cell.  This is an important cell because it will be used to identify values in the additional charts.  Now that the drill-down selection is in the destination cell (B2 in this example), it will be used by a series of simple HLOOKUP formulas to identify the data associated with it to display in additional charts.

4. Cell K3 receives the dynamic value placed in B2 and will be the new charts’ heading. The cell with the first dynamic value is K4.  This value is obtained through HLOOKUP by using the value in B2 and looking through the “detailed data” for a match.  Once the match is found, the value in the second row is displayed.  Here is the formula:  =HLOOKUP($B$2,$E$3:$I$8,2,FALSE)

Utilize the Excel HLOOKUP formula

Utilize the Excel HLOOKUP formula

5. A second example is used that is very similar to the one above but the value is passed to cell K13 and additional HLOOKUPS are used to identify the correct values associated with K13. These values appear in cells K14:K18 and are used to create the pie chart.

6. The main chart can set a Default Selection (on the Drill Down tab in properties) which will be the subsequent drill-down value picked up by the additional charts.

Drilling Down from Dollars by State

Drilling Down from Dollars by State

Feel free to send me an email if you have any questions.  Thank you.

  • SK

    Hello,

    Do you have the Xcelsius .xlf file for the drilldown. I am having with my drilldown data. Please see the data below.
    Eg:
    Click on DEF Row and it should show up Table 2.
    Table 1
    PA OU AB CD %
    ABC Test1 6000 6200 96.77%
    DEF Test2 200 250 80.00%
    GHI Test 3 200 300 66.67%
    JKL Test4 900 1015 88.67%
    LMN Test5 1500 1931 77.68%
    OPQ Test6 200 231 86.58%
    RST Test7 2000 2500 80.00%

    Table 2
    PA OU AB CD %
    DEF HOU1 5 5 100.00%
    DEF HOU2 15 20 75.00%
    DEF HOU3 10 7 142.86%
    DEF HOU4 20 30 66.67%
    DEF HOU5 20 26 76.92%
    DEF HOU6 20 15 133.33%
    DEF HOU7 30 60 50.00%
    DEF HOU8 40 45 88.89%
    DEF HOU9 20 25 80.00%
    DEF HOU10 10 15 66.67%
    DEF HOU11 30 35 85.71%
    190 248 76.61%

    I am having hard time to get this work. Can you please give your input.

  • s1

    hi, Jim

    Very nice & interactive dashboard design, can upload source xcelius.xlf file regarding this drill down chart

    thanks

    regards,
    s1

  • Ben

    Hello,

    I feel the example and design is good but it is not explained properly.
    I have the following doubts. I understand first drill down is just selecting
    the name of the state and putting it in B2. Then how the other stuff is working. I placed HLookup formula in K4 and once state in B2 changes it changes the value accordingly. My question is how the correct values of Food, Gas as per the state is populated for the drill down.

    BR,
    Beenish

  • Devendra

    Thanks 🙂

  • Abc

    nice……….

  • sunil

    Hi Jim…

    Its fabulous concept….

    i think it would be very useful for me…

    I did try to workout this concept at my end… unfortunately its not working for me…if possible could you plz share your excel sheet to my email (suniacad@gmail.com)…so that i can try to replicate the same for my requirement…

    anticipating yor reply..

    thanking you… 

  • Sushmithabe

    Hi i need code for this please send immediately… sushmithabe@gmail.com

  • Chirag.Patel22285

    Awesome concept…. I am Impressed…. Please email to me as well at chirag.patel22285@yahoo.com

  • Vijay

    Extremely useful for beginners.. was banging my head for this solution.. please email the xcelsius and excel sheet to vijay.aadithya@hotmail.com

  • joseph

    Hi ,

    I am having issues in drill-down upto 3 level in column chart in Xcelsius 2008.
    I have got following data and i want that in first column with summation of annual & potential fees by category and when i click on category then it should take me second column chart showing me summation of annual & potential fees by sub-category and when i click on the subvategory then it should take me third column chart showing description.I tried but could not get desired results and checked many post on internet but have yet not found anything substantial.

    Please help..

    Thanks
    Joseph

    CategoriesSub CategoriesDescriptionAnnual FeesPotential Fees11Domestic Custody Tier 1 $ 305,047 $ 169,471 11Domestic Custody Tier 2 (Cash) $ 6,127 $ 2,451 12International Custody $ 2,388,829 $ 972,933 23Domestic Transactions Equities – STP $ 95,670 $ 44,646 23Domestic Transactions – Unitised $ 2,125 $ 1,275 23Domestic Transactions – Fixed Income $ 9,060 $ 9,060 23Domestic Transaction Cancellations $ 810 $ 810 23Global Transaction Cancellations $ 20,820 $ 13,880 23Cash $ 39,160 $ 19,580 23FFX $ 23,960 $ 17,970 23Income Collection $ 106,680 $ 106,680 23Domestic Derivatives $ 33,930 $ 22,620 23Corporate Action $ 14,440 $ 14,440 24International Transactions $ 598,865 $ 250,411 35Portfolio Establishment $ – $ – 35Accounting – Direct $ 102,000 $ 51,000 35Accounting – Unitised 35Taxation (qtly) – Direct $ – 35Taxation (qtly) – Unitised 35Taxation (Mnthly) – Direct $ – $ – 35Taxation (Mnthly) – Unitised 35Board Rptg $ 500 $ – 35AASB7 $ 1,500 $ – 35APRA Reporting $ 63,000 $ – 3545 Day Rule $ 16,000 $ – 35TOFA $ – $ – 35Performance (Direct) – Monthly $ – $ – 35Compliance Monitoring – Direct $ 99,000 $ – 35Compliance Monitoring – Unitised $ – 35GST Reporting – Direct $ – $ – 35GST Reporting – Unitised $ – $ – 35Unit Pricing – Options $ 60,000 $ – 35Unit Pricing – Asset Class/Sector Pool $ 72,500 $ – 35Unit Pricing – Direct $ 200,000 $ – 35Unit Pricing – Unitised $ 120,000 $ – 35Corp Governance Fee $ – $ – 35Asset Class Allocation $ – $ – 35Attribution $ 6,000 $ 6,000 35Statistical Analysis $ 8,400 $ 8,400 35Derivatives Fees $ – $ – 46Registry Maintenance $ – $ – 46Fund Maintenance – Ell Special Opps Platform $ – 47Registry Transactions – Manual $ – $ – 47Registry Transactions – Electronic $ – $ – 48Reporting – KYC $ – $ –

  • Fia

    Hi Jim,

    I am very new in creating Xcelsius dashboard.
    Would you mind to share the xlf file, Jim?

    Thank you.
    Regards,
    Fia

  • Aicrag Franco Ephraim

    please email the xcelsius and excel sheet to SHOKER.EG1@GMAIL.COM THNKS

  • Fernando Romero

    Please email the Xcelsius and Excel sheet to fernandito75@gmail.com – Thank you!