Contextures News 20180327

Excel Hide the Details

March 27, 2018

Can you guess the function, 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 supports the free info on my site!

Hide the Details

If you downloaded my pivot table sample file last week, you saw a trick for temporarily hiding details on a worksheet. The grouping feature hides the links for the hints pages -- when you want to see those hints, click the plus button to show them.

chart colour scale macro

Another way to hide things is with conditional formatting. This example shows a second question, if "Yes" is selected for the first question.

The cells for the second question have white font and fill, and no border. Then, if "Yes" is selected, the font, fill and border are changed by the conditional formatting rules.

chart colour scale macro

Guess the Function

Last month, we had a formula challenge, and needed to convert people's heights from feet and inches (5'7) to feet with decimals (5.58). I used a RIGHT/LEN combination to get the inches from the height:

=RIGHT([@Height], LEN([@Height])-2)

However, since nobody was 10' or taller, there's a function that could have done the job on its own. Do you know what that function is?

unicode function

I could have used Excel's REPLACE function instead - it replaces text based on location and length. This formula starts at character 1 in the Height, and replaces 2 characters with an empty string:


So, if the height is "5'7", that formula removes the "5'" and leaves us with the "7"

Do you have formulas where you could use REPLACE, instead of other functions? See more examples for the REPLACE function on my Contextures Blog.

Excel Articles

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

Data Model - Are you confused by Excel's data model? Sign up for a free live webinar, Build an Awesome Data Model in 7 Easy Steps, from Excel TV. Pick a time on March 29 or 30. (Level - Int)

Education - Alfred Thompson wonders if we should do more with spreadsheets in computer science education. He links to a podcast about spreadsheets (and other less important things) -scroll down to the transcript, if you'd rather read than listen. (Level - All)

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

Office Tools

From this photo, you can probably tell how things were going in my office on Friday. The shredder seized up, and it took me about 30 minutes to clear the paper jam. That letter opener isn't used much, since email replaced snail mail, but it certainly came in handy for this job!

It was thanks to LexisNexis training, long ago, that I learned how to do efficient online searches. I ran the information centre for a financial services company, and connection time was expensive back then, so we had to work quickly!

weekly photo

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

NOTE: For the online version, paste this URL into your web browser:
I'll also post any article updates or corrections there.

Debra Dalgleish
ddalgleish @

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.



Get weekly Excel tips from Debra


pivot power free






Last updated: May 4, 2018 3:26 PM