+ Reply to Thread
Results 1 to 4 of 4

Pivot Chart with Formula

  1. #1
    Registered User
    Join Date
    09-12-2018
    Location
    Port of Spain, Trinidad
    MS-Off Ver
    Excel 16
    Posts
    53

    Pivot Chart with Formula

    I have a data table that consists of Students, their marks in various subjects and the respective year.

    I would like to have a chart automatically generated based on info supplied in cells J2:N2 (ie Student ID, Year, Subject)

    for example if i type 12 in cell J2 under Student ID and I input a specific subject like Maths in L2 I would like the chart to automatically show Student#12's Maths marks etc.

    So it's similar to a Pivot Chart but instead of toggling the Pivot Chart Fields i just want to enter the aforementioned data in the aforementioned cells and have the chart update/display accordingly.

    I do not know if this is even possible, so I am asking for guidance on this "problem".

    Please see worksheet attached and screenshot below

    Monster.PNG
    Attached Files Attached Files

  2. #2
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Pivot Chart with Formula

    Maybe look at using slicers to filter the data.

  3. #3
    Registered User
    Join Date
    09-12-2018
    Location
    Port of Spain, Trinidad
    MS-Off Ver
    Excel 16
    Posts
    53

    Re: Pivot Chart with Formula

    So yes I've had a look at Slicers and it almost does what I want.

    However I was wondering if it were possible to input the data I wanted in a cell and generate the filtered chart.

    Example if I entered
    the student ID 6 in J2
    the year 2018 in K2,
    with Subject Mathematics in L2

    Is it possible to get an automatically filtered chart generated of Student 6's Mathematics marks for 2018?

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

    Re: Pivot Chart with Formula

    As you are using the 2016 version of Excel you can use Power Query (Get & Transform) to convert your data into record form by unpivoting the columns for each subject.
    You could then use a pivot table and pivot chart either using slicers as kersplash suggested, or using the filters on either the table or chart (See Sheet 1).
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Pivot Combo chart - retain chart type for specific data series
    By meileetan in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 03-21-2019, 03:48 AM
  2. Change Chart Pivot series Colour depending from Pivot Table Filter value
    By BrianAll in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-30-2018, 02:20 PM
  3. pivot chart changed to normal chart after populate it in userform
    By farrag in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-09-2017, 09:12 AM
  4. Chart order in trad. Pivot Chart vs. PowerPivot chart
    By mr_jules in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 01-06-2015, 11:17 AM
  5. How to add target line to stacked column pivot chart chart
    By oleg mirzaev in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 11-04-2013, 07:46 AM
  6. Replies: 3
    Last Post: 09-11-2010, 06:55 AM
  7. LOST!! Chart? Pivot table? Formula?
    By Ed in forum Excel General
    Replies: 4
    Last Post: 02-21-2006, 03:50 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