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 

Contextures Posts

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.
  • 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.
  • 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.

Excel Announcements

Here are some upcoming events, courses, recently published books, and other new items, related to Excel.

498411exceltables Free webinar on Excel Tables with VBA, by Zack Barresse

Friday, 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:

  • Working with table objects
  • Why the macro recorder fails with tables
  • Creating and naming a table
  • Adding multiple rows or columns
  • Looping through rows and columns 
   
101excelformulas 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.”

   
excelhealthstatistics 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.”

   
powerquerywebb 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

__________________________________