Contextures

Contextures News 20191008

Excel Fun with Functions

October 8, 2019

Quick item count, fun with functions, 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.

Count Items

If you need a quick count of items in a list, a pivot table might be the fastest way to do the job. For example, here's a list of 100 month names, and we'd like to know how many times each month appears in the list.

Instead of setting up a COUNTIF formula, just select a cell in the list, and insert a pivot table. Then, add the month field to the Rows area, and to the Values area. All the list items are text, so it automatically appears as "Count of BirthMonth"

There are detailed steps and a video on my Contextures site, and a sample file to download.

count items with a pivot table

Fun with Functions

Did you know that you can use emojis in Excel? It was news to me, but Wyn Hopkins explained how to add one:

  • While typing in Excel, tap the Windows key on your keyboard, then tap the period (.) key
  • Choose an emoji from the popup window

You can use emojis in formulas too - just put the emojis inside double quote marks

emojis in formula

Or put emojis in a lookup table, and use an INDEX / MATCH formula to show results:

=INDEX($F$2:$F$4, MATCH(B2, $E$2:$E$4,1))

emoji lookup

See the detailed steps on my Contextures Blog, and you can download an emoji workbook from my site. Thanks to Ken Puls, from Excelguru, who built the emoji chart.

Also, take a look at the workbook's formulas, to see how the UNICODE, UNICHAR, HEX2DEC and DEC2HEX functions are used.

emoji formulas

Excel Articles

Here are a few Excel-related articles that you might find useful or interesting.

Charts: Michelle Rial makes fascinating charts, to amuse and/or inspire you. If you think you're too old to learn all the new Excel things, check out the "Is it too late to start?" chart, about halfway down the page. (Level - All)

Excel Tips: Dawn Bjork shared 6 tips for working with text in Excel. Tip #1 is the most important one, and some people try to ignore it. (Level - All)

Formulas: Have you tried Microsoft's Excel Formula Coach? You can enter numbers online, and it builds a formula that you can paste into your workbook. I found PMT, PV and FV coaches, and there's a spot to suggest other functions. (Level - Int)

Excel Tweets: Can you use Excel without crying? See what people tweeted about Excel this week

Also see: My Excel Products || Previous Issues

Party Like It's 1999

My nephew got married last weekend, so I dusted off one of my evening purses, in honour of the occasion. Apparently, I don't get out too often, because the purse still had the menu from the New Year's Eve party that we attended on Dec. 31, 1999! The hotel shut down the elevators just before midnight, so nobody would get trapped if there was a Y2K problem. Did you have to prepare work computers for that century rollover?

weekly photo

NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2019/20191008ctx.html
I'll also post any article updates or corrections there.

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

Debra Dalgleish
dsd@ contextures.com

Debra Dalgleish

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.

contextures newsletter info

 

Related Links

Conditional Formatting Rules

Highlight Lottery Number

ConCat User Defined Function

TEXTJOIN sample file

 

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: October 4, 2019 4:40 PM