Contextures

Contextures News 2021-03-23

Excel Formula Troubleshooting Tips

March 23, 2021

Pivot chart number format, formula troubleshooting, 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.

Thank you for reading the news, and you'll get the next email on April 6th.

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

Pivot Chart Number Format

When you make a pivot chart, it starts out with the same number format that's in the pivot table. My latest video shows how you can change that number format:

  • in both the pivot chart and the pivot table
  • OR, in the pivot chart only

Here are the steps to change the format in the pivot chart only, without affecting the pivot table:

  • In the pivot chart, right-click a number in the axis, and then click Format Axis.
  • In the Format Axis pane, on the Axis Options tab, click the arrow to the left of Number, to see the options
  • Choose a Category from the drop down list
    • In the video I used a Custom format for thousands: #,"K";-#,"K"
  • Close the Format Axis pane

To see the written steps, and to watch my video, go to the Pivot Chart Number Format page on my Contextures site.

pivot chart with different number format

And here is a short video with the pivot chart number formatting steps, from my Pivot Chart Formatting page

Formula Troubleshooting

What's your favourite way to troubleshoot Excel formulas? I like to click in the formula bar, so Excel colour codes the precedent cells on the worksheet.

go to precedent cell shortcut ctrl + [

Or, to go to the precedent cells, use this keyboard shortcut: Ctrl + [

That shortcut will even take you to a cell on a different worksheet, if it's the first reference in the formula.

To see more formula troubleshooting tips, go to the How to Audit Excel Formulas page on my Contextures site

go to precedent cell shortcut ctrl + [

You can also use a macro by Dermot Balson, to see where formulas have been copied down, across, or both. That makes it easy to spot cells where a formula is missing or different. The colour coding is done on a new sheet, so it doesn't mess up your worksheet!

To get Dermot's macro, and read more about it, go to the Formula Cell Colour Code Macro page on my Contextures site.

formula colour code macro shows where formulas were copied

And here is a short video with formula auditing tips, from my Audit Excel Formulas page

Excel Articles

Here are a few Excel-related links that you might find useful or interesting.

Charts: Jon Peltier shows how to make a histogram, using Excel's new functions, like SORT, UNIQUE and SEQUENCE. Also, for a fun look at charts, and other topics, check out Sketchplanations. (Level - Int/Adv)

Excel Tools: Jan Karel Pieterse has a new version of his GitHub VBA Exporter tool. NOTE: Even if you don't need that tool, take a look at the other free Excel files on that page, like AutoSafe, FlexFind, Follow Cell Pointer, and more. (Level - All)

Also see: Previous Newsletter Issues

Celebrations

Happy Spring (or Fall)! We had a virtual St. Patrick's Day party last week, but the food and beer were real, thank goodness! The chef, from Mississauga's Stonehooker Brewery, made the sausage for the appetizer, and it was delicious, as was the rest of the meal.

We also changed to Daylight Saving Time last week, and I agree with this cartoon that says, "Car clock - "Not worth it. Wait 6 months."

weekly photo

NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2021/20210323ctx.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
dsd @contextures.com
ctxdebra @gmail.com

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.

contextures newsletter info

 

Last updated: April 4, 2021 10:03 PM