Excel Hide the Details
March 27, 2018
Note: For some products mentioned below, I earn a commission on sales. That supports the free info on my site!
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.
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.
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:
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?
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.
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)
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!
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: https://www.contextures.com/newsletter/excelnews2018/20180327ctx.html
I'll also post any article updates or corrections there.
ddalgleish @ contextures.com
Last updated: May 4, 2018 3:26 PM