Contextures

Contextures News 20191029

Excel Stop Annoying Warning

October 29, 2019

Highlight trends, stop annoying message, 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.

Annoying Warning

In a few of my Excel files, a warning message pops up, when I click the Save button - "Be careful! Parts of your document may include personal information that can't be removed by the Document Inspector."

personal information warning

Even after I removed personal information, the message kept popping up. Finally, I found a way to get rid of that warning, so maybe this tip will help you too!

NOTE: This setting affects the active workbook only. Repeat the steps in other workbooks, if they show the same message.

  1. On the Excel Ribbon, click File, then click Options
  2. Click Trust Center, then click Trust Center Settings button
  3. Click Privacy Options
  4. In the Document-specific Settings section, remove the check mark from "Remove personal information from file properties on save"
  5. Click OK, twice, to exit the Option settings

Get more Workbook tips on my Contextures site.

Spot the Trends

If you have a long list of daily data, it can be hard to spot trends, as you scroll through the list. Maybe you're keeping track of sales per region, or machine downtime, or other numbers and text entries.

Here's how I use conditional formatting, to show the trends in my daily weather records. Those two hot and sunny days from last October really stand out!

weather data conditional formatting

There are two types of conditional formatting in the list:

  • Maximum temperature column has a Red-White-Blue Color Scale.
  • Weather column has 4 conditional formatting rules, to show fill colour based on the weather type - Sun, Cloud, Rain or Snow

The rules use an INDEX/MATCH formula that refers to a named Excel table with weather types (WthTypeList) and weather descriptions (WthDescList). Here's the Snow rule, and if the result is True, the cell gets light blue fill colour.

=INDEX(WthTypeList, MATCH(D4, WthDescList,0))="Snow"

conditional formatting rules

There are setup details on my Contextures blog, and you can download the weather data sample file from my Contextures site.

Excel Articles

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

Zombies: Here's a Halloween treat from David Ringstrom - a free 1-hour "Zombie Excel" webinar. See old features that were killed off, and some that can be resurrected. Thursday, Oct 31, 2 PM Eastern time. (Level - All)

Data Analysis: Detailed steps from Lisa Charlotte Rost, show how to prepare your data for analysis and charts. Data preparation takes work, but it's worth it! (Level - All)

Excel Tweets: See what people tweeted about Excel this week. Do you use Excel for "non-number" things?

Also see: My Excel Products || Previous Issues

Pumpkin Carving

It's almost Halloween, so my grandson and I carved pumpkins on the weekend. My grandson made a scary jack-o'-lantern face, and I carved a frightening Excel face on mine -- a V mouth, with eyes that look up and shoot laser beams. Do you think the trick-or-treaters will be afraid of the VLOOKUP pumpkin? Maybe their parents will !

weekly photo

NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2019/20191029ctx.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

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

 

Related Links

 

Excel Workbook Tips

Red-White-Blue Color Scale

INDEX/MATCH formula

Weather Data Conditional Formatting

 

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: October 28, 2019 11:30 AM