Excel Roundup 20140811
Does Ed Bott include your favorites in his list of 6 Excel power tips?
He included one of my favourites – Paste As Values. However, I usually Paste Values by dragging with the right-mouse button pressed, instead of using a keyboard shortcut. You can see that technique in the very short video below. Do you use the mouse or keyboard for Paste As Values?
Or watch on YouTube: Change Excel Formulas to Values With Mouse
Here’s what I posted recently:
- Use an Advanced Filter to find the top 5 orders, for products sold after a specific date. Show the results in place, or send the list to a different worksheet.
- Use a pivot table’s Top 10 filter to show best-selling products. Then, change the filter, to see how many of the low selling products it takes to equal the sales of the top 3 products.
- 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:
- David Ringstrom shows how to use the keyboard to select items from a filter list or drop down list. This will save you some time, if you like to keep your hands on the keyboard.
- Jeff Weir shares his macro for quickly changing or deleting Named Ranges. But if you’re not using a macro, you can edit the Refers To formula at the bottom of the Name Manager – you don’t need to go to the Edit window.
- Neil Saunders shows how to change cell fill colour to a category name, when reading Excel data into R. Even if you don’t use R, it’s worth reading the first paragraph!
- Mike Alexander has updated his Excel Dashboard add-in, with a couple of new features – Color Capture and Customize Percentages. If you bought a previous version, Mike explains how to get the new version.
- Are you using Office for the iPad? Recently, new features have been added, including Pivot Table interaction, and Send as PDF.
- Mark Tabladillo does a great job of outlining Microsoft’s Power BI tools, and what is available with different licensing options. His tables take some of the confusion out of the Power BI mystery.
- In his latest podcast, Chandoo recommends 3 books for aspiring analysts, and explains how to enter his Analyst Book Giveaway. Listen to the podcast, to get the details.
Here are some upcoming events, courses, recently published books, and other new items, related to Excel.
|Free webinar on Excel Tables with VBA, by Zack BarresseFriday, August 15, 2014 at 2 PM (Eastern time zone)Zack is one of the authors of the recently published book, Excel Tables: A Complete Guide. This hour-long webinar will include these topics:
|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