Contextures News 20181204

Annoying Excel Problem

December 4, 2018

Show an update warning, fix an annoying problem, 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: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Quick Fix

Here's a quick fix for an annoying Excel problem. If you're building a formula, and click on a pivot table value, Excel might insert a GetPivotData formula, instead of a cell reference. Then, when you copy that formula down, every row shows the same result. For example:

=GETPIVOTDATA("Total",$A$3,"Product","File Folders")

I love the GetPivotData function - it's a great way to pull summarized data without complex formulas. But, if you don't need it, here's now to turn it off, so you get normal cell references:

  • Select a cell in any pivot table
  • On the Excel Ribbon, click the Analyze tab
  • At the far left, click the Options arrow (or click the Pivot Table arrow, then click Options)
  • Then, click the Generate GetPivotData command, to turn the feature on or off.

NOTE: This is a global setting for Excel, not just for the active workbook.

Generate GetPivotData

What Would You Do?

One of my Excel sample files has a data entry sheet, with buttons to add/update database records that are stored on a different sheet (away from the fumbly fingers of your co-workers!)

Someone asked how to remind people to save their changes, before wandering off (to a different sheet or record). That's an interesting question! How would you get people to save their changes?

Data Entry Worksheet Form

My first thought was a macro -- Excel can run code when an "event" occurs, such as moving to a different sheet, or closing a file. But co-workers are tricky! No matter how many events we tried to catch, Joe in Accounting would do something unexpected.

So, my solution uses formulas and conditional formatting, to highlight any changes, and show an warning on the worksheet. If someone made changes, we'll remind them to save, but we won't track their every move, or nag them to save. That could slow down the workbook, and annoy everyone who uses it.

Off to the right, there are INDEX/MATCH formulas, to pull the record's current data from the database. The labels have conditional formatting that shows orange if the new value is different from the stored value.

Also, a message appears below the buttons, if anything has changed, with a bright yellow background. What do you think -- would that help your co-workers remember to save?

Data Entry Alert

To get a copy of the workbook, go to the Data Entry and Update Form page, and click on Download #6 (With Alerts). There are notes in the file, to explain how the alerts work.

Previous News

Quick Pick List: Last week, I showed you a Quick Pick list, and thanks for letting me know that you can also use Alt+Down Arrow to open the list. I made a short video to show how that works.

Advent Calendar: There was a no-macros Advent calendar in last week's news, and I've made another version, with fancier pictures. Get both versions on my Excel Samples page, in the Conditional Formatting section. They're files CF0011 and CF0010.

Excel Articles

Here are a few Excel articles that you might find useful or interesting.

Charts - Philip Treacy created a free Excel add-in that lists all the charts in a workbook, and lets you export them as images. Or, to export items from a specific sheet, try Andy Pope's graphics exporter. It shows a preview of the selected item, and works with shapes, charts, Slicers, and more. (Level - All)

Data Analysis - Alex Novet has a great intro to data analytics, for any topic, not just hockey. (Link found in David Napoli's newsletter) (Level - Int/Adv)

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

High Tech Low Tech

Last month I got a new iPhone, and when I take a photo, it also creates a little video file. Here's an animated gif, to show an example. My granddaughter had never seen a Bissell sweeper before, and was amazed that something so "low tech" actually worked!

Finally, Google showed me how to fix the photo problem. Open the Camera app, and at the top, tap the icon that looks like a yellow target. That icon turns the Live Photo feature on (yellow) or off (white).

weekly photo

NOTE: To read this newsletter online, paste this URL into your web browser:
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

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: December 3, 2018 9:47 AM