Excel Tips – Invest 30 Minutes Today and Save Hours in the Days to Come

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…

Excel 2007 Conditional Formatting – 4 Awesome Reasons I Love this Command

The new Excel 2007 Conditional Formatting features allow you to apply icons and bar color scales instead of only colors and formats to a cell or array.

So you will see your spreadsheets converted into more powerful visual dashboards!

And this is just the tip of the iceberg! Yes, Excel 2007 brings other enhancements to this command. I will show them to you here…

For those who have not heard about Excel conditional formatting, it is a command that allows you to format cells or arrays based on its values or on the values of other cells.

The new functionality means that you can to do these things even better…

  • Being informed in real time
  • Get clues when editing massive databases
  • Answer questions visually
  • Analyze data: find exceptions, find relationships, find trends, etc
  • Presenting data
  • And more…

I will show here the 4 things I love about the Excel 2007 Conditional Formatting command. You will love them too!

You will want to put your hands to work on this immediately…

1) I love Excel 2007 Conditional Formatting because it has a new friendly user interface

You can easily select the type of rule you want to apply on the “Select a Rule Type:” area (see graphic below); and then you can edit the rule parameters on another clear separated area called “Edit the Rule Description:”

I like the fact that all the rules are shown at once, this way you have a sense of the big picture of which rule would be better to apply for a given scenario (Format all cells based on their values, Format only cells that contain, Format only top or bottom ranked values, Format only values that are above or below average, Format only unique or duplicate values, Use a formula to determine which cells to format).

Give a look at the New Formatting Rule dialog box…

Additionally, with the new Conditional Formatting Rules Manager (see graphic below) you can do several things you could not do before. For example:

  • Now you can see/edit the range that the rules apply to
  • Now you can move up/down each rule so you change the order in which the rules apply (so simple now but a not so straightforward process in Excel 2003)
  • That’s not all, you can choose which workbook rules to show: selection, sheet, etc (see “Show formatting rules for:” area at the top of the manager dialog box below). This way you can work on a limited set of rules that you want to analyze the order of application, etc

In summary, the usability of conditional formatting command increased.

You can easily navigate through rules to administer them: creating, changing, deleting, reordering, etc. If you have been using the conditional formatting command on Excel 2003 you know that these little enhancements make a great difference.

2) I love Excel 2007 Conditional Formatting because it has pre-built formatting options

Once you access the command on the new Excel 2007 Ribbon (Home>Styles>Conditional Formatting), you can pick the preset rules and apply formatting right away.

For example: on the graphic below, you can choose one of the 5 preset formatting rules: Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales, Icon Sets.

After you choose one of them, you can then easily specify preset format criteria. More below…

Once you choose the type of rule and enter the condition, you can select a rich preset color configuration for your cells (see graphic below).

These preset formatting options allow you to prepare common formatting schemes quickly and easily. So in a couple of clicks you can format your cells as desired.

When can’t you use the preset rules and formatting?

As you will always need to create new and complex rules, you should create such criteria from scratch using the New Formatting rule manager (Home>Styles>Conditional Formatting>New Rule) or the Conditional Formatting Rules Manager ((Home>Styles>Conditional Formatting>Manage rules).

You will also love the next feature…

3) I love Excel 2007 Conditional Formatting because it has more rules and you can now move them up and down and more…

Excel 2003 and users of “previous versions” conditional formatting are limited to only 3 conditions (rules); see graphic below. There’s no such limit in Microsoft Office Excel 2007; you may have as many conditional formats as you like. And you can administer them all efficiently using the Conditional Formatting Rules Manager (see graphic above).

Let’s illustrate this shortcoming with an example…Imagine you had four age description ranges you would like to slice and dice with formatting. <2 infants, 2 – 18 children, 19 – 65 adults, >65 years old. Sorry, you are not able to do it with Excel 2003 conditional formatting (only 3 conditions).

Additionally, you cannot easily change the order of the conditions. Now you can! Yes, using the up/down arrows provided on the Conditional Formatting Rules Manager (see “Conditional Formatting Rules Manager” graphic above)

Here is another example…

Imagine you enter in Excel 2003 the conditions in this order:

  • 2 – 18 children
  • 19 – 65 adults

And then you want to add the condition <2 infants at the beginning. You need to reenter everything. You are not able to change the order of the conditions.

The other major limitation of conditional formats in Excel 2003 and earlier versions is that you couldn’t have multiple conditions be true for the same cell.

In Excel 2007, you can control whether the conditional formatting stops or continues after Excel discovers that a specific condition applies to a cell.

Don’t miss the next loved feature…

4) I love Excel 2007 Conditional Formatting because it has 4 new rule types (conditions)

More rule types means you can represent more types of trends and data; this can lead you to making awesome presentations and gaining powerful insights from your data.

You will hear great comments from the people who use your spreadsheets. And you will see the meaningful formatting taking shape as you change values in your sheet.

There is no reason to not put this to good use after you know the capabilities of the type of rules that Excel 2007 brings:

  1. Format all cells based on their values (New)
  2. Format only cells that contain
  3. Format only top or bottom ranked values (New)
  4. Format only values that are above or below average (New)
  5. Format only unique or duplicate values (New)
  6. Use a formula to determine which cells to format

Now, let’s explore each one briefly and envision some practical applications…

Rule 1) Format all cells based on their values (new)

This type of rule formats several cells based against the same scale. This rule type allows you to find relationships in your data.

The graphic below shows three columns. Conditional formatting in column A highlights cells according to a two color scale, highlights cells according to a data bar scale in column C, and highlights cells according to a icon set scale in column E.

You can compare sales, heights, etc.

Now, let’s explore the format styles very briefly…

Format style: 2 color scale and 3 color scale

Applications

  • Sales categories
  • Dates
  • Age descriptions
  • College grades
  • And more…

Format style: Data bar

Applications
  • Compare heights of the tallest mountains
  • Traffic accidents
  • Population
  • Sales
  • Profit/Losses
  • And more…

Format style: Icon set

Applications
  • Quickly see revenue status and trends from one quarter to the next
  • Compare different product criteria by using a rating scale
  • Examine profit trends from month to month
  • Identify regional sales below $1,000,000

Rule 2) Cells that contains

This type of rule is useful when you want to spot trends individually on cells. For example: highlight the cells that contain value greater than 10,000.

The graphic below shows two columns. Conditional formatting in column A highlights values = 5, and highlights blanks in column C.

Applications

  • Identify specific numbers, dates, and text in a list of products
  • Find blanks, non blanks
  • Identify dates that occur within specific periods like: today, yesterday, etc.
  • And more…

Rule 3) Top/bottom rank (new)

You can use it to spot the top performing products, top performing students, Pareto 20%, etc.

The graphic below shows two columns. Conditional formatting in column A highlights top 20%, and highlights bottom 20% in column C.

Applications

  • What are the top performing products in sales
  • Who are the top two students in the class
  • And more…

Rule 4) Above/below average (new)

The graphic below shows three columns. Conditional formatting in column A highlights “above average” cells (average =5.5), highlights “below average” cells on column C and highlights the cells that belong to the “1 std deviation above” in column C.

Applications

  • Identify top, bottom, and above average values in a marathon training log
  • Identify top, bottom, and above average values in students’ scores
  • Identify top, bottom, and above average values in sales

Rule 5) Duplicates (new)

The graphic below shows two columns. Conditional formatting in column A highlights duplicate cells and highlights unique cells in column C.

Applications

  • Find duplicate rows in a list of customers
  • Find unique entries in a list and filter them out
  • And more…

Rule 6) Formula

By using a formula, you can create any condition you can imagine.

The graphic below highlights the column A cells when the corresponding cell in column B is greater than 50

Applications

  • Format alternate rows in a range
  • Compare data in a cell outside the conditionally formatted range of cells
  • Format an entire row where more than one condition must be true
  • Format an entire row if the row is a unique value
  • Format cells that match a set of parameters specified in another group of cells
  • Identify a dynamically changed number or text value in a list of products

Conclusion

Manipulating rules is an important task, now you have the functionality to become a conditional formatting star.

And as I said to you above, more rule types and more representation types mean you can present data in more ways, get the message delivered and gain more new insights from your data.

You will increase satisfaction and confidence at work.

What are you waiting for?

How to Increase your Creativity in Excel…

Hi,

Do you want to be more creative in Excel?

As the universal desire to have good luck, everybody wants to be more creative and smarter

Come with me, I will show you how in Excel…

As a prerequisite…be prepared now for refreshing two old concepts because you will use them: deduction and induction. 

No Logic classes now…so don’t worry; these capabilities are hard-wired in your brain. Let’s go into some detail and see how these two notions can help you in Excel…

What is Inductive reasoning?

You use the inductive reasoning when you get hurt with hot water and you conclude that all hot liquids hurt.

Inductive reasoning uses premises from objects that have been examined to establish a conclusion about an object that has not been examined (source Wikipedia).

For example: if you delete a cell that is referenced in a formula and get the error REF, you can conclude that the error REF appears each time you delete the referenced cells of a formula.

The second capability is Deductive reasoning… 

You use deductive reasoning when you start with a premise (truth) for example: hot liquids hurt, so you know if the water is hot, and you get in contact with it, you will be hurt. So it is deriving a conclusion by reasoning. You infer a conclusion about particulars that follow necessarily from general or universal premises constructs. 

For example: if you read the Excel help about the REF error (the truth)… 

“Occurs when a cell reference is not valid….possible causes: Deleting cells referred to by other formulas, or pasting moved cells over cells referred to by other formulas” 

You can conclude…because I delete this referenced cell I will get a REF error.

To summarize the two forces…

  • Induction = prediction after the fact occurs
  • Deduction = prediction before the fact occurs

You can become a monster in Excel if you start using these concepts consciously. 

I will show you now 2 amazing inductive/deductive tips that work…

1) Master all the Excel tools AFTER mastering just ONE – Inductive reasoning

Users want to master all the commands, formulas and tips, however, if you focus on just one tool and really become a master of it, you can derive amazing truths that apply to other commands and functions as well. 

Going deep brings a lot of benefits. For example, when you understand how Excel dates work, you can predict how Excel will sort dates, how to enter dates in any function or formula, what happens when you format them differently and so on.

You can then predict the behavior of any Excel feature in different scenarios! Now you are more resourceful and creative. 

If you truly understand how English work first, you can imagine new phrases or ideas for an essay or writing. The same with Excel. For example, after understanding how Excel VBA language works, your mind will be blown by the macros you can create to save massive time at work.

2) Use all the Excel tools BEFORE mastering them – Deductive reasoning

I don’t know if you are part of this group, but a lot of Excel users say “how can I create a solution if I don’t know the tools that exist for that solution”. 

To find creative solutions, you should know beforehand the tools exist. For example: you cannot wait to figure out how to use your cell phone camera at the moment of a casual event because you will fail to capture the moment. Skills must be developed before they are needed.

Improve your creativity by imagining the possible applications of tools and functions. The best news is that you can play with tools before you need them. It’s free and no-risk, just back-up your file and if something goes wrong, just restore it.

The same applies to Excel. 

Keep learning new Excel tools and their likely applications, when the opportunity appears, you will open the toolbox that you already have and know what tools you have available to perform that task. 

When you find a new formula or command, read more about it, see examples and depict in your mind the uses you can give to it. The opportunity to use it will come and you will know which functions to apply.

Where can you find information and the truth about Excel tools and commands?

  • Forums
  • Excel help
  • Other spreadsheets
  • Articles
  • Books
  • Blogs
  • Courses

You think these methods will work? Tell us what you think below…

Excel Conditional Formatting – 5 Killer Reasons to Use It

What is Excel Conditional Formatting and why is using it so advantageous?

In a few words: ECF is assigning formats to cells if their values meet a given condition. For example: in the graphic below…if the balance is negative, the cells are colored…

It appears simple at first sight but once you start using it, you uncover many practical applications. And you will never be the same after you add this professional ingredient to your spreadsheets.

Here I will share with you some of the handy applications and benefits of using Excel Conditional Formatting command extensively…

Before we move forward…I want to share this thought with you: If Excel Conditional Formatting is so important then…Why don’t Excel users use it frequently? I really don’t know, I personally didn’t use it too much either until I discovered my productivity increased with a few applications.

I think we need to know some WHYs so you start using this awesome command right away.

And the more you use it, the more you will find creative uses for it.

Here are the reasons…

1) Excel conditional formatting keeps you informed in real time

You can create your own checks. As soon as they occur you will be notified by a change in cell formatting. Colors will get your attention and you will never miss a model feedback. This of course means you can make informed decisions and make them faster.

For example:

  • You can get a red color each time you insert a duplicate
  • You can create a check to alert you each time you enter a non-valid value
  • You can get an orange color as your month spending reaches a preset limit
  • And more…

Real time highlights means you get real time insights. You can make decisions right away instead of allowing the errors remain hidden or replicate in second instances that can lead to catastrophic consequences and delay important decisions.

That’s not all…Immediate feedback can help you to keep alert of underlying patterns taking shape!

When important decisions happen fast and accurately, it’s the type of thing that catapults careers and businesses into leaders of their field. When your work helps to make your company a rock star, or even to take a few solid steps in that direction, bosses have a tendency to offer you rewards and promotions.

Don’t miss the second great application…

2) Excel conditional formatting helps you edit massive databases

It is a very common task to edit data that does not comply with your requirements: duplicates, length of cell content, numbers stored as texts, etc.

This happens because Excel communicates with such diverse software packages and these programs don’t export the data as we would like. When working with thousands of lines of data, this can often be overwhelming.

You can highlight those problems while editing the raw data so you don’t over sight any error. And even better, you can combine the highlight with filters and sort commands to edit massive rows in seconds

Examples of conditional formatting on Excel:

  • Highlight Duplicates and decide which ones to delete
  • Highlight Unique entries and decide which one to keep
  • Highlight invalid data so you can filter them and correct them
  • Highlight specific text
  • Highlight  dates occurring in certain periods: today, last week, etc
  • Highlight Blanks, No blanks, Errors, No errors
  • And more…

Here’s more…

3) Excel conditional formatting helps you to answer questions visually

As a data analyst you play with a table to get specific answers. While you can create custom filter criteria or use lookup formulas or Pivot Tables, you can create conditional format criteria to show you where the answers are.

Examples of these questions may be:

  • What are the trends in product preferences over the past two years?
  • Who has sold more than $100,000 dollars this month?
  • What is the overall age distribution of employees?
  • Which products have greater than 10% revenue increases from month to month?
  • Who are the highest performing and lowest performing students in the freshman class?

The more you use it, the more you will find creative uses for it”.

4) Excel conditional formatting saves time

Since Excel conditional formatting is updated automatically, you can manipulate text on the go, you can change the underlying cells and see what’s going on, what’s still missing or needs to be added, etc

What I mean is that once you set the conditions that trigger the formats, you focus on editing, analyzing and making decisions accordingly without extra configuration.

And finally…

5) Excel conditional formatting helps you analyze data

If you put conditional formatting on key metrics that need to be measured and monitored against target values, then you can take a look at your dashboards and get a better understanding of patterns.

Conclusion

Additionally, my satisfaction is increased since I feel a kind of connection with my own models. End users also feel comfortable when they are notified by color rules.

No excuses not to start using Excel Conditional Formatting and let YOUR inner ‘Excel Rock Star’ loose today!

How to Learn New Excel Formulas

Hi,

Are you intimidated by the complex appearance of some Excel formulas?

This is an important point…

I was afraid of a large group of formulas like Array Formulas, INDEX and MATCH, SUMPRODUCT, OFFSET and many others.

But I am not scared anymore.

Well…

Sincerely, I am still afraid of many others like GETPIVOTDATA for example.

Don’t miss what I have to say you now…

Diane Berron, one of my valued subscribers replied me with this message…

“You make me laugh, there is nothing to fear, getpivotdata is nothing but a simple function that returns data found in a pivot table…”

She is so true. With fears, laugh loudly!

You know what I do with my fears?

I face them…

Trust me on this: I have learned that staying in your comfort zone is a suicide

We as humans have a natural predisposition against new things, and a predisposition towards the familiar.

You know that is right, don’t you…

So; no pain, no gain. Babies learn to walk by first falling down, then getting back up and falling down again, and getting back up again until they get it.

I spent a lot of time with INDEX and MATCH formula until I get it; the first step was to explore it. I take action when I hear people rumoring about a useful tool, I test those rumors always.

The benefits of leaving outside my comfort zone were enormous in this area: more productivity, more confidence in building reports my boss and end-users wanted, etc.

Here’s the story…

INDEX and MATCH formula is not so easy to read and write at the beginning. I kept away of it for almost two years. But I paid the price, I moved the needed column to the left of the backend table each time I used the VLOOKUP function.

The fact is that doing this backend-editing on a constant basis made me aware that this was a very inefficient process. Sometimes, I needed to search different fields on the same backend table. I could not imagine having different tables with different columns layout just for satisfying the VLOOKUP requirements.

Then, I discovered the functionality of the INDEX and MATCH formula; here are the 3 reasons why I love it…

1) I love the INDEX and MATCH formula because I can search the backend list as it is

Reason: I don’t need to move columns to the left of the backend list. Now; I can search any field by changing the formula instead of the lookup table.

2) I love the INDEX and MATCH formula because I get the position of the found value

Reason: the MATCH function returns the row number in which the lookup_value was found. I can later use this position to make extra computations or embed this value in other formulas.

3) I love the INDEX and MATCH formula because I can use it as part of an Array Formula

Reason: being familiar with the INDEX and MATCH formula facilitates your life when you need to write Lookup Array Formulas, since they use the INDEX and MATCH combo as the base formula.

Summary

The INDEX and MATCH formula appears daunting at first, but you replace the VLOOKUP function since this formula offers the same functionality and even more.

How to Find a ‘New and Easiest’ Way to do an Excel Task

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”

7 Excel Skills you MUST have!

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”

Easier life? – The lazy Way to Become an Excel Expert

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