How do you insert a sparkline into a pivot table?

Question

I would like to have my sparklines inserted into my pivot table so that as I filter the pivot table I don't have to adjust the sparklines that are outside of the pivottable manually.  Any thoughts on how to add a sparkline into the actual pivottable?

Answer 1

Excel 2010 PivotTable

Dynamic Sparkline position.

Always on right edge of PT.

Number and source refs of SLs dynamic.

With macro.

http://c3017412.cdn.cloudfiles.rackspacecloud.com/02_17_11.xlsm

If you get *.zip, don't unzip, just rename *.xlsm

Answer 2

Why would you have to adjust anything manually?

You can create a PT and then put sparklines in a column to the left or right outside the table, pointing at the data range of cells occupied by the data you want (not including column totals for example).

When you filter, the PT occupies the same cells and the sparklines reflect the data on the corresponding rows at the time. If any sparks refer to blank rows they are not drawn. I've just tried this to cinfirm it bahaves as I expected it to.

Make sure your sparklines are all one group - either create the top one and drag the cell handle down, or select the whole range where you want them before adding, and include the whole data range as the input.

Can you describe the behaviour you are seeing and the steps you took to get there? What is wrong with the outcome you get?

---

Adam Vero MCT, MMI, MBMSS:CRM 4, MCSA:Messaging Meteor IT - Training and Consulting Services

Answer 3

Thanks for the questions and request for clarification.

yes, it's relatively easy to add sparklines just to the right of pivot table.  However, when you add a slicer or when you change the filters on the pivot table the number of columns that are included in the pivottable may change based on what you are filtering on.  So at that point the sparklines are end up being several columns past the pivot table or covered up by the pivot table if more columns were included as the filters change.  Thus I am trying to figure out how I can always get the sparklines to be right next to the pivot table or in the pivot table so that as the filters change the table still looks good.

Thanks

Brady

Answer 4

Ah, filtering the whole table (I was imagining filtering by rows).

Well, you could put the sparklines to the left - would people really want to scroll right to see them in a very wide table? (if you want to keep them visible when they do scroll to see more data, just use freeze panes)

Maybe use some VB to find out how wide the PT is and create the sparklines in the right place whenever the PT is changed?

Possibly put the sparks way out of the way, then copy a picture of them and paste a link to it - the new easy way of doing the old camera function. You could then move that picture to a suitable location each time (again with VB)

No silver bullet here, really.

---

Adam Vero MCT, MMI, MBMSS:CRM 4, MCSA:Messaging Meteor IT - Training and Consulting Services

Answer 5

I would like to have my sparklines inserted into my pivot table so that as I filter the pivot table I don't have to adjust the sparklines that are outside of the pivottable manually.  Any thoughts on how to add a sparkline into the actual pivottable?

Interesting idea but I cannot think of a way to do this without some series programmatic support.  You *may* be able to do this with a dynamic formula as the source of the sparkline but that too will require a fair amount of work.

An alternative that works "out of the box," at least in my *extremely* limited testing, is to use a data bar (from conditional formatting).  Excel retained the c.f. when I added a new column field (the data bars now applied to all the columns) and when I refreshed the data.

---

Tushar Mehta (Technology and Operations Consulting)

www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)

Microsoft MVP Excel 2000-Present