Jump to content


Photo

Charts involving a drop down list


  • Please log in to reply
7 replies to this topic

#1 ARNGRyanJ

ARNGRyanJ

    Member

  • Members
  • PipPip
  • 13 posts

Posted 25 January 2015 - 07:19 PM

Hello,

 

I am unsure how to get excel to bring up a different bar chart depending on which name is selected from a drop down list.  I would like to be able to pick someone's name from a drop down list at the top of the PerfComp tab and have a bar chart come up that references their individual performance against plan (Performance Against Plan % on Inputs tab).  How would I go about creating this, or where can I go to find out?

Attached Files



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 25 January 2015 - 10:32 PM

Hi Ryan,

 

The easiest way to do this would be to use a PivotChart. PivotCharts allow you to create charts based upon pivot tables, which allows them to be filtered. A PivotChart actually has drop-down menus on the chart itself, which allow you to select a name.

 

For a walkthrough of how to create a PivotChart, see the video lesson: Expert Skills Lesson 5-23: Create a pivot chart from a pivot table.

 

Since a PivotChart is based on a pivot table, you can also filter it using the drop-down menu in the pivot table. For more on how pivot tables are filtered, see: Expert Skills Lesson 5-7: Use report filter fields.

 

You can make filtering even easier by applying a Slicer, which shows all of the names as large buttons. For a video walkthrough of creating slicers, see: Expert Skills Lesson 5-8: Filter a pivot table visually using slicers.

 

I have attached a copy of your workbook with the pivot table, pivotchart and slicer.

 

Attached File  Compensation Planning-1-1.xlsx   39.27KB   219 downloads

pivotchart.png

 

You can also enable users to choose items from drop-down menus using a List Validation. It probably wouldn't be the approach you'd choose in this case, but you can learn about applying List validations in: Expert Skills Lesson 2-11: Add a table-based dynamic list validation.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 ARNGRyanJ

ARNGRyanJ

    Member

  • Members
  • PipPip
  • 13 posts

Posted 27 January 2015 - 01:27 AM

Thank you, that worked perfectly.  If I would like to change the chart type to a combo and add a line that also track the employees salary over the same three years, how would I do that?  Salary (in $'s) can be on the right hand side of the chart.  It won't let me adjust the data range that is being used in the pivotchart.  Is it possible to reference the data where it already is located in the Inputs tab, or will I need to create another table?



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 27 January 2015 - 08:06 PM

Hi Ryan,

 

It is possible to create a PivotChart that has a secondary axis, in the same way as you can do with an ordinary chart.

 

For a video walkthrough of creating a secondary axis in a chart, see: Essential Skills Lesson 5-23: Create a chart with two vertical axes.

You can also create a combination chart using the same skills taught in: Essential Skills Lesson 5-24: Create a combination chart containing different chart types.

 

You can't use the Select Data option with PivotCharts, but you can adjust the data used by a PivotChart by selecing the chart and clicking: PivotChart Tools > Analyze > Change Data Source. This is the same command that is used to change the data source of a pivot table.

 

...however, I think you will find that you can't quite achieve what you want because of the layout of your data. In order to chart your salaries and percentages across the years, you'll need to add the Year as a column.  I've attached an example showing how you could do this:

 

Attached File  Compensation Planning-3.xlsx   40.93KB   165 downloads

pivotchart2.png

 

As usual, please let me know if you need any more help with this.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#5 ARNGRyanJ

ARNGRyanJ

    Member

  • Members
  • PipPip
  • 13 posts

Posted 28 January 2015 - 01:03 PM

Administrator,

 

Is it possible to have another Pivotchart right next to this chart that is linked by the name?  I could have the employees' salaries in a separate table, and when someone chooses an employee's name from the drop down list, his/her performance against plan percentages and salary over a three year period could show up on adjacent charts.  I would like to make it to where a manager would just choose someone's name from a single drop down list, and all of the information would show up, in order to avoid user errors. 

 

Thanks



#6 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 28 January 2015 - 07:19 PM

Hi Ryan,

 

Yes, it's possible to link a single Slicer to multiple pivot tables, enabling you to filter multiple pivot tables and charts using a single interface.  You can see a complete walkthrough of how to set up a system like this in the video lesson: Lesson 5-25: Use slicers to filter multiple pivot tables.

 

I've attached an example using your sample workbook:

 

Attached File  Compensation Planning-4.xlsx   46.17KB   220 downloads

compensationplan3.png


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#7 ARNGRyanJ

ARNGRyanJ

    Member

  • Members
  • PipPip
  • 13 posts

Posted 30 January 2015 - 12:06 AM

I think that looks great, and it would work fine.  The project manager has said that they would like a single drop down box with a list of names and a chart similar to the one that I have attached in the Sheet 1 tab.  Is there a fairly simple way to bring in Performance Against Plan and compensation numbers for each individual and put them into this simple table format so that the chart can show them? It would be similar to a lookup function, but with a set of six values brought over (2012A, 2013A, and 2014A %'s and $'s from Inputs tab). Also, I have seen some useful applications of the Index function and Match function lately. Are there any videos or helpful posts relating to these?

Attached Files



#8 ARNGRyanJ

ARNGRyanJ

    Member

  • Members
  • PipPip
  • 13 posts

Posted 30 January 2015 - 02:33 AM

I figured it out.  I researched Index and Match functions and used them to do this. I have attached the finished product in case something like this comes up again.  Thanks again for all the help in getting to this point.

Attached Files






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users