Something going wrong? Try SAFE mode

This works for all Office products. When things go wrong or the application starts very slowly, or gets hung while starting or misbehaves in any way, it is worth trying starting it in SAFE mode.  How do you do that? Simple.

Press CTRL key while clicking on the Office Application Icon. It will ask you if you want Safe mode. Choose Yes.

image

Another method is to use a command-line switch – more useful to techies. Press Windows Key and R. The run dialog opens. Write the application name followed by /safe and press Enter. For example:

winword /safe

Here are the exact application names for all products:
winword, excel, powerpnt, outlook, mspub, msaccess, onenote

Benefits?

It opens the application with no customization. All your custom settings are replaced with default settings. No add-ins are loaded. This usually makes the application start very quickly. And now you can troubleshoot which item or setting is causing the problem.

Starting in safe mode is NOT a permanent solution. It is usually the first step in troubleshooting causes of application instability or crashes.

***

Excel – Impact Analysis – Trace Dependents

The Need

Excel workbooks are complex. Multiple persons share and handle the same file. We keep mailing them to each other. Often the person who created the file has left the organization long back. There is never any documentation available about exactly how a file works.

Due to all this, it is almost impossible to remember which value is used in which formula. When you are about to change a value or add more data to existing block, it is absolutely necessary to ensure that all the related formulas are updated. If you forget to update the formula, the results shown there will be outdated and WRONG. This can hamper decisions and can have severe side effects.

What we need is a way to find out which formulas to update when any data changes or grows. Excel has provided this facility since 25 years. Unfortunately, very few people are even aware of it – leave alone using it!

Continue reading

How to edit a formula in Excel

Yes I know you have been using formulas in Excel for decades. But just trust me… there is more. Some small but nice things you should know.

Keep looking at the status bar

It shows you the current mode. First time you add a formula, it is in ENTER mode… next time you try to change it, the mode changes to EDIT.

SNAGHTML17534ab2

So what difference? In ENTER mode, moving the arrow keys selects cells outside the current cell. This way you can select the range for formula parameters.

In Edit mode, moving the arrow keys (right and left), moves the cursor WITHIN the formula cell – which helps you in editing the formula itself.

In some cases, you may want to change the mode. To do that press SNAGHTML175594c1

This is also very useful when you edit formulas in odd places – like Conditional Formatting formulas, Data validation formulas and so on… In these places, the default mode is Enter. You want to change something in the formula so you press arrow keys but it selects cells and disturbs the existing formula – which is very frustrating. In such cases, just press SNAGHTML175594c1[5]before editing and make sure that you are in EDIT mode before editing.

Syntax tooltip – why bold formatting?

All of us know that the formula syntax is shown whenever you are editing a function. But wait. Have you noticed some additional things there? Some of the parameters are in BOLD font. Do you know why?

image

Because your cursor is currently in that position. This is very useful in editing complex, nested formulas. It is sort of “situational awareness” while editing formulas.

Syntax tooltip – why Hyperlinks?

I am sure you have noticed it…

image

What is the use? Well, it helps you select the entire parameter by clicking the link Thumbs up

image

This can be very useful in selecting a particular parameter in complex, nested formulas…

image

Imagine doing this manually Ghost

Whatever is selected can be calculated with SNAGHTML17672b22

If you select part of the formula and press SNAGHTML17672b22[5], it calculates it and shows the results.

image

This is very useful for troubleshooting and quickly knowing the results of part of the formula.

REMEMBER NOT TO PRESS ENTER HERE. Else your formula will be overwritten with the result!

There is much more. But enough for one article already …