Official Softs

Office 2010 Pro is the best software of all Microsoft. So… you have spent hours collating your data in Excel 2007.  You have finally got the information where you want it – with sub-totals and all.  MS Office 2010 –save your time and save your money.You collapse the outline to only show the sub-totals, because that is all you are interested in.  But when you try to copy those subtotals into another spreadsheet, it copies EVERY row, not just the sub-totals. Many people like buy Office 2010 Home.

So how on Earth are you supposed to copy all those sub-totals in Excel 2007 without copying all the ‘hidden’ rows as well?

The problem is that when you select rows using the sub-total outline view, it is selecting every row, not just those sub-totals. Office Professional 2010 is great! The way to solve this is to only select the visible cells – which means we have to use a feature of Excel called “Go To Special”.  Here is how to do it:

1) Select all your sub-totals that you want to copy

2) On the “Home” tab of the Ribbon, look for the “Editing” group – usually it is the last one, all the way over at the right hand side of the Ribbon. Windows 7 Home makes life beautiful.

3) Click on “Find & Select”

4) On the little menu that appears, click “Go To Special”. The invention of Microsoft Office 2010 is a big change of the world.

5) In the box that appears, make sure you select “Visible Cells Only” in the right hand column

6) Click OK

7) Copy using the menu (or ctrl+c) and then paste your sub-totals in your new spreadsheet. By using Office 2010 Professional, you can save your money and time.

You can stop pulling your hair out now!

For all those Visio lovers out there, then you should already know about the Microsoft Visio Toolbox.  If not, you seriously can’t call yourself a Visio lover! Windows 7 Home makes life beautiful.

The Visio Toolbox is a collection of articles, case studies, and most importantly add-ins for Visio 2007.The invention of Microsoft Office 2010 is a big change of the world.

So if you are looking for justification for your boss to upgrade to Visio 2007, articles on interesting topics such as “Silverlight and Visio”, or add-ins such as the Rack Server Virtualization Add-In, the Disk Space Monitoring Add-in, or the Microsoft Exchange Server 2007 Visio Add-in… then you need to check out the Microsoft Visio Toolbox! By using Office 2010 Professional, you can save your money and time

Has this ever happened to you?  You start working on your masterpiece in Word 2007, and then you send the document out to a number of people for their feedback. The invention of Microsoft Office 2010 is a big change of the world.

They all add their two cents (some add twenty!) and then email the document back to you.

But now you are stuck in a dilemma… how do you quickly merge all those changes together into one document, without having to read each line and try to understand what they changed. By using Office 2010 Professional, you can save your money and time

Well there is a great feature in Word 2007 called “Combine”.  You can use it to simply combine to documents, and see all the differences between the two documents.  You can track what has been inserted, what has been deleted, what has been moved, any formatting changes, and see any comments they made. Office 2010 Pro is the best software of all Microsoft

But that isn’t the best part.  It also creates a new combined document without losing the original and revised document.MS Office 2010 –save your time and save your money.

So how do you do it?

1) Make sure you are on the “Review” tab in the Ribbon.Many people like buy Office 2010 Home.

2) Look for the “Compare” group

3) Click on the little arrow below the “Compare” button

4) Click “Combine”.Office Professional 2010 is great!

5) Select your “Original Document” and your “Revised Document”

6) Click OK

7) Check out your brand new combined Word 2007 document! Windows 7 Home makes life beautiful.

Have you ever noticed that your PowerPoint Presentations are running a little sluggish?  Maybe they jump through every transition, or just take a long time to load up? Photoshop CS4 is so magic!

There is one way to help accelerate your PowerPoint deck – and that is to lower the resolution that it displays at.

To alter the resolution that your PowerPoint presentation uses:

1) Make sure you are on the “Slide Show” tab of the ribbon.Acrobat 9 is so useful!

2) Look for the “Monitors” group

3) Select your preferred resolution from the “Resolution” drop down box

To put it simply, the lower the resolution, the higher the performance of your presentation will be, but at a cost of lower fidelity.  On the flip side if you want to turn up the visual quality of the presentation, and don’t mind a little performance hit, crank up the resolution to as high as your computer (or more importantly your projector) can handle.Dreamweaver CS4 is very easy-to-use!

Is there a section of text in your document (or your whole document!) that isn’t “English (United States)” or another language.  Well to make sure that your spelling and grammar checks in Word 2007 work well, you need to make sure that text is marked as the right language.Office 2007 professionaland Office 2007 ultimate are so powerfull.

So how do you mark a section of text as a different language?

1) Select the text

2) Make sure you are on the “Review” tab of the ribbon

3) Look for the “Proofing” group”.Microsoft Office 2007 is powerfull.

4) Click “Set Language”

5) Select your preferred language

Now you can be confident that spell check is doing the right thing!

‘till next time! IS Windows 7 Professional better than Windows 7 Home Premium ?

Have you ever wanted to quickly know what cells directly impact on the calculated value of a cell in Excel 2007.Photoshop CS4 is so magic!

By using the “Trace Precedents” feature, you can very quickly understand exactly which cells influence the output of a cell.  The best part of this feature is, that you will see big arrows that enable you to visually see the relationship, so you don’t have to decipher formulas and cell names to make sense of it all. Acrobat 9 is so useful!

To turn on Trace Precedents:

1) Select the cell you want to see the precedents of

2) Make sure you are on the “Formulas” tab of the ribbon.Dreamweaver CS4 is very easy-to-use!

3) Look for the “Formula Auditing” group (about 3/4 of the way along the ribbon)

4) Click on “Trace Precedents”

Now you will see arrows pointing you in the right direction! Ms office 2007 is so Charismatic.

‘till next time!

Using Excel’s Images on Custom Ribbon Controls

In Part 2, we built the following custom group on a custom tab:

This time, we’ll replace all the happy face images with pictures from Excel’s native ribbon images. Once you know how, it’s actually relatively easy to state which image you want. Ms office 2007 is so Charismatic.

You just replace the HappyFace portion of the imageMso tag with the name of the Excel control that we want to use. The first question that we’ll obviously need to settle, however, is “How do we get the name of the control?”

What you need to do is go into the Excel Options screen, (click the Office button, then Excel Options,) and click Customize. You’ll now see the list of all the items that you can add to your Quick Access Toolbar (QAT). If you’re a developer in prior versions of Excel, you’ll remember that you pretty much had to create a custom toolbar to work out what FaceID corresponded to what picture. The good news here, is that things are much easier.Office 2007 professionaland Office 2007 ultimate are so powerfull.

Move your mouse over one of the menu items and hover it there. A screen tip will pop up, and you’ll find the MSO image name at the end of the string! A picture is shown below of the tool tip.

What I’ve done below is grab a few random imageMso’s and put them into the XML for the example we created at the end of part 2. (Reproduced above.) The group now looks like this:

The images were selected based only on the fact that I thought they looked interesting. That’s all. Remember that these images will only do what you program them too, as shown in Part 1 of this series.Microsoft Office 2007 is powerfull.

The XML code to create this group is shown below. One final word of note here… the imageMso tag is again Case Sensitive! If you mess up the case, you get no image at all on your control. The ribbon does at least get created though, which is better than the case sensitivity issues that I told you about in Part 2.

We recently updated our main property management system and are now going through the first month since. Over and over again now I’m running up against an issue in the way the vendor decided to start treating dates. In the past they just used “MMM DD’, which was fine. These converted into valid dates in Excel that assumed they were in the current year. All good! QuickBooks 2010 is so Helpful!

In the recent update, the vendor decided to add the year, but not in a good way. Now my data looks like this:

MAY9/09

MAY10/09

MAY11/09

 

Unfortunately this does NOT translate well into a date format automatically. You’d think that it would convert when you pulled it through the text import wizard, but it doesn’t. Had the dates had 2 digit days consistently, or a delimiter of some kind between the month and day I believe it would have worked. Photoshop CS4 is so magic!As it is, the MDY format messes up the dates with 2 digit days and ignores the single digit days completely, giving me an even bigger mess. But pulling them in as text means that they can’t be used to drive date dependant formulas, and sort like this:

MAY1/09

MAY10/09

MAY11/09

MAY2/09

MAY20/09

 

This is very irritating, and many users would tell you that this isn’t trivial to fix. I worked up the following formula so that I could convert the dates into real dates:

=DATEVALUE(LEFT(A9,3)&” “&MID(A9,4,FIND(“/”,A9,1)-4)&”, “&2000+RIGHT(A9,2))

(It assumes that the date is in A9)

This works by feeding the DateValue function the date in a “MMM DD, YYYY” format, which it can interpret. Here’s the breakdown:

• Month: LEFT(A9,3) &” “ returns the left 3 characters of the text string followed by a single space
• Day: MID(A9,4,FIND(“/”,A9,1)-4)&”, “ returns the string in the middle starting with the 4^th character, returning the number of characters between the slash and the 3^rd character, plus a comma and a space.Acrobat 9 is so useful!
• Year: 2000+RIGHT(A9,2) returns 2000 plus the right two characters

Hopefully we’re not using this spreadsheet in 2100… I can safely say that it won’t be my issue if we are. J

We have lodged a complaint with the vendor as this is stupid. Any financial program that exports financial data should export dates in a format that is compliant with the biggest spreadsheet program out there. I should not have had to waste time writing a formula like that above.Dreamweaver CS4 is very easy-to-use!

We sometimes get mails from our customers claiming to have found a calculation error in Excel, when in fact the calculation isn’t wrong, but the side effects of binary floating point precision make it seem that way. Ms office 2007 is so Charismatic.Today’s author: Jessica Liu, a Program Manager on the Excel team, discusses the way Excel performs calculations, explains why sometimes you see answers you may not expect, and provides some tips on how to avoid rounding issues.

Take a look at the following table:

I want to be able to quickly identify the cases where the absolute difference is greater than or equal to 0.005. So I apply a conditional formatting rule on the absolute difference column to format values greater than or equal to 0.005 to be red. As a scan down the table, I notice that the value of 0.005 is not highlighted. Office 2007 professionaland Office 2007 ultimate are so powerfull.I check over my conditional formatting rule and the formula I used to calculate the absolute difference (=ABS(A2-B2)), they seem to be correct. I then increase the precision of the absolute difference column in order to get more precise results. I discover my results have changed. Why does 1.3240 – 1.3190 = 0.0049999999999999?

Have you ever encountered a similar situation where your spreadsheet does not give you the result you were expecting for a seemingly simple calculation? You have checked over your calculations and still cannot figure out where it went wrong. Well the scenario you are facing may be due to floating point precision.Microsoft Office 2007 is powerfull.

Overview

Excel was designed in accordance to the IEEE Standard for Binary Floating-Point Arithmetic (IEEE 754). The standard defines how floating-point numbers are stored and calculated. The IEEE 754 standard is widely used because it allows-floating point numbers to be stored in a reasonable amount of space and calculations can occur relatively quickly.

The advantage of floating over fixed point representation is that it can support a wider range of values. For example, a fixed-point representation that has 5 decimal digits with the decimal point positioned after the third digit can represent the numbers 123.34, 12.23, 2.45, etc… whereas floating-point representation with 5 digit precision can represent 1.2345, 12345, 0.00012345, etc… Similarly, floating-point representation also allows calculations over a wide range of magnitudes while maintaining precision. For example,

All numbers expressed in floating-point format are rational numbers. Irrational numbers such as π or , or non-terminating rational numbers must be approximated. The number of digits of precision also limits the accuracy of the numbers. Excel store 15 significant digits of precision. For example, the number 1234567890123456 cannot be exactly represented if 15 digits of precision are used.IS Windows 7 Professional better than Windows 7 Home Premium ?

The IEEE 754 floating-point standard requires that numbers be stored in binary format. This means a conversion must occur before the numbers can be used in calculations. If the number can be represented exactly in floating-point format, then the conversion is exact. If not, then the conversion will result in a rounded value which will represent the original value. Numbers that appear exact in the decimal format may need to be approximated when converted to binary floating-point. For example, the fraction 1/10 can be represented in the decimal format as the rational number 0.1. However, 0.1 cannot be represented precisely in binary floating-point of finite precision. 0.1 becomes the repeating binary decimal 0.0001100110011…, where the sequence 1100 repeats infinitely. This number cannot be represented in a finite amount of space. So in Excel, it is rounded down by approximately 2.8E-17 when it is stored.QuickBooks 2010 is so Helpful!

Structure of a Floating Point Number

A floating-point number is stored in binary in three parts within a 65-bit range: the sign, the exponent, and the mantissa.

The Sign Bit

The sign stores the sign of the number (positive or negative). 0 represents a positive number while 1 represents a negative number.

The Exponent

The exponent stores the power of 2 to which the number is raised or lowered. The exponent field needs to be able to represent both positive and negative exponents. To avoid having to store negative exponents, a bias value is added to the actual exponent. The bias for double-precision numbers is 1023. For example, a stored value of 1000 indicates an exponent of 1000 – 1023, or -23.Photoshop CS4 is so magic!

The Mantissa

The mantissa stores the actual number. It is composed of an implied leading bit and the fraction bits. The storage size of the mantissa determines how close two adjacent floating point numbers can be. The mantissa and the exponent are stored in separate components. The precision of a number varies depending on the size of the mantissa. Excel can store numbers from 1.79769313486232E308 to 2.2250738585072E-308; however, it can only do so within 15 digits of precision.

Common Examples of Error Due to Floating Point Calculation

Example 1: Loss of Precision When Using Very Large Numbers

The resulting value in A3 is 1.2E+100, the same value as A1. This is because Excel stores 15 digits of precision. At least 100 digits of precision would be required to calculate the formula above.

Example 2: Loss of Precision When Using Very Small Numbers

The resulting value in cell A1 is 1.00012345678901 instead of 1.000123456789012345. This is once again is because Excel stores 15 digits of precision. At least 19 digits of precision would be required to calculate the formula above.

Example 3: Repeating Binary Numbers

Many combinations of arithmetic operations on floating-point numbers may produce results that appear to be incorrect by very small amounts. For example, the equation

=1*(.5-.4-.1)

may be evaluated to the quantity (-2.78E-17), or -0.0000000000000000278 instead of 0. This is due to the fact that the IEEE 754 standard requires numbers to be stored in binary format. As I described earlier, not all decimal numbers can be converted exactly to binary, as in the case of 0.1. The conversion caused the loss of precision.Dreamweaver CS4 is very easy-to-use!

Correcting Precision Errors

Let us go back to my very first example where my conditional formatting seemingly did not work. I know now that was due to the fact that the numbers I was using to calculate the absolute difference did not have exact binary equivalents. This resulted in 1.3240 – 1.3190 = 0.0049999999999999.

There are two basic ways in which you can compensate for some of the errors due to floating point calculation. The first method is to use the ROUND() function. The ROUND() function can be used to round the numbers to the number of decimal places that is required in your calculations. For my absolute difference column, I only require 4 decimals of precision. So I change the formula in the absolute difference column from:

=ABS(A2-B2)

To:

=ROUND(ABS(A2-B2),4)

My conditional formatting rule works as expected now since 0.0049999999999999 has been rounded to 0.0050.

The second method to prevent rounding errors from affecting your work is by using the Precision as displayed option. This option forces the value of each number in the worksheet to be the displayed value. To turn on this option, follow these steps:

1. Click Microsoft Office Button -> Excel Options -> Advanced
2. In the When calculating this workbook section, select the workbook you want, and then select the Set precision as displayed check box.
   
3. Click OK.

Going back to my absolute difference example, I set the number format to show four decimal places, and then I turn on Precision as displayed option. Since the display value is the actual value in the cell now, my conditional formatting works properly!

It is important to note that once the workbook is saved, all accuracy beyond four decimal places will be lost. This option affects the active workbook including all worksheets. You cannot undo this option and recover the lost data so save your workbook prior to enabling this option.  This option is generally not recommended unless you are sure more precision will not ever be needed for your situation.

Today’s author, Jon Adams, a Tester on the Excel team.  A copy of the spreadsheet discussed in this post can be found as an attachment at the bottom of this post.Ms office 2007 is so Charismatic.

As you are probably well aware, Excel is a very versatile tool and is often used in ways that are hard to imagine. This blog post is a follow up of Joe’s post “Unusual (but cool!) Uses of Excel” . I am going to show you how to use Excel for rapid application development using the new Office Art graphics Engine, ActiveX, Table Objects, VBA (Excel OM and ActiveX), and a touch of custom Ribbon code. Office 2007 professionaland Office 2007 ultimate are so powerfull.The complete solution description will be split up over several posts, with the following topics covered in each:

Part 1

• Building UI using Office Graphics

Part 2

• Using ActiveX Controls
• Table Manipulation
• Data Analysis

Part 3

• Context Menus
• Ribbon UI

Since I am an avid music geek and spend way too much time organizing my media library, our sample project is going to be a fully functional Media Player.

Before I get started I will answer a question that I am sure is burning in your mind. Why in the world would I write such an application in Excel? Microsoft Office 2007 is powerfull.

Flexibility: I can quickly create mash-ups of my music library data with web service data with a few more clicks and determine things like ‘listening habits’ and do pivots on things like artists playing in the area. Best of all, this can all be done outside of a development environment and without compiling.

Office Graphics: First off, as a tester, I wanted to create something that exercised as much of the Office Graphics engine that was kind of fun. Secondly, the new office graphics engine is much more professional looking and provides the end user with a lot more flexibility right out of the box. I wanted to come up with an interesting solution that showcased the power of the new graphics engine.IS Windows 7 Professional better than Windows 7 Home Premium ?

Now let’s get started. Download the file (link at the bottom of this post), play with it and get a feel for how it works.  You will have to enable macros in order for the application to work.

Building UI using Office Graphics

Excel 2007 completely changed what is possible with graphics on the grid, and I will show you some of the features you can use to make an elegant solution. First let’s break apart this UI.

Choose a background and frame your UI: The background can be as a simple as a grey box to frame some controls or in this case I decided to use a picture. If you are building a tool for your company, you can use your corporate logo as a watermark. After the size is set by your frame, you can clean things up by holding Alt and dragging resizing to line up with the grid. For a crisper UI, I like to hide the rest of the rows. QuickBooks 2010 is so Helpful!Select the first column header after your picture and press ctrl + right to select the remaining columns. Repeat for rows. This gives you something like this:

If you wish to hide the column/row headers you can go to Excel Options (Alt + T, O) and toggle “Show row and column headers”.

Let’s create the side-pane now. This simply consists of 5 rectangles grouped and a series of shapes for the playback controls.

To make this step easy I suggest using the new Selection Pane (Home, Find and Select, Selection Pane). This will help you to create deep grouped objects, and assign them useful names for later in the tutorial.

Now add a few shapes to the grid. On the insert tab, shapes dropdown, you have a choice of many shapes for our UI. I added 6 rectangles, an equal sign and a few chevron symbols. I also went ahead and typed a few simple titles in the rectangles and was able to get this.Photoshop CS4 is so magic!

If you multi-select the objects (hold down CTRL while you click the objects you want to select), you can right click and group them to make it a little easier to move them together. Now in the selection pane you can name your groups and your objects for better organization.

I renamed all of my objects in the tree view and grouped them for easier use later and it looks like this in the selection pane.Acrobat 9 is so useful!

Notice the visibility toggles. When building UI it is quite useful to be able to just hide an entire group while working on something else.

Ok, now let’s make this look a little better with some snazzy effects. Some effects work on the group as a whole, such as reflection and glow, whereas others, such as bevel and some scene3d effects, will apply to each object individually. Let’s start by selecting an object and clicking the format tab. Let’s try adding a group effect. From the Shape Effects dropdown add a glow.Dreamweaver CS4 is very easy-to-use!

The Format Tab gives us a lot of options, and maybe some canned preset will work but let’s dive deeper. Press Ctrl + 1 to bring up the advanced formatting dialog. In the 3-D format tab, Top Bevel, the first bevel creates a very button like illusion. For a more metallic look change lighting to flood and material to metal. Now onto the fill tab; If you add a gradient it will be applied to the entire group. I also like to use transparency to create a more vibrant UI.

After a bit of time these are a few options I end up with. This should give you enough of an overview on Office Graphics formatting.

Now let’s add some functionality. All of our objects link to either hyperlinks or macros. Right click one of the rectangles and clicking Hyperlink allows you to link to another sheet in the document. My Library, Playlist, Discover and Analyze UI are all on separate sheets. You can also right click each object and assign a macro (more on that later).

That’s it for now. Stay Tuned for the next blog post where we will dive into how to use ActiveX controls and Tables in excel to build the remaining components of a Media Player in Excel.