Search Contextures Sites


Newsletter Index


Excel news
by email




Excel UserForms for Data Entry


30 Excel Functions in 30 Days



Contextures News

Excel Weekly News from Contextures Feb 11, 2014

Show pivot labels in every row + more Excel tips

In this week's Excel news, you'll see how to show pivot labels in every row, and other tips. Thank you for reading the Excel news!

 -  Debra

Find Last Item in Group With Index Match

The MATCH function can find the postion of an item in a list, and INDEX can return the contents of the cell in that position. In this example, the INDEX/MATCH formula finds a category in one column, and returns the last code for that category, from another column

Click here to see the details: Find Last Item in Group With Index Match

Turn Repeating Item Labels On and Off

In Excel 2010 and later versions, you can change a pivot table setting, so the item labels appear in every row, instead of just once, at the top of the group. This can be useful if the lists are long, so you can’t see the headings as you scroll down. You can also do lookups from the pivot table, if the names are filled in.

Click here to see the details: Turn Repeating Item Labels On and Off

Put Add-in Buttons on Excel QAT

To make the Ribbon commands easier to use, you can add a few to the Quick Access Toolbar (QAT). You can even add commands from your favourite add-ins, like my Excel Tools. Also, you can select a specific workbook, and show a command on the QAT only when that file is open.

Click here to see the details: Put Add-in Buttons on Excel QAT

More Excel Tips

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

  • On the Daily Dose of Excel blog, Jeff Weir shows how you can add leader lines to non-pie charts, in Excel 2010 and earlier. If you have Excel 2013, this feature is now built-in.
  • Do you have a favourite Excel book? Chandoo shared his top picks, and you can see other people’s choices in the comments. Add your own list of favourites to the discussion.
  • Mike Alexander shows how to use Power Query to combine data from multiple Excel files into one table. It's an amazing way to clean up your data, and you can see a demo in the End-to-End Power BI webinar (see the 24 Hour PASS Replays link below)
  • Finally, for a humorous peek at what other people are saying about Excel, read this week's collection of Excel tweets, on my Excel Theatre blog.

Online Courses

  • Last week, there were free Business Analytics courses online, in promotion of the upcoming PASS conference. If you missed them, the replays are available: 24 Hour PASS Replays
  • Chandoo has just re-opened his Power Pivot online course, and you can get the course info and registration details here: Power Pivot Course

Video: Quickly Insert Blank Rows

Watch this very short video to see a quick way to insert several blank rows in a worksheet, using a mouse shortcut.

Quiet Spot in the Forest

One of the places that I visited on Hilton Head Island, South Carolina, was the Shell Ring, in the Sea Pines forest preserve. Historians believe that this was a gathering place for ceremonies and feasts, for the people that lived there, long ago. If you look closely, you can see a bench at the bottom right of the picture. It's normal sized, so that should give you some idea of how tall those trees are!


Recommended Excel Tools

In addition to all the free Excel tips and tutorials, there are other Excel tools that you can invest in. To learn more about the products listed below, click on the links to take a look at their features, and decide if they're right for you.

Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.


Learn how to create Excel dashboards.


Privacy Policy


Contextures Inc., Copyright ©2014
All rights reserved.