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 firstname.lastname@example.org
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 Microsoft's Excel team blog, Jeff Johnson shows 4 ways that you can filter your data, to show only part of the data in a chart.
- 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.
- 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.
- Contextures Excel Tools Add-in
- Contextures PivotPower Premium Add-in
- Contextures 30 Excel Functions in 30 Days
- Excel Online Course
- Excel Charting Tools
- Excel Dashboard Kits
- Excel Project Management Templates
- Excel VBA School
Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.
Contextures Inc., Copyright ©2017
All rights reserved.