One of my subscribers wrote to me with a problem. It seems he had a worksheet that had multiple cells containing different variations of what should be identical information.
Luckily, I was able to advise him of a trick he could use to quickly edit multiple cells and make them all the same at one time.
Suppose you had a book list to keep track of the books and their authors so that you would know what you had read when you visited the library or your local book store? You noticed one day that one of your favorite authors was listed with a middle initial, without a middle initial and with his name misspelled in various cells. So there is an example of different variations of names and misspellings.
To quickly correct the data in multiple cells, follow the steps below:
- Select all of the cells containing the name you would like to streamline.
- For non-contiguous cells, depress and hold down your CTRL key and click to select each cell.
- Now you can key in the correct name and click CTRL + Enter, which will enter the same value in all of the selected cells.
- If you would like to avoid keying in the name, with the cells selected, use your Tab key to move the active cell to a cell containing a correct name.
- Click the F2 key (which will put you in edit mode).
- Now click CTRL + Enter to copy the correct name into all of the selected cells.
If you have a very large list of cells that need to be corrected, follow these steps:
- Use AutoFilter to select the names you want to correct (multi-select is available in versions 2003 – 2010).
- Select all of the cells and click CTRL + ; (semicolon) to select visible cells only.
- Click the Tab key to move the active cell to a correct cell.
- Click the F2 key to enter Edit mode.
- Click CTRL + Enter to copy the value from the active cell to all the other selected visible cells.
Extra! You can also use this same F2 | CTRL + Enter trick when editing formulas.