Posts

Posted on

How to correct names in lower case in Excel (Proper Case)

It can be a pain when you have a list of names that are all in lower case, and you want to be able to change them to the right case.  This can be easily completed using a formula to make the changes.

names-lowercase-excel-vba-courses-london

To change this list of names to the proper case, I can use one of Excel’s off-the-peg Text formulas by using PROPER.

proper-formula-excel-vba-courses-london
The Proper formula in the Formulas tab.

To apply this, I need to use an empty column (in this case B) to apply the formula to the original names.  This is only temporary as I will copy the names back into Column A when it’s all done.

I click on cell B2 to set up my formula.  I go to the Formulas tab, and select Text Formulas, and PROPER.

A dialogue box appears, and I can now select the first name in column A.

active-cell-excel-vba-courses-london
selecting the cell contents that need to be changed

When I click ok, the first name is now in the correct case.  I can copy this formula by using the autofill handle, to complete the changes to the remaining names.

Autofill-formula-excel-vba-courses-london
Autofilling the formula to the remaining cells

The names have all be corrected and I can copy them from column B back into column A.

Our Excel courses cover all levels from introduction to advanced, including Excel VBA courses london, https://www.stl-training.co.uk/vba-training-london.php.

Posted on

How to add Yes and No options to a drop down list in Excel

The previous post showed how to add a drop down list using a short list of terms.  Entering a drop down list can be even quicker when applying yes/no choices to Excel.

worksheet-for-yes-no-excel-training
I want to add “yes and no” options to this worksheet
  1. To set up the drop down list, I select the cells where I want the drop down lists to appear – in this example, I want to apply it to E2 to E6.
  2. In the Data tab select Data Validation, and in the Settings menu, select List
  3. I can add yes and no options, straight into the Sourcesection.

    yes-no-source-box-excel-training
    Adding the text options directly into the source section is quicker than creating a reference list.
  4. I tick the boxes for Ignore blank and In-cell drop down. Then click ok.
  5. The drop down list is now applied.

 

for more details on  /excel/training/ , take a look at https://www.stl-training.co.uk/excel-2007-intermediate.php