Contextures News 20150224

Dynamic Charts + more Excel tips

February 24, 2015

In this week's Excel news, you'll see how to create dynamic charts, select multiple items from a drop down list, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

• Select Multiple Items in Drop Down List
• More Excel Tips
• Excel Announcements
• Video: Create Links With HYPERLINK Function
• Weekly Photo: Cold Fun in the Park
• Featured Excel Product

Select Multiple Items in Drop Down List

You can add a drop down list in a worksheet cell, by using data validation. When you select an item from the list, that value is entered in the cell. If you go back to the list, and select a different item, that value replaces the cell's current value.

With a bit of programming, you can select multiple items from a drop down list, and have new values added to the cell's contents. The code can be copied from my sample file, and added to your workbook.

This is the most popular article that I've ever posted on my blog, with over 300 comments, and you can read the details here: Select Multiple Items from Excel Data Validation List

More Excel Tips

Here are a few of the Excel articles that I read recently, that you might find useful:

• Jon Peltier shows how to add option buttons to a worksheet, so you can select the data to display in a dynamic chart.

• MF shares another trick with the Ctrl + Enter shortcut – stay in the same cell, instead of moving down.

• Michael Nellessen shares 3 of his favourite Excel formulas, with examples of how to use them. I'd recommend typing your criteria in worksheet cells, and referring to those cells in the formula, instead of hardcoding the values.

• Meagan Longoria used a combination of tips to fix performance issues with her Power View reports, that are based on a Power Pivot data source.

• Sumit Bansal explains how to create a dynamic Pareto Chart in Excel, and you can download the sample file, and follow the tutorial.

• If you're starting to learn VBA, JD Sartain shares 5 essential tips for creating Excel macros. Tip #4 is valid if you're following their advice to use relative references.

Excel Announcements

Here are some upcoming events, courses, recently published books, and other new items, related to Excel:

• Amsterdam Excel Summit: Mark your calendar for April 13-15, so you can attend this amazing Excel event. Last year's summit was an outstanding success, and registration is now open, for this year's event.

Video: Create Links With HYPERLINK Function

Instead of manually creating links, you can use Excel HYPERLINK function to create them. For example, if you have a list of company names in column A, and website URLs or email addresses in column B, use a formula to create hyperlinks in column C.

Click here to watch the short video that show how it works: Create Links With Excel HYPERLINK Function

Cold Fun in the Park

Featured Excel Product

This week, I'm pleased to recommend Excel University -- an online Excel training course designed specifically for accountants. I went through several of the modules, and was very impressed by the content, delivery, and course structure.

The course focuses specifically on the Excel features, functions, and techniques that are most useful to accounting professionals. The training is practical and the concepts are easily applied to your workbooks. You'll learn how to get your Excel tasks done more quickly, how to save time, and how to automate your recurring-use workbooks.

The online course is taught through lecture videos, homework activities, and quizzes. A passing score of 70% unlocks the certificate of completion.

The Excel University format combines the best elements of traditional CPE delivery methods, including live seminars, webinars, and self-study. This is a unique training format for CPE and it is the most effective way to learn Excel.

• Similar to live education, you can ask the instructor questions at any time in the moderated discussion forums.
• Similar to webinars, you can learn at your desk.
• And similar to self-study, you can work at your own pace, pausing and replaying the videos as needed.

Instructor Jeff Lenning, CPA and Microsoft Certified Trainer, has taught thousands of CPAs and accounting professionals how to streamline their work by using Excel more effectively. He is a dynamic instructor and many of his Excel articles have been featured in accounting publications such as the Journal of Accountancy.

Click the link to learn more about Excel University.

That's it for this week! If you have any comments or questions, send me an email.

Debra Dalgleish
dsd@ contextures.com

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.