Excel VBA: Ignore Merged Cell Warning

Excel can be annoying sometimes. Take for example the stupid alert letting me know I'm trying to do something with a merged cell. Sometimes this sort of thing is good to know but more often than not, I know what I'm doing and want to do it without being nagged about it. So, for future reference, if you want to suppress the merged cell alert just enter this VBA code in your workbook:

Application.DisplayAlerts = False

Find Merged Cells in Excel without using VBA

1. Open a Find and Replace Menu (Ctrl + F).
2. Next to the Find What Field is a Format Box, Click it.
3. This opens the Find Format Menu screen which looks very similar to the Cell Format Tabs and screens
4. On the Alignment Tab, remove any ticks in the Wrap Text or Shrink to Fit boxes.
5. Leave a tick in the Merge Cells Box (tick mark should be bold) and click the OK Button to return to the Find and Replace screen.
6.Ensure you are searching Within: Workbook and Look in: Values and the Find what: box is not populated.

7. Now click the Find All Button and ALL merged cells are shown in the lower window and are selectable to jump to their location.

Nifty Excel Trick #1: Format Percentages Correctly

I work a lot with Excel and occasionally have to search the web to remember how to do a seemingly simple task. A common problem is when Excel thinks a number you entered as a percent should be multiplied by 100 rather than by .01.

For example, you have several numbers like so:

You want them to be formatted like percents rather than numbers so you hit the thinking that will solve your problem but Excel goofs it up and makes it look like this:


WTF! That's not what you wanted. Why did it do that! If you've got a ton of numbers you aren't excited about having to manually go in and delete all those zeros but wait there is an easier way.
You could insert a column next to the column you want formatted and add a formula (e.g. =A1*.01) like so:

Now it will have it in the right format and you can now hit the % button to make it work:

Copy the formula down by dragging that little black square in your highlighted cell or double click it and "boom goes the dynamite!":