Contextures

Contextures News 20180911

Excel Warning and Challenge

September 11, 2018

Formula challenge, comment warning, 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.

Comment Warning

If you have Excel in an Office 365 subscription, you should see a new feature soon - Threaded Comments. They'll be helpful for tracking team discussions, but read the warning below, before you use them.

Threaded comments

With the original Excel comments (Legacy Comments), you could add special formatting, such as shapes, pictures and fill colours. See my Comment Tips page, for details on how to create those.

With threaded comments, if you start to edit an existing Legacy comment, Excel will ask if you want to switch to Threaded Comments. If you, or one of your co-workers, clicks OK, any legacy formatting will be removed.

If you want to keep using Legacy Comment formatting, add the following icons to your Quick Access Toolber, or the Excel Ribbon. And, if you use formatted comments, go to UserVoice, and vote for Bill Jelen's Legacy Comments suggestion.

Legacy comments

Formula Challenge

Here's a little formula challenge for you, and my solution is below the screen shot. How would you solve it?

If you pay $12,000 rent per year, and that amount will increase by 5% each year, what is your total rent over a 3 year period?

Calculate total rent

To get the total rent, you ccould make a column for each year, and calculate the increased amounts, then use SUM to add them all up. However, if you wanted to check the total for a different number of years, you'd need to change the worksheet setup.

For a more flexible solution to this challenge, I used one of Excel's Financial functions -- FV (Future Value). It has 3 required arguments - Rate, number of Periods, and Payment amount (negative).

=FV(B3,B4,-B2)

With the FV function, it's quick and easy to test different scenarios, and get a total. See Microsoft's info on the FV function. For more Financial functions, see PMT function and Loan Payments on my website.

Future Value function

Excel Articles

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

Models - Brett Whysel, a former investment banker, shares 8 ways to make more powerful Excel models -- "begin with your end in mind". (Level - All)

Football - Get the Fantasy Football Draft workbook from Microsoft's Excel team blog. It uses Get & Transform, Conditional Formatting, VLOOKUP, and more. (Level - All)

Also see: My Excel Products || Excel Events || Previous Issues || Weekly Humour

Smart Watch

Do you have a smart watch? I just got my first one - an AmazFit Bip model, mostly to keep track of my walking, and to remind me what day it is! There were several face designs to choose from, and I chose the one with the biggest numbers. Now I just have to figure out how to export the daily steps data, to analyze in Excel!

weekly photo

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

NOTE: For the online version, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2018/20180911ctx.html
I'll also post any article updates or corrections there.

Debra Dalgleish
dsdalg @ gmail.com

Debra Dalgleish

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.

 

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: September 9, 2018 3:21 PM