Contextures

Contextures News

Hide Excel Filter Arrows

August 22, 2017

Hide some arrows in a filtered list, and more, in this week's Excel news. Visit my Excel website for many more tips, tutorials and videos. We're on our summer schedule now, so the next newsletter will arrive on September 5th.

Note: For some products mentioned below, I earn a commission on sales. That supports the free info on my site!

Hide Filter Arrows

In a worksheet list, you can show or hide filter arrows in the heading row. Just go to the Data tab on the Excel Ribbon, and click the Filter button -- that will toggle the filter arrows on and off.

In some lists, you might not need to filter on every column -- maybe you only want people to filter by Product name or Region. However, there's no setting to hide specific arrows -- it's all or nothing.

The good news is that you can use a macro to hide the arrows in one or more columns. In the screen shot below, the arrows are hidden in columns A, C and D. Get that macro, and many others, on my Autofilter macros page. There's a workbook to download, and it has a sample list, and all the macros.

Hide some filter arrows leave others visible

Quickly Naming Cells

Do you create names for cells on your worksheets? After I build a table, I usually name some of the table columns, to make it easy to refer to those columns in formulas.

Instead of naming the table columns one at a time, use this trick to name several table columns at once:

  • Point to the top of the table heading cell (not the column heading button), and click twice, to select the headings, and all the items -- be sure that you're not selecting the entire worksheet column!
  • (Optional) To select more columns, press Ctrl, and click twice on another column heading
  • On the Excel Ribbon, click the Formulas tab, and click Create from Selection
  • Check the box for Top Row, and click OK

The names are created, based on your column heading text, and you can see them in the Name Manager

Read more about Excel Names on my website, and there's a sample file to download too.

name cells with Create from Selection

Excel Articles

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

Power Pivot - If you're not a Power Pivot expert yet, read Matt Allington's short article, What is DAX? He explains why DAX is worth learning, if you want to go beyond the basics with Power Pivot. (Level - Int/Adv)

Functions - If you have an Office 365 subscription, there are a few new functions in your version of Excel. Teylyn has a fun example of how to use the new TEXTJOIN function. In a single cell, the formula shows all the people who selected a specific item from a list. It's like the CONCATENATE function on rocket fuel! (Level - Intermediate)

PowerPoint - Tom Urtis shows how you can run a PowerPoint presentation from Excel. Get his sample files to see how it works

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

Garden Rebel

The orange marigolds (and lots of weeds) have taken over our garden this summer. There are a few brave yellow marigolds though, like the one in this week's photo. After I downloaded it from my phone, I decided that the photo needed a little caption. Good job, yellow marigold -- just be yourself!

weekly photo

That's it for this week! Watch for the next newsletter in 2 weeks.

NOTE: If you have trouble with the images or links in this email, paste this URL into your web browser, to see the online version: http://www.contextures.com/newsletter/excelnews2017/20170822ctx.html

Debra Dalgleish
ddalgleish @ contextures.com

Debra Dalgleish

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.

 

Search Contextures Sites

Excel Data Entry Popup List

 

 

 

 

Last updated: August 18, 2017 9:50 AM