Contextures News 20190618

Strange Excel Functions

June 18, 2019

Strange Excel functions, list all Excel files, 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.

You'll get my next newsletter in 2 weeks -- we're on the summer schedule now.

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

List All Excel Files

There are lots of sample files on my Contextures site, and I always zip them. Someone asked if I could leave the files unzipped, but I've decided to leave things as they are. You can read the full story on my Contextures blog, but the main reasons are preserving the links, and getting through firewalls.

While I was deciding whether to keep zippng the files, I created a macro to quickly list all the files in a folder, with their name, size, and dates. You can download a sample file with that macro from the Excel Folder File Macros page on my Contextures site.

list of all files

Another macro on that page lists Excel files only, and includes the Content Created date. That can be quite different from the Date Created that you see in Windows Explorer.

old items in pivot table drop down

Strange Functions

Excel has two functions with 1-letter names -- T and N -- that you've probably never used. Here's a quick look at them, and please let me know if you've found a good use for either function. I've used N a few times, but not T. You can download the T and N sample file to see the formulas.

T Function: If a cell contains text, it returns that text. If the cell contains a non-text value, it returns an empty string. In the screen shot below, you can see the difference between the T function and ISTEXT, which returns TRUE or FALSE, so you need a longer formula to get the text.

T function and ISTEXT

N Function: This function returns a value converted to a number. In the screen shot below, you can see results for N, ISNUMBER and a double unary (two minus signs). N is handy if you want to convert TRUE and FALSE to numbers, or see the numeric value of a date.

N function comparison

Another use for N is adding hidden notes in a cell. Because N returns a zero for text, you can add it to the end of a formula, without affecting the result. The note can be seen in the formula bar, but doesn't add a cell marker, like a normal comment would.

=SUM(B3:C3) + N("Store01 closed in June")

Read more about the T function and the N function on my blog.

Excel Articles

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

Spreadsheets - This article has spreadsheet tips for journalists, but it's an interesting read for people in other professions too. The formula for % difference is correct, but the explanation is confusing. (Level - All)

Excel Sessions - If you didn't make it to last week's Microsoft Business Applications Summit, you can watch some of the Excel sessions online. Click the Sessions on Demand heading, choose a category, and then choose Excel in the Product drop down. (Level - All)

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

Almost Summer

We had a cool, wet spring, and everything seemed to bloom late this year. Our lilac shrub finally has blossoms, and brightened up our back yard. The sky was clear blue for a few days too, and the Toronto Raptors won the NBA championship last Thursday, so spring is ending better than it started!

weekly photo

NOTE: To read this newsletter online, paste this URL into your web browser:
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

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.



Get weekly Excel tips from Debra


pivot power free






Last updated: June 15, 2019 12:12 PM