Contextures News 20190326

Chart Problem Fix

March 26, 2019

Easy way to get data, chart problem fix, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Button Macros

Need an easy way to collect data? Click a worksheet button, and a macro stores the date and time, your name, and the button number, in a list on another sheet. This sample is for a stress test, but you could collect other types of data too.

click buttons to collect data

There's only one macro in the workbook -- AddButtonData -- and all 3 buttons run that macro. When you click a button, the macro adds a new row in the list, shown below.

data from button clicks

To get the button number, the macro uses the following line of code -- Application.Caller is the button that was clicked:

BtnText = .Buttons(Application.Caller).Caption

There are details on my Contextures Blog, for setting up the buttons and macros, and click here to download the sample file.

Chart Problem Fix

If you create a chart from worksheet data, and then filter the data, some of the chart date might disappear. Sometimes, that's what you want -- filter by Region, and see only the East's sales in the chart.

In other charts, you want to see all the data, all the time, even if some of the data is hidden. Here's how to fix a chart, so it isn't affected if some data is hidden.

  • Select the chart, and on the Excel Ribbon, click the Design tab
  • Click the Select Data command
  • Click the Hidden and Empty Cells button
  • Add a check mark to 'Show data in hidden rows and columns'

show hidden data in chart

There are detailed steps, and a video, on my Contextures Blog. There's an embedded Excel file there too, where you can see an example of filtered chart data.

If you need to apply this "Show data in hidden rows and columns" to lots of charts, click here to download my sample file with macros that quickly turn that setting on or off.

Excel Articles

Here are a couple of Excel articles that you might find useful or interesting.

Dashboards - Chandoo shows how to create dashboard tiles in Excel, using shapes that are linked to cells (Level - Intermediate)

Prediction - The Excel team at Microsoft built a college basketball prediction spreadsheet, and even if you don't follow that sport, take a look at the file, to see how it works. There's always something interesting to learn! (Level - Int/Adv)

Also see: My Excel Products || Excel Events || Previous Issues || Weekly Humour

Maple Leaf?

It's officially spring now, here in Canada, but there are still a few piles of snow in our yard. I noticed this leaf, on a sheet of ice, and wasn't sure if it's from a maple or sycamore tree. A bit of research (Googling) showed that I'm not the only one who's confused. One arborist claims that our Canadian penny, now discontinued, had sycamore leaves, not maple leaves! I hope they got our flag right.

weekly photo

NOTE: To read this newsletter online, paste this URL into your web browser:
I'll also post any article updates or corrections there.

That's it for this week! If you have any comments or questions, send me an email.

Debra Dalgleish

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.



Get weekly Excel tips from Debra


pivot power free






Last updated: March 23, 2019 12:11 PM