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!
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?
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".
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:
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.
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)
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.
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
ddalgleish @ contextures.com
Last updated: November 25, 2017 2:57 PM