Search Contextures Sites

 

Contextures
Newsletter Index

 

Contextures
Excel news
by email

 

 

 

Excel UserForms for Data Entry

 

30 Excel Functions in 30 Days

 

 

Contextures News

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 -  ddalgleish@contextures.com

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:

  • 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!
  • 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

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

_____________

________________________

Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.

 

Learn how to create Excel dashboards.

 

Privacy Policy

 

Contextures Inc., Copyright ©2015
All rights reserved.