Excel Weekly News from Contextures July 2, 2013

Dependent drop down lists + more Excel tips

In this week's Excel news, you'll see how to create dependent drop down lists from a sorted list, show percent of subtotal in a pivot table, and other tips. Thank you for reading the Excel news!

 -  Debra ddalgleish@contextures.com

Calculate Loan Payments with PMT Function

Even if you're not a financial wizard, you've probably had to figure out a loan payment at least once in your life. Fortunately, Excel makes it easy, with the PMT function.

Enter the rate, number of payments, and loan amount, and see the monthly payment. You can also enter a future value, and payment type, but those are optional.

Click here to see the instructions and video: Calculate Loan Payments with PMT Function

Show Percent of Subtotal in Pivot Table

With custom calculations in a pivot table, you can quickly add a running total, show differences between amounts, and other calculations.

In Excel 2010, a few new custom calculations were added, including % Parent Row Total and % Parent Column Total. These two calculations let you see an item's percentage, based on its parent's subtotal amount.

In earlier versions of Excel, you can only see each row as a percentage of the total, not a subtotal, so this is a useful new feature.

Click here to see the details, and to watch the video: Show Percent of Subtotal in Pivot Table

Dependent Drop Down Lists in Excel

In Excel, you can set up drop down lists that show items based on the selection made in another cell. In this example, you select a region in column B, and only the customers in that region are in the drop down list in column C.

To set this up, you create a sorted list on the worksheet, and use the OFFSET function to pull the related items.

Click here to see the details, and to download the sample file: Dependent Drop Down Lists in Excel

More Excel Tips

Here are a few more Excel articles that I read this week, that you might find useful:

  1. On her blog, Annielytics shows how to fix the thing that annoys her most about pivot tables -- Autofit Column Width. And if you have a copy of my PivotPower add-in, there is a button on its Ribbon tab, to quickly turn Autofit on or off.
  2. 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.
  3. Over at the Daily Dose of Excel blog, there's an interesting discussion on How to Be Great at Excel. Read Dick Kusleika's short article, and add your opinion in the comments section.
  4. The Excel Web App has been updated, and you can learn about the new features on the Microsoft Excel team's blog. I'm glad to see that they've finally added data validation.
  5. Chandoo shares 5 keyboard shortcuts for writing better formulas. Maybe there are a couple that you could use to speed up your Excel work.

Video: Show the Developer Tab in Excel 2010

In this video you'll see how to show the Developer tab on the Excel Ribbon. With the commands on the Developer tab, you can open the Visual Basic Editor, see a list of macros, add controls on the worksheet, and record a macro.

For more info on recording macros, please visit my Contextures website: Record and Test a Macro

A Little Blurry

My 3-year-old granddaughter took my picture last week, but she wasn't satisfied with her first attempt. "It's a little blurry," she said. When I looked at the picture later, I saw what she meant! ;-)


Contextures Products

Save time with our "Done For You" Excel products.


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




Privacy Policy


Contextures Inc., Copyright 2019
All rights reserved.