Highlight all formulas

In a sophisticated worksheet, we so often have to work extra careful in order not to accidentally messed up the worksheet, like replacing formula cells with static values. This is very troublesome and a pain-in-the-ass. Fortunately there is an easy way to highlight all cells that have formula so that we can easily identify which cells are calculated-cells.

Step 1

We start by selecting the entire sheet by pressing the entire sheet select button

Step 2

Then create a new conditional formatting (Alt + H, L, N) from the HOME menu.

Step 3

Then select "Use a formula to determine which cells to format", and enter the following formula into the rule box:
=ISFORMULA(A1)

Step 4

Press the Format button. This will open the Format Cells dialog.
Set the Font color to a color, here I choose purple. You can also set the Fill color so that formulas that return empty strings will also be visibly highlighted. Tips: To make the highlights look nice, set the Border color as well, like 10% grey.

Step 5

Press OK to apply and return to the Edit Formatting Rule dialog.
Press OK again to apply the formatting rule.

Now all cells that contain formula will be highlighted.

~Cheers

Delete custom styles in Excel (VBA)

If your workbook has too many 'custom' styles and is taking forever to delete them one-by-one, why not delete them all at once with the help of macro in 5 simple steps.

The macro will not delete the 'built-in' styles such as shown below.

Step 1

In excel, press Alt + F8. This will bring up the Macro dialog.

Step 2

Type in a name for the macro, let's give it "DeleteCustomStyles" although you can use other name you see fit.

Step 3

Then press Create. This will bring up the VBA window.

Step 4

Type in (or just copy and paste) the following code into the Sub region.

Note that the first and the last lines are already in the VBA window.

Sub DeleteCustomStyles()

    Dim iStyle As Style

    On Error Resume Next

    For Each iStyle In ActiveWorkbook.Styles

        If Not iStyle.BuiltIn Then

            iStyle.Delete

            Application.StatusBar = "Custom style " & iStyle.Name & " deleted"

        End If

    Next iStyle

    Application.StatusBar = False

    MsgBox "All custom styles have been deleted."

End Sub

Your code should look like this.

Step 5

Close the VBA window (Alt + Q), go back to excel, and press Alt + F8 again.

Select the "DeleteCustomStyles" macro and then hit Run.

If your workbook has about 100+ customs styles, it will only take a few seconds to finish. But if your workbook has so much, like 50,000+ custom styles, it may takes about 10 to 20 minutes to delete all the custom styles.

So if your workbook has so many custom styles, just sit back and relax while the macro delete the unwanted custom styles.

~Cheers

The F9 key could be your lifesaver

Remember the time when you wrote an Excel formula which you are very familiar with and probably used it over a thousand times but suddenly the formula seems to stop working as expected?

You go berserk retyping and retrying the formula again and again while your boss is standing there waiting for you to give her a quick answer.

Your heartbeat is racing and your fingers start sweating, not knowing what's going on!?! Well, the rescue is probably just one button away... the F9 key!

So what's going on?

If you are using a company provided computer which is using Excel add-in, you are likely to have faced this situation. The reason is add-ins sometimes turn Automatic Calculation off to speed things up, put it simply. These add-ins should have turn Automatic Calculation back 'on' after finishing its task. But sometimes an error could cause it to "skipped" the final step without notifying the user.

To be fair, this is not bad programming by people who created add-ins to help you perform your job. Errors do happen and it is very difficult, almost impossible, to debug everything.

Besides it has to do with the very not-so-obvious place where Microsoft places the Calculation Options.

In Excel 2003 and before, it is even worst!

How F9 can help here is that it is the shortcut key for "Calculate Now", a little button sitting right beside the Calculation Options.

It forces Excel to perform calculations on all formulas across the workbook immediately when Calculation Options is set to manual.

Next time when you find your highly familiar formula behaving wildly, try the F9 button first before chasing down the sneaky one. If it doesn't tame it, then you are most likely at fault with your formula. If it does, then make sure to go to Calculation Options and set it to Automatic.

3 ways to manipulate cell value in Excel (VBA)

3 ways to change the value of a cell (or cells).

Lets say the value on cell B5 currently is 80 and we want to change the value to 100, here are the 3 methods we can use achieve this.

Range()

Range() is probably the most commonly used method and would be the first one to learn if you already learn how to code in Excel VBA. For the above task, it is written as follows

    Range("B5").Value = 100

To use this method you have to provide the address in text form i.e. in double quotation marks " ". You can also use a variable to represent the 5, such as x = 5, in which the code will be written as

    Range("B" & x).Value = 100

This can be useful if you want to loop through a few cells and change the values sequentially. However, "B" will be a fixed reference and so there is no way to make changes across to other columns with this method. This is where Cell() comes in.

Cells()

This could be the second most commonly used method after Range(). The syntax is Cells(row number, column number), so cell B5 would have the coordinate (5, 2), then we write

    Cell(5, 2).Value = 100

Using this method we can represent both column number and row number with variables giving us a 2-dimensional way to manipulate cells' values. So let's say we have x = 5 and y = 2, then we can write

    Cells(x, y).Value = 100

Cells() is difficult to use because it doesn't support IntelliSense. Nonetheless its still a powerful choice.

[Address]

This is the least popular one I suppose, but it is arguably the most elegant one.

    [B5].Value = 100

See how elegant, short and clear the code is. In fact in can be reduced to just [B5] = 100. However using this method we cannot use variable to present the row number or column number. So this method is only used when the context is fixed to a cell. Besides, this method also doesn't support IntelliSense. But it makes your code neat and nice, easy to read and therefore easy to debug.

In Summary, the 3 methods discussed above are essentials and basic to know for Excel Macro programming. Each has its own advantages and disadvantages so using them must be suit according to scenario. But it is ok to simply pick one to use first as you can always switch to other method when you finally realise which is a better one to use.