Are you aware of the huge amount of time you waste daily due to manual and repetitive tasks in Excel?

The fact is that every Excel User needs to do the dirty job before preparing the data for analysis, reporting, etc.

Here are some examples of frustrating but necessary tasks…

• Delete blank rows in a table
• Write already-entered-entries again
• Formatting cells again and again with the same format
• Drop down cell handle to copy formulas
• Separate content in a same cell
• And more…

Saving time does not mean you won’t do those tasks, the key is doing them faster, with less keystrokes.

These dirty tasks may be done quicker if you use shortcuts and tips.

There is another fact, not all users face these tasks the same way, some users continue repeating the long version of doing things for a long time, while others hurry to find the shorter route.

I particularly renounce almost immediately to do things the longest way.

I invest time in finding the way to do it. I don’t do anything at the beginning, just research. Once I find the tip then I go ahead. For example, I invest 30 minutes but save hours in the same day and in the days to come.

On what side are you?

I encourage you to use Excel Tips, of course you already use them but I mean improving your toolbox.

The more tips you know, the more resourceful you become, this means you do things in less time and with comfort.

Use your energy to do what brings you more benefits: analyzing and presenting data.

Click here for more…

Why tips are so powerful?

A tip does not require significant effort to learn and give you a boost in your productivity in some way.

• Additionally, an Excel Tip is perceived to be the quickest/unique way to do a thing.
• Save time by performing a given action in less time (less keystrokes, less manual work, etc)
• Help you connect gaps that open doors to more knowledge
• Grow your enthusiast for Excel so you keep more productive (action causes inspiration)
• Reuse the knowledge you already possess in different ways
• Learn a Tip in a flash and get results immediately in your work
• Get instant reward because its instant application nature
• Perform an action you didn’t imagine possible
• Reuse Tips along time in all circumstances

Conclusion

Learn a relevant Excel tip today, maybe a formula tip, a formatting tip, a Pivot Table tip and experience an immediate impact in your productivity.

Tips are digestible and for immediate use.

Use your time to focus on analyzing and reporting instead of struggling in dirty tasks.

Click here for more…

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”

Hi,

The fact that you and your company use Excel for managing business data means that everyone will benefit if you increase your Excel skills: you, the end users who receive your reports, even your boss will love you.

Improved Excel skills will have a significant impact in your overall productivity and marketability.

This is so true because…

• You analyze data in Excel
• You communicate information using Excel
• Your end users use Excel

The good news is that improving a few skills will have a huge impact in your work. Here you will become aware of 7 “must” Excel skills.

So if more rapid career advancement sounds good, let’s start…

1) Organizing your Workbook

Here are some reasons to keep your workbook and sheets organized…

Become more efficient because you won’t be disoriented in your own sheets, get more insights because you look at the data that matters (no cluttered sheets), you and your end users will  never guess about where the inputs/outputs are, etc.

Also, organization creates patterns; these patterns will allow you to be aware of weird things so you avoid/detect errors early.

At the end, you will be perceived as a skillful professional, earn more money, be promoted and get more good things happening in your life.

Here are some tips about how to organize the data in your Excel workbook…

The best piece of advice I can give to you is this: use formatting and sheets purposefully.

What does this mean?

For instance: use red color to show errors and not because you like the red color. People are comfortable when colors remind them of similar things. For example: red = danger, orange = warning, green = good things, etc.

Create sheets for the purpose of collecting data, presenting data, writing notes, etc. This way you won’t clutter your spreadsheets with intermediate steps that increase the size of your books and not the value of them.

Organize stuff at the Workbook level…

• Create a Cover Sheet where you have the title, purpose, etc
• Define a Contents Sheet where you have the constraints of the sheet, some directions, etc
• Have a sheet for the Schematic of the model
• Have a sheet where you present the data, enter the inputs, make the calculations, etc
• Have a Blank Sheet where you store comments, future reviews, make test calculations, etc
• Have a Lookup Sheet where all the lookup formulas take the data from
• Define a Chart Sheet when appropriate
• Keep formatting consistent across all your books: sheet title position, column and row dimensions; hyperlinks, visibility of gridlines, grouping levels, zoom level, window panes and splits, and formats and colors

Define three things at the Excel Cell level

• Clearly identify the input cells. Use a consistent format across your books
• Define a separate format for output cells. This procedure will prevent you and your end users from ruining your spreadsheets by replacing formulas with inputs
• Use a special format for mixed cells (input and output). These type of cells are those which have a constant inside the formula

The following skill is worth having too…

2) Manipulating Cell Content (mainly strings)

You know that data usually comes from different sources you cannot control: external software exporting process, manual compilation, .pdf to Excel conversions, csv files, etc. The fact is that you will always deal with data that doesn’t comply with your formats or the standards of Excel.

Here are some tactics to manipulate strings in cells…

• Get rid of leading, trailing and inter spaces with the TRIM function
• Get rid of leading zeros. For example: remove them from the string “0000123123456789012” with a formula like this one: =RIGHT(A1,LEN(A1)-4) so you get “123123456789012”
• Extract the portions you need from any string. For example, extract the left side of the string “eee-444” with the formula =MID(A4,1,FIND(“-“,A4)-1) so you get the text “eee”
• Perform the majority of string manipulation tasks using the Text functions: LEN, FIND, LEFT, MID, RIGHT
• Use the Text to columns command when you have text with a regular spacing or a clearly defined delimiter (, or ; or -)

Here is the third essential skill…

3) Formatting Cell Data

Your end users require to see data in as many formats as users exists. Being able to customize how your cells look is critical to deliver your data to every recipient.

Additionally, formatting your cells make your sheets and models more readable. That’s not all, if you combine the data with conditional formatting you can gain powerful insights.

Here’s how…

Formatting cells

The shortcut CTRL + 1 triggers the Format Cells dialog. The use of the dialog is very intuitive. Just pick a format you want and the cell content will be shown accordingly. Keep in mind that the format doesn’t affect the underlying content.

Here are some examples…

• Numbers can be shown in different ways: with the desired number of decimal places (100.12, 100.123), with currency symbols ($100.12) with a suffix or a prefix (100.12 m3 by using the custom format #,##0.00 “m3”)
• Texts can be shown as plain text or with a prefix, for example *Joseph by using the custom format “*”@ or suffix @ “*”
• Dates can be shown as: Friday, March 19, 2010; 19-Mar, etc

 

Conditional formatting

You can color your cells based on an established rule. For example if the cell value is less than 0 then format it red and bold.

Access the command in Excel 2003 this way Format>Conditional Formatting or in Excel 2007 this way: Home>Styles>Conditional Formatting

Some of the above formatting can be done massively with a mouse click; you can do it easily if you learn the skill of…

4) Recording a Basic Macro

Many of the tasks you do every day before you start working on what really matters is routine: inserting a column, naming a column, applying borders to cells, changing the font size, etc.

If you package all this routine work in a macro, you will save tons of time. Additionally, you will cover more ground because you don’t get drained of energy due to excessive manual work.

Here’s How…

This is a very simplistic explanation but it shows the handiness of the Macro feature.

1. Record a macro (Excel 2003: Tools>Macro>Record new Macro or this way for Excel 2007: View>Macros>Macros>Record Macro)
2. Perform the operations: hide columns, apply borders, write field names, etc
3. Stop the recording
4. Run the macro (ALT + F8) each time you want to repeat the above operations

The power is unleashed when you modify Macros and write your own code.

Things start to become more interesting!

Having the next skill will give you a competitive advantage…

5) Crunching Data

Being skillful at crunching data in Excel is critical because…

The more you manipulate data with ease, the more you can find patterns, the more patterns you find the more insights you get, the more insights you get the more knowledge you gain and can present to the team, so everyone makes better decisions for the business.

You can also increase your focus on specific figures or categories in big tables with thousands of rows.

The two commands that primarily allow you to crunch tables are: Filter and Pivot table.

Filter allows you to filter by category or by criteria. For example: you can filter all the sales made by “Susan” that are greater than 10,000 and then filter out the products that are not “Electronics”.

You can copy and paste the filtered data so you split tables very easily.

On the other hand, a Pivot table allows you to create reports in seconds by compacting categories and showing you the related attributes. For example: sales by category, sales by category and by product, average sales amount for each product, identifying the three best-selling products within each group, All Quarters Sales for each product and group by quarter, etc

You can increase the power of a Pivot table results by applying filter criteria to the fields.

Here’s a skill I really love…

6) Combining Functions

Successful Excel users don’t retype data, they call other cell content by using cell references, functions and functions inside those functions.

As a result, a sheet with functions that call other functions/references is not static. What does this mean? Any change in your input cells update the entire model.

Here’s How…

The first thing you need to do is to learn the Excel function, after that you need to learn two key things: 1) what the function retrieves (value, cell reference, array reference, TRUE, FALSE, etc) and what each argument is (value, reference, etc).

You can use a function to feed each argument given the fact that the function retrieves the same type of data as the argument.

Here’s an example

IF(logical_test,value_if_true,value_if_false)

The argument logical_test needs a TRUE or FALSE value or any other function that retrieve such values, examples:

• IF(AND(A2=0,B2=”Susan”),value_if_true,value_if_false)
• IF(A2>C2,value_if_true,value_if_false)
• IF(A2>MAX(D2:D10),value_if_true,value_if_false)
• IF(A2>1000,value_if_true,value_if_false)

And to really add versatility to your work you need to have the following skill…

7) Extracting Information from Tables

What happens when you start creating reports exactly as “End-users” want?

Your boss is most likely to say, I like the report you sent me…it allowed me to make a decision right away. Could you prepare this other one for a client?, etc…because you have provided him new insights about a key business metric, he is now confident in trusting you, knowing his life will be easier.

The fact that data comes in tabular form and the fact that we need to get access to specific figures of those tables, implies that the increase in your skills to manage tables, will give you a cutting edge advantage.

Here are some formulas you can use to extract information from tables

• VLOOKUP It allows you to search a value in a back end index column and to retrieve the corresponding value from another column. It only searches values on the left and retrieves values to the right. For example: search the Product Code and return the Product Price

• INDEX & MATCH It has the same VLOOKUP functionality but it is not limited to search only the left column

• Array Formulas allow you to search multiple columns and complex computations. For example: search the Orders made by Susan in August and return the total

• SUMPRODUCT It is a kind of Array Formula but more readable

• SUMIF It allows you to sum various lines that match one condition. For example: sum all the Sales made by George

• SUMIFS It allows you to sum various lines that match multiple conditions, these conditions are evaluated in multiple columns. For example: sum all the Sales made by George in August

• COUNTIF It allows you to count various lines that match one condition. For example: count all the Yes Answers

• COUNTIFS It allows you to count various lines that match multiple conditions, these conditions are evaluated in multiple columns. For example: count all the Yes Answers of Female respondents

• AVERAGEIF It allows you to average various lines that match one condition. For example: average all the Sales made by George

• AVERAGEIFS It allows you to average various lines that match multiple conditions, these conditions are evaluated in multiple columns. For example: average all the Sales made by George in August

You may be aware that “knowing” about the Vlookup and Hlookup functions and “using”  the functions are on very different levels. Once you face the wall of several conditional choices, multiple column criteria and complex criteria, then you need to look for formulas like SUMPRODUCT, INDEX and MATCH, and Array formulas.

Just one more thing…

7 + 1) Compiling Excel Tips

If there is a “shortest route” to perform a task, then you need to know it. That shortest route is often called a tip.

An Excel tip allows you to have quick access to tools so you will save time, increase your productivity and have fun.

Saving time does not mean you won’t do those tasks, the key is doing them faster, with less keystrokes and with a packaged sequence of strokes.

You find tips on the internet and you will develop your own toolbox with time, as you nurture your tip hunting mindset.

That’s all for today!

All the best

John Franco

“Boost your Excel Skills and you’ll boost your career”

Hi,

You may already have started to become aware of the difficulty in finding the formulas you need.

You are very right. Writing Excel Formulas is an art and you need to create a system to master this art!

Don’t miss this short story…

The first spreadsheet I opened at my job, contained a novel in each cell, believe me…lines and lines of formula coding. It was Greek for me.

I thought to myself, what a monster genius of Advanced Excel formulas (I thought the author had conceived the formulas in one sitting).

Nothing was farther from the truth…

Later I thought that the Expert who had written those formulas, must have had and followed a kind of system.  I never discovered it, but I built my own system and I will reveal it to you right now…

I will show you how to start from nothing and become an expert in new functions in Excel.

Why is it so important to master Excel Functions?

 

The fact of knowing the fine details of an Excel Function means you can use them more creatively in the middle of another function or formula and achieve the results you want. The better you know your options, the more efficient you are.

 

Here is the system applied to the IF function…

 

1) Manipulate your mind

If you don’t manipulate your mind, your mind will manipulate you.

Make your mind  believe that the function is easy. Do not make a monster out of it before you even begin……

Go to the help section and explore the examples (Don’t look at the syntax). Depict in your mind what each formula does without focusing on technical stuff.

Read only the description of the formula. For example…

“The IF function returns one value if a condition you specify evaluates to TRUE, and another value if that condition evaluates to FALSE. For example, the formula =IF(A1>10,”Over 10″,”10 or less”) returns “Over 10” if A1 is greater than 10, and “10 or less” if A1 is less than or equal to 10.”

Another useful tactic is to write in a cell the result you want to achieve using formulas and functions. Yes, imagine the formula is there!  Always focus on the results you want, let the amazing power of your mind bring you the steps.

 

2) Eliminate hesitation
Your central nervous system is hard wired to protect you from dangerous situations. We evolved in the past because we kept aware of flooding, lightning, wild animals  –  so we searched for shelter in caves, etc.

This same mindset can work against you. When you are heavily conditioned against making mistakes (by teachers, parents, peers etc.) and interpret mistakes as dangerous.

Making mistakes is an incredibly important part of learning, growing and exploring, so long as they are not life threatening.

Once you eliminate the sense of danger from making mistakes, you will feel comfortable because your brain will not perceive them as a thread. And you will learn faster, and therefore learn more.

Here are some tips…

First create a kind of master table to make mistakes on it. For example:

Name, Sales, Bonus

Nancy, 1000,

George, 800,

William, 50,

You will improve this master table with use. So you can use it to test any function or formula.

Choose the cell C2 and insert the function, in this case =IF(

It appears the syntax: IF(logical_test, value_if_true, [value_if_false])

At this point…

On the help section, read the description of the syntax. Don’t focus on the wording, just pick an example of the syntax. For example: for the argument logical_test focus on…For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, the expression evaluates to TRUE…

Focus only on what you need to do it correctly.  You will learn the details with use.

Give what the function asks you. for example IF(A2=”Nancy” or IF(B2>500, etc.

Insert the comma (,) and specify the next argument.

If you are afraid of inserting numbers, texts, references, etc. Just do something weird so you get rid of the fear of  making mistakes. You will learn what you cannot do and this is a vital aspect of learning functions.

 

3) Split test
To gain confidence with the function, test it in several scenarios: use negatives, test opposites, use text, use numbers, etc.

Change the parameters, for example: change the formula =IF(B2>500,”Yes”,”No”) to =IF(B2<500,”Yes”,”No”), =IF(B2>1000,”Yes”,”No”), =IF(B2>0,”Yes”,”No”), etc.

Change the  inputs also, for example: see what happens when you change the values in cells…

Name, Sales, Bonus

Nancy, 0,

George, 10000,

William, Hello,

The more you know about the function behavior and the more you feel and see it, the more likely you are to use it to solve more and more problems.

 

Well, I think that’s enough for today.

Conclusion

What happens when you start creating Excel reports exactly as “End-users” want?

Your boss is most likely to say, I like the report you sent me…it allowed me to make a decision right away. Could you prepare this other one for a client?, etc…because you have provided him new insights about a key business metric, he is now confident in trusting you, knowing his life will be easier.

The fact that data comes in tabular form and the fact that we need to get access to specific figures of those tables, implies that the increase in your skills to manage tables, will give you a cutting edge advantage.

All the best

John Franco