Excel Weekly News from Contextures Aug 06, 2013
Multi-column drop down lists + more Excel tips
In this week's Excel news, you'll see how to show multiple columns in a drop down list, view survey responses in a pivot chart, and other tips. Thank you for reading the Excel news!
- Debra firstname.lastname@example.org
Array Formulas Book Giveaway
The winner of last week's giveaway, for Jon Peltier's Excel Chart Utility, was Krystal, who posted comment #11.
This week, you can enter the giveaway for a chance to win Mike Garvin's new book, Ctrl+Shift+Enter: Mastering Array Formulas. The publisher, Bill Jelen (aka Mr. Excel), has donated 4 copies of the book as prizes.
Go to my blog post, read the rules, and add your comment, to enter the draw for this utility. The deadline is Wednesday, August 7th, at 12 noon, Eastern time.
Click here to see the details, and to enter the giveaway: Array Formulas Book Giveaway
Show Survey Results in a Pivot Chart
Survey data is shown in a pivot chart, in this week's sample file. Select one of the questions from a drop down list, and see that question's responses, by years of service and by department.
There is one line of code that refreshes the pivot tables, but you could do that step manually, if you don't want to use macros in your file.
Click here to see the details, and to download the sample file: Show Survey Responses in Pivot Chart
Multiple Columns in a Drop Down List
With data validation, you can create a drop down list of items, but it can only show a single column. With a combo box, you can customize the drop down list, and show multiple columns. In this example, see a customer name, and the address information too, so you can select the right location for an order form.
Click here to see the details, and download the sample file: Show Multiple Columns in Excel Drop Down List
More Excel Tips
Here are a few more Excel articles that I read this week, that you might find useful:
- If you'd like to add some finishing touches to your dashboards, Mike Alexander, from Bacon Bits Blog, shows how to Enhance Dashboards with Shape Layering Tricks
- On Microsoft's MVP blog, John White shows how to analyze SQL Server data in Excel, using Power Pivot and Power View.
- 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.
- On Microsoft's Excel Team blog, you'll see how you can use data validation in the Excel Web App.
- Old items can linger in a pivot table, and Dick Kusleika shows how to avoid programming problems that those "ghost" items can cause, when looping through PivotItems.
Upcoming Excel Courses
Chandoo's online PowerPivot course, starts on August 19th, and you can register now. In addition to the basic course, there is a new advanced level course, taught by Rob Collie, who used to work on Microsoft's PowerPivot team. This is a great opportunity to learn from the best!
Mynda Treacy's Excel Dashboard course, is also ready to start, and enrollment closes this Friday, August 9th. The course is video based, delivered online and is available 24/7. Click here for details of the course, and watch the 'behind the scenes' video that shows you what you'll receive as a member. Former students have given the course rave reviews.
Video: Copy a Custom PivotTable Style
If you create a custom pivot table style in a workbook, there isn't a built-in way to copy that style to a different workbook. However, with the technique shown in this video, you'll be able to move that custom style in just a few easy steps. Watch this short video to see the steps.
To learn more about pivot table formatting, please visit my Contextures website: Pivot Table Formatting
Giant Glass Spreadsheet
I was at the local mall on Saturday morning, and snapped this picture. As you can see on the clock in the background, it was bright and early -- just before 9 AM. To me, the roof looked like a giant glass spreadsheet, but maybe that's a sign that I should get away from the computer more often! ;-)
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.