Essential Excel Tricks
December 15, 2015
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
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.
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.
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.
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.
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: https://www.contextures.com/newsletter/excelnews2015/20151215ctx.html
That's it for this week! If you have any comments or questions, send me an email.
[email protected] contextures.com
Last updated: October 29, 2019 4:41 PM