Excel graphical charts with odd formatted data
I am currently working in Microsoft Excel 2011 on Mac OS X. I am given a large amount of data in different tables and I need to make 2 tables with data.
I understand that the usual way of orienting bar charts in excel involves placing the data like this:
a b
A 1 2
B 3 4
However, I am currently going to work with a lot of data in the format:
A a 1
b 2
B a 3
b 4
Is there a quick way to either 1) convert the data from my format to the correct format, or 2) plot a histogram (without having to go in and do a ton of data tweaking) with the current format.
Currently the only solution I'm working with is to manipulate and move data around and rename everything as soon as the histogram is created, but this is very cumbersome.
source to share
I would do it in three steps:
-
Fill in the missing values for the first column. It's a little tame. If the data is huge, you can write a quick vba routine. Anyway, make the data look like:
-
Highlight this data and turn it into a pivot table.
-
Structure the pivot table according to the data format of the bar chart and add a pivot chart:
source to share