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 firstname.lastname@example.org
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:
- 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.
- 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.
- 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.
- 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.
- 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.
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.
Recommended Excel Tools
In addition to all the free Excel tips and tutorials, there are other Excel tools that you can invest in. To learn more about the products listed below, click on the links to take a look at their features, and decide if they're right for you.
- Contextures PivotPower Premium Add-in
- Contextures 30 Excel Functions in 30 Days
- Excel Online Course
- Excel Charting Tools
- Excel Dashboard Kits
- Excel Project Management Templates
- Excel VBA School
Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.
Contextures Inc., Copyright ©2017
All rights reserved.