I recently received an email from a reader asking how to add a hidden drop down menu inside a cell. I was unable to respond back to the reader (email address was invalid), but I thought it was a great question and wanted to add it to Excel Hints.  In just a couple steps, you’ll be able to add this feature to your spreadsheet and improve the validity of many spreadsheets you create. Continue Reading »

How many times have you tried to import a file into Excel, only to have extra blanks all over the place in the document?  Well that’s where the Trim Formula can come into play.  The Trim formula is used to clean up and remove all the leading and trailing blanks as well as all but one blank in-between text that you may have in a list of cells.  So now instead of going into each cell one-by-one, there is one formula to do all the work for you.  Let’s take a look at the formula first (it’s a short one):

=Trim(text)

This formula is very easy to use but can make you job a whole lot easier. Let’s take a look at some of the examples now.

Continue Reading »

First of all, I’d like to thank everyone that has subscribed to Excel Hints over the last month or so. I have been a little busy with changes at my full time job as well as other websites I’m working on, so the posting on here has been put on hold for the last month and a half. Starting next week, I have it on my schedule to begin adding posts to Excel Hints again.

Again, thank you for your patience and hopefully I can get some more good tips on her that will help you with your projects. If you have any questions or comments please use the contact form or leave a comment under a particular post.

Thanks, John.

The new design for Excel Hints is now active and can be seen at the homepage. Hopefully you will find that the new design is much more user friendly and easier to read than the old design.

A few of the new features that have been added:

• New Feedburner Feed: If you would like recieve all the updates for Excel Hints to be up-to-date with what is going on you have two options - RSS Feed or you can subscribe by email.
• Improved Search Capability: Use the search button on the top right of the page to search for posts related to what you are searching for.
• Improved Category Grouping: Catagories are now grouped under parent catagories to help with your navigation of the site. If you would like a list of all categories available please look at the bottom of the second column.
• New Contact Form: Any questions you have for Excel Hints that may not belong in a comment section can be emailed directly. There is also an option to have the email copied to your email address as well.

Over the next few days, I hope to get all the old links converted over to the new design. Anyone coming through a link on a search engine may still visit the old site until they try to access the homepage as I don’t have all the redirects completed yet. Please bare with us during that transistion time.

As always, any suggestions for improvement of the site is definitely encouraged.

Looking forward to adding more Excel Information.

I am currently in the process of redesigning the website, so please bare with me while I get this finished. I have been using Blogger to post the updates to the website, but have been limited in some of the things I want to do. So recently I decided to make the switch to Wordpress and redesign the site in the process.

Hopefully, the changes are for the better and will make for a better reader experience. Feel free to drop some comments on what you think of the change or any other suggestions you may have.

John

Excel offers an easy way to include some documentation within the spreadsheet you are working on. A few reasons for including comments in your work may be to explain a complex formula, note possible future changes or to remind you that you have some hidden columns you might want to come back to.

Adding comments is a very simple process in Microsoft Excel. There are two easy ways to go about adding a comment.

Adding a Comment #1: Select the cell where you want to add the comment. Select the “Insert” toolbar menu and select “Comment”. A box will appear and then go and enter your comment in the bubble/box provided.

Adding a Comment #2: Select the cell where you want to add the comment. Right-click inside the cell and select “Insert Comment”. Go ahead and enter your comment in the space provided and that’s it.

After your comment has been added, you can easily identify what cells have comments attached to them by the red triagle on the right side of the cell. To Edit the commented Cell, select “Edit Comment” from your right-click menu. To delete the comment, just right-click on the cell and select “Delete Comment”.

The Excel Length formula is a useful formula that can be used in many different circumstances. I’ll be showing a few of the useful examples of it below. But first let’s take a look at the formula itself:

LEN(text)

where text is any string you want to find the length of. This formula is really easy to use, and the hardest part may be remembering that to use this formula it is LEN(text) and not LENGTH(text), as you will receive an error if you try to use it this way.

EXAMPLES

Example 1: =LEN(”Bird”) will return 4, since there are 4 characters in the string “Bird”

Example 2: =LEN(123456) and =LEN(”123456″) will both return 6, as the formula sees both of these as strings and not the first one as a number.

Example 3: =LEN(”Excel Help”)will return 10, because in the string there are 9 letters and a space, which is included in the count for this formula.

Continue Reading »