0

I have transaction data I am trying make a histogram from. I want to group the data by account description (ex: small tools), but for that account I also want to show the total transactions (sum) for each individual that made a transaction in that account.

How do I create this chart?

1
  • Can you sketch something that would show us what your desired outcome would look like? #MSPaint
    – AdamV
    Commented Nov 29, 2021 at 12:24

2 Answers 2

1

I know you asked this three years ago, but I'll answer so that anyone (like me ...) who needs this in future can maybe find it.

The person who answered above misread the question. There are a couple of ways to create a clustered/stacked histogram in Excel, but I can only remember one of them right now.

Let's say you have a numerical variable in column B and you want to do a histogram of it, then, sure, highlight column B and insert a histogram. If, however, you'd like to cluster it by a categorical variable in column C, then you do this:

  1. You have to break the data up into bins yourself to do this; I suggest using FLOOR. Ex, if your variable ranges from 61 to 75 and you want to put it into bins of width 2, then you add a column D with the equation "=FLOOR(B?,2)".

  2. Then do a pivot table and drop B in as your rows and D as your columns, and make sure the values is doing a count and not a sum or anything.

  3. Copy and paste (by value) your pivot table contents into a new sheet. Make a clustered or stacked bar graph. (You could probably also do a pivot chart based on the pivot table, but that gives you less flexibility, and you'd have a problem for #4 below.)

  4. You'll have to do something to fix the row labels; ex, if your bins are size 2, then everything labelled "58" is from the interval [58,60), so replace the row name accordingly. Or I guess you could offset your labels to get them to display on the vertical grid lines (add some space in the name of each one?). Then do whatever else it is you do to make a chart look good!

0

To create a Histogram in Excel:

  1. Select the entire dataset.
  2. Click the Insert tab.
  3. In the Charts group, click on the 'Insert Static Chart' option.
  4. In the HIstogram group, choose the one you need and click on the Histogram chart icon.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .