Efficiency365 Test 3: Excel Date Cleanup : Solution

This is the continuation of the article Efficiency Test: Excel Data Cleanup

The date cleanup challenge was to change all cells having dd-mm-yy formatting to dd-mmm-yy formatting across all sheets in the most efficient way.

Common methods are inefficient

Copy – Paste Formats, Format Painter with double click, Select All – Format Cells are typical approaches which most of us will think of.

The methods which naturally come to your mind will work. But are repetitive and time consuming. Also error prone because you are manually looking for cells to change. Therefore, Copy and Paste Format or Format Painter are not efficient approaches.

Select All and change format is not possible because all numbers will be converted to dates as well. We don’t want that to happen. In addition, select entire sheet operation will also make ALL EMPTY CELLS in the selection forcibly have date formatting. So if you add any data in it, all numbers will unnecessarily get converted to dates. You will waste additional time repairing these issues.

The right approach

The key thing is not to get stuck in your “comfort zone” of known features. Once you think with a clear mind, you will realize that this is a Find / Replace type of requirement.

The only problem is that we are finding FORMATTING – not data or formulas.

The Find – Replace Dialog

Ctrl F opens Find dialog. Ctrl H opens Find – Replace tab directly.

We cannot type the format we are looking for dd-mm-yy in the Find textbox.

Most people would tend to give up at this stage. Here is a new thought which will make you more efficient.

Before you give up – explore all options in that context

It takes few seconds of active and conscious observation to notice the Options button. Most people have never clicked on it. Those who have opened Options before only remember what they have used, like match case, workbook, formula or value, etc.

When you notice the Options button you have two choices.

Choice 1: Give Up. Convince yourself that there is no better way. Click Close or press Escape and use your favorite inefficient method lifelong.

Choice 2: Have the courage, willingness, curiosity and proactivity to explore by clicking on the Options button and try to find a solution.

Most of us tend to ignore buttons like More, Options, Advanced because we think we don’t need those option.
We simply don’t think that Excel may have noticed your problem and provided a solution.

Fortunately, every small problem has been observed and a solution is already created in most cases.

It is in your interest to explore so that you find the most efficient method and stop wasting your life.

Options

Clicking the Options button shows more features. Now you are actively exploring every item in the dialog. You will surely notice the Format… button.

Most of you who have tried this test will be so happy that you have found the solution that you will click on the Format button next to the find textbox immediately.

Now it shows the entire Format Cells dialog and asks you what kind of format you want. Notice that there is an additional Clear button for each tab of the dialog. Did you notice it? Did you try to find out why the button is inactive? Probably not.

Problem: What is the exact format we are trying to “Find”?

The format cells dialog has many options. You will need to click on Number Formatting – Custom and then specify the format you need.

At this stage you will realize that you do not know the exact format. Yes it is dd-mm-yy but in the actual cells it could be dd-mm-y or dd – mm – yy. If you type the wrong format, Excel will not be able to find the cells.

Same problem exists while specifying the Replace Formatting. If you type a different variation of dd-mmm-yy, like for example dd/mmm/yy you will end up creating a third variation and complicating matters further.

If you want to be accurate with this operation, these are the steps you have to perform.

1. Ctrl H
2. Click on Options
3. Click on Format
4. Realize the problem
5. Press Escape twice
6. Right click in a cell with the wrong format
7. Choose Format Cells
8. Choose Custom
9. Copy the exact format from the cell
10. Again go to Find – Options – Find – Format – Custom and Paste the format
11. Press Escape again
12. Right click in a cell containing the desired format (mmm)
13. Go to Custom
14. Copy the format
15. Again come to Ctrl H dialog – Options – Replace Format – Paste the format

Does this sound efficient? Obviously not!

So here is what you missed.

That Drop Down was not created for decoration purposes.
Open it and see what it has to offer.

Some great analyst and developer from Microsoft know that you would have to take 15 steps to get the formats right. So they created a very useful feature Choose Format From Cell…

Unfortunately, the very beneficiaries of this great feature – which includes billion + people – never noticed the option because they never dropped the drop down.

Imagine what must have happened to the developer who wrote the code for the feature? Probably in deep depression

Choosing the option makes your mouse cursor like a color picker – this is a format picker.

Click on the cell with dd-mm-yy formatting.

Open the same option from the Replace Format Drop Down and choose a cell having dd-mmm-yy formatting.

Wait. DO NOT click on Replace All

We are so impatient that we forgot the fact that the find replace has to work across sheets.

Open the drop down and choose Workbook.

Now click on Replace All and surprisingly, the job is done instantly – by Excel

That is the correct way.

If you had multiple variations of undesirable formats, we will need to repeat the operation multiple times. But this repetition is NOT inefficient. Excel is doing all the work. Not you.

Summary

To cut a very long story short, here are the steps.

1. Ctrl H
2. Open Options
3. Choose format from dd-mm-yy cell in Find – Format
4. Choose format from dd-mmm-yy cell in Replace – Format
5. Choose Workbook under Search In
6. Click Replace All

Job done? Not yet

Now you must be really angry with me. But trust me there is one problem still left to be addressed.

Of course the core job is done. You will now close this file and get back to your regular work. Later during the day, if you had to do some different Find operation, it will FAIL.

Keep the file open. Go to Sheet 2 – look at cell A4. What is the value? 6642. Right?

Now go to Find, type 6642, click Find Next. It will FAIL

Frustrating. What went wrong?

You still missed a feature!

In spite of my insisting on you noticing all features, dropdowns you missed this feature.

Why? Because when you saw it, you were so happy that you found Choose Format From Cell… that your brain did not register the Clear Find Format option.

Another reason is that the option is inactive (disabled – in technical terminology). That makes even less noticeable!

So why is the search failing?

Because the formats you selected earlier are still remembered by Excel. Notice that there is a * Preview written there.

Because of this, Excel is searching for a cell containing 6642 and has the format dd-mm-yy

To complete the story…

Open the dropdown next to each Format button and choose Clear Find Format. This button is now active. Because there IS a selected format. Earlier when you went there you had NOT chosen the format – that is why the button was irrelevant – hence inactive!

In short, whenever you use Find / Replace with FORMATTING, remember to clear both formats as soon as you finish the replace job. This will prevent you from confusing yourself later in the day.

Of course if you close and reopen Excel, the format is cleared automatically.

What did we learn?

Although this sounded like a test, it was a very good learning experience. Here is what you should learn from this activity.

When stuck with a problem, think differently – beyond your comfort zone.

Always explore – don’t give up

Look at ALL the options, drop downs, user interface elements

Remember to notice inactive options and think when will the option be relevant

Notice any side effects your actions may create and handle them proactively

If you notice any inefficiency, have confidence that Microsoft must have already noticed it and given you a solution for it. Explore, Find, Use.

Of course you also learnt how to do Find Replace with formatting

Was this information available on web?

Go to browser and search for Find Replace Formatting in Excel.

You will find many references. I read the first five. You can read more if you like.

Check what is written there. The content is obviously correct. But the problem is you did not know this was the solution to your problem. That is why you would never search for this topic!

Furthermore, most of the content is just showing the steps. Not the thought process, learning methodology and nuances.

That is the problem with the Feature specific Tips and Tricks content!

What Next?

There is much more to Find Replace Formats than what we saw just now. I will cover it in another article soon.