Contextures

Contextures News

Do you know this Excel trick?

Oct 24, 2017

Formula challenge solutions, time-saving trick, and more, in this week's Excel news. Visit my Excel website for many more tips, tutorials and videos.

Note: For some products mentioned below, I earn a commission on sales. That supports the free info on my site!

Pivot Table Trick

Are you one of the few people who know this pivot table trick? To quickly add another pivot field to the layout, just type that field's name over another field's name.

In the screen shot below, the heading for "Products" was in cell A4, and I typed "Category" in that cell. When I press Enter, the Category field appears in column A, and Product moves to column B.

Go to my Pivot Table Labels page to see a short video that shows the steps.

Another way to save time, if you build lots of pivot tables, is to use my Pivot Power Premium add-in.

pivot table trick

Lookup Formula Challenge

Thanks for sending your solutions to last week's lookup challenge. As shown below, we needed a formula in column E, to get the correct customer code from column B, based on the imported data in column D.

code lookup challenge

Here's the formula that I used -- it's array-entered with Ctrl+Shift+Enter

=INDEX($B$2:$B$4,MATCH(1,IFERROR(SEARCH("*"&$A$2:$A$4&"*",D2,1),0),0))

To see an explanation of how this formula works, and all the other solutions, download the Lookup Formula Solutions workbook.

Some people used the LOOKUP function in their solutions, and those formulas didn't need to be array-entered. Kevin Lehrbass enjoyed the challenge, and made a video to explain his solution.

Get Started With Macros

Excel formulas are awesome, but you can add more power to Excel with macros. To help you get started, Jon Acampora is hosting free webinars that show you 7 of the basic steps. The webinars available this week only -- October 23rd to 27th.

This webinar is for beginners, so don't worry that it will be over your head -- Jon will explain things clearly and simply. Sign up now -- click here to pick a day and time to attend. (You can watch the replay later, if you miss your session).

Excel Articles

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

Excel AMA - Last week, Microsoft's Excel team held their "Ask Me Anything" (AMA) session on the Reddit website. If you missed it, go to the Excel AMA page, to read all the 4000+ questions and answers. Lots of great info, with occasional profanity -- just ignore that. (Level - All)

Date/Time - Mynda Treacy is sharing an amazing collection of Date and Time tips and formulas. There are tips on the page, and you can also get the free Excel file that is packed with examples. (Level - All)

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

New York Life

Last week, while I was stuck in my office, working on Excel videos and tutorials, my daughter had the time of her life in New York City. She and her friends bought Hamilton tickets a year ago, and it was worth the wait - they loved the musical. They also went to a Jets vs Patriots football game, and as far as I know, that's not really quarterback Tom Brady who's sitting in front of her. ;-)

weekly photo

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

NOTE: If you have trouble with the images or links in this email, paste this URL into your web browser, to see the online version: http://www.contextures.com/newsletter/excelnews2017/20171024ctx.html

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

 

Search Contextures Sites

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: October 22, 2017 1:45 PM