• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Add sparklines automatically to a pivot table column|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Add sparklines automatically to a pivot table column|General Excel Questions & Answers|Excel Forum|My Online Training Hub

vba course banner

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Add sparklines automatically to a p…
sp_PrintTopic sp_TopicIcon
Add sparklines automatically to a pivot table column
Avatar
MohamedGadAllah
EGYPT - Cairo
Member
Members
Level 0
Forum Posts: 40
Member Since:
December 13, 2021
sp_UserOfflineSmall Offline
1
November 27, 2022 - 9:59 pm
sp_Permalink sp_Print sp_EditHistory

Hi,

I've googled for any way to automatically add a new column to a pivot table and insert sparklines inside it automatically but I was not able to do it automatically.

All I was able to do, was just to inset a sparkline in the cell far after the last column of the pivot table, then drag it till the end.

Is there any way to include it inside the pivot table in order to be updated automatically when data changes?

And if it is not possible, is it possible to insert a formula beside the pivot table that could be updated automatically with my interaction to drag the cell down to update the sparklines?

My point is to get an updated sparkline automatically when data inside the pivot table changed and add new rows automatically.

Thanks

The attached file includes a sheet titled "Sparklines-Inside-PivotTables" for the pivot table. K column includes the manually inserted sparklines, and as you can see I need to manually drag the cells down manually to update the sparklines.

My request please if there is any way to update the sparklines automatically shown in column M. 

Or maybe there is any way to insert a table in the column M and let it get updated automatically according to the contents of the pivot table, then insert the sparklines inside the table cell, so when the pivot table gets updated the newly added tables get updated as well, and in turn, the sparklines get updated as well.

Thanks a lot and too much appreciated your valuable time reading my post and helping me.

sp_AnswersTopicSeeAnswer See Answer
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4515
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
November 29, 2022 - 11:52 am
sp_Permalink sp_Print

Hi,

As you've found, it's not possible to include a Sparkline in a PivotTable. Please see this tutorial on relative dynamic named ranges. Towards the end there is an example that uses Sparklines.

I hope that points you in the right direction.

Mynda

Avatar
MohamedGadAllah
EGYPT - Cairo
Member
Members
Level 0
Forum Posts: 40
Member Since:
December 13, 2021
sp_UserOfflineSmall Offline
3
November 29, 2022 - 4:29 pm
sp_Permalink sp_Print

Thanks a lot Mynda ... I will follow your advice.

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 648
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
4
November 29, 2022 - 7:12 pm
sp_Permalink sp_Print

You can also do it with code. Add this to a normal module:

Sub UpdatePivotSparklines(pt As PivotTable)
    With pt
        Dim sparkRange As Range
        Set sparkRange = .DataBodyRange.Offset(, .DataBodyRange.Columns.Count).Resize(, 1)
    End With
    pt.TableRange1.Worksheet.UsedRange.SparklineGroups.Clear
    With sparkRange.SparklineGroups
        .Add Type:=xlSparkLine, SourceData:=pt.DataBodyRange.Address

With .Item(1)
With .SeriesColor
.Color = 9592887
.TintAndShade = 0
End With
With .Points
.Highpoint.Visible = True
.Lowpoint.Visible = True
.Firstpoint.Visible = True
.Lastpoint.Visible = True
.Markers.Visible = True
With .Negative.Color
.Color = 208
.TintAndShade = 0
End With
With .Markers.Color
.Color = 208
.TintAndShade = 0
End With
With .Highpoint.Color
.Color = 208
.TintAndShade = 0
End With
With .Lowpoint.Color
.Color = 208
.TintAndShade = 0
End With
With .Firstpoint.Color
.Color = 208
.TintAndShade = 0
End With
With .Lastpoint.Color
.Color = 208
.TintAndShade = 0
End With
End With
.Axes.Horizontal.RightToLeftPlotOrder = True
End With
End With
End Sub

 

then right click the pivot table worksheet, choose View Code and paste the following code into the code pane:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
UpdatePivotSparklines Target
End Sub

Macro enabled version of your file attached.

sp_AnswersTopicAnswer
Answers Post
Avatar
MohamedGadAllah
EGYPT - Cairo
Member
Members
Level 0
Forum Posts: 40
Member Since:
December 13, 2021
sp_UserOfflineSmall Offline
5
December 8, 2022 - 5:04 am
sp_Permalink sp_Print sp_EditHistory

Hi,

Thanks a lot for your valuable time helping me.

Excellent indeed.

But, if I've several sheets (about 59 sheets), each one includes a pivot table, and each has sparklines beside it, of course.

Is it possible to set different colors and styles for each sparkling sheet? I do not want to let them all have the same look and theme, as with each pivot table refresh all sparklines reset to the same theme.

Thanks

MGB!

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 648
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
6
December 8, 2022 - 9:31 pm
sp_Permalink sp_Print

I do like the idea of a sparkling sheet! 😉

If you already have the sparklines in place, then the code can be a lot simpler:

 

Sub UpdatePivotSparklines(pt As PivotTable)
   With pt
      Dim sparkRange As Range
      Set sparkRange = .DataBodyRange.Offset(, .DataBodyRange.Columns.Count).Resize(, 1)
   End With
   With pt.TableRange1.Worksheet.UsedRange.SparklineGroups.Item(1)
      Set .Location = sparkRange
      .SourceData = pt.DataBodyRange.Address
   End With
End Sub
Avatar
MohamedGadAllah
EGYPT - Cairo
Member
Members
Level 0
Forum Posts: 40
Member Since:
December 13, 2021
sp_UserOfflineSmall Offline
7
December 9, 2022 - 3:29 pm
sp_Permalink sp_Print sp_EditHistory
Sub UpdatePivotSparklines(pt As PivotTable)
   With pt
      Dim sparkRange As Range
      Set sparkRange = .DataBodyRange.Offset(, .DataBodyRange.Columns.Count).Resize(, 1)
   End With
   With pt.TableRange1.Worksheet.UsedRange.SparklineGroups.Item(1)
      Set .Location = sparkRange
      .SourceData = pt.DataBodyRange.Address
   End With
End Sub

Is this code to be added after the last line of the original code above?

Or it is to replace the old code with the new one?

If you already have the sparklines in place, then the code can be a lot simpler:

Please note that I can not determine the future growth of the pivot table growth, as many rows are to be added later on.

I've tried to paste the above code after the end of the original code but got an error.

Thanks a lot and much appreciated your valuable time helping me my friend.

MGB

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 648
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
8
December 10, 2022 - 6:01 pm
sp_Permalink sp_Print

That routine replaces the previous one of the same name. The worksheet code that calls it remains the same.

 

as long as there are spark lines on the pivot sheets it should work.

Avatar
MohamedGadAllah
EGYPT - Cairo
Member
Members
Level 0
Forum Posts: 40
Member Since:
December 13, 2021
sp_UserOfflineSmall Offline
9
December 10, 2022 - 6:18 pm
sp_Permalink sp_Print

Thanks a lot for your valuable time replying to my thread and helping me.

But if I want to merge both VBAs into one single code, what would it be?

The reason for asking is that I need to add the sparklines to other workbooks and I want to randomize its theme.

So I want to keep the full code to achieve the 2 things in the same newly created workbook.

Thanks a lot.

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 648
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
10
December 12, 2022 - 4:32 am
sp_Permalink sp_Print

I don’t really understand. One code adds and formats spark lines while the other only adjusts the location and data range of existing spark lines. It doesn’t make sense to combine them.

Avatar
MohamedGadAllah
EGYPT - Cairo
Member
Members
Level 0
Forum Posts: 40
Member Since:
December 13, 2021
sp_UserOfflineSmall Offline
11
December 13, 2022 - 2:31 am
sp_Permalink sp_Print
Thanks a lot for your valuable time helping me.

Please, I mean if I am creating a new workbook, and this new workbook has several sheets.

Each sheet includes a pivot table and no sparklines had been added yet at all.

And I want the format or the theme of the newly added sparklines for each sheet beside its associated pivot table to be a random theme.

So, I am asking what would be the code to do both tasks for the new workbook that has a pivot table only.

1st task: Add sparklines.

2nd task: Set the sparklines theme to be a random theme.

Sorry if my poor English does not help me to express myself correctly.

Thanks a lot for your help.

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 648
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
12
December 14, 2022 - 11:01 pm
sp_Permalink sp_Print

Unfortunately there is no option in VBA to simply apply a theme to a sparkline. You have to individually set the colours of the line, marker, high point, low point, first point and last point for example. Doing that randomly will most likely produce terrible combinations a lot of the time. I would personally suggest that you pick a theme and stick to it.

Avatar
MohamedGadAllah
EGYPT - Cairo
Member
Members
Level 0
Forum Posts: 40
Member Since:
December 13, 2021
sp_UserOfflineSmall Offline
13
December 15, 2022 - 3:40 am
sp_Permalink sp_Print

Thanks a lot.

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 648
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
14
December 15, 2022 - 8:15 pm
sp_Permalink sp_Print

If you really wanted to pursue it, one option might be to create arrays with related colour values in (eg one has colour values for the line, one for the markers and so on) then use something like Randbetween to select a particular item from each array. At least that way you can design some combinations that work well beforehand.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: ash seth, Andrew Wilkie, Scott Miller, Les Harold
Guest(s) 6
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 217
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
David du Toit
leandro barbarini
Melanie Ford
Isaac Felbah
Adele Glover
Hitesh Asrani
Rohan Abraham
Anthony van Riessen
Erlinda Eloriaga
Abisola Ogundele
Forum Stats:
Groups: 3
Forums: 24
Topics: 6356
Posts: 27795

 

Member Stats:
Guest Posters: 49
Members: 32326
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.