Excel Weekly News from Contextures Sep 17, 2013

Block duplicate entries + more Excel tips

In this week's Excel news, you'll see how to block duplicate entries, and other tips. Thank you for reading the Excel news!

 -  Debra ddalgleish@contextures.com

Excel Dashboard Tool Giveaway Winners

The two winners of last week's giveaway, for Mike Alexander's new Excel Dashboard Tool, are Bruce H. Johnson and Andrea R. Congratulations!

That's the last of our summer giveaways, and thank you for participating. I hope you enjoyed the contests, and I'll try to organize more of them in the future.

Click here to see the details: Dashboard Tools Add-In Giveaway Winners

Duplicate Items Appear in Pivot Table

Pivot tables are perfect for summarizing a large amount of data -- they roll thousands of records into a single line, and show the total amounts. Occasionally, you might see duplicates in a pivot table, instead of a single summarized item, like the Boston amounts shown below.

Usually, this is caused by extra spaces at the end of an item, and you can clean up these trailing spaces to fix the problem.

Click here to see the details: Duplicate Items Appear in Pivot Table

Prevent Duplicate Entries in Excel Table

To prevent duplicate entries in an Excel column, you can use data validation, with a custom formula. In this example, employee data is being entered, and each employee must have a unique ID number.

With data validation, using the COUNTIF function, a warning will appear if a duplicate number is entered. This video shows you the steps for creating a formatted table, naming one of the columns, and setting up the data validation.

Click here to see the details, and watch the video: Prevent Duplicate Entries in Excel Table

More Excel Tips

Here are a few more Excel articles that I read this week, that you might find useful:

  1. On the Daily Dose of Excel blog, Dick Kusleika explains how he created dynamic named ranges based on table columns. You can use these names in formulas, such as VLOOKUP.
  2. Jon Peltier shows how to Fill Under or Between Series in an Excel XY Chart. You can use this technique to show a target range for the chart's data.
  3. For a humorous peek at what other people are saying about Excel, read this week's collection of Excel tweets, on my Excel Theatre blog.
  4. If you're using Excel 2013 Pro Plus, you can download a preview copy of Microsoft's new Power Map add-in. It lets you plot data based on geographical indicators, such as postal code, country or longitude and latitude.
  5. In Excel's Conditional Formatting, you can use the "Stop If True" checkbox, to prevent all of the conditions from being tested. Mike Alexander shows how to use that setting with the Icon Sets, so only the below average cells are marked with an X.

Video: Create Very Hidden Worksheets

If you don't want people to see a worksheet, you can right-click on its sheet tab, and click Hide. This isn't a security feature though -- it's just as easy to unhide a sheet! If you want to make it a little tougher to see a sheet, you can change its properties to make it Very Hidden. This video shows you the steps, and you can read the details in this blog article.

Abundant Tomatoes

It's almost fall, here in Canada, and our garden is full of ripe tomatoes. It's full of weeds too, but we won't talk about that! ;-) I usually make tomato sauce, and store some in the freezer, so we can enjoy the garden crop in the winter months too.


Contextures Products

Save time with our "Done For You" Excel products.


Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.




Privacy Policy


Contextures Inc., Copyright 2019
All rights reserved.