Excel Roundup 20140825
There is a free app available for Excel (2013 or Online version), that lets you create 8 different chart types, including some that aren’t standard Excel charts, such as box plots. The app is called XLMiner Data Visualization, and it’s created by Frontline Systems, which also makes the Solver add-in.
If you log in to your Microsoft account, you can download and install the app from the Microsoft Store. Then select data, and the chart appears in the app’s window. In the screen shot below, you can see the box plot chart that I created, in just a few seconds.
The free version of XLMiner lets you do the basics, and if you want more features, you can upgrade to the Pro version. It costs $995, about the same as Tableau Desktop. And if you want quick, fancy charts, at a much lower cost, take a look at Jon Peltier’s charting utility.
Here’s what I posted recently:
- Earlier this month, I updated my Data Validation Multi-Select Premium kit, so it’s much easier to customize the code, and add it to your file. If you’ve purchased a copy of the kit, you should have received an email with a link to download the new version. I’ve also created a page with answers to frequently asked questions about the product, and please let me know if you have any other questions.
- Create random text for sample data, with the functions RANDBETWEEN and CHOOSE. There are great suggestions in the comments too.
- Instead of leaving the default headings, you can remove "Sum of" and "Count" from pivot table value field headings. A short video shows the steps.
- Finally, for a humorous peek at what other people are saying about spreadsheets, read the latest collection of Excel tweets, on my Excel Theatre blog.
Other Excel Articles
Here are a few of the Excel articles that I read recently, that you might find useful:
- How big is that worksheet? Francis Hayes (The Excel Addict) calculated the size in his latest newsletter, and you might be surprised.
- Sumit Bansal shows 10 ways to clean up data in Excel.
- Prof. Lee Townsend shares her code for printing to either the default printer or to a PDF file. She found that the PC version works nicely, but the Excel for Mac version isn’t as cooperative – it will crash if you don’t follow the exact steps that she outlines.
- Maybe there aren’t any bugs in your code, but Andrew Wulf explains the fine art of solving strange bugs, using examples from projects that he’s worked on.
- Mike Alexander helps us understand weighted averages, and reminds us not to take an average of an average.
- With Excel formulas, you can convert numbers to words in English, Italian or Hungarian, using the sample files from The FrankensTeam.
- Previously, Ken Puls shared his favourite Text functions, and now he has posted a Power Query version of that list.
- Scott Lyerly is getting a “multiple selections” error when copying data from one file to another — even if it’s a single cell. Do you have any suggestions for fixing this?
- If you get bored with budgets and financial reports, try making one of the cool Excel projects that Mashable found.
Here are some upcoming events, courses, recently published books, and other new items, related to Excel.
|Registration has opened for this year’s ModelOff competition. Test your financial modeling skills against top modelers from around the world. Participants from over 100 countries progress through two Online Qualification Rounds with a Live Finals Event held in New York.
|Microsoft Excel 2013 Functions and Formulas 3rd Ed. by Bernd Held
If you know Bernd Held, please let him know that the description for his book is incorrect on Amazon (the notes are for a VBA book). Here are the notes from the publisher’s site:
”In this completely updated edition covering Excel 2013 and previous versions, Microsoft Excel Functions and Formulas 3/e demonstrates the secrets of Excel through the use of practical and useful examples in a quick reference format. A comprehensive CD-ROM accompanies the book with tips, video tutorials, shortcuts, and ready-made Excel formulas.”
|101 Ready-to-Use Excel Formulas by Mike Alexander and Dick Kusleika
”The recipes in the book are structured to first present the problem, then provide the formula solution, and finally show how it works so that it can be customized to fit your needs. The companion website to the book allows readers to easily test the formulas and provides visual confirmation of the concepts presented.”
|Excel 2010 for Health Services Management Statistics by Thomas Quirk and Simone Cummings
”This is the first book to show the capabilities of Microsoft Excel to teach health services management statistics effectively. It is a step-by-step exercise-driven guide for students and practitioners who need to master Excel to solve practical health services management problems. If understanding statistics isn’t your strongest suit, you are not especially mathematically-inclined, or if you are wary of computers, this is the right book for you."
|Power Query for Power BI and Excel by Chris Webb
”Power Query makes it easy to extract data from many different data sources, filter that data, aggregate it, clean it and perform calculations on it, finally loading that data into either your worksheet or directly into the new Excel 2013 Data Model used by Power Pivot. This concise, practical book provides a complete guide to Power Query and how to use it to solve all of your Excel data-loading problems.”
Share Your Events and Articles
If you read or wrote any other interesting Excel articles recently, or have upcoming Excel events, please share a link in the comments below, with a brief description. Thanks!
Links to Recent Excel Books on Amazon.com