+ Reply to Thread
Results 1 to 5 of 5

Adding multiple data series on bar graph with line graph on top

  1. #1
    Registered User
    Join Date
    02-11-2019
    Location
    toronto, canada
    MS-Off Ver
    2013
    Posts
    64

    Adding multiple data series on bar graph with line graph on top

    Hi!

    I am creating a bar graph for work and have made it look good BUT right now it shows the # of people and I also want to show the total $ of people.

    I would like to keep the graph the same as in the attachment but had the yellow cells (people $) in a line graph/series above the bars with its own data series.

    As shown in the attachment i current put the values that I want as a line hovering over the bars with its own data labels in a text box!

    Let me know if this is possible!

    Thank you,

    Jeffrey
    Attached Files Attached Files

  2. #2
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482

    Re: Adding multiple data series on bar graph with line graph on top

    What you need to do is make sure that the needed amounts for current HC, FY2020 totalHC COst and FY2021 TOtal HC Cost are in the same columns as the Headcounts.

    When you changed that. Then you need to add a data series for that row
    Next you need to select te graph and choose "Change Chart type"
    It will then give you an overview of all data series and their graph type. Now find the Total cost row and change the Chart type to line graph and mark the secondairy axis box.
    when you have done that you can show a line over the bars for HC with costs. because the secondairy axis will rescale automaticly to show value in miljons on the same height as the single values on the primairy axis.

    see attackment for the result
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    02-11-2019
    Location
    toronto, canada
    MS-Off Ver
    2013
    Posts
    64

    Re: Adding multiple data series on bar graph with line graph on top

    Quote Originally Posted by Roel Jongman View Post
    What you need to do is make sure that the needed amounts for current HC, FY2020 totalHC COst and FY2021 TOtal HC Cost are in the same columns as the Headcounts.

    When you changed that. Then you need to add a data series for that row
    Next you need to select te graph and choose "Change Chart type"
    It will then give you an overview of all data series and their graph type. Now find the Total cost row and change the Chart type to line graph and mark the secondairy axis box.
    when you have done that you can show a line over the bars for HC with costs. because the secondairy axis will rescale automaticly to show value in miljons on the same height as the single values on the primairy axis.

    see attackment for the result
    AWESOME!! Thank you!

    Last question, is there a way to remove the legends on the side - when I remove it the graph doesnt work. since there are data labels I dont want to show the side axis

    Jeffrey

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Adding multiple data series on bar graph with line graph on top

    To remove the axis labels and keep the chart:
    Select vertical axis > format selection > Labels > Label position > then select None from the drop down menu.
    Repeat the same steps with the secondary vertical axis.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482

    Re: Adding multiple data series on bar graph with line graph on top

    These legend keys are part of the Data table not the normal Ledgend. You need to change the data table setting not to show the markers

    so go to the + sign next to the chart,
    find Data table in the list
    then select "No legend keys"

+ 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. Singe Time-Line Graph with Multiple series
    By eko1130 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 11-14-2017, 04:20 AM
  2. Formatting multiple data series in a line graph
    By lc527442 in forum Excel General
    Replies: 1
    Last Post: 06-06-2017, 12:13 PM
  3. 3 Data Series Line Graph Help
    By dladysvmp in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-05-2016, 05:40 PM
  4. Select series(lines) in a line graph by adding userform with checkboxes on a chart
    By aab_489 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-24-2014, 12:25 PM
  5. Adding multiple series to a graph
    By Pedro9000 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-05-2013, 10:37 AM
  6. line graph with multiple data series vs. time/date leaves out most data
    By Lee Collins in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 09-18-2012, 10:54 AM
  7. Multiple Series Conditional Formatting in a Line Graph
    By help_clueless! in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-15-2012, 02:08 PM

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