Excel Connect

The Excel Connect feature allows to use Spreadsheet Server with any data table in Excel.

Following are the steps to analyze the data using Excel Connect:

1. Get the data into Excel

2. Add to Excel Connect

3. Create EXL formula

1. Get the data into Excel

   You can get the data into Excel using the following methods:

   • Import a CSV file to Excel.

   • Copy the data and paste in Excel.

   • Get External Data feature in Excel.

2. Add to Excel Connect

   To load data into memory for Excel Connect, do the following:

   1. Select Excel Connect > Add to Excel Connect. The Add Excel Connect Data window is displayed.

      

      Enter the required details:

      • Where is your data

        • Type a range of cells: Indicates the cell range where the data is available.

        • Name your range: Indicates the name of the cell range where the data is available.

      • Select an existing Excel Name: Indicates that Excel range name where the data is available.

      • Select an existing Excel Table: Indicates the Excel table where the data is available

      Note:

      • Include the column headers.
      • Excel Connect supports multiple data cache items.
      • The Name entered cannot have any spaces, however the underscore symbol is acceptable, for example, New USA Balances 1.

   2. Click Next. The Check your Column Format window appears.

   3. Make the required changes, and select Next. The Excel Cache window is displayed.

   4. Click Ok.

3. Create EXL formula to query data

To work with the data in memory for Excel Connect, do the following.

1. Select Excel Connect > Create EXL formula. The Create EXL formula dialog box is displayed.

   

2. Select or enter the required details:

   • Which Dataset: Indicates the datasets that are in Excel’s memory.

   • Which action would you like to take: Indicates the actions to be performed on the datasets.

   Following are the available options:

   Sum	Sum of selected data.
   Min	Minimum value identified in data.
   Max	Max Maximum value identified in data.
   Average	Average value of selected data.
   CSV	CSV: Comma Separated Value list of selected data. When CSV is selected, additional options are displayed. Distinct removes duplicate values. Sorted returned values will be sorted in ascending order, [0-9] [A-Z].
   Count	Count of data elements that fit the filter criteria. When Count is selected, an additional option is displayed. Distinct duplicate values are not considered in the operation.
   StDev	Standard deviation of selected data.
   Var	Variance of selected data.

   • What information would you like returned: Indicates the columns on which the selected action will be performed.
3. Select Add Filter to add a filter to the formula. Three fields are displayed.

   Enter the required information and select OK:

   • First field: Indicates all the columns recognized in your data set.

   • Second field: Indicates the logical operator to evaluate the selected column.

     Excel Connect provides the following logical operators for filter criteria:

     Operator	Description
     =	Equal To: Used with single values and only exact matches are returned.
     !=	Does Not Equal: Used with single values; records matching this criterion are excluded.
     >	Value(s) is Greater Than.
     <	Value(s) is Less Than.
     >=	Value(s) are Greater Than or Equal To.
     <=	Value(s) are Less Than or Equal To.
     LIKE	Compares numbers and text. The only available wild card is an asterisk (“*”). Note: LIKE is the only operator that can be used with a wildcard.
     IN	Creates an inclusive list of values for filtering. For example: Segment1 IN ('1205','1310') will return balances where Segment1 is either ‘1205’ or 1310’.
     NOT IN	Creates an exclusive list of values for filtering. For example: Segment1 NOT IN (‘1000’, ‘1200’) will return balances for all Segment1 values except ‘1000’ and ‘1200’.

   • Third field: Indicates the limiting value. Select a value from the Available Values list or enter the desired value.