Search Contextures Sites
Excel Weekly News from Contextures June 25, 2013
Group pivot table text + more Excel tips
In this week's Excel news, you'll see how a UserForm works, group text items in a pivot field, and other tips. Thank you for reading the Excel news!
- Debra firstname.lastname@example.org
UserForm for Data Entry
With programming, you can create an Excel UserForm, to use for data entry. Click a button, and the data is stored on a hidden worksheet, and the form is cleared out, so you can start a new entry.
This video shows the completed form, how it works, and were the data is stored. The steps for building the UserForm are shown in step-by-step videos on my Contextures website.
Click here to test the interactive workbook: UserForm for Data Entry
Group and Ungroup Text Items in Pivot Table
In a pivot table, it is easy to group fields that contain dates or numbers. However, if you right-click on a text field's item, the Group command does not work. First, you have to manually select the text items that you want to group, and then use the Group command.
After grouping, the group name can be changed, and you can add more items to the group. If you no longer need them, you can ungroup one or more of the groups.
Click here to see the details, and to watch the video: Group and Ungroup Text Items in Pivot Table
Update Multiple Pivot Tables
Instead of manually updating pivot tables, one at a time, you can use programming to update several pivot table automatically. Jeff Weir has updated his sample code for this, and optimized it for Excel 2010.
If you have workbooks with more than one pivot table that use the same source data, download the sample file, and see how this code can save you time.
Click here to see the details, and to download the sample file: Update Multiple Pivot Tables
More Excel Tips
Here are a few more Excel articles that I read this week, that you might find useful:
- On Microsoft's Excel blog, they show off some of the fancy new features for Excel 2013 chart data labels. Now you can add text to a specific label, link it to a worksheet cell, and even change the label's shape.
- 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.
- On his MS Excel Unplugged blog, Gaspar Kamensek shows us a nifty trick with the MONTH function. You can get the month number from a cell that just has the month name in text.
Video: Copy Excel Code to Another Workbook
If download sample Excel files from my Contextures website, you might want to copy some of the code to one of your own Excel files. To do this, you can drag the modules from one file to another, as shown in this short video.
For more info on copying Excel code, please visit my Contextures website: Adding Code to an Excel Workbook
Excel Expert Course Discount
Mynda Treacy, who runs the popular Excel Dashboards course at My Online Training Hub, has opened registration for a new online course -- Excel Expert. This is the final week to get the early bird discount.
The course has 27 sessions, with beginner to advanced topics. Each tutorial has been categorised into levels; beginner, intermediate and advanced. Check out the syllabus and see what level you are. You'll find a huge range of advanced formulas including array formulas as well as PivotTables in depth, Conditional Formatting tricks and much more.
For a sneak preview, you can watch the sample video, Troubleshoot Excel Formulas.
Once you complete the Excel Expert course you'll be able to confidently list 'Advanced Excel Skills' on your CV/Resume. No more wondering if you have the skills, you will know you do. You will also get a certificate of completion to include in your resume arsenal.
Remember, this is the final week to get the early bird discount. Sign up by July 3rd, and receive a 20% discount. After that date, the course will be available at the full price.
If you're adding instructions to an Excel workbook, I hope that they're easier to understand than the confusing signs in this picture. I snapped this photo at a local park last week, and it seems that the washroom is a very scary place!
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 ©2014
All rights reserved.