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
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:
- 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.
- 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.
- 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.
- 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.
- 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! ;-)
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.