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!

Weekly Excel Roundup http://blog.contextures.com/

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!”

________________

Weekly Excel Roundup http://blog.contextures.com/

You may also like...

2 Responses

  1. derek says:

    Grr, Kaiser Fung’s incorrect terminology is spreading. He says a bumps chart is a chart of quantity against category. I say that’s a line chart. A bumps chart is a chart of rank against stage, as described by Tufte reporting on the bumps races of Oxford and Cambridge.

  2. Doug Glancy says:

    Thanks Debra! Good point about the pivot tables.

    I was thinking about the example I gave in the post of “show all the pies ordered by all the people who ordered berry pie.” In database terms it’s a quick way to look at both sides of a many-to-many relationship. Just thought I’d mention that here, since that’s where I am (and I think it’s interesting).

Leave a Reply to derek Cancel reply

Your email address will not be published. Required fields are marked *