#Excel trendline missing data code
#Excel trendline missing data download

The file will work in Excel 2010, but sparklines are not available in earlier versions.HereвЂ™s a common data visualization challenge: SlidesВ with some, but not all, of the chronological data included. In the Charts section, look for CH0007 вЂ“ Show Sparklines for Hidden Data.

#Excel trendline missing data download

To download the sample file, please visit the Excel Templates page on my Contextures website. And I hope it helps you too! Sub SparklinesFix()ДЇor Each spk In

#Excel trendline missing data code

The code worked really well, and it should come in handy if I create sparklines in a different workbook. So, I decided to write a macro that would change all the sparkline groups on the active sheet. I tried selecting two sparkline groups, to change their settings at the same time, but the Edit Data command isnвЂ™t available in that situation. In the example shown here, there are only two sparkline groups, but in my actual file there were about 20 groups. So, I had to follow the same steps to open the dialog box, and change the Revenue sparkline settings. In Excel, you can press the F4 key to repeat the last action, but that doesnвЂ™t work for sparkline settings, apparently.

Next, I had to change the Revenue sparkline group settings, so I selected those cells. Hurray! The Expense sparkline group reappeared. In the Hidden and Empty Cell Settings dialog box, I clicked Show Data in Hidden Rows and Columns, to add a check mark, and clicked OK. There was a Hidden & Empty Cells command, which sounded promising, so I clicked that. I selected the Expense sparkline group, and on the Ribbon, under Sparkline Tools, clicked Edit Data. Regular Excel charts have a setting that you can change, to show hidden data, so I figured that Sparklines must have a similar setting. Unfortunately, the sparklines disappeared too! Once the sparklines were set up, I decided to clean up the sheet, so I hid the columns with the sparkline data. That worked perfectly! I then set up the Revenue sparklines in column D. Next, I selected the cells where the sparklines should appear, and selected the Expense data in the linked range. Then, I used formulas to link to the existing data, and show an NA if there was no city name in column B. To create a contiguous range of data that the sparklines could use, I set up Expense and Revenue sections to the right of the existing data. When I clicked OK, an error message appeared - вЂњThe reference for the location or data range is not valid.вЂќДЄpparently the sparklines need a contiguous range, and wonвЂ™t work with non-contiguous ranges. In the Create Sparklines dialog box, I selected the first range of expense cells, then pressed the Ctrl key, and selected the other two ranges. So, I selected those cells, and on the RibbonвЂ™s Insert tab, click the Line command in Sparklines.

In this example, I had expenses and revenue in alternating columns, and I wanted expense sparklines in cells C4:C14. It didnвЂ™t go smoothly at first, but I finally got things working, and then I ran into another hurdle! HereвЂ™s what went wrong, and how I fixed things.

Do you use the sparklines that were introduced in Excel 2010? Last week, I was building a dashboard, and wanted to show sparklines for expenses and revenue.