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.
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:
Here are the steps to change the format in the pivot chart only, without affecting the pivot table:
To see the written steps, and to watch my video, go to the Pivot Chart Number Format page on my Contextures site.
And here is a short video with the pivot chart number formatting steps, from my Pivot Chart Formatting page
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.
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
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.
And here is a short video with formula auditing tips, from my Audit Excel Formulas page
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
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."
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.
Last updated: April 4, 2021 10:03 PM