Excel Weekly News from Contextures Oct 15, 2013
Fix pivot table groups + more Excel tips
In this week's Excel news, you'll see how to create dependent drop down list, and other tips. Thank you for reading the Excel news!
- Debra ddalgleish@contextures.com
Select an employee name from the first drop down list, then see a list of that person's skills in the second drop down list. The employee information is stored in a table, with information for each person in a single row. This technique uses data validation lists, and an OFFSET formula.
Click here to see the details, and download the sample file: Dependent Drop Down List From a Row
When you group dates in a pivot table, items are added to the beginning and end of the date range, to represent all items outside the date range. You might see those date groups in the pivot table headings, or just in the drop down lists. You can't turn this feature off, but this article shows a couple of ways to hide these date groups.
Click here to see the details, and to get the sample code: Grouping Shows Items Before and After Date Range
The AGGREGATE function was added in Excel 2010, and it's like SUBTOTAL, but more powerful. You can select a function for it to use, such as SUM or COUNT, and then choose an option for what to ignore, like errors, or hidden rows.
To make it easier to use, I've created a sample file with drop down lists of all the functions and options. Make your choices, and see the results.
Click here to see the details, and download the sample file: Change Functions with AGGREGATE in Excel
Here are a few more Excel articles that I read this week, that you might find useful:
Sometimes the first few rows or columns on a worksheet are hidden, and it can be tricky to unhide them.
To see an easy way to show the hidden rows or columns, please watch this short video tutorial.
For a limited time, Mynda Treacy from My Online training Hub is opening her Excel Dashboard Course, and if you sign up by 8 pm Pacific time on October 24th, you can get the course for 20% off.
The course is video based, delivered online and is available 24/7. You'll receive comprehensive workbooks and sample dashboards to keep, and there's even an option to download the videos.
I've been through this course, and highly recommend it. The previous classes were very successful, and you can read the glowing reviews from the students, who loved all the techniques that they learned in the course, and are using them to impress their colleagues.
Click here to find out the details, read the student comments, and watch the 'behind the scenes' video that shows you what you'll receive as a member. Remember, if you sign up by October 24th, you can get the dashboard course for 20% off.
Bonus: If you sign up for Mynda's course through one of my links, I'll send you a copy of my new Excel add-in -- Contextures Excel Tools -- when it's ready next month. (If you signed up for one of Mynda's earlier courses through my link, send me an email if you'd like a copy of the new add-in.)
The beautiful fall colours will soon disappear, so here is one last picture, of a brightly coloured tree in our neighbourhood. All too soon, I'll be taking pictures of the snow.
___________________________
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.
________________________
__
Contextures Inc., Copyright ©2019
All rights reserved.
