Search Contextures Sites

 

Contextures
Newsletter Index

 

Contextures
Excel news
by email

 

 

 

30 Excel Functions in 30 Days

 

 

 

Contextures News

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 ddalgleish@contextures.com

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.

Excel Expert Course

Confusing Signs

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.

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

________________________

Learn how to create Excel dashboards.

 

Privacy Policy

 

Contextures Inc., Copyright 2014
All rights reserved.