Contextures News 20180227

Calculate Age in Excel

February 27, 2018

Change a setting to allow multiple filters, 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 supports the free info on my site!

Multiple Filters

There are three kinds of filters that you can use on a pivot table field:

• label filters (based on the label text)
• value filters (based on the amounts)
• manual filters (using the check boxes)

By default, Excel only lets you use one of those filters at a time. So, you could filter a Month field to

• see the 3 months with top sales, OR
• show the sales for Jan - June.

If you need to use both filters at once (top 3 months, from Jan-June), change a setting in the pivot table:

1. Right-click a cell in the pivot table, and click PivotTable Options.
2. On the Totals & Filters tab Under Filters, add a check mark to 'Allow multiple filters per field.'

See more pivot table filter tips on my website, and watch a short video on applying multiple filters.

And if pivot tables are a big part of your job, my Pivot Power Premium add-in can help you save time.

Age and Height

Last week at the Olympics, the USA team beat Canada in the women's hockey final. To compare the two teams, I got the team rosters, which have player names, height, date of birth, etc.

What formulas would you use to get each player's age, and height in feet (5.58 instead of 5'7)? Mine are below the screen shot.

Here are the formulas that I used:

For the age, I used DATEDIF, but be careful with that function - it can give incorrect results.

=DATEDIF([@DOB],TODAY(),"y")

The height was listed as feet and inches (5'7), and this formula converts it to feet (5.58).

=SUM(LEFT([@Height],1), RIGHT([@Height], LEN([@Height])-2)/12)

• The first character at the LEFT is the feet (there were no double-digit heights)
• The 1 or 2 characters at the RIGHT are divided by 12
• SUM those amounts, to get the height in feet

Excel Articles

Here are a couple of Excel articles that you might find useful or interesting.

Excel Tips - Neil Patel shares 7 advanced Excel tips. The examples show how to analyze website data, but are useful for other types of Excel projects too. NOTE: Tip #4 is a function for Google Sheets, not Excel. (Level - Intermediate)

Data Viz - On the Flowing Data blog, Nathan Yau posted a link to Beginners Guide to Visualization literacy. Click that link, then click the "static version" link. (Level - All)

Also see: My Excel Products || Excel Events || Previous Issues || Excel Humour

Sibling Rivalry

My sister came to visit last week - she was attending an engineering conference here in Mississauga. She brought along a bottle of Sibling Rivaly red wine, from nearby Niagara-on-the-Lake. Fortunately, we didn't have any sibling rivalry during her visit - just an enjoyable time!

During the conference sessions, she made Sketchnotes instead of written notes, so I want to learn more about that technique. Have you ever tried visual note taking?

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

NOTE: For the online version, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2018/20180227ctx.html
I'll also post any article updates or corrections there.

Debra Dalgleish
ddalgleish @ 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.