Let's Talk about .. Excel 2007-Freeze the panes

You may got the problem to read when your spreadsheet is too large. You will not be able to know that what the column is. The way to solve this problem is freezing the panes.

The instruction to freeze the panes

• If you would like to freeze the selected panes,
  Go to View->Freeze Panes-> Freeze Panes


• If you wolld like to freeze the top row,
  Go to View->Freeze Panes-> Freeze Top Row


• If you would like to freeze the first column,
  Go to View->Freeze Panes-> Freeze First Column

• For the example, I selected Freeze Top Row. The result is here. The Top Row is freezed so I can see what each column is.

Let's Talk about .. Excel 2007-Simple Drop Down List

This is the instruction to create the Simple Drop Down List.

• Go to Data -> Data Validation -> Data Validation.


• Inside the Data Validation, in Settings tab, set allow to List.


• Specify the source. For example, I have these list.

• Click OK.


• Then, I got the Drop Down List.

Let's Talk about .. Excel 2007-Pivot Table

Pivot Table is a powerful summation tool that is used to analyse. It's very essential thing but easy to create and use. I will show you how easy to create it is

1. I create the excel table as the example as shown in this figure.


2. Then, I select the cell that I want to create the Pivot Table for example, G2.


3. Go to Insert tab->PivotTable->PivotTable.


4. The CreatePivotTable window appears, then you select all the cells of your information that you want to create the Pivot Table. Then, the Range shows in the textbox. Moreover, you can select the Table if you're already name your table.
   
5. Click OK.
   
6. The PivotTable Field List appears in the right zone. You can drag the fields into the below bottom which are Report Filter, Column Labels, Row Labels, Values. You can manage your Pivot Table to make it useful for your analysis. I managed my Pivot Table as shown in the following figure.


7. Then, I got my Pivot Table.

Let's Talk about .. Excel 2007-Synchronizing Tables with MOSS Lists

If you've ever used Excel 2003, you can connect to MOSS, both to read and write the list. But for Excel 2007 you may found that it can not do it directly. One of the methods is to do via Access 2007. I think because Microsoft recommended to use Access as the platform to connect to SharePoint. So you can transfer your Excel table to Access first, then use Access to update the list of SharePoint.

Anyway, there is the add-in from Microsoft to make your Excel 2007 be able to synchronize with MOSS. You can download the add-in from Microsoft at this link.

http://www.microsoft.com/downloads/details.aspx?familyid=25836e52-1892-4e17-ac08-5df13cfc5295&displaylang=en

The instruction to Add-in:-

1. Go to Excel Options.
   
2. Add-Ins-> Go.
   
3. In the Add-Ins window, click Browse.
   
4. Select your add-in.
5. Click OK.

After you added in, the new Publish button appears in menu of Excel 2007. Then, you can synchronize your table.

Let's Talk about .. MOSS2007-Excel Web Access

Today I participated the training course of How to apply MOSS2007 Excel Web Access. The instructor teached about the basic concepts of the important features of Excel Web Access in MOSS2007.

Interaction between Excel Service & Excel
- View the latest formular results.
- Refresh the live data from an extended data sources such as OLAP cube.
- Navigate the different worksheets, parts of the worksheets, or selected named items in workbook such as chart or excel table.
- Sort and filter data.
- Expand or collage levels of data and use a report filtering a Pivot Table report.

Security
- We don't have to worry about the security coz it leverages the security infrastructure provided by SharePoint.
- We can scale up add more CPU

Excel Service Config
- Excel Calculation Service

• Open SharePoint 3.0 Central admin site
• Click on Operation Tab
• Click on Services on the Server link from the topology and Service
  

Trusting the Location
- Workbooks must be placed in trusted location.
- Call to open fails if it comes from untrusted site.
- Trust a location using SharePoint 3.0 Central admin or Script.

Pivot Table (Pivot = round; rotate)
- Pivot Table reports are tables that organized and summarize info for easier analysis
- Its grapical representations of the same data
- PivotTable and PivotChart are interactive -> trend & relationships

Name Item
- Normally we user the name of the coumn as the name of variable we call, but when the column is changed, the formular got problems.
- In office 2007, we can refer it by the name instead of the column.

Parameterized Excel
- Interactivity

• we can define cell in excel as parameterized .
• we can work with the parameter task pane (the right-hand-side windows in OfficeEx. Textbox, list box ) -- Value of drop down in task pane come from SQL server. User defines, then export the parameter via the parameter task pane.
• we can see the changes in the data / reports

- Visibility we can show / hide sheet and/or part of data

Let's Talk about .. MOSS2007-Excel Services

My division executive suggessed the member of the division to read the MSDN article about the Excel Services. I think MSDN article is very useful when we want to know about the features of Microsoft's products.

The Excel Services is one of the new technology in MOSS 2007. The users can load, calculate, and display the Excel worksheets on SharePoint Server 2007. The important thing is the ability that user can the data to display, and maintain a single version of Excel worksheet without writing custom code.

Two primary interfaces for Excel Services are available:-
(1) Excel Web Access Web Part: you can view and interact with a live worksheet via browser.
(2) Excel Web Services: for programmatic access.

The user can view live, interact worksheets and save it within portal sites. It's able to analyze PivotTable reports and charts by browser.

The selected cells in worksheets can be edited by making the parameters to them. When saving the items that users choose to make viewable, they will appear in the Parameters pane in Excel Web Access.

Anyway, the users have to create a workbook by MS Office Excel 2007 because creating function is not enable in Excel Services.