VLOOKUP Tutorial: Updating prices in a master price list

The Problem: It's taking way too long to find and update prices in a master price list

Suppose that you maintain an Excel workbook with over 10,000 prices for products that your company sells. Parts are arranged in sections by category. You have part numbers in column A, prices in column B, and other information in column C. Now, imagine that your manager just gave you a worksheet with updates for 475 of the items in your list.

Attempt 1: Use CTRL + F to update the list

 You decide to copy the price update to a blank section of your master price list.

From this point, you start trying to find each item from the new list in the old list. Since the items in the old list are not sorted and cannot easily be sorted across the various sections of the price list, you decide to use the Find command to make the process easier.

Here is your first attempt:

1. Select the first part number from the new list in cell G3
2. Press Ctrl+C to copy this item to the clipboard
3. Select all of column A by clicking on the A heading
4. Press Ctrl+F to open the Find dialog
5. Press Ctrl+V to paste the part number into the Find dialog
6. Click the Find button

You are now at row 2063 and fairly proud that you didn't have to click PgDn one hundred times to get here. Unfortunately, you've forgotten the new price so you have to start over. 

Watch me try using CTRL + F to update the list:

Attempt 2: Use Find All to update the list

You try jotting the new price on a sheet of scrap paper, but then you think there might be a faster way. After some experimenting, you end up with this awesome set of steps:

1. Select the next part number from the new list (you are down to G9) by now.
2. Press Ctrl+C to copy that cell to the clipboard
3. Press Ctrl+A twice to select all cells in the worksheet
4. Press Ctrl+F to open the Find dialog
5. Press Ctrl+V to paste the part number in the Find dialog
6. Press Alt+A to Find All! This brings up a list of both the item in the original list and the item in the new list
7. Switch between the two cells using the up and down arrow keys.
8. At this point, it is probably easier to switch to the mouse. Click on the new price. Right-click and choose Copy. Click on the old price. Right-click and choose paste.
9. You can return to the right section of the new price list using the hyperlinks in the still-open Find dialog.

Watch me using Find All to update the list: 

Even with this improved set of steps, it is taking almost 1 minute per item. You do some quick math and realize that 450 items are going to require 8 mind-numbing hours. Your first thought might be, "There has to be a better way, but what is it?"

  

The Solution: Speed up the process with VLOOKUP 

I began working as a financial analyst in 1989. I've learned that data is rarely perfect.

This price list workbook is a case in point. It has been handed down in your department for the last dozen years. It was set up by some guy named Bob who no longer works here. In the workbook, there are lots of things that would have made life easier.

For example, it would have been nice if:

• The category information was in another column, so you could easily sort the list by part number and then back by category.
• The list of new prices included all the items where the price did not change, and if it was in the exact same order as your list, so you could copy and paste huge ranges of prices.
• Your manager would have just done this himself.

But, in real life, data is not perfect. When data is not perfect, knowing an Excel function called VLOOKUP can save the day. 

VLOOKUP explained

VLOOKUP stands for "Vertical Lookup." The vertical means that your list of new prices is going down the spreadsheet instead of across. (If you are wondering, there is an HLOOKUP for when your lookup table is going across.)

The tooltip for VLOOKUP says you need:

=VLOOKUP(lookup_value,table_array,col_index_number,[range_lookup])

• Lookup_value is the part number that you are looking up. To find a new price for the part number in A2, you are looking for A2. =VLOOKUP(A2,
• Table_array is the lookup table. The data that you are looking to match must be in the first column of the lookup table. That works in this case, because part number is in column G and new price is in column H. If those had been reversed, you would copy the prices so they are to the right of the part numbers. When you type the address of the table, make sure to use dollar signs so that the table reference doesn't change as you copy the formula down your worksheet. Instead of G3:H477, use $G$3:$H$477. Those dollar signs tell Excel to not lower the range as the formula gets copied down.=VLOOKUP(A2,$G$3:$H$477,
• Col_index_number tells Excel which column you want to return from the lookup table. In this case, you only have two columns. Part number is in the first column of the lookup table. New Price is in the second column of the lookup table. Since you want to return New Price, use 2 for the column.=VLOOKUP(A2,$G$3:$H$477,2
• Range_lookup - the square brackets in the tooltip say that this argument is optional, but in real life it is not optional! You should always put False as the fourth argument. If you leave the argument off, you are allowing Excel to find close matches. Close matches are never appropriate when you are doing the type of lookup described in this situation. =VLOOKUP(A2,$G$3:$H$477,2,False)

Add a new column to your price list called New Price. Enter =VLOOKUP(A2,$G$3:$H$477,2,False) in cell C5. You get the new price. This looks promising!  

Your first VLOOKUP returns the new price

Select the formula in C5 and double-click the fill handle to copy the formula down. (The fill handle is the square dot in the bottom right corner of the selected cell.) Because of the blank cells between categories, the fill handle will only copy the formula down to the end of the first category. Already, you can see that something seems to be wrong.  

 

Uh-oh! #N/A errors are usually bad, but maybe not in this case 

Techniques for dealing with inevitable #N/A errors

A few of the VLOOKUP formulas are returning numbers. Many more are returning the #N/A error. When you do a VLOOKUP, the #N/A error means that the value you are looking up is not found in the lookup table. In this case, this is to be expected, since only 475 of the 10,000 prices are being updated.

Since you are expecting the #N/A errors, you can use the IFERROR function (available in Excel 2007 or later) to replace the #N/A with something else. You might put a space. You might put a zero. Or...in this case, if you don't have a new price, then the old price is still valid, so you could put the old price!

Edit the original formula in C5. Here are the three formulas you could use:

• To show a blank, use =IFERROR(VLOOKUP(A5,$G$3:$H$477,2,FALSE),"")
• To show a zero, use =IFERROR(VLOOKUP(A5,$G$3:$H$477,2,FALSE),0)
• To use the old price, use =IFERROR(VLOOKUP(A5,$G$3:$H$477,2,FALSE),B5)

Choose a formula and copy it down. Your new column shows the new price if there is one, and the old price if there isn't one.  

 

 =IFERROR() to the rescue

In this video, see how I define VLOOKUP and explain the VLOOKUP formula, and get rid of #N/A: 

Use conditional formatting to highlight new prices

When I look at the two columns of data, it is hard to see which prices changed and which did not. You can use conditional formatting to highlight which items changed.

1. Select C5:C18. Although you are formatting a large range of cells, you need to do the following steps thinking only about the first cell in the range, C5.
2. Select Home, Conditional Formatting, New Rule.
3. Choose Use a Formula to Determine Which Cells to Format
4. In the rule description, type a formula of =C5<>B5
5. Click the Format button. Choose a red font. The dialog will look like Figure 4.
6. Click OK to apply the conditional format.

 

Any prices that have changed will appear in red.

Pain is relative: Copy your VLOOKUP formula to other categories

So far, you've solved the problem for a very small category of the master price list. You need to copy the C5 cell to the first row of the next category and use the fill handle to fill down. With 10 more categories, you will repeat this process several times and it might take 5 minutes to perform the copy operation repeatedly. Maybe it would be easier to copy all the way down to row 10000 and then manually clear out the blank rows between the categories. Someday you should reorganize the spreadsheet, but for today, you've save a lot of time. 

 

Don't forget: Change those formulas to values before you delete the new price range

At this point, you decide you don't need the temporary range over in G:H any more. Don't delete it yet! Those VLOOKUP formulas back in column C are still using that range. You need to convert the formulas to values.

1. Select all of column C
2. Right-click and choose Copy
3. Right-click and choose the 123 icon for Paste Values 

I'm Speechless

If you've followed along here, you might not know what to say. You might be upset that you wasted so much time using the Find method and wasted one day of your life every month for the last 18 months. You might be angry that you never knew about VLOOKUP before. You might be giddy that you solved this problem in 20 minutes instead of 8 hours. (And...once you gain more confidence with VLOOKUP, you'll be able to solve the problem faster and faster each month.) You might be plotting what you can do with all the time you just got back today ("long lunch!").

VLOOKUP solved this cool problem and can solve many more problems.

When you were manually finding the items, it was natural to start with the shorter list of 475 items with the intent to work through it. Since VLOOKUP is incredibly fast, you can throw the VLOOKUP at all 10,000 items and let the IFERROR clean up the unfound entries.

Many people get discouraged when they encounter the #N/A errors from VLOOKUP. Understand that some #N/A are to be expected. It is part of the messiness of data in real life. 

What to do with your free time–perform a quick quality-check

 Have you done this process manually in the past? Did the part numbers in the new price list match up correctly? As a human, you would recognize "A-1234" and "A1234" to likely be the same item, but VLOOKUP will not understand this. Take an extra five minutes to make sure that all of the items in your new price list are found in the original table:

1. Add a column heading in I2 called "Is it OK?"
2. The formula in I3 is =VLOOKUP(I3,A:A,1,False). This formula will look for the part number from I3 over in column A. If it is found, it will return the first column of the lookup table, which is the part number from column A. If it is not found, it will return #N/A.
3. Copy that formula down by double-clicking the fill handle.
4. Sort descending by column I. Select one cell such as I2 and click the ZA button on the data tab. If there are any #N/A values that come to the top of the list, it means that the part number in I is not found in column A.
5. Do some research on these. Are they mis-typed? Are they new? 

This is just one example of the many ways to use VLOOKUP. Check out vlookupweek.wordpress.com for many additional articles on other ways to use VLOOKUP.

Format and customize Excel 2013 charts quickly with the new Formatting Task pane

The new Excel offers a rich set of charting capabilities that make creating and customizing charts simpler and more intuitive. One part of the fluid new experience is the Formatting Task pane.  

Until now, precise adjustments to chart elements were made in the Format dialog box. The box sometimes obscured a portion of the chart, changes entered in the box were not visible until you closed it, and you had to select the exact element on the chart in order to see the options that were the best fit for the job.

In the new Excel, the Format dialog box is replaced by the Formatting Task pane. The pane aligns neatly with the right or left side of the screen, so it's less likely to obscure the chart, and changes happen in real time, so you can immediately see how your choices affect the chart. The Formatting Task pane also offers an element selector so you can jump quickly between different elements without having to select one to modify.

The new Formatting Task pane is the single source for formatting--all of the different styling options are consolidated in one place. With this single task pane, you can modify not only charts, but also shapes and text in Excel.

Using the Formatting Task pane

The fastest way to open the Formatting Task pane is to double-click a chart element. You can also use the keyboard shortcut CTRL+1 while a chart element is selected. There are two other ways to open the task pane:

The first way: On a chart, select an element. On the Ribbon, select the Chart Tools Format tab, then click Format Selection.

The second way: On a chart, select an element. Right-click, then select Format <element> where <element> is the axis, series, legend, title, or area that was selected.

Once open, the Formatting Task pane remains available until you close it. Since it always stays on the right or left side of the screen, the pane remains unobtrusive as you concentrate on other tasks. The options in the Formatting Task pane will change based on which element is currently selected. For example, if you select a legend, the Formatting Task pane offers layout, fill, and effect options uniquely tailored to the legend element. With this more intuitive experience, you can feel free to leave the Formatting Task pane open while you format different elements of your chart.

Selecting chart elements in the Formatting Task pane

The element selector in the Formatting Task pane allows you to jump quickly between different elements without selecting them in the chart itself. Since some chart elements are small and perhaps difficult to select, the element selector is a great alternative to the "hunt and peck" approach.

The element selector is in the top left of the Formatting Task pane. The selected element is displayed, along with a down arrow. Clicking the down arrow opens a dropdown menu that shows all elements of the selected chart. When you choose an element from this menu, the Formatting Task pane displays options uniquely tailored to this element, and it also selects this element in the chart.

You can also find the element selector on the Ribbon on the Chart Tools Format tab.

Chart options in the Formatting Task pane

Chart options in the Formatting Task pane are in two categories: Size & Properties and element-specific.

Size & Properties option

Size & Properties deals with sizing, alignment, and miscellaneous properties such as alt text and locks.

The Chart Area element is unique in that its Size & Properties options affect the chart as a whole. Size, scale, and aspect ratio can be adjusted only on the Chart Area element, from which all other chart elements derive their inherent size and scale. The Chart Area element also controls the alt text of the chart, whether the chart moves and/or sizes with its underlying cells, and whether the chart is printable and locked.

Other chart elements can adjust their alignment from the Size & Properties options, which dictate vertical alignment, text direction and angle, and margins.

Element-specific options

There are unique, element-specific options for axis, legends, and series:

Axis Options. Axis options allows you to adjust the axis bounds and units, the placement and interval of its labels, and other options including tick marks and number format.

Legend Options. Legend options allows you to specify the position of the legend, and whether or not it overlaps the chart.

Series Options. Series options allows you to specify whether the series should be plotted on the primary or secondary axis, the spacing and width of the columns on a bar or column chart, and the angle and explosion (separation) of the slices of a pie chart.

Line, fill, and effect options in the Formatting Task pane

In addition to chart options, the Formatting Task pane allows you to fine-tune the visuals of each chart element by offering a multitude of line, fill, and effect styles. These styling options are available for shapes as well.

Line options

Line options allow you to adjust the styling of the lines and borders of each chart element. There are two line types: solid and gradient. A solid line uses one color throughout, while a gradient line changes smoothly from one color to another along its path. Regardless of the line type you choose, you can adjust a line's thickness, transparency, dash type, and endpoint settings.

Fill options

Fill options allow you to choose how the inner portion of each chart element is filled. There are four fill types: solid, gradient, pattern, and picture. A solid fill uses only one color, while a gradient fill smoothly blends multiple colors along its filled region. A pattern fill tiles the inner fill region with preset imprints such as crosshatches and tiles, and a picture fill uses preset textures or a picture that you specify.

Please note that certain chart elements, such as the series of a Line Chart, do not have an inner fill region to color or tile. Fill options are disabled for these chart elements.

Effect options

Effect options offer a powerful set of effects that add visual impact to your charts. Effects include shadows, glows, soft edges, and 3-D formatting.

Shadow. This effect adds a shadow either outside or inside the chart element.

3-D Formatting. This effect adds a bevel to the chart element to give it a 3-dimensional relief.

Text options in the Formatting Task pane

In addition to modifying the look and feel of charts and shapes, you can also style text inside these objects.

Text options are in the top right of the Formatting Task pane. Clicking Text Options toggles the task pane into text styling mode. You can toggle back to the former chart and shape styling mode at any time by clicking the element selector's title.

In text styling mode, you can modify the look and feel of your text using the same line, fill, and effect options that are available for charts and shapes.

Understanding Word Templates

by Susan Daffron

Every document has a template attached to it. If you don't specifically select one, it starts off using the Normal template. Every template has styles stored in them, whether you use them or not. Styles you create can also be stored in the template as well. The template also stores page layout information and text for a certain type of document.

Unlike some desktop publishing applications, in Word, if you change a template, your changes are not reflected in old documents that use the template, unless you reapply the new version of the template to the document. Even if you have a lot of documents that are all based on the same template, they may not look anything like one another. Although the document is based on a certain template, the document matches the template as it was at the time the document was created, which may not be what the template looks like now. It's important to realize that if you change the template and then create new documents, older documents won't necessarily match the new documents.

 

Understand How Templates Work

To understand how templates work, you need to understand where they fall in the Word architecture. If you don't understand Word's view of the document world, it's easy to lose track of where your settings are stored.

 

According to Microsoft, Word has a "layered" architecture. The document is the top layer. Underneath it is the Word application itself. Settings you add to the top layer supersedes the settings below. Here's a list of the layers from the bottom to the top:

• Word: The Word application itself is the bottom layer. It contains all the built-in commands and functions. The program controls basic function such as right-justifying a paragraph.
• Global: The next layer is the Global layer. This layer includes global templates, add-ins, and the Normal template. This layer also includes macros, AutoText entries, and toolbars. For example, if you add a macro to the Normal template that has the same name as a built-in Word command, Word runs your command instead of the built-in one.
• Custom Templates: Your templates live above the Global layer. So the styles and other settings you put in your templates override the settings stored in the Normal and global templates. For example, if you set up style called Heading 1, your settings override the Heading 1 style in the Normal template.
• Document: The top layer is the document itself. The settings you add to the document override the settings in any templates. If you reformat a Heading 1 paragraph by adding local formatting, those settings override the settings stored in the Heading 1 style in either the Normal template or your own templates.

If you don't create your own templates or styles, Word gets all its document formatting information from the document (the top layer). Any other default settings come from the Normal template or the built-in Word settings.

 

If you have set up your own styles in a template, Word looks for local formatting settings in the document, then looks at the Custom Template layer for your style settings. Then it looks at the Global layer and finally the Word layer to find other settings to construct the document.

Create New Templates

If you create a lot of documents, it makes sense to start creating templates that contain styles geared toward particular types of document. Although many people just add more and more styles to the Normal template, doing this can get very confusing very quickly. When you set up templates for specific document types, you don't have to scroll through thousands of irrelevant styles to find the one you need.

 

You can create a template in two ways. You can create a new file with a .dot extension or base it on an existing file and saving it with a .dot extension. To create a template from scratch, choose File, New and change the radio button in the Create New box from Document to Template. You can base the new template on an existing template or on a Blank Document (the Normal template). You add styles, formatting, macros, toolbars, and text just as you would to any other document. Then you save the file as a template with a *.dot file extension.

 

To save a file as a template. Open it and choose File, Save As. Change the Save As Type drop down-box to Document Template (*.DOT). When you switch the file type to a template, Word automatically switches to its default template directory. If you save the file there, it will be available in the list the next time you do a File, New.

 

When you create a template from an existing document, Word leaves the text in there too. So you may want to delete the text that is specific to the original document, so that just the styles and layout information remain.

Attach Templates

At some point, you may want to reformat the styles in a existing documents to styles you've created in a new template. This process is known as "attaching" a template to a document.

To attach a template, you select File, Templates and add a check mark to the Automatically Update Document Styles option. When you click the OK button, Word overwrites the style formatting of styles in the document that have the same name as the styles in the new template you're attaching. All the matching styles change to the new formatting. Any new styles in the new template are added to the style list. Text formatted with styles from the old template that don't match styles in the new template retain the old style. If you want to make sure that everything is reformatted to the new settings, make sure all the text is formatted with style names that match across the two templates.

 

If you want to retain certain styles from the old template, make sure you rename any matching styles you want to preserve. Choose Format, Style, click Modify, and type a new name.

 

Attaching a template only changes the styles in a document; it does not change other elements such as margins, page size, headers, or footers. Because margins, headers, footers and so on are document-level settings they supersede template-level settings. When you attach a new template, because the page settings are stored at the document level, Word can't overwrite them with settings stored at the template level.

 

Because document settings cannot be overridden, in this situation you must bring the document you want to change into the template. This way, you end up using the document settings stored in the template. You create a new document based on your template and then choose File, Insert. The new document contains the document-level page elements as well as the styles stored in the template.

Copy Styles Between Templates

You can copy styles from existing documents or templates to other documents or templates. To copy styles from one document or template to another:

1. Choose File, Templates.
3. Click Organizer and select the Styles tab.
5. You may need to close the document or template on one side or the other and open the desired document or template. Be sure to check that the names are correct before you start copying.
7. Choose a style from the Styles in box. When you click a style name, you see a description.
8. Click the Copy button. The styles are copied to the document or template.

Copying styles can be useful if you set up a nifty new style, but forgot to add it to the template. Just open the document with the style and copy it to the desired template.

Excel Array Formulas

What are Array Formulas?

Excel Array formulas are very powerful and useful formulas that allow more complex calculations than standard formulas. The "Help" in Excel defines them as below:

"An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments."

Array Formula Rules:

Before we show some examples of array formulas it is important to know 4 fundamental rules.

• Each argument within an array must have the same amount of rows and columns.
• You must enter an array by pushing Ctrl+Shift+Enter.
• You cannot add the {} (braces) that surround an array yourself, pushing Ctrl+Shift+Enter will do this for you.
• You cannot use an array formula on an entire column.

Pet Shop Example:

Suppose you have 5 Columns of data each with 200 rows.
Column A is used to keep track of the sex of each dog sold i.e. Male or Female
Column B is used to keep track of the breed of the dogs sold.
Column C is used to keep track of the age of the dogs sold.
Column D is used to keep track whether the dog is sterilized or not i.e. Yes or No
Column E is used to keep track of the cost of the dog sold.

• To count the number of male Poodles sold:
  =SUM(($A$2:$A$200="Male")*($B$2:$B$200="Poodle"))
• To count the number of male Poodles sold over 3 years old:
  =SUM(($A$2:$A$200="Male")*($B$2:$B$200="Poodle")*($C$2:$C$200>2))
• To get the total cost of male Spaniels sold:
  =SUM(IF($A$2:$A$200="Male",IF($B$2:$B$200="Spaniel",$E$2:$E$200,0),0))
• To find out the average age of male dogs sold:
  =AVERAGE(IF($A$2:$A$200="Male",$C$2:$C$200))
• To find out the average cost of male dogs sold over 2 years old:
  =AVERAGE(IF($A$2:$A$200="Male",IF($C$2:$C$200>2,$E$2:$E$200)))
• To find out the Minimum age of dogs sold that are sterilized:
  =MIN(IF($D$2:$D$200="Yes",$C$2:$C$200))

All the above formulas must be entered with Ctrl+Shift+Enter

TIP: If you are having problems writing an array formula to sum your totals then use the Conditional sum wizard, Tool>Wizard>Conditional sum. If you don't see it then you will need to add it via Tools>Add-ins>Conditional sum wizard.

 

While using array formulas can be very handy they have one draw back and that is, too many of them within your workbook WILL slow down Excels recalculations. If you will need a lot of array formulas within the same workbook consider using, Pivot Tables the Database functions instead. These are ideal for extracting information from tables and databases.

Excel Autosum: sum up values in Excel automatically

Because adding numbers is probably the most common function that Excel is used for, Excel has a built-in Feature called AutoSum located on the Standard toolbar.  AutoSum is represented as the Greek Capital letter Sigma Σ.  You can use AutoSum to sum a range of cells.  A Range can be one single cell, or many cells.  You can sum cells in a contiguous (no gaps) range of cells, or a non-contiguous (cells not joined together) range.

 

To use AutoSum  you must click in the cell that you wish your result, or addition to appear in.  As a default, AutoSum looks up a column for figures immediately above it to add together.  This works great, unless it encounters a blank row or text.  If it does, then it stops at the last cell with a number in it.  If there are no numbers above it, AutoSum will automatically go to the left looking for numbers to add up, but will again stop at a blank column or text.   This is Excel's default, but you can easily change it.

 

The SUM Function is written as =SUM(number 1, number 2).  = is the trigger to Excel that a function or formula is following.  SUM is the name of the function and (number 1, number 2) are the arguments that the SUM function needs to work, or in our case the numbers it is to add up.  When you click the AutoSum icon, you will see the SUM function written in your cell, with a marquee (floating dotted line) around what the AutoSum intends to add up.  If the highlighted range is what you wanted to add up, click OK, if not then change the range you wish to add.

 

Following are three screen shots showing the AutoSum.

AutoSum automatically picks up the numbers above it

AutoSum automatically looks left for numbers if it encounters no numbers immediately above it, but numbers to the left.

AutoSum automatically stops when it encounters a blank line, or text in the middle of the range it is trying to add up.

Add Background Pictures To Excel 2007 Worksheets

Chart, numbers, worksheets … at a business presentation it can get rather monotonous looking at the same data sheets.  Here is a way to add background graphics to your Excel spreadsheets to spice up those meetings.

First, open the spreadsheet you want to add the background to.

On the Ribbon click on Page Layout and select background.

This pops up the Sheet Background screen where you can select the background image you want to use.  Once you have chosen your background click the Insert button.

Now you have a background to your spreadsheet.  You may have to adjust some of the fonts and colors depending upon the background image.

Editor's note: Might want to choose either a really dark or really light colored image.

This content was originally posted on http://officeresources.blogspot.com/ © 2008 If you are not reading this text from the above site, you are reading a splog

Add Background Color To Word 2007 Documents

Instead of using the standard white background with Word documents, here is how to add some background color to spice up your documents.

Open your word document and choose Page Layout on the Ribbon, then select page color to select the background color you want. 

The nice thing about Word 2007 is you just need to hover the mouse over each color to see a preview of how it will look in the document.  Select the background color you want and you are done.

This content was originally posted on http://officeresources.blogspot.com/ © 2008 If you are not reading this text from the above site, you are reading a splog

Excel: Find the nth Occurrence of a Value

Note also that this extra column can be hidden from view to make for
less clutter. If you have not already guessed, we now simply use a
standard VLOOKUP, or INDEX/MATCH to obtain the occurrence we want, for
example;

=VLOOKUP("Bill 3",$A$1:$C$22,3,FALSE)

Would yield a return value of "Bill # 3"

Find the Nth Occurrence of Specified
Value Custom VBA Function

The custom function/formula below was written in Excel 2003 and may not
work in earlier Excel versions.

The custom function/formula can now be used like shown below 

=Nth_Occurrence($B$1:$B$22,"Harry",3,0,1)

The syntax is

=Nth_Occurrence(range_look,find_it,occurrence,offset_row,offset_col)

This content was originally posted on http://officeresources.blogspot.com/ © 2008 If you are not reading this text from the above site, you are reading a splog

INDEX/MATCH: Left Lookup Formula Excel

The MATCH  Formula/Function Returns the relative position of an item in
an array that matches a specified value in a specified order. Use MATCH
instead of one of the LOOKUP functions when you need the position of an
item in a range instead of the item itself.

Returning the Column Number and Row NumberWe can either take this a step further and ensure the
column_num
argument supplied is always correct by nesting another MATCH
Formula/Function into the column_num argument. The formula for this
would be;

=INDEX(DataTable,MATCH("RKP4",ID,0),MATCH("Name",Headings,0))

OR, with no Named Ranges

=INDEX($A$1:$D$9,MATCH("RKP4",$C$1:$C$9,0),MATCH("Names",$A$1:$D$1,0))With both the above examples, we can assure that moving the
Names
Column will not cause our formula to return an incorrect result.

This content was originally posted on http://officeresources.blogspot.com/ © 2008 If you are not reading this text from the above site, you are reading a splog

Stop The #N/A! Error in VLOOKUP and other Lookup Functions

1) Add your lookup formula to a spare column (e.g Column "A") and allow the
#N/A! to happen.

2) Now reference these cells from the required cells like this;

=IF(ISNA(A1),0,A1)

3) Hide Column "A" by selecting it and going to Format>Column>Hide

Instead of;

 =VLOOKUP("Dog",A1:D100,2,False)

Use;

=IF(ISNA(VLOOKUP("Dog",A1:D100,2,FALSE)),0,VLOOKUP("Dog",A1:D100,2,FALSE))

If you don't like the Zeros showing you can hide them via
Tools>Options>View - Zero values. Or, cell-by-cell with a
Custom Format like:
General;-General;

This content was originally posted on http://officeresources.blogspot.com/ © 2008 If you are not reading this text from the above site, you are reading a splog

OOXML is defective by design

This post is from http://ooxmlisdefectivebydesign.blogspot.com. This blog discuss a lot of flaws in Microsoft claims about Office 2007. Below is just an

Custom XML? What Custom XML?

Stéphane Rodriguez, March 2008



Other than backwards compatibility, the expression "Custom XML" plays an important role in Microsoft ISO OOXML evangelism. It's interesting that Microsoft bloggers don't even seem to be embarassed by ridiculous expressions such as "Custom XML". Custom XML is indeed just as silly as "Office Open XML" : the reason is X in XML already means Custom. So there cannot be a meaningful sense for Custom XML...unless Custom XML is short for Custom XML applications. And from that it makes sense. But there is a problem : Custom XML is part of the ISO proposal of a document file format, whereas Custom XML applications implies the apparatus and logic related to applications, inherently tied to products, platforms and operating systems, not documents. Did Microsoft feel guilty, removed the word applications knowing it wouldn't stand a chance otherwise ? That's for anyone to guess. In this article, we are going to delve into the so-called "Custom XML", and how little useful it is in practice.

Custom XML definition, as per Microsoft

Straight from the horse behind it, Brian Jones :

Custom XML is the support for custom defined schemas. It's that support that allows you truly integrate your documents with business processes and business data. You can define your data using XML Schema syntax, and then you can use that data in your Office documents. By opening up our formats with our reference schemas, and supporting your custom defined schemas, you get true interoperability of your documents. Sorry if this is currently sounding more like a marketing pitch, but I wanted to make sure I reiterated our vision for XML support in Office documents and hopefully that will help you see the power that we see. (...) Up until now we've talked about all the parts that we in Office have defined to create our documents. You as a developer also have the ability to add your own parts though. You can take any XML file and put it inside the ZIP package.

Translation : by XML, we actually mean several completely different things, and we've put all of it in the same pot. We think storing XML-based data inside the ZIP package is an efficient way to share your confidential corporate data to the outside world and we are proud to make it easy to do so. Likewise, storing business data and the document together illustrates the grand Microsoft vision about independent layers.





Straight from the marketing people at Microsoft, here Doug Mahugh (answering Patrick Durusau during the INCITS V1 review of OOXML back in April 2007) :

DIS 29500 (OOXML) serves other purposes that are not served by ISO/IEC 26300 (ODF), especially in the area of integration options for external schemas. (By "external" I mean schemas that are not part of the spec itself -- in common usage we tend to call these "custom schemas" as opposed to the "reference schemas" in DIS 29500).

Translation : at Microsoft, we ship products to make it possible to do what you can already do without.





Straight from a top Microsoft Office brass :

Open XML allows for custom XML markup within the body of a document which is a handy way to allow users to tag their content for interoperability with other types of software such as a custom line of business system.

Translation : we dare you put foreign XML markup into an existing XML, with no agreed-upon semantics between the two languages, in order to improve the interoperability. What kind of interoperability we are talking about is left as an exercise to the reader.

Enough marketing fluff. What it really is.

First, let's get out of the way that "Custom XML" actually means "Custom" "XML" at all.

• Start Word 2007.


• Create a new document.


• Type "test".


• Save and close the document.


• Unzip it.


• Grab the part word/document.xml, you should see the following :
  
  
  
  

  
  <w:p>
    <w:r>
      <w:t>test</w:t>
    </w:r>
  </w:p> 
  
    

  
  
  
  


• Now add some custom XML markup :
  
  
  
  

  
  <w:p>
    <w:r>
      <w:t mytag="myvalue">test</w:t>
    </w:r>
  </w:p> 
  
    

  
  
  
  


• Put the edited part back into the ZIP file and open it in Word 2007. It opens perfectly well.
  
  


• Close it. Unzip the file again. Grab the part word/document.xml, you should see the following :
  
  
  
  

  
  <w:p>
    <w:r>
      <w:t mytag="myvalue">test</w:t>
    </w:r>
  </w:p> 
  
    

  
  
  
  


• Now remove the custom attribute and instead add some other custom XML markup, this time an element :
  
  
  
  

  
  <w:p>
    <w:r>
      <mytag>myvalue</mytag>
      <w:t>test</w:t>
    </w:r>
  </w:p> 
  
    

  
  
  
  


• Put the edited part back into the ZIP file and open it in Word 2007. This time it brings the following error message :
  
  
  
  
  
  Corrupting the document by adding custom XML markup
  
  
  
  


• Perhaps this is a namespace issue. Let's prefix our custom XML with w so that the fragment looks like the following :
  
  
  
  

  
  <w:p>
    <w:r>
      <w:mytag>myvalue</w:mytag>
      <w:t>test</w:t>
    </w:r>
  </w:p> 
  
    

  
  
  
  


• Put the edited part back into the ZIP file and open it in Word 2007, and indeed this time it opens well.
  
  

From those simple tests, we can infer the following :

• There is no such thing as strict XML schema validation in Word 2007.
  
  
  
  


• You can add a custom XML attribute with no namespace (therefore the attribute uses the element's namespace, in our case w), and Word 2007 will not complain even though the mytag attribute is not part of the OOXML reference schemas. See ECMA 376, part 4, page 199, section 2.3.2.23, the w:r element is defined as follows :
  
  
  
  

  
  <complexType name="CT_R">
    <sequence>
      <group ref="EG_RPr" minOccurs="0"/>
      <group ref="EG_RunInnerContent" minOccurs="0" maxOccurs="unbounded"/>
    </sequence>
    <attribute name="rsidRPr" type="ST_LongHexNumber"/>
    <attribute name="rsidDel" type="ST_LongHexNumber"/>
    <attribute name="rsidR" type="ST_LongHexNumber"/>
  </complexType>
  
    

  
  
  
  and therefore does not allow the mytag attribute to be present.
  
  
  
  


• You cannot add a custom XML element unless it is prefixed by one of the namespaces declared in the header of the XML document, in our case w (xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main"). Rather interesting again since w:mytag does not belong to the OOXML reference schemas, and yet Word 2007 does not complain about it.
  
  

Needless to say, this is a broken implementation of XML that neither satisfies the "strict XML" camp, nor the "loose XML" camp.



It can be summarized by the following table :

Word 2007
Test	Result	Strict validation
mytag="myvalue"	OK	NO
<mytag>myvalue</mytag>	FAIL	N/A
<w:mytag>myvalue</w:mytag>	OK	NO

And for the other two applications, Excel 2007 and Powerpoint 2007, the results are as follows :

Test	Word 2007 result	Excel 2007 result	Powerpoint 2007 result
mytag="myvalue"	OK	FAIL	FAIL
<mytag>myvalue</mytag>	FAIL	FAIL	FAIL
<w:mytag>myvalue</w:mytag>	OK	FAIL	FAIL

Since Word 2007, Excel 2007 and Powerpoint 2007 do not handle Custom XML the same way, as the table above shows, it's very hard to trust Microsoft when they claim that Custom XML is a feature of OOXML.



Either application will see the document as a corrupt one is guaranteed to be random, Microsoft cannot possibly imply that the Custom XML they are talking about is what we take for granted when we say "Custom XML", i.e. the ability to add our own XML within the document.

Custom XML, as per ECMA 376

Since "Custom XML" does not mean "Custom" "XML", we have to rely on ECMA 376's definition of such thing. The first surprise is that there is a notion of "Custom XML markup" and a notion of "Custom XML data".



The second surprise is that the notion of "Custom XML markup" only appears in the documentation for Word documents. If we assume that this "Custom XML markup" will be used to bind the "Custom XML data" to the document at run-time, we can infer from ECMA 376 that this is only made possible for Word documents.



In other words, whatever "Custom XML" is, it is only fully implemented for Word, so it should be called "Custom XML in Word". When Microsoft marketing people are trying to sell us "Custom XML" as a feature of OOXML, it is a lie. Let's create a table of what we have just learned.

	Word 2007	Excel 2007	Powerpoint 2007
Custom XML data	YES	YES	YES
Custom XML markup	YES	NO	NO

"Custom XML data", ECMA 376 part 4 section 8, supported in both documents, is the ability to store an independent XML stream in the ZIP package. In fact, it is not a feature of OOXML at all, it is a feature of any ZIP archive. After all, a ZIP entry in a ZIP package can be anything, including an XML stream. We can infer from that, that "Custom XML data" is in fact nothing custom : the ability to store an independent XML stream is not something we should thank Microsoft for allowing us to do so.



"Custom XML markup", ECMA 376 part 4 section 2.5, supported only in Word documents, is the ability to bind the "Custom XML data" to the document's content, at run-time. Interestingly enough, the Word team at Microsoft haven't quite managed to merge this concept and the old "smart tag" concept. That's why in the ECMA 376 specification, we end up with several flavors of "Custom XML markup", one of which is smart tags, the ability to add metadata to content (eg. stock quote).



An interesting element is "run-time". If you write the Custom XML markup that make it possible to do the said data binding, it has to be reminded that the data binding is done by an instance of Word, not a third-party application. So data binding is just a lock-in. That's the difference between standardizing on a document versus standardizing on an application!



There is no mechanism for doing such thing in Excel spreadsheets and Powerpoint presentations. In Excel spreadsheets, the XML data binding, a feature available from the user interface, is a special case of data source querying where the data source is an XML stream. The XML stream is external to the ZIP package. In other words, the "Custom XML data" in spreadsheets is useless. In Powerpoint presentations, it's even more trivial since there is no such thing as an XML data binding mechanism from the user interface.

The merit of "Custom XML data"

Something interesting to note is that Microsoft thinks that storing data inside the ZIP package independently of the document is a good thing. From a pure technical point of view, you can view this "Custom XML data" as a cache of values thanks to which the consumer is able to drill into the data without a connection to the actual data source (corporate data). But there is a major flaw. Anybody using this feature will end up storing arbitrary data in ZIP packages shared across colleagues and others inside and outside the organization. Eventually, confidential information from the corporate databases will end up there, and a PR disaster automatically follows. You don't want to use this feature.

Conclusion

"Custom XML" does not mean much, despite Microsoft ample evangelism of said feature. Technically speaking it has no merit within the enterprise space because you end up sharing corporate data. An interesting fact is that "Custom XML" is actually only implemented in Word 2007. For instance, the ECMA 376 specification provides a data binding for Word 2007 documents, exclusively. Ironically enough, the ability to store an independent XML stream as part of a ZIP package, is just a feature of the ZIP library, not Microsoft's innovation.

A look at Microsoft Office 2007

Open a blog with some rants, yea, that's how things work...

When I started writng this, I've been using Office 2007 for over 2 months (3 at the most recent edit xD), at first I always wanted to write a review, just didn’t have the time yet :P. Common feeling is mostly satisfied but there are still some stuffs that annoys me.

• First it’s the new file format. Yes it appears to be smaller than the last version’s. But a closer look to it reveals a lot of metadata, those a normal user not working for some big enterprise will ever need, exceedingly stored under a fancy directory structure, xml and stuff. It’s smaller just because it’s compressed (which means it shouldn’t be compressed anymore). The older file format will compress way better :).


• Second, the introduction of a new file format just does not worth the trouble having to install converters to the old version or even upgrade to 2007. Yes it’s open, but isn’t most text processor are able to open and save Word’s old file format? Now the convenience of composing a file on 2003 then load it in 2000 with only the new features disabled is gone. 2003 can’t just open 2007’s file without the file format converter, the converter is included in 2003’s SP3 though, everyone is advised to upgrade as usual.


• Lastly, the new feature count is not what a user would expect after 4 years waiting from the previous release. It adds new feature, it changed its look but it’s mostly the o’-good-features from a decade ago (from my view 70% of the current version’s features existed in Word 6.0 running on Windows 3, but Word 6.0 is much smaller ;). Seems like little innovation can further be made for this business.

After four years, Microsoft must have added a lot of things that (they hope) will brighten the day. I didn’t find enough time to scour what’s new list yet, so here is comes the long-waited list of new stuffs that I noticed when using Word for practical purposes

• When you first fire up any application in the suite, the first thing you’ll notice is the all-blue interface, your file menu is gone, your toolbar seems a little weird, like a mix between some options panel, toolbar and menu. Well, that’s the new innovative interface of Office, the ribbon, which replaces the old menu and toolbar functions. You may not see the underlines but the ribbon actually can be accessed with your keyboard. Press and hold Alt to see the approriate keys.
  
  
  
  




• Except for the simple black & white icon, you could also see that other icons are different from the previous version. They are more “crystallized”, possibly to look better with the all-round-glassy Vista interface.
  
  
  
  


• Despite the renewed glassy (and must be 32bits) icons, Microsoft still left 16 colors icon around 8-> click the browse button (the round dot between the double arrow up and double around down under the vertical scrollbar in Word)
  
  
  
  
  
  
  
  


• There’s also some button you won’t find on the ribbon like undo and save / open. They are essential, they aren’t removed, they are just moved to the quick access toolbar. Its name reflect its attributes, it is a small remnant from the old toolbars, now serving you with convenience. I don’t use it that much, I often use keyboard shortcuts for those actions :)
  
  
  
  


• The ribbon does not shorten like old toolbar so no matter how many button you actually use, the ribbon still takes up that much space. You can hide the ribbon to free up screen space (by double clicking the tab), but it will take you an extra click to reveal the ribbon before you can use any button
  
  
  
  


• Pros of the ribbon
  
  
  
  




• Look good
  
  
  
  


• It’s easy to catch up with the new design, may be even better for first-time learners.
  
  
  
  




• Cons
  
  
  
  




• Some buttons are allocated according to “Microsoft logic”. Should you unable to find a button, remember to press F1.
  
  
  
  






• ‘Cause the menu is gone, the dialog boxes now pop-up when you click the small little arrow (or in the right click menu) . They are mostly intact, which means the find dialog will still appear right in the middle of your screen and block the text you want to find. One thing I like about Firefox: find dialog at the bottom and is designed to fit as a toolbar. You may argue that find dialog is limited. Actually, WordPerfect is the first to arrange a dialog as toolbar and its function is not limited ;)
  
  
  
  


• When you right click, there will not just one menu pop up, but twice the fun. It’s the font formatting menu, just a few pixels away from your pointer (it won’t appear if you use the popup menu button on your keyboard, ‘because it would be inconvenience).
  
  
  
  
  
  


• The LCD-oriented font smoothing technology Clear Type is enabled by default, even when you are not using it. It looks good on LCD screens but people with CRT displays may need to touch the Options (now hidden in the big office button)
  
  
  
  


• The reading mode now automatically switches Word to full screen. Logic though, if you concentrate on reading a novel, you don’t need to switch to another application and distracts yourself but what if that is some kind of technical report…
  
  
  
  


• Design Science’s Math type (aka Microsoft Equation) is replaced with MathML (for storing) and the new linear syntax (for input), which is what I like most in this new version and should be implemented, say… a decade ago (there have been various plugins since then but they could cause a whole lot nuisance :P). Now I don’t have to Insert / Object / Microsoft Equation, point to the integral, click, type numbers, type x, select the expnonent, type the number but instead Just Insert / Equation and type something like “\int_3^2 3x^5+5/2 x+2dx” and… voilà
  
  
  
  
  
  
  
  




• The math interface is good, though not good like advertised ;) Microsoft claims that anything use MathML will be able to copy and paste equation with Office, I used it with another MICROSOFT product and the result is gabbish
  
  
  
  


• The documentation for equation is not yet finished, and except for those who’s familiar with L_EX or L_AT^EX, it’s just point-and-click because there’s no hint what’s the linear syntax for a specific symbol (some proposed this for Microsoft though, but they say it’s too late to change the design)
  
  
  
  




• The Organization Chart and similar illustrations have been replaced with “Smart Art”, they are not actually “thinking” but they are really well-designed to server their purpose… looking good.
  
  
  
  


• The new WordArt looks good too, but it’s another funny thing: Excel’s WordArt is more beautiful than Word’s (above:Excel below:Word)
  
  
  
  

It's a lot of features, but I’m not using Office 2007 for real though. I often:

• Use it to compose pages that need a good-looking layout (for web and stuffs)


• Then save it to the old .doc format. Many computers aren’t equiped with this version though, and due to experience, in anytime soon. Word 2003 is already quite good at what it’s doing and low-end machine in cybercafes aren’t capable of running flashy and shiny stuffs.

That’s all, the sum of my view on the most recent version of Office, hope it helps xD