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

  • 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

__________________________________

You may also like...

11 Responses

  1. Randy Rhea says:

    New blog post – Data Cleansing Quick Trick. An easy way to help in cleaning up data that has blank cells.
    http://topnotchexcel.com/data-cleansing-quick-trick/

  2. MF says:

    Hi Debra,
    Nice trick in using mouse for converting formula into values. I know that right-click trick for FILL. Didn’t know that for moving cells until now. Thanks for sharing! :)

  3. MF says:

    Hi Debra,
    FYI. The links for “pivot table’s Top 10 filter” and “Top 5 Items in Advanced Filter List” are the same.

  4. Hi Deb, I posted a new article on my blog on the GoTo Method of the Excel Application Object : http://dataprose.org/2014/08/advance-to-r1c1/

    Along the way, I touch on a few other topics as well:

    Copy Method of the Worksheet Object
    Cells Property of the Range Object
    Cells Property of the Worksheet Object
    Find Method of the Range Object
    Range Property of the Worksheet Object
    Select..Case Statement
    Application Worksheet Function Randbetween
    InputBox Method of the Excel Application Object
    VBA Instr() Function

  5. Hi Deb,

    I posted a new article on my blog on how to load RecordSet Objects using VBA and ADO: http://dataprose.org/2014/08/this-will-go-down-on-your-permanent-recordset-ado/

  1. August 23, 2014

    […] week, I learned from Debra Dalgleish to convert formula into value by using mouse.  What a simple and handy trick!  Honestly, I learn a lot from Contextures Site.  After […]

Leave a Reply

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