Hi,
I always wondered…How does he get those tricks, how does he find new and ‘quickest ways’ to do things in Excel?
When I was starting to learn Excel, I was always fascinated by the way my boss did things, he is an Excel Maven, and he knows almost every tricky aspect of Excel.
I used my intuition to uncover the methods he used to fill his Excel toolbox so quickly and easily.
I will show you here some powerful tactics so you become more productive and efficient at work.
Please catch this next statement…
You will increase your productivity and satisfaction at work once you master systematic ways to find new and ‘easiest ways’ to perform a given task in Excel.
Yes, you can find the best way to do a thing in Excel by yourself. The problem is when you think there is a universal compilation of quick methods.
What happens when you think that there is a magic library to do things in Excel? You feel frustration because…
- There is not a secret Excel oracle that teaches you how to do it
- There is more than one way to perform an operation in Excel
- Excel like language, with it’s infinite combinations it is a never-ending learning process
Before we move forward…let’s determine what is ‘the easiest way to do a thing in Excel’? In brief: less keystrokes, shorter formulas, less commands needed, and the most important one…knowing what to do.
The good news is that you will learn here some powerful strategies so you empower yourself to find tips/commands/functions in Excel and to find the ‘easiest way’ to perform all your Excel tasks.
Come with me, the journey will be interesting and productive…
1) Know what you Want – Ask Smart and Deep Questions
One of the reasons you don’t find the new/easiest way to do things in Excel is that you start thinking spread, aiming at nothing specific. What does this mean? Inefficient hunters aim at the body of the prey as a whole and fail; on the other hand, effective hunters aim at a small and specific part of the prey body and they get it. This principle is described as “aim small – miss small” and applies to “aiming” at any target. The smaller and more specifically you focus your aim at exactly where you want to hit, the better.
If you want to be efficient in finding new things in Excel you should be specific about what you want.
For example: you won’t find the way if you ask… “How do I find a better method to compare tables?”
On the other hand…
You will find the way (or you will get closer to finding it) if you ask (smart question): what Excel functions can be used for table comparison? If your answer is: lookup functions and logical functions; then, you can narrow your question and ask (deep question): what lookup formulas are used to compare tables? Your answer would be Vlookup, Hlookup, etc.
You can go deeper and ask: what column of the table should I compare using Vlookup function?
At this point, you have enough information to explore the Excel help, to search on Google or actually begin to solve your problem.
You get the point, you started from nothing and now you are getting closer.
If you have an expert in your office, go and ask them. On the other hand, if you are working late at night on a due date assignment, you should find it by yourself. You CAN find it yourself. Think…”smart and deep”.
You may say… this will take time. Yes. You need to develop this skill. The fact is that…if you don’t start now, the time will pass, and you will remain unable to empower yourself.
Don’t miss the second tactic…
2) Start from the End – Reverse Engineering
Starting from the end is counter intuitive, but powerful. What happens if you are not result oriented in Excel? You won’t complete assignments, if you can’t even start, you certainly can’t finish.
Terrible! Isn’t it…
Imagine you have this problem…”All comments in my sheet collapse so I cannot read them. I need to use Edit Comment and then grab the handles to re-size the comment so I can read the contents.”
How can you solve this issue? Start from the end…
How do you start from the end? State your result and then move backwards until you find the steps needed.
For example: the two possible solutions to solve the above problem would be: a) Look in the Excel Options and b) Look in the right-click Comments format dialog box.
Now you expand each option backward…
a) Look in the Excel Options. You can explore the more relevant tabs: View, Edit and General.
If you don’t find the answer there, then move to the other one: b) Look in the comments format dialog box. By exploring the relevant tabs, you may feel you are getting closer: Alignment, Properties and Margins.
The Alignment tab sounds good!
You find on the Alignment tab a parameter called Automatic size. Then you apply it and test it. If it works, now you have the chain of steps: Right click>Edit Comment>Alignment>Automatic size>Ok
A new tip has been conceived!
Of course the tip needs further refinement. We will talk a bit more about it in tactic 5.
But next is tactic 3…
3) Get in the Mood to Find Things – Focus on what you Want
This topic is quite esoteric for a lot of people, the fact is that we are body and consciousness.
If you want to be a successful Excel user, you must use both parts of yourself.
What’s the reason you don’t stop focusing on “what you don’t know”?…
A leading expert in conscious thought says “Your eyes see what you believe”. The fact is that as soon as you believe it is not possible, your eyes look for confirmation and guess what? Your eyes will find evidence that it is not possible. This keeps your focus on “what you don’t know”, rather than on the results you need.
It is happening to you and me always without noticing…
In other words, you won’t find a new/easiest tip by thinking you don’t have a tip. If your tip is related to tables, go to table related menus, find connections from where you are now.
Remember, the law of flotation was not discovered by the contemplation of “how things sink”.
Think of the last time you found an awesome tip, was it while you were working, or maybe while you were thinking of a solution to a problem.
Here are some useful strategies to get in a tip hunting mindset…
Imagine you have this problem: “I have a column with all the content entered into each cell, and I want to split it into 2 columns”.
- Ask purposeful questions instead of questions that talk about your immediate reality. Instead of saying I don’t know how to do this, think in what menu/ribbon is the option that splits a cell into several cells, or what function allows me to split the content of a cell
- If you don’t find the thing you are looking for, don’t feel frustrated. Think “I am searching in the wrong place”. or “I am not searching correctly”. Then change your strategy. For example: users usually search by a method that resolves the whole problem in a bunch. Solving your problem may require two or three steps
- Search without looking for help or forums first. Otherwise you will never develop the connections needed to find new things. It will be hard at first since you are breaking the inertia, you soon will get momentum
- Search more complex things on forums. Yes, only when you are unsuccessful looking in smart ways, you should ask for help, not before
The following tactic is one of my favorite ones. Don’t miss it…
4) Make Things Easier – Add Intermediate Steps
The “immediate solution” or “instant gratification” mindset is an obstacle to a lot of users. I think this happens when one see precious formulas built by others and automatically assumes they were built in “One sitting”.
I nest long formulas by writing the small parts separately. With time you develop the skills of nesting from left to right but this is not always the case.
What do you achieve when you add intermediate steps?
- Shorter formulas
- Not array formulas
- And more…
How can I add intermediate steps in Excel? add intermediate cells or helper columns.
For example, this formula: =IF(AND(C2=”HP”,B2>10000,B2<100000,TEXT(YEAR(D2),”0″)=”2005″),6%,”Not applicable”) could be broken up 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″)
We are almost finished…
5) Treasure your Findings – Package It!
You don’t need to perform the same steps you do for the discovery of a given tip. Once you discovered it, you need to package it.
Make future access to the tool easy… Create a new mental model of how to access the tool.
The best way to package operations in Excel is by using: Tip, Macro and Toolbar
Packaging takes time and effort. Why? mainly because you need to package only proven systems, if not you will suffer from the cumulative effects of reusing a wrong tip. Once done, you can reuse the knowledge with great personal satisfaction and increased confidence.
That’s all for now!
John Franco
“Boost your Excel skills and you’ll boost your career”
