Excel Roundup 20150511
In this week’s roundup, choose a file format, get an Excel book sampler, fix dates, and build an add-in.
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:
- Use the Percent Running Total feature, to analyze sales in a pivot table. Which products sold best, or how did sales progress over the year?
- 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. Power Query Training
Excel’s new Power Query add-in can do amazing things with your data. If you’d like to learn more about it, experts Ken Puls and Miguel Escobar have launched a new website, where they will offer live, online Power Query training. You’ll have two days of live online training (4 hours per day), then a live follow-up Q&A session, about a week later. Take a look, and if you decide to register, use the coupon code DEBRA to get 10% off.
3. Office 2016 Public Preview
The Office 2016 preview is now available to the public, and you can read about it on the main blog.
Or, get instructions on how to get access to the preview.
4. Resources for Formulas
Joel Lee shares 7 resources that will help you with Excel formulas. Contextures is included, so it must be a good list!
5. Build an Excel Add-in
If you’ve ever wondered how to build your own add-in in Excel, Jan Karel Pieterse shows you the steps.
6. Dot Graphs
Do you ever make dot graphs? Maarten Lambrechts shares 7 reasons why you should.
If you connect the dots, you’ll end up with a Slope chart, also known as a Bump Chart. The Science Goddess explains how she uses those to present data.
7. Choose a File Format
When you’re saving an Excel file, which format should you use? xlsx? xlsm? xlsb? Zack Barresse explains the pros and cons of each format.
8. Filter by All Selected Values
Recently, we’ve seen was to filter a table by the selected cell’s value. Now, Doug Glancy has taken things a step further, with a macro to filter by the values in multiple selected cells.
This feature is already built in for pivot tables – select multiple items in a field, right-click, and click Filter. Then, click Keep Only Selected Items.
9. Change YYMMDD to Real Dates
In a YouTube video, Mike (“ExcelIsFun”) Girvin shows how to use a formula to change text numbers in a YYMMDD format, to real dates. Also read the comments, to see a couple of other formulas that solve this problem.
10. Excel Book Sampler
You can download a free sampler of Excel books from Que Publishing, and you don’t even have to provide a name or email address. The book is over 200 pages, and contains chapters from 8 books, with a range of skills levels.
For example: Getting started with Excel 2013, Understanding Formulas, Understanding Data Models, and Creating a Basic Pivot Table. The download link is about halfway down the page, at the right side – click on the book cover, or the text link above it.
11. Excel Songs
Bill Jelen (Mr. Excel) has updated the list of Top 10 Excel songs on YouTube.
At the top of the list is “Excel Funcs“, a parody of (and even better than) Uptown Funk. The lyrics are in the description, so you can sing along. “Pivot Tables, hallelujah!”