Contextures News 20150120

Pivot Table Makeover + more Excel tips

January 20, 2015

In this week's Excel news, you'll see how to give your pivot table a makeover, and much more., 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.

• Delete Rows Based on Colour
• Pivot Table Makeover
• More Excel Tips
• Excel Announcements
• Video: Quick Drop Down List
• Weekly Photo
• Featured Excel Product

Delete Rows Based on Colour

Did you know that you can filter a list based on the conditional formatting colours that you applied? This is one of the newer features in Excel, and in this example, I used it to select highlighted rows, then delete them..

Click here to see the details: Delete Rows With Conditional Formatting Color

Pivot Table Makeover

When you create a new pivot table, it is automatically formatted with the Compact Report Layout. You can change to one of the other layouts, and make a few other simple changes, so your pivot table looks better, and the data is easier to read and understand.

See the details in my blog post, and download the sample file: Give Your Excel Pivot Table a Makeover

More Excel Tips

Here are a few of the Excel articles that I read recently, that you might find useful:

• Robert Lepper sent me his tip for creating a color spectrum with 2-color gradients in a range of cells, and I added it to my Formatting Tips page.

• Sohail Anwar, posting on Chandoo's blog, shares 8 reasons why you must get better at Excel in 2015.

• Miguel Escobar uses Power Query to combine multiple files of different file types.

• Jon Peltier uses a chart's secondary axis to show the current donations as a percentage of a fundraising target.

• Charles Williams explains how Excel handles array expressions and constants in single-cell and multi-cell Excel formulas. He's filling in the gaps in the official documentation. Have a cup or two of coffee, before you dive into this one!

• Here's a quick Power Pivot tip from Melissa Coates (SQL Chick), so you can see the calculated measures in alphabetical order. That makes it easier to find things in a long list. You can do the same thing for the field list in a regular pivot table.

• Teylyn explains why you need to use True  or False in the last argument of a VLOOKUP formula

Excel Announcements

Here are some upcoming events, courses, recently published books, and other new items, related to Excel:

• Excel Dashboard Course: Mynda Treacy has opened registration for her acclaimed Excel Dashboard course, and you'll get 20% off, if you sign up by January 22nd. See details in the Featured Excel Product section, below.

• Mynda is also offering a free introductory webinar on How to Build Excel Dashboards. Get the details on this one-hour session, and sign up for a date and time that is convenient for you.

• PASS BA Marathon: Feb. 3, 2015 -- Sign up now, for this free online preview of the upcoming Business Analytics Conference. There will be 6 live webinars, including "Productivity Revolution in Excel", led by Chandoo (Purna Duggirala) and Avi Singh.

• Amsterdam Excel Summit: Mark your calendar for April 13-15, so you can attend this amazing Excel event. Last year's summit was an outstanding success, and registration will open soon, for this year's event.

Video: Quick Drop Down List

To make data entry easier, you can add a drop down list in a cell, then quickly select an item from that list. This video has a few quick tips for creating a named range, then using that name to create the drop down.

Click here to watch the short video that show how it works: Quick Drop Down List Video.

Staying Warm

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

Debra Dalgleish
dsd@ contextures.com

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.