Search Contextures Sites
Excel Weekly News from Contextures Sept 4, 2012
Fix drop downs that open at the end, create different pivot tables from the same data, and other tips, in this week's Excel news from Contextures.
Excel Drop Down Opens At EndTo make data entry easier, create a drop down list in an Excel cell. Sometimes the drop down opens with a blank selected at the end of the list, instead of showing the top of the list.
To prevent this problem, don't leave blank cells at the end of the range used for the drop down list items.
Instead, create a dynamic named range, that changes size automatically, if you add or remove items.
There is a video that shows you the steps for creating a dynamic named range.
Visit my Contextures blog for a sample file, and written instructions: Excel Drop Down Opens At End
Update Specific Pivot Tables AutomaticallyYou can use programming to update multiple pivot tables automatically -- select items in one pivot table's Report Filter fields, and the Report Filter fields for pivot tables on the other worksheets will change to the same selections.
My old code for this has been updated by Jeff Weir, and it runs much faster, especially in large pivot tables.
Read the details on my Contextures Blog, and download the sample file: Update Specific Pivot Tables Automatically
Grouped and Ungrouped Dates from Same Pivot Table SourceIf piovt tables share the same pivot cache, they also share features such as grouping and calculated items.
If you want different grouping in one of the pivot tables, you can create a different pivot cache. You can manually create a separate pivot cache, or use programming, like the sample code in this article on my Pivot Table Blog.
Read the details, and see the sample code here: Grouped and Ungrouped Dates from Same Pivot Table Source
Best Practice Modeling - Make these 5 changes todayChandoo is starting a series on spreadsheet modeling, and the first article lists five simple Best Practice Modeling concepts that you can start using today.
Read the article here: Best Practice Modeling - Make these 5 changes today
Recommended Excel ToolsThey 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.
- Contextures 30 Excel Functions in 30 Days
- Excel Charting Tools
- Excel Dashboard Kits
- Excel Project Management Templates
- Excel VBA School
- Excel Pivot Table Intro Course
Note: I am an affiliate for the products mentioned in this newsletter, and earn a commission on the sales.
Contextures Inc., Copyright ©2014
All rights reserved.