Search Contextures Sites

 

 

Contextures
Newsletter Index

 

 

 

 

30 Excel Functions in 30 Days

 

 

 

Contextures News

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 End

To 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 Automatically

You 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 Source

If 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 today

Chandoo 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 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.

 

 

Privacy Policy

 

Contextures Inc., Copyright 2016
All rights reserved.