Contextures

Contextures News

Excel HLOOKUP and Hidden Zeros

Nov 28, 2017

Hide zeros on a sheet, use HLOOKUP, and more, in this week's Excel news. Visit my Excel website for many more tips, tutorials and videos.

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

Hide Zeros

On some worksheets, I hide the zero values, so it's easier to focus on the cells that have other numbers in them. Don't hide zeros though, if you have midnight times on the sheet! That can be a problem.

Unfortunately, there isn't a check box on Excel's View tab, to turn Zeros on or off. You have to go to the Options window, choose Advanced, and scroll down to the Worksheet settings section. And who has time for that?

hide zeros

If you want to turn zeros on and off quickly, use a macro. You could add this code to your personal workbook, so it's always available. It toggles the setting, so if zeros are On, the macro turns them Off, and vice versa.

Sub ToggleZeros()
  With ActiveWindow
  .DisplayZeros = Not .DisplayZeros
  End With
End Sub

If you have a copy of my Excel Tools Add-in, there is a Toggle Zeros command under "Sheet Appearance".

HLOOKUP For Rate

The poor HLOOKUP function doesn't get much attention, compared to its popular relative, VLOOKUP. HLOOKUP does horizontal lookups, and one way that I've used HLOOKUP is to find an interest rate, based on dates across the top of a table.

In the screen shot below, the formula in cell D5 finds the interest rate for the date that is entered in cell C5, based on the heading dates in row 2:

=HLOOKUP(C5,C2:F3,2,TRUE)

There's more information on HLOOKUP on my website. You can do a similar horizontal lookup with the INDEX and MATCH Functions.

HLOOKUP formula

Interactive Dashboards

Get ready for the new year, with a fresh set of Excel skills. Join Mynda Treacy for her free one-hour webinar, and learn how to build interactive dashboards in Excel, with or without Power BI tools.

The webinars won't be available again for a few months, so click this link to register today.

Power BI Webinar

Excel Articles

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

Comparison Charts - It's the holiday season, and if you have to do any shopping, or build any charts, this pair of pricing comparison charts might inspire you. How could you do something similar in Excel? The link takes you to Twitter - click the link there, to avoid the WSJ paywall. (Level - All)

Clean Data - Since you work in Excel, you've probably had to struggle with messy data. Apparently that is the biggest headache for machine learning and data science. That article is based on Kaggle's interesting survey of 16K data scientists. (Level - All)

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

Building a Village

Our granddaughter came to visit on the weekend, and one of our projects was to set up our little Christmas village. She likes things to look "just right", so it took a while to get the backdrop, fake snow and buildings arranged. We also did a bit of online gift shopping - that's much easier than going to the mall! We ended the weekend with a bowl of chili, while watching the Grey Cup game -- Canada's football championship.

weekly photo

That's it for this week! If you have any comments or questions, send me an email. I'll be out of the office today (Tues. Nov 28th), and will reply to you tomorrow.

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/20171128ctx.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

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: November 25, 2017 2:57 PM