MrExcel Publishing
Your One Stop for Excel Tips & Solutions

4D data?

Posted by Jozef Marak on February 27, 2001 4:09 AM

Hi everybody,
I have the problem with the Excel data evaluation. Why? I would like to describe my problem in the example. I created Excel file containing my data arranged in the following way:
1. I have several worksheets (12, one for each month) in my file;
2. each worksheet contains 3D data arranged like this

Item Paid Tax
car 85 12
food 41 8
beverages 15 2

My problem is that I would like to see my data from this point of view
"How much did I pay for the food through the whole year?"
It means it is necessary to pick let say only one value (cell crossing "food" and "Paid") from each sheet (month) and to create the graph. I was not able to do this with Excel.

Is there any solution of the problem like this? I don't want to rearrange the table because the number of items is hundreds.

Any advice is very appreciated.




Posted by David Hawley on February 27, 2001 4:14 AM

Hi Jozef

I'm not sure I fully understand you but, If you have Excel 2000 a Pivot Table with a Pivot Chart amy suit your needs..


OzGrid Business Applications

Posted by Jozef Marak on February 27, 2001 5:56 AM

Hi Dave,

thank you for your help.But when I have read your answer (and once again my description) I realized that one very important fact is missing (or is not explained in details). It is that I would like to see the "trend" in my data (in the whole budget). It means, the "month" is my X data and "Paid" (from each worksheet) is my Y data. I'm not interesting in the SUM (how much I spent during the whole year!!!) or AVERAGE (how much I spent monthly) or anything like that.
Pivot Table is OK if I would be intersting in that but I'm not. So your help is not solving my problem. But anyway, thank you.

I hope that this is now better explained what I'm looking for in Excel.

Thank you for any advice.


Posted by GregA on February 28, 2001 7:45 AM


I believe that you are going to have to create a new workbook page that contains all the data from each of the sheets, along with a "Month" field added, and then from that you will be able to sort/filter and create your chart.

This should't be hard to do. Just copy and paste each worksheets data to the new sheet, and as you do that create and copy the month for the data you just transferred. Then you can sort by category and month, and create the graph.