Chart Problem Fix
March 26, 2019
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
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.
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.
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
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.
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.
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)
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.
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2019/20190326ctx.html
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.
Last updated: March 23, 2019 12:11 PM