Contextures News 20191105

Excel Latest Payment Highlighted

November 5, 2019

Pivot table tip, latest payment highlighted, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Pivot Table Tip

After you build a pivot table, here's a quick way to remove a field from the worksheet layout, if you don't need it there anymore:

1. In the pivot field that you want to remove, right-click the heading cell, or any item in that field
2. In the pop-up menu that appears, click on Remove [your pivot field name]

In this screen shot, I right-clicked on Binder, to remove the Product field.

Add a Pivot Field: You can also use the right-click menu to add pivot fields. This saves time if you want a field in a place that Excel doesn't put it automatically. For example, if you check the box for Year, Excel puts it into the Values area, because it's a number.

In the PivotTable Field list, right-click on the field that you want to add, and choose a location in the pivot table layout, or add that field as a Slicer.

Get more Pivot Field tips on my Contextures site.

Loan Payment Details

If you're taking out a loan, Excel's PMT function lets you calculate the monthly payment, based on 3 numbers -- loan amount, number of months for the loan term, and interest rate.

I've uploaded a new loan payment schedule template, where you can enter that information at the top of the sheet, along with the date of the first payment. Here's the PMT formula in that yellow cell:

=-PMT(LoanRate/12, LoanMths, LoanAmt)

I use a minus sign at the start, so it returns a positive number, and the annual interest rate is divided by 12, to get the monthly rate.

Below that, there is a table full of formulas, to show the details for each monthly payment.

In column D, the IPMT function calculates how of each monthly payment goes toward interest:

• =IF(G7="","",-IPMT(LoanRate/12, G7, LoanMths, LoanAmt))

To get the principal amount, you could use the PPMT function, but I just used subtraction:

• =IF(G7="","",C7-D7)

There's also a conditional formatting rule in the table, to highlight the row with the latest payment. That makes it easy to see where you are in the schedule.

The rule uses an INDEX/MATCH formula, to find the date that's on or before today's date:

=$A7=INDEX($A$7:$A$54, MATCH(TODAY(), $A$7:$A$54,1))

To see all the formulas, and to test how it works, go to the Loan Payment Schedule Template page on my Contextures site. The file is in xlsx format, and doesn't have any macros in it.

Excel Articles

Here are a few Excel-related articles that you might find useful or interesting.

Dashboards: Mynda Treacy shows how to make impressive interactive dashboards, using built-in Excel features, in her one-hour free webinar. Mynda offers full dashboard courses too - get 20% off if you register by Thursday, Nov. 14th. Click here for Excel Dashboard Course or Power BI Course info. Don't miss out - this is your last chance to attend this year (and this decade!)

Drop Downs: On the Design Smarts site, Josh Wayne explains the pros and cons of using drop down lists. He's talking about web forms, but the design ideas are good if you're using data validation drop downs in Excel too. (Level - All)

Excel Tweets: See what people tweeted about Excel this week. Do you ever get spreadsheet-related anxiety?

Also see: My Excel Products || Previous Issues

Too Much Candy

It was cold, wet and windy here on Halloween night, and only about 30 trick-or-treaters came to our door. That left us with a big pile of Canadian chocolate treats to get rid of, unfortunately. Those yellow wrappers are Coffee Crisps, so I'll volunteer to take care of those. The little boxes are Smarties, which are like M&Ms candies. Do you have regional treats, that you can't get elsewhere?

NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2019/20191105ctx.html
I'll also post any article updates or corrections there.

That's it for this week! If you have any comments or questions, send me an email.

Debra Dalgleish
ddalgleish @contextures.com

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.