Search Contextures Sites
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 firstname.lastname@example.org
- Unique Count in Pivot Table
- Group Pivot by Week
- New SHEET Functions
- More Excel Tips
- Video: MATCH and VLOOKUP
- Weekly Photo: Forget Me Not
- Excel Tools
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
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
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
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!”
- Doug Glancy shows how to use the MOD function, without getting any zeros in the results. So, when grouping things by fours, you’ll get 1,2,3,4 instead of 1,2,3,0.
- 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.
- If you’ve got some really small numbers for a chart, along with a few big ones, The Frankens Team shows how to create a broken line panel chart, to display the results.
- 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.
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.
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.
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 UserForm Course
- 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 ©2014
All rights reserved.