Contextures News 20190416

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.

Copy Pivot Format

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:

• Select the pivot table, and copy it
• Click the cell where you want to paste the copy.
• On the Excel Ribbon's Home tab, click the Dialog Launcher button in the Clipboard group
• In the Clipboard, click on the pivot table copy, in the list of copied items
• The pivot table's values and formatting are pasted in the new location

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.

Chart Challenge

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.

• To fix the problem, add the horizontal axis to the chart again. (Add Chart Element > Axes)
• Right-click on the horizontal axis, and click Format Axis
• On the Axis Options tab, click the Axis Options icon
• Under Bounds, set the Minimum to zero.

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.

Excel Articles

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

Grey Skies

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
[email protected] 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.