Top 5 Items in Advanced Filter List

When you try to use the Top 10 filter, on a list that already has some filters applied, the results probably won’t be what you want. The Top 10 feature ignores the filters on other columns, and just returns values that are in the overall Top 10.

Recently, I showed a workaround for that problem in this blog post: Top Ten Values in Filtered Rows. In that example, I added a new column, and used the SUBTOTAL function to show the value, then filtered that new column. Hidden rows would have a value of zero, thanks to the SUBTOTAL function, so they wouldn’t be included in the ranking.

autofiltertop10filtered05

Continue reading “Top 5 Items in Advanced Filter List”

Excel Roundup 20140728

The Office 365 Garage Series has a video on what’s new in the Office Apps, and they demonstrate some cool new features. The Excel App is mentioned at the 7:12 mark, when they look at the new APIs for formatting.

However, at the 3:12 mark, they downplay the old development methods, including VBA and COM, and rave about the new methods — HTML and JavaScript, that are used for the online versions of Office.

Apparently it’s almost impossible to find people who are still working in the older development platforms. What do you think of the chart they use to “validate” the trend? Are they really using a sample size of 12 COM jobs, compared to a final number of 5 JavaScript jobs? I’m not a statistician, but that doesn’t qualify as “dramatic decline” versus a “huge insurgence” in my opinion!

I did like the way that he drew on the chart though, to change it from a column to a line chart. You can see that at the 4:20 mark in the video below.

Or watch on YouTube: What’s new in the world of apps for Office?

Continue reading “Excel Roundup 20140728”

Set Row Height With REPT Function

Thanks to AlexJ for suggesting a great use for the REPT function – setting a minimum row height. He uses this technique to add a bit of spacing in his tables, so they’re easier to read.

You can watch the steps in this video (or watch it on YouTube), and the step-by-step instructions are below the video.

Add Space in an Excel List

For example, here is my To Do list, with a few items to work on, around the house. Most of the Task Descriptions are short, and fit in a single line.

Continue reading “Set Row Height With REPT Function”

Excel Roundup 20140714

Here’s this week’s Excel roundup, with news, tips, and announcements.

Contextures Posts

Here’s what I posted recently:

  • Enter a number in the input box, and that amount is added to each cell in the selected range, with this macro. I use this to add a week, to a  group  of date cells.
  • I sent out an update for my PivotPower Premium add-in last week. If you missed the email, or didn’t open it before the deadline, please let me know and I’ll send you a new link. You can read about the new features on my Pivot Table blog. [Update: This product is no longer available]
  • 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:

  • On Microsoft’s MSDN Blog, Eric Ligman shared a list of free ebooks, including Excel shortcuts and quick start guide.

Excel Announcements

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

  • Mynda Treacy’s Excel Dashboard course is open for registration, and I highly recommend this online course, which has excellent content, and great student support from Mynda. Save 20% if you sign up by July 24th. Registration closes on July 31st.
    BONUS: If you sign up for the course through this link, you can have a copy of any one of my Contextures Products. When you sign up, type DebraD in the Referral Code box (below the Buy Now button), and email me your receipt number.Excel Dasboard Course
  • Charles Williams has released a new version his add-in, FastExcel V3, and you’ll get 50% off the bundle price, if you use Coupon Code FXLV3Intro Warning: The 50% Discount ends soon — July 31 2014. Don’t miss the deadline!
Excel Tables: A Complete Guide (pdf version)
by Zack Barresse and Kevin Jones

”The book begins by explaining what tables are, how to create them, and how they can be used in reporting before moving on to slightly more advanced topics, including slicers and filtering, working with VBA macros, and using tables in the Excel web app. Novice Excel users and experts alike will find relevant, useful, and authoritative information in this one-of-a-kind resource. “

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!

_____________________

Learn Excel Dashboard Course

____________________

Add New Items to Excel Drop Down List

There are a few sample files on my web site that let you automatically add new items to a drop down list. The most recent version works in newer versions of Excel, and uses named tables to store the lists.

When the code detects a new item, it asks if you want to add it to the drop down. If you click Yes, the item is added, and the lookup list is sorted in alphabetical order.

Continue reading “Add New Items to Excel Drop Down List”

Excel Roundup 20140616

If you still have a Commodore 64 in your basement, you could run a one-line maze generator, in Basic. If your mom threw out that C64, years ago, you can do something similar in Microsoft Excel.

On Google+, Graeme McRae posted a VBA function that will create a maze, using a random string of / and \ characters.

Here’s a maze that I created in Excel 2013, with a gold background, and bold font. That should keep your kids (or you) busy for a while!

Excel Maze
Excel Maze

Continue reading “Excel Roundup 20140616”