Excel Fun with Functions
October 8, 2019
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
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.
Did you know that you can use emojis in Excel? It was news to me, but Wyn Hopkins explained how to add one:
You can use emojis in formulas too - just put the emojis inside double quote marks
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))
Also, take a look at the workbook's formulas, to see how the UNICODE, UNICHAR, HEX2DEC and DEC2HEX functions are used.
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
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?
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.
Last updated: October 4, 2019 4:40 PM