Search Contextures Sites

 

Contextures
Newsletter Index

 

Contextures
Excel news
by email

 

 

 

30 Excel Functions in 30 Days

 

 

 

Contextures News

Excel Weekly News from Contextures Nov 6, 2012

Show interactive drop lines in a chart, create pivot table copies automatically, and other tips, in this week's Excel news from Contextures.

 -  Debra ddalgleish@contextures.com

Allow Other Entries With Excel Drop Down List

If you add drop down lists in a workbook, it's easy for people to enter data, by selecting items from the list. In some cases though, you might want to allow other entries in those cells, in addition to the items in the drop down list.

To allow other entries, you can turn off the Error Alerts in the data validation cells.

To see the steps for changing this setting, please click here: Allow Other Entries With Excel Drop Down List

Create Worksheet for Each Pivot Item

With a built-in feature in Excel, you can automatically create copies of a pivot table, on new worksheets, named for items in a pivot table's Report Filter.

If you want to copy all the other content from the original worksheet, along with the pivot table, you can use the sample code on my Contextures site on my Contextures site. My code adds a "PT_" prefix to the sheets, and deletes any existing "PT_" sheets, if necessary.

You can read the details, and find a link to my sample code, here: Create Worksheet for Each Pivot Item

Find Email Address With Index and Match

My daughter needed some help with Excel, while planning events for Movember Canada. We created and INDEX and MATCH formula that found the city, based on a person's email address in a master list.

See how we solved the Excel problem, and learn a bit about Movember: Excel and Movember Moustaches

Programmers Want Less Stupid And More Programming

Here is another thought-provoking article from Andrew Wulf, in which he talks about motivating programmers. What makes you work harder, and stay loyal to a company? Does this sound like you:

"the real motivator is writing great code, building cool products, and solving nasty problems."

Here is the link to the article: What Programmers Want Is Less Stupid And More Programming

Interactive Drop Lines on Excel Charts

On his Clearly and Simply blog, Robert Mundigl shows us how to add interactive drop lines in an Excel chart. Click on a data point, and lines connect that point to one or two of the axes.

This technique requires programming, and there are examples for line, bar, scatter and column charts.

See the details here, and download a sample file with the charts and VBA code:

Interactive Drop Lines on Excel Charts

Recommended Product: Excel Dashboard Course

Remember, you only have a few more days to register for the online Excel Dashboards Course, offered by Mynda Treacy at My Online Training Hub.

This comprehensive Excel Dashboard Course will teach you the simple techniques you can apply in Excel to make killer dashboards that will set your skill level apart from the crowd.

Sign up now, to save 20%. This early-bird discount ends tomorrow, Nov. 7th, at 8 PM Pacific time, and registration closes on November 13th.

You can see the course details and a sample video here: Excel Dashboards Course

I highly recommend this training, to improve your dashboard skills. Go at your own pace -- you'll have 12 months of access to the videos and sample files. Or, go for one of the options that includes downloadable videos, so you can watch them offline, at any time.

Excel Dashboard

Recommended Excel Tools

They aren't free, but these Excel tools are a worthwhile investment, so please click on the links to take a look at their features, and decide if they're right for you.

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

Learn how to create Excel dashboards.

 

Privacy Policy

 

Contextures Inc., Copyright 2014
All rights reserved.