Chart depicting Profit and Loss Trends

Hello,

Can someone show me a way to create a P&L chart using the attached data table? I am not sure how to handle negative values. Should I make them positive before plotting on a chart? I am looking to create either a stacked column or bar chart but open to other suggestions that will enable me to present this data in a meaningful way. Thanks

What does P&L mean? Keep in mind not everyone one the board uses your technical/commercial jargon .

And while you are at it please change your title to something more descriptive of your problem; Thanks

Title much more descriptive

How much of this question is specifically about how to create a chart in Excel, and how much is the more generic question (independent of your choice of charting application) of how to create a good Profit&Loss chart?

My first thought -- using your original sample file and focused mostly on the mechanics of creating a chart in Excel -- was to clear A1 (because, for some reason, Excel's charting engine likes to have an empty upper left cell when creating charts), then select A1:J13 -> Insert Chart -> choose a chart type. See if you like it. If not try a different chart type, and so on until you decide you like one of the charts or until you decide you need something more complex than what Excel creates with its default settings. If nothing else, that should show the basic mechanics of how to create a chart in Excel.

If you already understand the basic mechanics of creating charts, and the question is really more about the generic how to choose a chart type, then we probably need to talk about what you are trying to show with the chart. Ultimately, a chart is a means of communicating something about the data to an audience. Who are you trying to communicate the data to? What do you want them to be able to see in the data? I also sometimes think this part of the discussion can be helped if you create a few charts in your file and explain what they show well and what you want them to show differently. Help us understand what you are trying to communicate with the chart, and we can discuss different chart types and formatting strategies that will help with your communication goals.

My apologies for not having something more concrete, but I think we need to understand exactly what you are having trouble with when it comes to creating this chart.

But I want to show each category within a month. Is that too much to depict on a chart?

It might be, one would have to cobble something together and see. It is certainly possible to have too much information in a chart so that you cannot see anything through the mass of information. How much is too much depends on the chart type, the data, and what you want to be able to show.

I read somewhere that I am supposed to convert all negative values to positive for the chart to work.

I don't think I would accept a blanket statement like that, because it probably depends on all of these other questions that we are dancing around. What chart type, what are we trying to show, and so on. Some variations may well want us to change negative values to positive. Other variations will want the negative values left alone.

You say that you kind of like torachan did, but you want to break the "charges" up into categories. So I:
1) cleared A1 in his file (again, Excel likes to have the upper left cell empty at chart creation).
2) Selected A1:J13 and inserted a stacked column chart. I needed to click on "switch row/column" to get dates along the x axis and categories as series/legend entries.
3) Deleted the "gross sales" and the "other charges" series, so my chart only showed the values that were included in torachan's total charges category.

With that, I had a nice stacked column chart that shows how each category contributes to the total charges in a separate chart. I opted for this because the total charges tends to be small compared to some of the gross income values, and I did not think I would be able to really see the "stacks" if I tried to incorporate the stacked chart as part of torachan's overall chart.

How about something like that? Rather than try to fit everything into a single chart, use multiple charts to show different aspects of the data.