Excel Graph - Grouping Categories and Subcategories

Question

I rarely use excel and do not have a deep understanding of graphics and graphics related functions. Having said that ...

I have dozens of rows of data consisting of 4 columns

• column 1 = amount / price (in numbers)
• column 2 = description (as in the text)
• column 3 = category (in text)
• column 4 = subcategory (in text)

I want to create a histogram of my data rows so that the end result looks like this:

X axis - categories Y axis - quantity / price

The trick here for the categories is NOT to be repeated. For example, if our data is similar to ...

• 100 | boat purchase | boats | 3 motor boat
• 200 | boat purchase | boats | 2 motor boat
• 500 | buying an airplane | airplanes | 4 engines
• 900 | car purchase | cars | 1 car engine

Then on my schedule there should be only one instance of boats, planes and cars, under which all related data will be summarized.

And last but not least, I saw graphs in which these uniquely non-repeating categories, and not just one “bar”, as it were, consist of smaller bars. In this case, I want these smaller columns to be subcategories, so the end result will look like this:

For the first time in this sample image, I present a “basic classic” graph, where blue, yellow, and red each represent a unique, different category. Right below is what I want, a “breakdown” of each category into subcategories, where blue / yellow / red each represents an imaginary 3 different subcategories for each category.

This means that the subcategories will be repeated for each category, but the categories themselves will not.

For clarification, I currently have only 3 main categories and 6 or so subcategories, but this may change in the future, therefore, the desire to have it in automatic / dynamic mode

Regards G.Campos

EDIT: new image:

Answer 1

Here I accept it. Unfortunately, I can’t post screenshots because I don’t have enough posts.

One solution is to use pivot charts placed in "Values", "Category" in "String Rows" and "Subcategory" in "Column Labels".

I uploaded the appropriate images to the free image download service.

This is our raw data:

Amount Decription Category SubCategory 100 boat purchase boats 3 engine boat 200 boat purchase boats 2 engine boat 500 plane purchase planes 4 engine plane 900 car purchase cars 1 engine car 450 boat purchase boats 2 engine boat 110 plane purchase planes 4 engine plane 550 car purchase cars 1 engine car 230 car purchase cars 2 engine car 450 car purchase cars 5 engine car 

This is the desired graph (Edit: This has ghost lines):

http://imageshack.us/photo/my-images/849/pivot.gif/

I just read the comment about the lack of ghost graphics. This may be what you are looking for:

http://imageshack.us/photo/my-images/266/pivotnoghost.gif/

Just googled and found something very similar here:

peltiertech.com/WordPress/using-pivot-table-data-for-a-chart-with-a-dual-category-axis/

You need to add http: // (I cannot have more than two hyperlinks due to the small number of posts)

Answer 2

I'm not sure that this will give you exactly what you want, but I am generally in excel, the easiest way is to summarize your data in a separate tab.

For example data like this

you would create a second tab on the sheet that looks something like

totals are calculated using the sumif formula

 =SUMIF(Data!C:C,Summary!A2,Data!A:A) 

For category totals

and

 =SUMIF(Data!D:D,Summary!E2,Data!A:A) 

For subcategory totals (Assuming subcategories are mutually exclusive). Now that this data is summarized, select the cells and insert a column table for the following diagrams.

Adding new categories and / or subcategories will require adding rows to the totals, and then adding rows to the charts. You can use the vba macro to automate this task, but I suspect it is too much, since your dataset is “tens” and not “thousands”