Contextures News 20151215

Essential Excel Tricks

December 15, 2015

Essential Excel tricks, data visualization, 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.

Find Project End Date

If you want to find out when a project will end, you can use the WORKDAY function. Just enter a start date, and the number of working days, and it calculates the end date. You can add a list of non-working holidays too, and Excel will exclude those.

There is a new and improved WORKDAY.INTL function in Excel 2010 and later. The older function assumes that you work from Monday to Friday, and treats Saturday and Sunday as weekend days. In the newer version, you can set your own non-working days. Just pick from the list of options, or type a 7-digit string with 1s and 0s.

See how it works, and download my sample file to follow along.

working days

MAX IF Calculation

There are SUMIFS, COUNTIFS and AVERAGEIFS functions, if you need to calculate based on multiple criteria. If you need to calculate a MIN IF or MAX IF, there isn't a built-in function. Instead, you can combine the MIN or MAX function with IF, in an array-entered formula.

Another option is to create a pivot table based on the data, and with just a few clicks, you can find the highest order amount in each city, for each product. After you add the Amount field, change its calculation to Max, to see the highest amounts. There are examples on my website.

pivot table max price

Excel Articles

Here are a couple of Excel articles I read recently, that you might find useful.

Essential Excel Skills -- Do you agree with this list of 7 essential Excel tricks for office workers? I'd definitely pick data validation over Power View. Here's my list of 14 basic Excel skills, but it would be hard to cut that list in half. (Level - Basic)

Data Visualization -- The Science Goddess creates some impressive charts, and she recently led a data viz workshop for educators. You can download her training materials, to get inspiration for your own work. (There is a link in the second paragraph.). (Level - Intermediate)

More Excel Articles -- Find links to more articles in the latest Excel Weekly Roundup on my Contextures blog.

Holiday Traditions

We're almost ready for Christmas, and all the treasured ornaments and decorations are on display. This shepherd and angel sit on the fireplace mantle, and were made by my kids, long ago. They've suffered a bit of damage over the years, but their inner beauty still shines through! Whatever holidays you're celebrating, I hope they're filled with fun, family, friends, and fond memories.

weekly photo

NOTE: If you have any problems with the links in the email, you can see this newsletter on my website -- copy this link and paste it into your browser:

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

Debra Dalgleish

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.


Last updated: October 29, 2019 4:41 PM