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!
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.
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.
Here's the formula that I used -- it's array-entered with Ctrl+Shift+Enter
To see an explanation of how this formula works, and all the other solutions, download the Lookup Formula Solutions workbook.
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).
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)
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. ;-)
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: https://www.contextures.com/newsletter/excelnews2017/20171024ctx.html
ddalgleish @ contextures.com
Last updated: October 22, 2017 1:45 PM