Hi ,
You will love the Excel Formulas tactics I have for you today As I said you in a past communication, you will hear about me from time to time; only when an idea worth spreading pop up in my mind. That’s the reason why I am talking to you now {!name}.
I want to share with you some productive Excel Formula tips…
Who else would like to hear desperate peers calling you {!name}, {!name}…help me with this assignment. They have listened to your boss and other colleagues talking about your expertise with Excel formulas and reports.
Mastering Excel Formulas = Excel Expert.
A professional who is an Excel expert becomes more productive and so he gets promotions and earns more money than their peers.
This is likely to happen {!name}, if you practice these tips on a constant basis. You will become better and better at Writing Excel Formulas. Here we go…
Be creative
Before you write an Excel formula, you should know the different functions that Excel possesses. This way you have a rich toolbox that inspire you solutions. Imagine you have an opportunity to take a unique picture, maybe you see an UFO and you don’t have a camera. Sorry {!name}, you miss the fame! The fact is that you must equip with a camera (with flash, with a good case, etc) before you need to use it.
Equally, you should acquire the knowledge of the most used Excel Functions before you need it. So when the urgency arises, you have a toolbox to pick tools from. And you will take a better picture if you have grasped how to use the camera (how to turn it on, how to focus, how to shoot at night, etc) before the shooting moment.
So master Excel functions first, even if you don’t have an immediate need now. Later you will surprise yourself how many uses you find, how many solutions you create for your Excel formulas. How to grasp a function? Read about each function, see examples and create your own cases.
Where to find information? Excel help is a good start, but for some users is confusing. You can learn Excel Functions the fun way
The better you know a function, the most creative uses you will give them in your formulas.
Use reverse engineering
In Excel; alternatives for a solution are infinite, you need to keep the focus on a solution and implement it. There is no such thing as a correct solution. You will develop with time, the capacity to remove the obvious so you build more elegant solutions. At the beginning you need to build formulas that work.
When you want to buy a house, you know that you need to make a loan, buy a land, hire an architect, etc. The bank asks you to have other requisites, etc. Your end result is buying the house, you put it as a goal but first you go for each requisite separately. Then, after you have all the requisites ready, you start building the house. The same happens when building a complex formula.
Here are some suggestions to use reverse engineering on your Excel formulas…
- Start from the end
- Break into smaller parts
- Choose the methods you are more familiar with
- Develop the formula in small chunks you can assemble later
This technique is most effective when you have a good background of Excel Functions
.
Start small
Motivation is lost when you start too big and fail. Failures are normal and you should expect them if you want to move forward in life. With time you will build upon those small stepping stones and the knowledge you get will allow you to do bigger things, easier, faster.
This happens only with time and practice.
By starting small, you won’t be disappointed, you will feel progress. Starting small does not mean you think small, by doing small steps at the beginning you pass inertia and after that, the momentum is built and you can move forward faster.
Instead of setting a goal like master all the lookup formulas, set a goal like: master Vlookup this month, or even smaller like understanding the lookup_value argument of the Vlookup function. Time will pass and you will add bits of knowledge to your store that later snowball.
Test your Excel formula
Cumulative effects of not testing a formula may ruin your credibility and confidence in your work. As you later use formulas in other formulas, you won’t be able to know where the errors reside. With time, your spreadsheets may turn into unusable. A waste of time
I make the calculation by hand and then compare with Excel, I do this every time I learn a new function. This increases my confidence to use it in the middle of another formula.
The most basic of the formulas like this one: =A1*A2/A may give you headaches. One of the input A1 or A2 may contain a number stored as text, etc. Another way to test is by comparing the formula partial results. For example: you can check the formula =IF(A4-A3<=2,A4-A3,(A4-A3-2)*-1)
By checking the condition: A4-A3<=2 in a cell apart, and this way each part of the formula. And the best way to test a formula is to implement it under realistic conditions before you release it to the world. With this you will avoid surprises.
Optimize your Excel formula
A long formulas is not good to read, edit and maintain so to turn such formula into an efficient piece of code, break it into smaller parts. This also gives you control over the results.
For example, this formula: =IF(AND(C2=”HP”,B2>10000,B2<100000,TEXT(YEAR(D2),”0″)=”2005″),6%,”Not applicable”) could be break into this manageable version: =IF(D2,6%,”Not applicable”)
Where D2 contains this formula: =AND(C2=”HP”,B2>10000,B2<100000,TEXT(YEAR(D2),”0″)=”2005″)
After you finish writing a formula, it is copied down, right or in any direction through the sheet. Some inputs remain on the same cells, other ones running along a row or column, you should use relative reference extensively (by pressing F4 in editing mode; you can write the $ by hand).
- =A1. It changes as you copy your formula.
- =$A$1. It always points to the same cell A1
- =$A1. It always points the column A; you can only have A1, A12222, etc
- =A$1. It always points to the row 1; you can only have A1, B1, F1, etc
Another important way to optimize your formulas is to avoid at all costs to hard code their arguments, because of two main reasons:
- You easily lose track of the hard coded numbers
- You need to edit each formula separately instead of just changing the inputs in hub cells
Finally, use Excel names instead of references, this is the way an Excel formula would match a real formula. What is more readable: =A1*A2/A or =base*height/2
I hope these tips help you to improve your Excel formula writing skills
Enjoy!
John Franco
Boost your Excel Skills and you’ll Boost your Career
