+ Reply to Thread
Results 1 to 4 of 4

Adding Weekly Data to Monthly Chart

  1. #1
    Registered User
    Join Date
    10-21-2016
    Location
    Kansas City, MO
    MS-Off Ver
    2010, 2013
    Posts
    64

    Adding Weekly Data to Monthly Chart

    I'm trying to add weekly sales data to a chart (attached) tracking monthly count that can't be broken out by week. Ideally, I would have the monthly 2019 and 2020 count as a bar chart and the weekly 2020 sales as a line chart. In the examples I'm finding for combo charts, the data has the same frequency, whereas I'm trying to match 52 records up against 12. Can anyone advise how I do this?
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,146

    Re: Adding Weekly Data to Monthly Chart

    OK, I sort of stumbled into this one.

    Right click on the chart and select Selecte Data. Add the weekly data as a third series. Put this series on a secondary axis. THEN you get the option to add a secondary horizontal axis. Select the new series under select data and add the dates to the secondary horizontal axis.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    10-21-2016
    Location
    Kansas City, MO
    MS-Off Ver
    2010, 2013
    Posts
    64

    Re: Adding Weekly Data to Monthly Chart

    Wow, @dflak, this is great! Can you explain the steps you took to add the weekly data as a new axis? When I tried to replicate, I clicked Add Series, and made the "Sales" column my series but no matter what I tried, it tried to make these sales numbers continuations of my X axis (to where my 2019/2020 counts and sales figures were on the same horizontal axis) - I tried separating onto a second axis but didn't seem to find a solution...

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,146

    Re: Adding Weekly Data to Monthly Chart

    Here is what I did to get the secondary axis.

    Select Data is a powerful tool for charting. I call it "getting under the hood."
    Attached Files Attached Files

+ 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. Changing Weekly data into monthly
    By Jimbo123456 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-18-2019, 06:00 PM
  2. Running monthly totals based off a weekly adding
    By jamesraywebber in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-20-2017, 11:02 PM
  3. Convert weekly to monthly data
    By Svilen Pachedzhiev in forum Excel General
    Replies: 5
    Last Post: 08-10-2017, 04:09 AM
  4. Converting weekly to monthly data and Yearly data to monthly
    By MilicaMatovic in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 01-29-2016, 09:48 AM
  5. Adding a Worksheet to work out my Weekly/Monthly/Quarterly/yearly income
    By DaleWinton in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-15-2014, 08:21 PM
  6. Replies: 4
    Last Post: 07-07-2011, 04:58 AM
  7. Replies: 1
    Last Post: 07-11-2008, 05:57 AM

Tags for this Thread

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