Announcement

Collapse
No announcement yet.

Chart With Multiple Variables

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Chart With Multiple Variables

    Hi Everybody,

    I am trying to create a column chart in which i want to incorporate 2 variables. However I don't want to use the line for the second variable. What I would like to do is to add the information of the number of deals a company has done on top of the column. So you see a column which gives you the average size of a deal, and the you should see a number at that column to see the number of deals done with that company.

    To make this more clear I have attached an example. There are 2 tables of which the information I would like to see in one chart.

    Hope someone can help me on this.

    Kind regards,

    Sirus
    Attached Files

  • #2
    Re: Creating Chart With Multiple Variable

    2 ways of doing this.

    To have the labels on top of each column you will need to add a duplicate set of data and move it to the secondary axis. Apply data labels and link then to the number of deals cells.

    To have the labels at the bottom of each column add a set of deals data and move to secondary axis. Set alignment position for data labels to be inside Base.
    Attached Files

    Cheers
    Andy

    Comment


    • #3
      Re: Chart With Multiple Variables

      Andy this great, exactly what I needed. Thank you very much.

      The only thing is that I don't really understand how you got this done. If possible could you talk me through the steps?

      Thanks!

      rgds, Sirus

      Comment


      • #4
        Re: Chart With Multiple Variables

        Labels on top chart -

        to get the extra data series into the chart select the range A9:J15 and drag and drop it on to the chart. At the paste special dialog Add cells as New Series. Values in Columns. Tick both Series Names in 1st Column and Categories in 1st row.

        Select the new Jan column series and use the format dialog to move it to the secondary axis. Now repeat for all other new series. You can use the F4 button to repeat changes.

        You should now have a duplicate set of columns infront of the original ones.
        You need to apply data labels to these series and then link the data labels to the ranges with the deal numbers in.
        Here is a link to a free addin that will make that process easy.

        Actually thinking about it you don't really need the 2nd set of repeated data. Simply use the existing columns data labels to link to the cells.

        For the chart with labels at the bottom you can either do the same as above but just change the position of the labels.
        Or add the range A1:J7 to the chart as described above.
        Move to the secondary axis.
        Display data labels for these new series and position at bottom.
        Format columns to have no border or no fill colour.

        Cheers
        Andy

        Comment


        • #5
          Re: Chart With Multiple Variables

          thats amazing!

          Thanks a lot andy!

          rgds, Sirus

          Comment


          • #6
            Re: Chart With Multiple Variables

            Hi Andy,

            How do I add the data label? I am having some trouble with that.

            Thanks!

            rgds, Sirus

            Comment


            • #7
              Re: Chart With Multiple Variables

              Have you downloaded and installed the addin I suggested?

              If so just select the chart and use Tools > XY Chart Labels > Add Chart Labels.
              Select required series and cell range.

              Cheers
              Andy

              Comment


              • #8
                Re: Chart With Multiple Variables

                Ah have not done that as the firewall at work does not allow me to do that. Is there another way to do this? otherwise I will try to do it at home tonight.

                Cheers!

                rgds, Sirus

                Comment


                • #9
                  Re: Chart With Multiple Variables

                  The addin takes the effort out of doing it manually.

                  To do it manually see here

                  Cheers
                  Andy

                  Comment


                  • #10
                    Re: Chart With Multiple Variables

                    Hi Andy,

                    I actualy just saw your piece on linking charts to a cell in the book Excel Hacks as well. However, I am having trouble getting this part done manualy. For example I am trying to do this for January, I click chart options and then data labels, but from there I have no idea how to do the link.

                    Thanks again mate!

                    rgds, Sarju

                    Comment

                    Working...
                    X