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 firstname.lastname@example.org
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
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
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
Here are a few more Excel articles that I read this week, that you might find useful:
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
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! ;-)
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.
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.