You've never used this handy Excel function
Excel Weekly News from Contextures 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 find many more tips, in this week's Excel news.
- Debra - firstname.lastname@example.org
- TRIMMEAN Function
- More Excel Tips
- Excel Announcements
- Video: Filter For Items in a List
- Weekly Photo: Perfect Temperature
- Featured Excel Product
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.
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
A few years ago, I got this Galileo thermometer as a gift, and I love the way that it looks. It doesn't give the temperature with digital accuracy, but the balls with coloured liquid are much nicer to look at than a digital display.
The lowest floating ball shows 72F as the indoor temperture, which is perfect. Too bad it isn't the same temperature outside!
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.
As an exclusive offer to Contextures Newsletter subscribers, you can save 20% with this discount code: contextures2015 (code expires March 31, 2015)
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. And remember, until March 31, 2015, you can save 20% with this discount code: contextures2015
See more recommended Excel products.
Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.
Contextures Inc., Copyright ©2016
All rights reserved.