Best way to arrange multi-level data in Excel?

[Dan Lewis]

Hi,

I'm going to have difficulty explaining this but I'll give it my best shot! I have attached an example spreadsheet to help with my explanation.

I have a large amount of data on different company's performance indicators that I need to arrange in Excel.

Firstly see sheet "BrokenDown".

Each company (Col A) operates in multiple regions (Col B) and will have a number of performance indicators (Col C) that we track. Each indicator has to be verified and a final figure is decided (Col D), and these stats need to be logged on a yearly basis.

The "BrokenDown" sheet shows the best way I know how to divide the data, but it is on so many levels the spreadsheet would be huge, and I am worried that with the data in this format, it will be difficult to analyse using Excel. We will want to be able to compare and graph the performance indicators for all companies, for example look at the turnover for all of the companies by region in one go!

I created a second sheet "Table". My understanding is that arranging the data like this would make it easier to analyse? But to an end user inputting data, it is quite confusing.

My question (finally!) is does anyone have any experience with organising this sort of multi-level data, and what is the normal way to do it. I need it to be easy for somebody to input new data each year.

Thanks if you made it this far

 

[Somendra Misra]

Hi Dan Lewis,

Welcome to Chandoo.org forum.

Personally I feel, you should stick to Table format. There is an inbuilt feature in excel Form(Excel 2010), you can select form by going to customize ribbon in option Select All Command in Choose Common from, now scroll in the list to find "Form", now you can add this in your quick access toolbar.

How to activate it: Select any cell inside table and press it, it will display you an input entry form.

This way the end user will not be confused.

See attached file for this method

 

[Smallman]

Hi Dan

In my opinion the Table is set out perfectly.



The data above really is not confusing. You have Reported, Verified and Final for each of the Company, Year, Indicator combinations. This will roll into a pivot table nicely and you will be able to slice and analyse the above dataset and produce clean graphical comparisons.

Pardon the pun but the broken down tab was very much 'broken down'. Very difficult to report against metrics when each row does not have all the data against it.

I think you are on the right track with the table.

Take care

Smallman

 

[Dan Lewis]

Thanks guys that's really helpful, will persist with the tables then!

Another question, they want info for each company, such as 'Project Manager', 'Sector', 'Activity' etc.
Where would I store this information? Would that be in a separate table?

I'm used to databases, finding it difficult to visualise how to organise data just on a spreadsheet!

Thanks again,
Dan

 

[Debraj]

I'm used to databases,

Hi Dan,

If you are used to databases, then I guess.. you also have good knowledge on Front End & Back End
Dont mix them..
Your back end should always be like sheet "Table", where your Front End, may be like Sheet "Broken Down".

 

[NARAYANK991]

Hi Dan ,

You can use the same principles of database structuring in Excel too ; for instance the Table tab cannot be normalized any further , since every row of data will need to have the fields it has at present. This is assuming that you do not have a field such a company+region code , in which case the 2 fields company and region can be replaced by a code ; in this case you will need to set up a lookup table having the company name , the region and its associated code. I cannot say anything about the Amount column , since I do not know what relationship it has to the other fields ; is it the amount that was spent on the indicator ?

More than that however , is the issue of what kind of report you can generate from this data ?

Do the years represent the year in which the activity was carried out , or the year in which it was / is scheduled to be carried out ? What value is added by putting in 1 year alone ? You can easily have it as either Done or Not Done instead. The year makes more sense if it is to be used to calculate the promptness / tardiness with which the testing was done , in which case two years will be needed , the target and the actual. Having the 1 year figure does not allow you to do any comparisons ; after all what can be concluded from the fact that Company A region France did something in 2012 , while region Germany of the same company did it in 2013 ?

I would suggest that you do it the other way round ; start off by listing down all the reports that you need to generate ; from that , you can see what kind of data will be needed to generate those reports ; if you have this data at present , that will be nice , otherwise you will need to see how that data can be gathered. The last stage is that of how to put that data into an Excel workbook.

Narayan