Software

Office challenge: How would you improve this Excel sheet?

This week, share your Excel skills by completing a production sheet and learn why a Word document might show index tags, despite your best efforts to hide them.

I used the sheet below last week in Use Excel's conditional formatting features to highlight input cells. In that entry, I mentioned that with just a few enhancements, you could distribute the example sheet to users. This week's challenge is to prepare this sheet for distribution.

First, a word about the sheet: users select a savings method from the list box in A6 and conditional formatting highlights the specific cells that require input to return a result in B2:B4. In the original entry, I explained how to apply the conditional formatting. I didn't prepare the sheet for distribution as doing so would've distracted from the technique I was sharing. But, as I noted, the sheet isn't ready for prime time, not yet!

You can read the prior entry to recreate the sheet, or you can download it. Feel free to upload an illustrating figure and include a link in your response (unfortunately, you can't upload figures to the blog), but doing so isn't a challenge requirement. There aren't any restrictions, but do include reasonable instructions, if necessary, so everyone can apply your enhancements.

Please don't worry about hurting my feelings - I have no attachment to the sheet and I have thick skin! Now, how would you improve the example sheet?

Last week we asked…

Last week's challenge, Why won't Word hide index tags? has a simple solution, if you know where to look. Eljensen shared the answer I had in mind: the document is showing hidden text. This happened to me a few weeks ago when I opened a user document. Using the normal toggles, I couldn't hide the index tags. It took me a minute, but sure enough, I discovered the Show Hidden Text option checked.

To access this option, click the File tab or the Office button, select Options, and then choose Display from the left pane. In the Always Show These Formatting Marks On Screen section, you'll find the Hidden Text option. (In Word 2003, choose Options from the Tools menu.)

If you check the option, just to experiment, be sure to uncheck the option when you're done! Thanks Eljensen - you might not think of this option, unless you've experienced it firsthand.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

2 comments
vijaym.ghantasala
vijaym.ghantasala

I willmake the following changes before releasing for distribution. 1. Formatting to show what type of value user has to enter. For example I will add % to the heading at B5 , (#) to C5, ($) to rest of the columns, so that the user will understand a percentage has to be entered. 2. Enter comment s about the type of input expected in cells B6:G6 so that this will help the user to enter correct inputs. 3. Protect the sheet and allow only A6:G6 cells for input. This is to make sure the user does not change the formulas.

Editor's Picks