February 2, 2017
Hi fellow Excel Addict,
It's February and fre-e-e-e-e-zing cold but I'm not complaining too much. There's not much snow around here right now so I'm still able to get outside and run.
Unlike many places going through winter right now that have snow on the ground since November or December, Newfoundland is an island out in the North Atlantic, so during the winter months we have frequent freeze and thaw cycles due to our temperate ocean climate. We do get our share of snow during the winter but it often melts away. As you can see from this picture, there is virtually no snow on the ground now despite having a big snowstorm just two weeks ago.
I like it this way because I have been able to keep up a pretty consistent running schedule.
But when the snow does come, it may slow me down but it doesn't stop me. I'm prepared...
In today's 'Excel in Minutes' tip I'm going to show you a simple technique you can use to 'Quickly Fix Inconsistent Data in Multiple Cells'.
If you missed my 'Excel in Seconds' newsletter on Tuesday, I revealed 'Secrets of the AutoSum Function'. You can read that tip here.
I hope you have a great weekend and keep on Excelling,
Keep on Excelling,
Francis Hayes (The Excel Addict)
If you missed my last newsletter, you can click here to view it online.
If you have a favourite quote, send it to me and I may post it in my newsletter.
Quickly Fix Inconsistent Data in Multiple Cells
If you have a list of data with many cells containing different variations of, what should be, identical data, here's a trick you can use to quickly edit multiple cells and make them all consistent.
In this example, I have compiled a list of some of the bands, albums and songs I listened to when I was growing up. Since I copied the data from various sources, you'll see that there are many inconsistencies in the artists' names. There are different variations of names and some of the names are even misspelled.
If your dataset is not too large or is not spread out too much, you may be able to easily select the cells containing the names you want to fix by holding down the CTRL key and selecting each one. Then you can put the same value in all of these selected cells in one shot by typing the correct name and pressing CTRL+Enter.
You can even avoid typing the name by pressing the Tab key until you move the 'active cell' to a cell containing a correct name. Press F2 (to go into Edit mode) and then CTRL+Enter to copy the correct name into all of the selected cells.
If you have a large list of cells that you need to fix...
1) Use the multi-select feature of AutoFilter to select the names that you want to fix;
2) Highlight all of the cells and press ALT + ; (semicolon) to select 'visible cells only', then press the Tab key to move the 'active cell' to a cell with the spelling you want to use;
3) Press F2 to go into Edit mode', then press CTRL+Enter to replicate the value from the active cell to all the other selected, visible cells.
There are other ways to fix inconsistent worksheet data such as using Find and Replace or simply Copy and Paste but the main idea of this tip is to demonstrate to you how easily F2, CTRL+Enter can be used to quickly duplicate multiple cells.
Once you get used to doing this, you will see that selecting all of the cells, (F2) editing the 'active cell' and pressing CTRL+ENTER really is easier than selecting one cell, editing it, copying it, then selecting all of the incorrect cells and pasting.
I use this technique quite often and I'll bet now you will think of many situations where you can use it too.
Note that this also works the same for editing formulas.
!! Please let me know your thoughts on this.
|"Spreadsheets Tips From An
Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630