Feedback by UserVoice

How can we improve Excel for Windows (Desktop Application)?

Allow a group of Sparklines to use Dynamic Named Ranges or Reference a PivotTable

Currently if you want a group of Sparklines to use a PivotTable as its source data and enable it to grow with the PivotTable, you must create a dynamic named range for each Sparkline.

A group of Sparklines cannot use a single dynamic named range, nor does a Sparkline or group of Sparklines recognise a PivotTable as its source data.

This makes for tedious set up or maintenance of Sparklines.

20 votes
Sign in
(thinking…)
Sign in with: facebook google
Signed in as (Sign out)

We’ll send you updates on this idea

Mynda shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

2 comments

Sign in
(thinking…)
Sign in with: facebook google
Signed in as (Sign out)
Submitting...
  • Eric commented  ·   ·  Flag as inappropriate

    You can in a way "embed" sparklines into your pivot table. Add a calculated field to the table and move it to the column in the table where you want the sparklines to be located. Set the field equal to zero then create a custom number format of ";;;" (without the double quotes). This will give you a blank column in your pivot table. Insert sparklines and set the location range equal to the blank column you just created. Make sure the number of rows exceeds the highest number of rows that your pivot table could ever be. Set the data range as the same number of rows as the location range and more columns than your data could ever possibly be.
    Rows that have no data won't show a sparkline but new sparklines will be created as your pivot table grows. They will also disappear when you slice you pivot table to less rows.
    Now you have dynamic sparklines "embedded" into your pivot table.

  • Mathew commented  ·   ·  Flag as inappropriate

    I fully agree, it would be better still if we could embed sparklines into our pivot-tables, or if selecting data from a pivot-table or excel table, to auto expand as the data changes,

    Each month I want to be able to hit refresh on my Pivot-Table which shows months and years across the top and category down the left

Feedback and Knowledge Base