A Technique for Analysing Large Tables of Data

Posted on November 4th, 2010 in Charts and Graphs , Excel Howtos , Learn Excel , Posts by Hui - 21 comments

Once you start using Excel to develop systems, budget, forecast and large tables of data you may come across the dilemma of “How do I know this is right” or “How do I truth check this”.

This post, Huis second, will add a tool to your arsenal to help you out.

The technique below allows for the rapid visual evaluation of 12 or more days/months/years of data via a chart of 2 variables and the ratio between them, utilising 2 sliders to rapidly change input variables.

DATA

Typically all businesses deal in 3 types of numbers, ie: Dates or Times, Physicals and Dollars.

Dates may include Times, Days, Weeks, Months, Years or any periods in between

Physicals directly relate to the business you are in but may include inputs and or outputs: eg: metal, tyres and cars, sugar, flour and cakes or whatever your business makes, strangely Physicals may also include Dollars, if you’re in the financial services sector. People are a Physical.

And Dollars which could be revenue, costs, cashflows or profit types of numbers.

I have found one of the best methods of looking at large tables of data is to look at a key input or output physical and then interactively scroll through the cost or income and calculate a ratio between the two.

I am going to use as an example a budget which has 40 rows of data including Physicals and Costs. But you will see that the techniques here can easily be extended to hundreds of rows. You can download this file here: Bobs Homes

The technique below allows rapid visual evaluation of 12 or more months data a chart of 2 variables and the ratio between them.

Using 2 sliders the user can rapidly move between variables and evaluate the variance over a given time frame against another variable.

The technique involves the charting of 3 ranges which in turn will be controlled by 2 sliders.

The first Range and slider will allow the selection of a Physical.

The second Range and slider will allow the selection of the Cost/Income component.

The third range will be the ratio of these, but specifically cost or income per physical.

I have attached a workbook, Bobs Homes, which contains 2 models, an example model for you to practice the following techniques on and a completed model which you can examine and pull apart to see how it works.

Bobs Homes

Load the model and scroll around and see what data is available. You will notice a time scale across the top with Physicals and Dollars down the left side. The Physicals and Dollars are grouped into common areas like inputs, outputs and costs, income and profit.

You will also note that there are 10 blank rows at the top of the worksheet. I do this so that I can perform simple calculations, charts or other workings without upsetting the data and other calculations below and for macro’s which automatically find the bottom of the data I know there is nothing below my data to upset the calculations.

Let’s jump in

SETUP

We’re going to add the following components

Headings

A2: Physical

A3: Cost/Revenue

A4: Ratio

E1: =E12

and copy across to Q1

Data

C2: 1

C3: 1

D2: =CONCATENATE(OFFSET(B$13,$C$2,0),OFFSET(C$13,$C$2,0))

D3: =CONCATENATE(OFFSET(B$22,$C$3,0),OFFSET(C$22,$C$3,0))

D4: =CONCATENATE(D3,”/”,D2)

E2: =OFFSET(E$13,$C$2,0)

E3: =OFFSET(E$22,$C$3,0)/1000

Copy E2:E4 across to Q2:Q4

Chart

Select Area D1:Q4

Insert Chart, Line Chart with or without markers to your liking

Suggestion – Place the Chart between the Data and the new Headings and formulas you have just added

Adjust the Legend to be at the Bottom of the chart

Sliders

Insert 2 Sliders

Developer Tab, Insert Scroll Bar (Form Control)

If you don’t have the Developer Tab, Have a read of: http://chandoo.org/wp/2009/05/26/excel-2007-productivity-tips/

Position the scroll bars so they are vertically next to the Chart, Use Alt whilst dragging to snap to Cell corners or edges

Link the sliders to the lookup cells

Slider 1
Current value: 1
Minimum: 1
Maximum: 7 (This is the number of Rows of Physicals data)
Incremental Change: 1
Page Change: 0
Cell Link: $C$2

Slider 2
Current value: 1
Minimum: 1
Maximum: 19  (This is the number of Rows of Cost/Revenue data)
Incremental Change: 1
Page Change: 0 (set this to maybe 10 if you have more than 30/40 rows)
Cell Link: $C$3

FINAL MODEL

You can now select a Physical by dragging the Left Scroll bar

You can now select a Revenue/Cost by dragging the Right Scroll bar

The Ratio of Cost/Revenue to the Physical is calculated and the 3 are all charted

Examine the model and see what variances in inputs/outputs can be seen.

HOW DOES THIS WORK?

The Formula in E2:Q3, are extracting the physicals and Cost/revenue data from the main body of the report by simply using an Offset function from the top of the Physicals and Cost/Revenue area.

The Distance they offset is retrieved from the control Cells C2:C3

The labels for the Physicals and Costs/Revenues are also retrieved using 2 Offsets inside a Concatenate. This is done to allow Heading Rows and Sub Headings to be displayed and joined if available from 2 separate columns.

The Chart is a simple Line Chart which is charting the 3 Data Rows (E2:Q4) against the Time Period (E1:Q1) at the top of the work area.

You can customise the chart to your content.

The 2 sliders control the control Cells C2:C3, and allow for interactive selection of Physicals and costs.

In use often you will find that one of the Physicals, Costs/Revenue or Ratio is generally much smaller in scale than the other 2 measures. Generally it is a good idea to plot the odd scale against a secondary Y axis.

Select the series line, Right Click and select Format Data Series

FUNCTIONS USED:

Offset: http://chandoo.org/wp/2008/11/19/vlookup-match-and-offset-explained-in-plain-english-spreadcheats/

Concatenate: http://chandoo.org/excel-formulas/concatenate.html

How do you truth check your data? Let us all know in the comments below:

How are you finding the content level of my posts? Let me know in the comments below:

Next Thursday – Selecting Individual Data Points in Charts and More Sliders

Written by Hui...
Tags: , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

21 Responses to “A Technique for Analysing Large Tables of Data”

  1. RT says:

    Thanks for this Huis. It's very useful! Just a question - would it not be better to separate the data sheet from the graphical visualisation?

  2. Steve Sexton says:

    These two lines are in error:
    E2: =OFFSET(E$13,$D$2,0)
    E3: =OFFSET(E$22,$D$3,0)/1000

    They should read:
    E2: =OFFSET(E$13,$C$2,0)
    E3: =OFFSET(E$22,$C$3,0)/1000

  3. Hui... says:

    @Steve
    Good pickup, it has been fixed above.

    @RT
    For production style systems you are right, but for quick and nasty one of's and demonstration I prefer to have the charts near the data. You can actually do both, with a second series of charts and controls on another worksheet, but accessing the same data and summaries above the data

  4. Stacey says:

    Is there a Developer Tab or say to create sliders in Excel 2003?

  5. RB says:

    Very interesting set up. Any chance a sample file would be available for download?

  6. RB says:

    Nevermind. Just caught the link up at the top.

  7. Hui... says:

    @Stacey
    Goto View, Toolbars, Control Toolbox
    and select the Slider control

    or right click on a Menu Bar at the top and select the Control Toolbar

  8. V S VENKATRAMAN says:

    Dear Chandoo

    Wishing you and your family a VERY VERY HAPPY DIWALI.... MAY GOD BLESS YOU WITH EVERYTHING YOU AND YOUR FAMILY DESIRE......

    Regards

    V S Venkatraman

  9. Stacey says:

    Hui, many thanks!

  10. Vijay says:

    Hi Chandoo,

    I did not find the need the need for offset and concatenate for the first two rows (physicals & cost). I deleted the concat function and only included the offset and it sounds good enough.

    Regards,

    Vijay

  11. Hemant says:

    Hi Chandoo

    Can we convert excel files into html & share it on our website. 😉

  12. Lorin Rivers says:

    I am so thrilled that Office for Mac finally has VBA support again! This works great, except for the formula for D4. It looks like something auto-corrected your double-quotes to curly ones.

  13. Hui... says:

    @Vijay, These posts are for educational and example purposes only. You are free to take the bits you need and leave the bits you don't.

  14. A nice modification for this very helpful sheet might be to use list-boxes instead of slider (scroll) bars. That way, the user can see the option selected as well as the group from which it was selected. I found this one little change pretty helpful in terms of ease-of use. It also might be helpful to add labels to the slider/lists. Thanks for the post. This is a very useful sheet/technique!

  15. [...] A Technique for Analysing Large Tables of Data | Chandoo.org – Learn Microsoft Excel Online Once you start using Excel to develop systems, budget, forecast and large tables of data you may come across the dilemma of “How do I know this is right” or “How do I truth check this”. This post, Huis second, will add a tool to your arsenal to help you out. (tags: excel data processing dataset) Comments (0) [...]

  16. garyk says:

    just a question: what's the benefit of using concatenate?
    D3: =CONCATENATE(OFFSET(B$22,$C$3,0),OFFSET(C$22,$C$3,0))
    D4: =CONCATENATE(D3,”/”,D2)
    instead of
    D3=OFFSET(B$22,$C$3,0)&OFFSET(C$22,$C$3,0)
    D4 =D3&"/"&D2

  17. Hui... says:

    @Noah
    You can use list boxes, but they are so very slow in use when you want to evaluate hundreds of rows of data. In use you often just want to skim through variables so you can see that the key measures are correct and there are no aberrations.
    .
    @Garyk
    Concatenate is a Function, & is an operator
    Technically it may be slightly faster to use & instead of Concatenate, but i've never tested it.
    .
    In use I find that If I see Concatenate I know the formula is joining some text together where as If I don't see the Concatenate I have to pull the formula apart to work out what is going on.
    .
    EG: compare the following 2 equations
    =Concatenate([Mercury1.xlsm]Sheet1!$G$9,[Mercury2.xlsm]Sheet2!$I$6)
    =[Mercury1.xlsm]Sheet1!$G$9&[Mercury2.xlsm]Sheet2!$I$6

  18. garyk says:

    i can see your point about knowing what to expect when you see concatenate, but i've mostly used to & because i have to type less.
    this is a little more obvious when you add spaces:

    =[Mercury1.xlsm]Sheet1!$G$9 & [Mercury2.xlsm]Sheet2!$I$6

  19. rajranja says:

    Yahoooo!!! i finally learnt how to use Scroll bars in Graphs... itss too good. 🙂

  20. rajranja says:

    Yahoooo!!! i finally learnt how to use Scroll bars in Graphs without using offset function(Jugad Techonologies)... its too good. 🙂

Leave a Reply