Spiga

### 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;

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

=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 :

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 myvalue FAIL N/A myvalue 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 myvalue FAIL FAIL FAIL myvalue 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… voilà

• 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 LEX or LATEX, 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