Excel Pivot Table Fix
June 4, 2019
You'll get my next newsletter in 2 weeks -- we're on the summer schedule now.
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
I used to import lots of data from Microsoft Access into Excel, and it often had strange blank cells. They looked empty, but Excel's ISBLANK function said that they weren't. Even stranger - the COUNTA function included those cells in its totals, and the COUNTBLANK function counts them as blanks.
See more COUNTA examples, and other count functions on my Contextures site.
If you run into this problem, here's a quick way to make those cells "really" blank:
See more solutions (manual and macros) on my Contextures blog. There's a video on that page too, and you can download the sample file with the strange blank cells.
After you delete data from a pivot table's source, those old items might still appear in the pivot table's drop down lists. For example, the East and Central regions were merged, and "Central" isn't in the data now.
After refreshing the pivot table, the Central region disappeared from the headings, but it's still in the Region drop down.
To fix the problem, and keep old items from appearing again, change a pivot table setting:
See other ways to fix the pivot table old items problem on my Contextures site.
NOTE: If you're using Excel 2019 or Excel for Office 365, you can make this a default setting for new pivot tables. In Excel Options, click Data, click Edit Default Layout, click PivotTable Options.
Here are a couple of Excel articles that you might find useful or interesting.
Spreadsheets - Did you ever have to work with paper ledgers? The BBC looks back at VisiCalc - computing's first "killer app". (Level - All)
Excel Uses - Excel is used for all kinds of things, and some new businesses are created by "unbundling" those tasks. For example, Tableau and QuickBooks created specialized products for charts and payroll. There's an interesting "unbundling" graphic near the top of the article. (Level - All)
Life isn't all glamour, and having fun with Excel. No, there are household tasks to do too, like washing and folding laundry. If you're lucky, there's someone to help with the chores.
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2019/20190604ctx.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: May 30, 2019 3:28 PM