Power BI Webinar

The Excel Addict - Help with Excel 2013, 2010,
2007, 2003

February 2, 2017
Greetings from The Excel Addict

Hi fellow Excel Addict,

Francis Hayes (The 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...

Francis Hayes Running Snow Studs

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
Francis Hayes (The Excel Addict)
Email:  fhayes[AT]TheExcelAddict.com


If you missed my last newsletter, you can click here to view it online.

Quote of the Day

"Whoever is careless with the truth in small matters
cannot be trusted with important matters."

-- Albert Einstein
If you have a favourite quote, send it to me and I may post it in my newsletter.

Excel in Minutes with TheExcelAddict.com

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.

Quickly Fix Inconsistent Data in Microsoft Excel 2007 2010 2013 2016 365
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;

Multi Select Autofilter in Microsoft Excel 2007 2010 2013 2016 365
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;

Select Just The Visible Cells In Autofilter in Microsoft Excel 2007 2010 2013 2016 365
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.

F2, Ctrl+Enter copies the Active Cell's value to all other selected cells in Microsoft Excel 2007 2010 2013 2016 365
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 F2CTRL+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.

If you've found this tip helpful, please share it.

Click here to get more tips like this every week

Thanks for supporting this newsletter and website

Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose.
"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