Contextures News 20150310

You've never used this handy Excel function

March 10, 2015

I'll bet that you've never used Excel's TRIMMEAN function in one of your workbooks. It's a handy way to calculate an average on a set of numbers, and exclude some of the highest and lowest numbers. See how it works, 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.

• TRIMMEAN Function
• More Excel Tips
• Excel Announcements
• Video: Filter For Items in a List
• Weekly Photo: Perfect Temperature
• Featured Excel Product

TRIMMEAN Function

Have you ever used Excel's TRIMMEAN function? It can calculate an average that excludes the outliers -- a specific percentage of the highest and lowest numbers in the dataset. If you have some data that is throwing off the average, try TRIMMEAN, as an alternative way of looking at the results.

Read the details on my blog, and watch the video, to see how it works. I don't use those conditional formatting color scales too often, but thought it would help in this example: Ignore Outliers with Excel TRIMMEAN

More Excel Tips

Here are a few of the Excel articles that I read recently, that you might find useful:

• Of course, you already knew this, but the Wall Street Journal has confirmed that Excel skills are key to landing a job that pays well. The last paragraph reveals details on who funded the report.

• On the Junk Charts blog, Kaiser Fung shares data science principles, including this quote from Hans Rosling, "the passion of the people plus Excel were all you need. You don't need fancy software."

• Doug Glancy had trouble changing a letter from upper to lower case in a pivot table label, but found a workaround. It's little things like this that can drive you crazy!

• Chris Newman posted sample code to help you get started with programming charts and graphics in Excel.

• In his latest podcast, Chandoo explains how to make things disappear in Excel. I hope he also tells us how to make them re-appear! You can also download the transcript, in case you'd rather read the info.

• The Office 2016 for Mac preview has been released, and you can download it and see what you think of the new features. The Excel screen shot shows a pink pie chart and a rainbow-coloured table, but claims the new version is "retina-friendly".

• Finally, for a humorous peek at what other people are saying about spreadsheets, read the latest collection of Excel tweets, on my Excel Theatre blog.

Excel Announcements

Here are some upcoming events, courses, recently published books, and other new items, related to Excel:

• Amsterdam Excel Summit: Mark your calendar for April 13-15, so you can attend this amazing Excel event. Last year's summit was an outstanding success, and registration is now open, for this year's event.

Video: Filter For Items in a List

It's a little trickier to use an Advanced Filter, instead of an AutoFilter, but it can be much more powerful and flexible. In this example, I typed a short list of products on a worksheet. Next, I typed a COUNTIF formula at the top of the worksheet, in the criteria area for the filter. When I apply the filter, it only shows the rows that contain one of the products from my short list.

Click here to watch the short video that shows how the advanced filter works: Filter For Items in a List

The Perfect Temperature

Featured Excel Product

This week, I'm pleased to recommend Excel University -- an online Excel training course designed specifically for accountants. I went through several of the modules, and was very impressed by the content, delivery, and course structure.

The course focuses specifically on the Excel features, functions, and techniques that are most useful to accounting professionals. The training is practical and the concepts are easily applied to your workbooks. You'll learn how to get your Excel tasks done more quickly, how to save time, and how to automate your recurring-use workbooks.

Click the link to learn more about Excel University.

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

Debra Dalgleish
[email protected] 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.