Excel Roundup 20150601
In this week's roundup, fix drop down lists that allow invalid entries, create an interactive chart, get a free Excel book on Kindle, and much more.
If you read or wrote any other interesting Excel articles recently, or have upcoming Excel events, please let me know. Thanks!
1. Contextures Posts
In case you missed them, here are the articles that I posted recently:
- Fix an Excel drop down list that allows people to make invalid entries -- the problem is usually caused by blank cells.
- For a humorous peek at what other people are saying about spreadsheets, read the latest collection of Excel tweets, on my Excel Theatre blog.
2. Flexible Formula
To make an INDEX formula more flexible, John Michaloudis uses a drop down list to select the row number for the result.
3. Fun With Text
On the CFO blog, Bill Jelen shares a couple of quick tips for working with blocks of text. I've never used that Justify command – have you?
For more fun with text, Tomer Dean shows how to replace part of a text string in a cell, by using the SUBSTITUTE function. You can see more text function examples in my 30 Excel Functions in 30 Days kit.
4. What's the Frequency, Kenneth?
Oscar Cronquist is having a problem with the FREQUENCY function, when combined with the MOD function. Can you explain the results?
5. Power BI
Chris Webb explains how to work with international date and number formats in Power Query. For example, does 6/2/15 mean June 2nd, or Feb 6th?
Also, SharePoint Mike explains how to install Power View in Excel 2013 Pro Plus, and use it to create charts in Excel.
If you're still confused about which version to get, Mike Alexander found details on all the Office 365 plans, and summarizes their features, to identify which ones have the Power BI tools.
6. Free Book Download
Doug H recently published Excel for the New Project Manager: A Primer on Microsoft Excel 2010 as an Amazon Kindle ebook. You can get a free copy this week -- from June 1st to June 5th (Pacific Time). If you download the book and like it, please leave a review on the Amazon site.
7. Interactive Chart
8. Track Exam Results
A professor uses Excel to track exam results, down to the individual questions, so he can improve the learning outcomes for his students.
9. Conditional Formatting Icons
In a short video, Mike Girvin (ExcelIsFun) show 3 ways to use conditional formatting icons with a set of student grades.