"Deadline-Beating Blueprints Trusted by Intermediate and Advanced Excel Users"

Excel data handling and presentation (dashboards)

Excel VLOOKUP review

by

I was introduced to Excel by accident. I started a job and got a PC with Excel on it. The first tasks involved data entry and lists maintenance.

As my assignments evolved, I needed to do some basic queries…

That was the time when the VLOOKUP function made me  feel the power of Excel.

Here are the pros why you should implement a VLOOKUP function

  1. It searches a value in the backend left index column and retrieves the specified attribute.
  2. It is easy to learn and write.
  3. It does not require any complicated DB relational stuff.
  4. It updates automatically.
  5. It can use wild cards.

After the initial feel, I found some shortcomings worth talking about…

Here are the cons why you should keep away of the VLOOKUP function

  1. Only searches a value based on a single condition.
  2. Only searches a value in the left index column (you keep moving columns in the backend list to implement the formula successfully).
  3. You can not know the location of the returned value (in terms of row or cell reference).
  4. It only retrieves the first instance.
  5. It is affected by a non sorted ascending index column.
  6. It does not allow you to search a value based on multiple criteria.

I still use the VLOOKUP formula because it is very easy to write; however, I cannot cope with the limitation of not being able to search multiple criteria, and the limitation to search only the leftmost column. I feel bored by moving columns to the left and trying to find out the solution to a multiple criteria lookup.

In consequence…

I now use the INDEX and MATCH formula. It appears complex at first, but once you get it, you can overcome those well-known VLOOKUP limitations and even get more versatility.

What do you think of the VLOOKUP gem?

How to Convert a Data Table to a Range

by

A reader imported some data from an external source and ended up with a table. They asked how they could get a copy of the data itself into the spreadsheet, without the table.

What is going on here?

When Excel 2007 imports data from an external source that can be updated back, such as the data connection, database (like SQL Server), query (via MS Query), and so on, it puts the data into a ‘Table’ object instead of just putting the contents right into your spreadsheet. Any change in the data is updated back to the data source if possible, and the data is updated when the source is changed.

This is useful behavior … apart from when you want a copy of the information, not a table! The user that asked the question wanted the data as a regular Excel worksheet data instead so it could be edited and manipulated without impacting the original data source.

Importing Data

If you want to import some data, first, we can look up our friend, Microsoft Query. Select the Data ribbon, click the From Other Sources button, and select your external data source.

After selecting the source, we need to specify where the data will be put. Note that there’s no option for just putting it as data, it has to be a ‘Table’, pivot table, or a pivot chart and report:

Now we’ve got the data imported as a table object into Excel, as seen here. Note that when a cell inside this table object is selected, the ribbon displays ‘Table Tools’:

Converting to a Range

Now for the actual solution we wanted, that is converting the data so it will become a part of the worksheet.

To do that, we’ll use the ‘Convert to Range’ button in the ribbon:

Pressing it, we’ll have to OK the change. Note that it’s not reversible, that is, you can’t undo it:

And now the data is a part of the worksheet instead of in the table object; note the ribbon:

Finishing Touches

One thing remains, the data still looks like a part of the table object and it does not match the rest of the sheet. To fix this, we’ll use the Format Painter. First select a cell outside of the (former) table area, so we can copy its format, then click on the Format Painter button:

Now, select the old table area:

And voila! Fully editable contents in your Excel worksheet.

About the author

Yoav Ezer co-authors the technology and productivity blog Codswallop. He is also the CEO of a company that produces PDF to XLS conversion software.

For more Excel tips from Yoav, join him on Twitter or Facebook.

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

by

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?

Why so Much Noise about Vlookup?

by

Hi,   I celebrate today another newsletter and that I will eat a delicious local dish: mashed grill plantain (a kind of banana) with a handy made pulpy mass of peanut. It is usually served with coffee!

When you come to Ecuador, drop me a line and I can prepare one of these for you with my family.

Today, we invited my brother and his wife to share this recipe, so we will have a wonderful time here.

I like eating cultural dishes. I imagine the origins of the recipe, maybe passed by mouth from ancient tribes and the like.

I hope to eat the most popular dishes from many different countries soon.
With anticipation, we have more time in the future to talk about your favorite meals. However this is about Excel so let’s move forward…   I will show you here why you don’t need to use VLOOKUP anymore!

Come with me…

The fact that Excel VLOOKUP function receives a lot of word of mouth means that you feel you’ve missed something if you don’t use it too.   And so, as you hear about it, you’ve asked your peers or researched on Google about how to put this function to work for you. Now you may think it was for sure one of the best moves you did in Excel.

But it is also true that…

If you have been using VLOOKUP for a while, you may have realized it’s well known shortcomings: not being able to look up other than the left index column of the table array, unable to look up in multiple columns with complicated criteria, to name a few.   So, you may have heard about the next move…Yes, the “growing in popularity” INDEX and MATCH formula.

If you have been using INDEX and MATCH formula, you may be wondering why you should use VLOOKUP, if you now can do its tasks and even more already.   I think you keep thinking of VLOOKUP because you entered the advanced world of Excel through this amazing Excel function. Before using VLOOKUP, you were for sure copying and pasting data from tables to create reports.   So this question is very relevant now: should I keep using VLOOKUP function if the INDEX and MATCH formula does the same things?

Let’s listen to John from Australia…   After he learned how to use the Index & Match formula he sent me an email…   “From reading your Lookup book I now use the Index/Match combo and rarely Vlookup. It seems to me it does all the same things with the 2 advantages that the list doesn’t need to be sorted and you can look left and right of the reference. Am I missing something in this?  If I am when do you use either?” John H from Brisbane Australia

I replied to John…

“Yes, it does the same things with the key advantage that you can look up in any column of the table array. The column needs to be sorted when you perform an approximate match (MATCH function) as you do in Vlookup.

You don’t need to use Vlookup ever again if you are already comfortable using I & M.” Vlookup remains as a recurrent option for some users because it is easier to write and read and because Vlookup appears to be the trend. I still use it for simple lookups.   But INDEX and MATCH overrides VLOOKUP!
Let’s see two main reasons why you should use INDEX and MATCH

  • You can look up any column of your table array (from the leftmost to the rightmost) and retrieve any corresponding column/field value (from the leftmost to the rightmost)
  • You can adapt the formula to build very advanced lookup formulas like: case sensitive lookup, multiple column vlookup, multiple sumif, multiple countif, etc.

Let’s illustrate the first point with an example…   Imagine you have the table below…

Product, Price, Top Month

A, 100, January

B, 200, March

C, 120, October

D, 250, December

And you want the answer for this question: what is the price of Product A?   The formula would be =VLOOKUP(“A”,$A$2:$C$5,2,0) that returns 100.

But if you ask…   What is the price of the Product whose top month was October?   Sorry VLOOKUP. A land for INDEX and MATCH formula…   The formula would be =INDEX($A$2:$C$5,MATCH(“October”,$C$2:$C$5,0),2) that returns 120   So nothing to lose. And even better by using I & M you get familiar with the advanced formulas you are in need to develop to have your work done.   Yes, you later create more advanced reports using this scalable formula.

That’s all for now.

Copy Conditional Formatting – 5 Handy Ways to Do It

by

Why do people want to copy conditional formatting in Excel?

The main reason – I think – is that nobody wants to retype conditions. Yes, Conditional formatting conditions may take time to develop and tweak. If they are in some workbook or sheet you certainly don’t need to retype  them all again.

Here are 5 handy ways to copy conditional formatting in your Excel sheets…

It is important that you know…Excel treats conditional formatting as standard format, so with a simple copy and paste you will inherit the conditional formatting criteria. This is not always the case since you may have content in the target cells or perhaps you don’t know where the conditional formats are.

The following tips will show you how to do it quickly and easily for those various scenarios…

1) Copy and paste

Do this when the destination cells are empty or you want to replace the content.

Yes. Only copy the cell that contains the conditional formatting and choose the destination cell or range and paste.

2) Copy and paste special

Do this when the destination cells are not empty.

Select the range>Copy>Select destination cells>Edit>Paste Special>Formats>Ok

On Excel 2007

Select the range>Copy>Select destination cells>Home>Clipboard>Paste>Paste special>Formats>Ok

The next tip is quicker than this one…

3) Use the Format Painter to Copy Conditional Formatting

I love this way of copying Excel conditional formatting. It is handy and you are accustomed to doing it with plain formatting.

Just select the cell or range with the conditional formatting you want to copy, and then pick the format painter (if you don’t know the location of the tool you are living under a rock!) and then click on the target cells. The conditional formatting will be inherited.

Tip: double click the painter so you can apply format to multiple target cells or ranges

As I said to you before, Excel treats conditional format the same way as plain format.

The following way of copying conditional formatting is not so know but still very powerful…

4) Extend list formats and formulas to Copy Conditional Formatting

It means that the format of the table will be automatically replicated downward as you add more entries to the list.

The graphic below illustrates the point…

The cell A7 doesn’t have any conditional format. If you have the extend list formats and formula option activated; then, the cell A7 will acquire the adjacent table formatting (color if cell < 30, in this example).

This option is activated by default. If not…

Activate this option this way:

On Excel 2003 go to Tools>Options>Edit>extend list formats and formulas

On Excel 2007 go to Office button>Excel Options>Advanced>Editing options>Extend data range formats and formulas

Then, the formatting is copied automatically as you enter data down in your lists.

5) Select the adjacent range and apply conditional formatting (Excel 2003)

You can select contiguous cells to the range that contains the conditional formatting, and select the conditional formatting dialog and click ok.

The format will be copied to the adjacent cells.

Conclusion:

Now that you know more than one way to copy conditional formatting, you won’t be out of ideas to do it. You will do it with confidence and speed.

Important reminders

  • Sometimes the destination range does not contain the same values that trigger the conditional formatting format. This does not mean that the conditional formatting has not been copied. If the cell remains non-formatted, change the value and see that it works
  • You can copy and paste conditional formatting between sheets and workbooks the same easy way
  • If you don’t know where the cells with conditional format are, use Go To command this way: F5>Special>Select>Conditional formats>Ok

That’s all for now….

Please use these tips to develop and hone your Excel skills, advance your business and your career, and to add value to the world.

Excel Conditional Formatting – 5 Killer Reasons to Use It

by

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!

Solve Almost All your Excel Reporting Needs

by

Hi,

I will share an important Excel Reporting fact today. So, pay attention…

Creating Excel reports for managers and customers from flat files the whole day, requires your concentration and creativity. Hopefully, you are equipped with the easiest and most flexible Data analysis software available in the market, Excel.

I owned the software too, but I was not able to use the relevant tools for my reporting assignments.

That’s not all…

I spent hours trying to build the formula that will extract the data or trying to find the direct formulas to do it. At times; I felt exhausted, since Excel has more than 300 functions. After years of trial and error attempts; I know which formulas are the relevant ones to create Excel Reports.

Don’t quit reading now! I will show them to you now…

  1. VLOOKUP It allows you to search a value in a backend index column and to retrieve the corresponding value from other 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
  2. INDEX &MATCH It has the same VLOOKUP functionality but it is not limited to search only the left column
  3. Array Formulas It allows you to search multiple columns and complex computations. For example: search the Orders made by Susan in August and return the total
  4. SUMPRODUCT It is a kind of Array Formula but more readable
  5. SUMIF It allows you to sum various lines that match one condition. For example: sum all the Sales made by George
  6. 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
  7. COUNTIF It allows you to count various lines that match one condition. For example: count all the Yes Answers
  8. 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
  9. AVERAGEIF It allows you to average various lines that match one condition. For example: average all the Sales made by George
  10. 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

Once more thing – it’s important…

By using the above formulas you will be able to create and customize any advanced Excel report; no matter the questions, no matter the style of presentation. Where you have a cell, it could be an answer.

Conclusion.

The most important reason I use Lookup formulas is because I can present only the information that is expected by end-users and I can control the cell inputs and see the changes instantly. This is not a land of Pivot Tables.

All the best

John Franco

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

P.S. The amount of reports you need to prepare won’t decrease; even they will increase, so be prepared to produce them more efficiently.

Disclaimer:

*This site and the products and services offered on this site are not associated, affiliated, endorsed, or sponsored by Microsoft, nor have they been reviewed, tested or certified by Microsoft.

**All product and company names are trademarks™ or registered® trademarks of their respective holders. Use of them does not imply any affiliation with or endorsement by them.

Products & Services

Free courses

Company

© 2009-2019 ExcelGuruBlueprints.com. All rights reserved.