Friday, October 2, 2009

New features in Office 2010

Ribbon


Office 2010 the Ribbon to all the Office programs, including Outlook and OneNote. New thing is, now you have control over your Ribbon, what items appear on its tabs and you can even add tabs of your own. Put your favorite commands on there.



















Outlook improvement

  • New Ignore button : Makes it easy to bypass entire conversation threads.
  • New Quick Steps section : Lets you perform multi-step tasks with one click.
  • Actions: You can perform more actions on a conversational thread, including clean up.

Easier screen shots

Inserting screen shots into Word is easier than ever.

If you’re a tech writer, you’ll appreciate a new feature in Word 2010 — the ability to capture screen shots from inside the application and paste them into the document, all in a couple of clicks. The Screen shot button has been added to the Insert tab of the Ribbon.

When you click the Screen shot button, you’ll see the available screen shots, and you can click on the one you want to insert into the document. After you insert a screen shot, the picture tools will automatically appear to allow you to perform photo editing.

Excel sparklines and slicers

The most notable additions to Excel 2010 are two new features called sparklines and slicers. Sparklines are tiny charts that fit into a cell, as shown in Figure.

Sparklines are charts that fit into a cell on an Excel spreadsheet.

The sparklines shown in the figure use the line format, but you can also create column or win/loss sparklines. You can edit the design of the sparklines.

You can edit the design of your sparklines.

Slicers are objects you can use to filter the data in pivot tables, which you can move around or resize on the screen. When data in the pivot table changes, the slicer is automatically updated. Both sparklines and slicers are created via the Insert tab on the Ribbon.

You create sparklines and slicers via the Insert tab on the Ribbon.

To see a demo of the new Excel features, check out the video at http://www.microsoft.com/office/2010


Simultaneous editing

Here is another one of my favorite. Often we leave a document that we are working on one computer, and then need to open and work on it from a different computer. we get the familiar “file in use” dialog box that gives us the option to open a read-only copy, create a local copy to merge later, or receive notification when the original is available. Office 2010 does away with that annoyance.

Now we can pick up where we left off, or two people can edit a document simultaneously. A notification in the status bar tells you who else is currently editing the document, and where they’re making changes. Very cool!

Word can also cache shared documents so we can edit them when we are offline, and any changes we make will automatically be synchronized with the original on the server when we come back online. Now we don’t have to remember to merge document when we get back.

Setting up the web query

Excel provides a very useful option to gather data from websites, called web queries. These have been introduced with Excel 97 and have been further enhanced in the versions released after Excel 97. This article shows how you can setup a web query so that you can make the worksheet dynamically update based on values in so called parameter cells.

Setting up a web query is as simple as clicking the Data tab and then clicking the “From Web button on the Get External Data tab.

image

You’ll get this screen:

image

Enter http://moneycentral.msn.com/companyreport?symbol=MSFT and click the Go Button.

Don’t do anything yet, first click the “Options” button and set the webquery to return full html formatting results (if so desired):

image

After changing these settings, select the part you want to be imported into your sheet by clicking the appropriate black-on-yellow arrow:

image

Now we’re ready to click “Import”. If the page takes time to load, you’ll see a progress screen:

image

Next Excel asks where to put the results; put them in cell A3 so we have room above the table:

image

If you want an interactive result in your sheet, enabling you to enter new search criteria in a cell, you have to go through a little VBA. Don’t worry, this is easy.

These steps are needed:

  1. Click any cell in the new table.
  2. Open the Visual Basic Editor by hitting the Alt+F11 key.
  3. Hit control+g to open the immediate window.
  4. Now type the following line:

    activecell.QueryTable.Connection="URL;http://moneycentral.msn.com/companyreport?symbol=[""Ticker""]"
  5. Click Enter to confirm.
  6. Close the Visual Basic Editor.

You have now made part of the url work as a parameter, by replacing that part of the url with some text between quotes and square brackets (we used a bit of VBA because with the MSN site you can’t do this through the user interface). The string you entered will be used as both the name of the parameter and the prompt.

Click the refresh all button on the data tab of the ribbon, in the connections group. Excel will interpret the part between the square brackets as a parameter and prompt you for a value:

image

Excel detects we have a parameter and now asks what value you want.

Thursday, October 1, 2009

Using the New Microsoft Office File Format

Starting with the 1997 release, all Microsoft Office programs have used a binary file format that computers (but not humans) can read. Excel 2007, Microsoft Office Word 2007, and Microsoft Office PowerPoint® 2007 have a new and improved file format that, in addition to being somewhat readable, creates much smaller files than the older binary format.

The new Microsoft Office Open XML Formats combine the XML and the Zip file compression format to create robust files that (on average) are about half the size of similar Excel 972003 files. You can open and save Excel 972003 files in Excel 2007, of course. If you want to open Excel 2007 files in Excel 2000, Excel 2002, or Excel 2003, you can install the Microsoft Office Compatibility Pack for Office Word 2007, Excel 2007, and Office PowerPoint 2007 file formats from this book's companion CD.

Besides smaller file sizes, the 2007 Microsoft Office system file formats offer several other advantages:

  • Improved interoperability. Because the new file formats use XML as their base, it is much easier for organizations to share and exchange data between the Microsoft Office system programs and other applications. The older binary file format was difficult to read and wasn't standards-based.
  • Enhanced customization. The letter "X" in XML stands for "extensible," which means that information professionals and developers can create custom document structures, or schema, that meet their organization's needs.
  • Improved automation. The Excel 2007 file format is based on open standards, which means that any program written to process data based on those standards will work with Excel 2007. In other words, you don't need to write special routines or use another program in the Microsoft Office system to handle your Excel 2007 data programmatically.
  • Compartmentalizing information. The new Microsoft Office system file format separates document data, macro code, and header information into separate containers, which Excel 2007 then combines into the file you see when you open your workbook. Separating macro code (automated program instructions) from your worksheet data improves security by identifying that a workbook contains a macro and enables you to prevent Excel 2007 from executing code that could harm your computer or steal valuable personal or business information.

Some capabilities found in Excel 2007

The table below summarizes the expanded data storage and other capabilities found in Excel 2007.

Limit

Excel 2003

Excel 2007

Columns in a worksheet

256

16,384

Rows in a worksheet

65,536

1,048,576

Number of different colors allowed in a workbook

56

4.3 billion

Number of conditional format conditions applied to a cell

3

Limited by available memory

Number of sorting levels of a range or table

3

64

Number of items displayed in an AutoFilter list

1,024

32,768

Total number of characters displayed in a cell

1,024

32,768

Total number of characters per cell that Excel can print

1,024

32,768

Total number of unique cell styles in a workbook

4,000

65,536

Maximum length of a formula, in characters

1,024

8,192

Number of nested levels allowed in a formula

7

64

Maximum number of arguments in a formula

30

255

Number of characters that can be stored and displayed in a cell with a text format

255

32,768

Number of columns allowed in a PivotTable

255

16,384

Number of fields displayed in the PivotTable Field List task pane

255

16,384