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 firstname.lastname@example.org
Dependent Drop Down List From a Row
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
Grouping Shows Items Before and After Date Range
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
Change Functions with AGGREGATE in Excel
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
More Excel Tips
Here are a few more Excel articles that I read this week, that you might find useful:
- Last week he looked at the negative side of being a consultant. This week Paul Ballard looks at the benefits of this career choice.
- Charles Williams runs tests on Excel performance, and finds that heavy conditional formatting can be slow. Read his report on what's slow and what's faster.
- 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.
- Before you build another chart, take a few minutes to read Jon Peltier's article on Excel Charting Dos and Don'ts.
Video: Trouble Unhiding Rows or Columns in Excel
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.
Excel Dashboard Course Recommendation
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.
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 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.