+ Reply to Thread
Results 1 to 13 of 13

Combine series of data from different excel files

  1. #1
    Registered User
    Join Date
    01-18-2016
    Location
    Norway
    MS-Off Ver
    Office 2013
    Posts
    8

    Combine series of data from different excel files

    Hello all

    Is there any chance of combining different data on a chart?

    I have this series:

    =SERIES([sample.xlsx]Assid.!$B$33;[sample.xlsx]Assid.!$C$3:$Q$3;[sample.xlsx]Assid.!$C$33:$Q$33;1)

    I am calling the series from a different excel file and all I wanna do now is to combine more data into that series and show on a chart. Help me out here

    Thank you for your help in advance.
    Last edited by thunta; 01-18-2016 at 04:49 AM.

  2. #2
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Combine series of data from different excel files

    Hi thunta,

    Welcome to the forum...

    All you need to do is to click once on your chart to select it (click the chart border so the whole chart is selected) and then from the 'Design' ribbon, click 'Select Data' and select your extended 'Series' so it includes the new data you want adding to the chart:

    Q1 to Q2 Data Small.jpg

    to give you your original chart...

    Q1 to Q2 Chart.jpg


    and then select the additional data you want to include by clicking on the chart and then 'Select Data' from the 'Design Ribbon' and then going to your other sheet and selecting the extended data range...

    Q1 to Q4 Data Small.jpg

    to give you your 'updated chart...

    Q1 to Q4 Chart.jpg

    If this isn't quite what you are trying to achieve, perhaps you could upload a sample of your two sheets.
    Thanks,
    HangMan

    You can say "Thank you!" by clicking Add Reputation below the post.
    Please, mark your thread [SOLVED] if you are happy with the solution.

  3. #3
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Combine series of data from different excel files

    Maybe it will help: Peltier
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  4. #4
    Registered User
    Join Date
    01-18-2016
    Location
    Norway
    MS-Off Ver
    Office 2013
    Posts
    8

    Re: Combine series of data from different excel files

    Hi HangMan,

    Thank you a lot for your reply.

    As you can see below my chart is a Line Chart so maybe that's the reason why. I have tried to concatenate the data (from chart number two below) into my series from my main chart, by using a comma or a "+" but it did not work either way.

    Here's what I have tried: =[sample.xlsx]Assid.!$C$3:$Q$3,Assid.!$C$33:$S$33 AND =[sample.xlsx]Assid.!$C$3:$Q$3+Assid.!$C$33:$S$33

    PhS3XcS.jpg

    dQRLGyg.jpg

    Only the data in blue is to be combined, the orange line is basically the average of all values on that series.

    I hope I made myself understood
    Last edited by thunta; 01-18-2016 at 05:34 AM.

  5. #5
    Registered User
    Join Date
    01-18-2016
    Location
    Norway
    MS-Off Ver
    Office 2013
    Posts
    8

    Re: Combine series of data from different excel files

    up

  6. #6
    Registered User
    Join Date
    01-18-2016
    Location
    Norway
    MS-Off Ver
    Office 2013
    Posts
    8

    Re: Combine series of data from different excel files

    Quote Originally Posted by sandy666 View Post
    Maybe it will help: Peltier
    Hi sandy666,

    Thanks for your reply.
    It is not quite what I was looking for. See my issue above

  7. #7
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Combine series of data from different excel files

    Hi thunta,

    Are you able to upload your Excel file(s), or at least a sample with dummy data? It would make it much much easier to see the true nature of the problem you are facing and how your data is currently arranged.

  8. #8
    Registered User
    Join Date
    01-18-2016
    Location
    Norway
    MS-Off Ver
    Office 2013
    Posts
    8

    Re: Combine series of data from different excel files

    Quote Originally Posted by HangMan View Post
    Hi thunta,

    Are you able to upload your Excel file(s), or at least a sample with dummy data? It would make it much much easier to see the true nature of the problem you are facing and how your data is currently arranged.
    Here it goes:

    issue.xlsx

    As I mentioned, all I wanna do now is to combine the data from chart #2 into the series of my main chart.

  9. #9
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Combine series of data from different excel files

    So to add the second line, you simply need to add it as a 'second series', (see attached).

    However the issue (to me at least) is the positioning of your line. The date range for your 'second' line is beyond the date range for your first line which means logically the 'second' line would sit as shown in the third graph (on the attached sheet).

    Where exactly are you wanting the 'second' line to appear when added to your first graph? Where it is currently sitting on the first graph is misleading because it doesn't reflect the true date range of the data...
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    01-18-2016
    Location
    Norway
    MS-Off Ver
    Office 2013
    Posts
    8

    Re: Combine series of data from different excel files

    Quote Originally Posted by HangMan View Post
    So to add the second line, you simply need to add it as a 'second series', (see attached).

    However the issue (to me at least) is the positioning of your line. The date range for your 'second' line is beyond the date range for your first line which means logically the 'second' line would sit as shown in the third graph (on the attached sheet).

    Where exactly are you wanting the 'second' line to appear when added to your first graph? Where it is currently sitting on the first graph is misleading because it doesn't reflect the true date range of the data...
    Thanks for your quick reply!

    Yeah. The position of the second line would exactly after the last value from the first series, so it would look like something continuous and a single series. This is the issue since I am calling data from a different file. I see now that the most logical thing is to copy over a new sheet both data and then run a chart out of the combined data

  11. #11
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Combine series of data from different excel files

    In which case, you are right, you would have to set up your data to be one continuous data set because you can't select a non-contiguous data range which is what I think you were hoping you could do.
    Last edited by HangMan; 01-19-2016 at 06:10 AM.

  12. #12
    Registered User
    Join Date
    01-18-2016
    Location
    Norway
    MS-Off Ver
    Office 2013
    Posts
    8

    Re: Combine series of data from different excel files

    Quote Originally Posted by HangMan View Post
    In which case, you are right, you would to set up your data to be one continuous data set because you can't select a non-contiguous data range which is what I think you were hoping you could do.
    Everything is working now

    http://i.imgur.com/6Pnnavd.jpg

    Thanks a lot for your help!

  13. #13
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Combine series of data from different excel files

    You're welcome... Glad it is all working for you now

+ 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. Excel vba to tel adobe combine two pdf files based on a spcified list of files
    By Abbadon486 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-30-2014, 12:04 PM
  2. [SOLVED] Combine data of some cells in multiple excel files into one sheet
    By ricdamiani in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 08-07-2013, 12:07 AM
  3. Combine data from 4 workbooks and then export filtered data to individual files?
    By womanket in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-16-2013, 12:20 PM
  4. Combine data from several rows in one series in scatter plot
    By marikladd in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 12-07-2012, 08:11 AM
  5. Merge/Combine Excel Sheets/Data/Files
    By nay in forum Excel General
    Replies: 0
    Last Post: 04-06-2009, 11:35 PM
  6. Combine Data Series.
    By beemanmc in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-29-2009, 06:36 AM
  7. How to combine two excel files?
    By dreamgirl in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-06-2006, 05:07 PM

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