Multiple variables

[Zahra123]

Hi all,

I am a historian and I have very little experience with Excel. I am assuming this is a silly question, but I would like to have three sets of variables in my data. I.e. I want to look at which textbooks were read at different schools over a 20 year period of time. So I have around 70 textbooks, 23 institutions, and the textbooks used at each institution each year. So I would like to produce a graph or table that lets me show which textbooks were more popular at which points in time, and in which institutions. I would greatly appreciate any help!

Best wishes,

Zahra

[pjwhitfield]

Do you have a workbook with the data in? If so, can you post it here? or if you're precious about the data, create a sample sheet with some pretend versions of the data in.

[Pepe Le Mokko]

At first glance you might want to use a Pivot Table. Basics are explained at http://www.contextures.com/CreatePivotTable.html ( and other places of course)

[Zahra123]

Hi, I'm not precious about my data! I just don't even have it in a workbook properly because I don't know how to arrange it. Right now it's in this kind of form (I just started putting it in this way and know that even this might be flawed):

1830 1831 1832
Institution
Calcutta Madrasa Bedil
Calcutta Madrasa Tughra
Calcutta Madrasa Akhlaq-I Nasiri
Calcutta Madrasa Akhlaq-I Jalali
Allahabad Free School Akhlaq-I Muhsinin
Allahabad Free School Insha-I Harkaran
Allahabad Free School Sikandarnama
Allahabad Free School Meena Bazar
Allahabad Free School Zuhuri
Allahabad Free School Urfi
Allahabad Free School Tughra


As you can see, since everything varies each year, I have started putting in the data, repeating each institution so that I can write in each textbook (such as Zuhuri, Bedil) separately.

Thank you!

[Zahra123]

The data is in a table form and that didn't paste properly. But The institutions are under institution, while the years run across the table, and so in the table above 'Bedil' 'Tughra' etc are under '1831'

[pjwhitfield]

Fist things first, is the data you want in a workbook hard copy or electronic? if the latter then we may be able to get it into Excel without much fuss but thats an aside to your question.

Apologies, Im still having problems visualising the data you have even with the explanation as you have 4 heading "1830","1831","1832","Institution" but then the 1st row has 3 items of data but your explanation seems to indicate that the 3rd item "Bedil" is in the 2nd column "1831"

Could you make that table into a very basic spreadsheet, by just typing the data into the relevant cells and then upload that in a post?

[Zahra123]

Ok thank you for your time! I have got this so far. Am still filling in the data. The years go across the table, while Institutions go down. The texts are in the middle.

Textbooks.xlsx

[pjwhitfield]

What Id say, to start with is that if you're just looking for a way to store the data then the best way for charting, data analysis etc would be to change it to a simple 3 column sheet showing (in no particular order)

Institution
Text
Year

Ive amended your workbook to show this in a 2nd sheet, Ive then added a very basic Pivot Table/Chart to show the power of these as per Pepe's suggestion. These are definitely worth playing with. Once you have some definite view of the data you want out then I'll happily take a further look.

What should alwys be remembered is that you dont have to store your data in the format you want it displayed, many of my workbooks have a mass of data hidden away thats just a jumble but then have numerous sheets ordering and displaying this in some sensible way.

[Zahra123]

Thank you so much! This is incredibly helpful. I had no idea something like this could be done - thank you again!

Also Pepe le Mokko - I didn't respond to your earlier suggestion because I hadn't heard of pivot charts and thought they would be incredibly complicated. But thanks!

[newdoverman]

The pivot table appears to be the way to go. I have reservations about the use of graphs because of the number of texts, institutions and years. I think that this would create a very "busy" chart that is difficult to read. I agree with pjwhitfield about the layout of the data. I created the same layout that he did by selecting all the data and sorting in order of institution then used Freeze Panes to keep the institutions in view as well as the year. This made it very easy to copy and paste the data for each institution and enter the proper year.

The pivot table is the same, I think, but I applied Conditional Formatting to show the values rated by colour. You could also have a table produced that would identify the most popular text for every year. This would be more easily shown with a lot more data because right now there are years where all the texts are equal.

I will upload the workbook so that you can maybe see the possibility of the Conditional Formatting.