+ Reply to Thread
Results 1 to 5 of 5

Creating a Stacked Column Chart That Displays Monthly Data

  1. #1
    Registered User
    Join Date
    10-04-2016
    Location
    Miami, FL
    MS-Off Ver
    2007
    Posts
    11

    Creating a Stacked Column Chart That Displays Monthly Data

    Hello everyone. I have a somewhat basic spreadsheet that keeps track of my income and expenses. I was trying to create a stacked column chart that displays my monthly income and monthly expenses. I have my income on one sheet and expenses on another. Each row on both sheets contain data for a particular day an income or expense was made. I am trying to have the chart sum all of January's income and display it as one column or line graph depending on what chart type I think works best. So for example January might have 10 rows of income with the dollar amount in cells D1:D10 while February has 20 rows of income with the dollar amount in cells D11:D20. And same scenario for the Expenses sheet. January and February will have different amount of rows recording for each month. Is there a way to highlight column D (amounts column) and have the chart create a column that displays the sum for each month for both the Income sheet and Expense sheet?

    I hope I am explaining my challenge correctly.

    Regards,
    BP

  2. #2
    Registered User
    Join Date
    12-06-2016
    Location
    India
    MS-Off Ver
    2007
    Posts
    72

    Re: Creating a Stacked Column Chart That Displays Monthly Data

    Hi,

    To show your monthly income and expenses in stacked column chart, first you need to create pivot which will show month in row and days in column and expenses in value field.

    Month 1/1/2017 3/1/2017
    Jan-17 10
    Mar-17 10
    May-17 10
    Jul-17 10
    Sep-17

    and then create stack chart using this

  3. #3
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Creating a Stacked Column Chart That Displays Monthly Data

    You will need to create merged table. Using SUMIFS or some other formula.

    Alternately you can use Pivot Wizard to consolidate two tables (I don't have a copy of Excel 2007 but I believe it had some limitations).

    Read through link for details on consolidating multiple sheets to PivotTable.
    https://support.office.com/en-us/art...ID=HA010226585

    Also if you search for term "pivot wizard consolidate excel 2007" you should find quite a few tutorials.

  4. #4
    Registered User
    Join Date
    10-04-2016
    Location
    Miami, FL
    MS-Off Ver
    2007
    Posts
    11

    Re: Creating a Stacked Column Chart That Displays Monthly Data

    Wow. I was afraid of that. I am not familiar with pivot tables and such. Guess I am going to have to do some studying. Let me see what I can do with your suggestions guru and CK. If not I will be back here =Þ.

    Regards,
    BP

  5. #5
    Registered User
    Join Date
    10-04-2016
    Location
    Miami, FL
    MS-Off Ver
    2007
    Posts
    11

    Re: Creating a Stacked Column Chart That Displays Monthly Data

    For now I summed each month's total in its own cell using SUMIFS then created a chart showing that data. I thought I would be able to create a chart and tell the series to do that using a formula like I am using without having to do that in a cell then graph it. I will eventually get to study pivot tables and such as you guys suggested.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Creating even percentile plots in 100% stacked column chart
    By cartman88 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-22-2015, 04:29 PM
  2. Creating a chart that only displays lines with data
    By Melvinrobb in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 01-25-2013, 06:18 PM
  3. Creating a stacked column chart with percentage in the graph
    By MQ in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 10-10-2011, 02:53 PM
  4. Creating an average line for a 100% stacked column chart
    By nacky in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 12-02-2008, 08:05 AM
  5. stacked column/bar chart w/ monthly data + single line of daily data
    By dommarsh in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-06-2008, 11:29 PM
  6. [SOLVED] creating stacked column/line chart combinations
    By Cori in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 07-31-2006, 01:23 PM
  7. [SOLVED] To create a stacked column chart and group the stacked bars togeth
    By Jacqueline in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-18-2005, 10:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1