Microsoft Excel Hints and Tips

This page contains random questions and solutions that you may find useful or interesting. If you have a question or an interesting solution to an Excel problem that you would like to share with other users - then email me here.

Questions

• I would like to add a comment to a cell but do not want to use Insert Comment. Is there a way I can add a comment to the Formula Bar ?

• How do I restrict my users to using only a certain range of cells within one or more of my worksheets ?

• I have two dates and would like to calculate the number of years, months and days between them. For example, if my dates are 13 May 2002 and 28 March 2006 - then the result should be 3 years, 10 months, and 15 days. Is there an Excel function to do this ?

• Is there a keyboard shortcut for Paste Special Values ?

• I have exported a report to Excel from another application, but the dates have been exported as text. Is there a way to change the text into a date - with the correct date serial numbers so that I can sort my report in date order ?

• The dates are in US format and in text. How do I convert them into UK date format, with the correct serial numbers ?
   
• I have a list of data (values and / or text) in cells A1:A20 - some of the cells contain duplicate data and I would like to extract the unique items and display them in column B, but without using Advanced Filter. How do I do this using a formula ?

• I have a list of text in cells A1:A10 - I would like to count the number of cells that contain the text "Apple" anywhere in the cell - How can this be done ?

• How do I hide a worksheet so that it does not appear in the Format > Sheet > Unhide... Dialog Box ?

• I use F2 (edit) all the time, but often press F1 by mistake. How do I disable F1 so that nothings happens if I press it ?

• How do I perform a lookup based on the values in two columns ?

• How do I find out the name of the last user who opened an Excel file ?

• How do I count the number of words within a cell ?

• Is there a way to count the number of words within a range ?

• I have a spreadsheet with a column containing employee names.  The names are displayed last name, first name.  How can I reverse this so that the names are displayed with the first name first and the last name last ?

Return to Excel Exchange homepage.