Excel Weekly News from Contextures May 27, 2014

New SHEET functions + more Excel tips

In this week's Excel news, you'll see how to troubleshoot with SHEET functions, and other tips. Thank you for reading the Excel news!

-  Debra   ddalgleish@contextures.com

## Unique Count in Pivot Table

There isn't a built-in function that shows a unique count in a pivot table. You can see the sum of sales, or the count of orders, but not the number of unique (distinct) stores where those orders were sold. Roger Govier shares two techniques that let you create a unique count -- one method for Excel 2010 and later, and one method for earlier versions.

Click here to see the details, and download the sample file: Unique Count in Pivot Table

## Group Pivot Table by Week

When you show totals by date in a pivot table, you can list each date, or group the dates. In this example, we'll group the dates into 4-week periods, to match the periods in the company's sales calendar. Then, set a starting date for the grouping, so that the periods start on your preferred day of the week

Click here to read the details, and watch the video: Group By Weeks in Excel Pivot Table

## New SHEET Functions in Excel 2013

There are new functions in Excel 2013 that let you calculate the sheet number or the number of sheets in a workbook. You can use these functions to help with troubleshooting, and to discover if any sheets are hidden.

• The SHEET Function returns the sheet number of the active sheet, or the sheet number of the cell reference.
• The SHEETS function returns the number of sheets in a workbook, or the number of sheets in a 3D reference.

Click here to see the details, and watch the video: SHEET and SHEETS Functions in Excel 2013

## More Excel Tips

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

• No matter how long you've used Excel, there's always a little trick or two that you can learn. Glen Gilchrist finally found the feature that lets you quickly delete blank rows. As he says, “splendiferous!”
• They've never been disagreeable with me, but in his latest podcast, Chandoo explains that Averages are Mean. And they must be really mean, because there will be a part 2, later this week.
• 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.

## Video: Use MATCH With VLOOKUP

Excel's MATCH function finds items in a list, and returns the postion number. You can combine MATCH with VLOOKUP, to create flexible lookup formulas.

## Forget Me Not

Spring is in full force, here in Canada, and the combination of rain and warmer weather is making everything grow rapidly. Unfortunately, that means the weeds are growing too! These little forget-me-not flowers are pretty, but they are taking over the lawn, so they'll have to be treated like weeds, and pulled out.

__________________________

### Contextures Products

Save time with our "Done For You" Excel products.

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

________________________

__