Contextures

Contextures News 20190604

Excel Pivot Table Fix

June 4, 2019

Pivot table fix, strange blank cells, 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.

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.

Blank or Not?

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.

strange blank cells

If you run into this problem, here's a quick way to make those cells "really" blank:

  • Select cells C2:C8
  • On the Excel Ribbon, click the Data tab
  • Click the Text to Columns command
  • In Step 1, select Delimited, then click the Finish button

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.

Pivot Table Fix

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.

old items in pivot table drop down

To fix the problem, and keep old items from appearing again, change a pivot table setting:

  1. Right-click a cell in the pivot table, and click PivotTable options
  2. Click on the Data tab
  3. In the Retain Items section, select None from the drop down list.
  4. Click OK, then refresh the pivot table.

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.

And if you have my Pivot Power Premium add-in, or Pivot Power Free, there's a "Clear Old Items" button that quickly changes the setting in the selected pivot table.

Excel Articles

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)

Also see: My Excel Products || Excel Events || Previous Issues || Weekly Humour

It's the Thought

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.

"I folded the sheets," he said. "But not exactly like you'd do it." (It's the thought that counts!) And really, nobody can fold fitted sheets nicely. (comic link h/t Jon Peltier)

weekly photo

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.

Debra Dalgleish
dsdalg@ 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.

 

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: May 30, 2019 3:28 PM