Excel Roundup 20150525

In this week’s roundup, see how to reverse last and first names, pin a shape to a chart point, learn some Solver history, and more.

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:

  • 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. Reverse First and Last Names

Ben Kusmin uses find and replace, and a formula, to reverse first and last names in a column. If you’re using Excel 2013, you could use the Flash Fill feature, to quickly reverse the names.

On my Contextures site, I show how you can reverse the names with a formula too.

3. Stick a Shape on a Chart

Jon Peltier shares a simple trick for adding a shape to a point on a chart, and making it stick to that point, even when it moves.

4. Enjoy the Challenge

Long ago, Andrew Wulf built a spreadsheet program named Trapeze, and he still loves a good programming challenge. So, don’t let those complex formulas, or that VBA code defeat you — “The next time impossible stares you in the face, stare back.”

5. A Little History

If you use Solver in Excel, and/or are interested in Excel’s history, listen to Chandoo’s interview with Dan Fylstra, who created Solver

And speaking of history, Bill Kamm looks at how baseball and its statistics have changed, since his dad played for the White Sox, back in the 1920s and 30s. Fortunately, you can do the analysis in a spreadsheet now, instead of with pencil and paper.

6. Create an Excel Add-In

If you’ve ever wanted to build your own Excel add-in, to store some of your favourite macros, Chris Newman shows you the steps.

7. Clear the Errors

If you delete columns, or rows, or an entire worksheet, John Michaloudis shows how to clear the cells that might end up showing #REF! errors.

8. Writing Dashboard

Previously, we saw how Matthew Eaton uses Excel to keep track of his daily writing. Now he has created a dashboard, to summarize his progress. If you don’t like purple, you can use a different colour.

9. Push to Excel

If you’re working with data from a database, or another Excel file, Winston Snyder shows how to use VBA to push that data to Excel, and create a pivot table.

10. New Chart Types

Jorge Camoes shows the new chart types that will be in Excel 2016, and wishes there were more additions, like small multiples.

If you’re not sure what that means, Wikipedia explains what small multiples are. I Googled that “for a friend.”

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

________________

You may also like...

5 Responses

  1. Chris Newman says:

    Thanks for featuring my article this week, Debra!

  2. Doug Glancy says:

    As I commented on Winston’s blog, I think his “push to Excel” post is very well done. On the other hand it’s a lot of VBA to accomplish the same things that can be done by pulling data into Excel with little or no VBA. Do you have any thoughts on pushing vs. pulling data?

    I like Chris Newman’s ribbon article, particularly the mass handling of control attributes. Bob Philips takes the concept of dynamic ribbon controls as far as I’ve seen in this post: http://blogs.msmvps.com/xldynamic/2012/03/06/who-says-the-ribbon-is-hard/ I’ve never gotten around to implementing his method, but I like the concept.

    • Doug, I use both push and pull, but in most situations, find that a pull works better. When I want a static set of data in a workbook, I use macros, to pull in a snapshot of the current data. Occasionally, I use Microsoft Query to create a live link to the data, and now I’m experimenting with Power Query, but not many clients are using it.

  3. Chris Newman says:

    Thanks Doug! I think the only thing that Bob’s code has that I hadn’t thought of was making the icon size variable (normal vs. large). Thank you for pointing me in that direction.

Leave a Reply to Chris Newman Cancel reply

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