Excel Chart Challenge
April 16, 2019
Chart challenge, pivot table formatting, 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: Watch for the next newsletter on April 30th - we'll have a week off for the Easter holiday.
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
Occasionally, you might want to send someone a pivot table - but just the values and formatting, not the full pivot table. That way, you can give them a summary of the data, without the underlying details, which might be confidential.
There's a trick to doing this -- a simple copy and paste won't apply the formatting from the Pivot Table style. Here's one way to do it:
You can also copy and paste in two steps, as shown in the video on my website. There's a macro on that page too, if you need to do this frequently.
Here's a chart challenge for you . In the chart below, the blue bars are correctly sized, until I change cell B3 from 83 to 84. Then, all three bars change size!
Can you figure out why that happens? The answer is below the screen shot. You can download the sample file if you'd like to experiment with the chart.
In the chart, the horizontal axis is hidden, to make the chart look less cluttered. That keeps you from seeing the problem - Excel automatically changes the minimum value on that axis, from zero, to 75.
NOTE: 84 seems to be the trigger point* - changing it to a lower number didn't have the same effect.
Update*: This fits the 5/6 rule that Jon Peltier describes. (Thanks to Miguel C)
There are more chart examples on my website, and if you do lots of charting, check out Jon Peltier's time-saving Excel Charting Utilities.
Here are a couple of Excel articles that you might find useful or interesting.
Excel Tips - Ten top Excel experts shared their best tips for Excel beginners. What top tip would you share? (Level - Intro/Intermediate)
Organization - How many things do you keep track of in Excel? Angela Lashbrook shares her Google Sheets that help her stay organized, and you could set up similar files in Excel (Level - All)
Also see: My Excel Products || Excel Events || Previous Issues || Weekly Humour
I took this flower photo last week, and soon after that, the skies turned darker grey, and pelted us with a mixture of rain and snow. It didn't stay on the ground too long, fortunately, but it's not time to plant our gardens yet, obviously!
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2019/20190416ctx.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.
Debra Dalgleish
dsdalg@ gmail.com
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.
Last updated: April 16, 2019 1:41 PM