Contextures News 20190205

Excel Drop Down Trick

February 5, 2019

Pivot table formulas, drop down trick, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Drop Down Lists

To make it easy for people to enter data on a worksheet, you can use data validation to create drop down lists. For example, make a table of employee, and show all those names in the drop down.

Someone asked me how to make the drop down show a shorter list, if the employee list is filtered. To do that, I used a formula workaround (no macros).

First, I added another column in the employee table, with a formula to number the visible rows:

=--SUBTOTAL(3,B$3:B3)

The SUBTOTAL function ignores the hidden rows, and numbers the visible rows only 1 to 6.

Next, I created another small table, with numbers typed in the first column, and this formula in the second column:

=IFERROR(INDEX(tblEmp[Employees], MATCH(B2,tblEmp[ListNum],0)),"")

The final steps were to create a dynamic named range, based on the new table, and change the drop downs to use that list, instead of the full list of names.

To see all the details for setting this up, go to the Drop Down from Filter List article on my blog.

Pivot Table Formulas

It's easy to change the summary function in a pivot table, from Sum to Count or Average, and several other functions. And, if you need to, you can create your own pivot table formulas, with calculated items and calculated fields.

• For example, use a calculated field to show tax: ='Total Sales' * 8%
• Or, use a calculated item to sum 2 products: =Pen + 'Pen Set'

If you're not sure how these formulas work, watch my new video -- it shows a simple example of each formula type, and also shows how to make a quick list of all the formulas you created.

These formulas only work with normal pivot tables though, not OLAP-based ones. So, if you checked the box to "Add to Data Model", the formula commands aren't available.

Go to my Contextures site to learn more about pivot table Calculated Items and Calculated Fields.

Excel Articles

Here are a couple of Excel articles that you might find useful or interesting.

Personal Finance - Len Penzo uses Excel for his personal finances, because it's more hands-on than sites like Mint. He shares a few tips, and there's a good discussion in the comments. (Level - All)

Spreadsheets - You'll see familiar items in Ariel Fischman's spreadsheet collection. He's got VisiCalc, Multiplan, beta copies of Excel, and much more. He can't find Excel 2001 for the Mac though, if you have a copy of that. (Level - All)

Also see: My Excel Products || Excel Events || Previous Issues || Weekly Humour

Temperatures

There was a deep freeze here last week, and lots of snow, so it was perfect weather to stay inside and work with Excel. There's a digital weather station in our front hall, and it shows the outdoor temperature (that was a "warm" day), and other info.

I also have a Galileo thermometer that shows the indoor temperature. It's not as accurate, but it's much prettier than the digital one.

NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2019/20190205ctx.html
I'll also post any article updates or corrections there.

That's it for this week! If you have any comments or questions, send me an email.

Debra Dalgleish
dsdalg@ gmail.com

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.