Budget Category Values Duplicating on X-Axis

Hi All,

I'm trying to determine if there's a way to display my budgeting data in the way I want, because I'm running into some snags. I believe I've attached my spreadsheet of sample data, if I've done it correctly (first time posting an issue here), so hopefully anyone who can help is able to review it.

The problem I'm running into is that after downloading transactions from a banking account, I went through them all, and assigned them each a category using a dropdown box via data validation. Then, after struggling to create a graph with the data as it exists on the "Sample Data" tab, I split the "Credit" and "Debit" transactions into the "Income" and "Spending" tabs respectively, thinking that would help. I also changed all amounts on the "Spending" tab since I know they're all money going out, and I'm not putting them in the same graph as the income transactions.

I'm wanting to create some kind of graph (I started out with a column chart thinking this would work well) that displays the amount of money spent in each category cumulatively. My issue is that when I create a column chart using just the "Classification" and "Amount" columns of the "Spending" tab, I get a different column for each instance of the classification. You can see this on the "SpendingReport" tab with the classification of "Restaurants" since it occurs twice in my sample data.

Is there a way to display the amount spent cumulatively in each category? Do I need to use a different chart type entirely? Is the dropdown box affecting the chart?

As a side note, I tried this type of chart as a last ditch effort to simplify things. At first, what I was really wanting was a breakdown of money spent in each category per month, but I realize that with so many categories, it would probably just make more sense to have separate charts for each month. I can always post a separate issue if I get to that point and hit snags, though. Before that, I need to know how to display the data cumulatively at all.

Please let me know if I've left anything out or if I can clarify anything.

Hi Jess & Welcome to the Forum,

There are so many ways to go with this, but the main idea, use a Pivot Table to switch between Debit and Credit. Usually no real good reason for making extra tabs.

Also, since you are using 2016, take advantage of the Pivot Chart.

Hi Jess and Happy New Year,

If you click on the Pivot Table, you should see on the right hand side the PivotTable Fields box. That is where you can setup all of the items you want to see in your Pivot Chart and Pivot Table.

If you don't see the PivotTable Fields box, then it was closed and simply clicking on the Pivot Table won't work. Instead >> Click on Pivot Table >> Analyze tab >> Show >> Field List

Hi Jess,

I think what you are after is the Subtotals.

• On the PT
• right click on Credit >> Subtotal "Transaction"
• right click on Deposit >> Subtotal "Category"

The web has many resources and here's one.

Another one

and here

and here

After you have turned on the Subtotals, you can now move your cursor over the PT just on top of DEPOSIT total. Not a click just a hover. Once you hover over DEPOSIT total you should see a black arrow pointing to the right.

Regular click once you see this arrow. Now all of the Subtotals should be highlighted. At this point you could pick a fill to separate out the totals. Aesthetics really.

Also, I you don't want all of the filters on the chart, right click a filter >> Hide all field button on chart

Sorry, I misunderstood.

Try this.

Click on the PT >> Design >> Layout >> Report Layout >> Show in Tabular Form

Good to hear Jess. You are very welcome and thanks for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.