Tips to Automate Excel with Certify

Usage of ZKeys:

1. Enabling filters using Zkeys

• Launch any csv formatted file using Certify action Open Workbook.Columns appear with no filter option.

• Column filters can be enabled with %HSF ZKeys. Below are the Certify steps for using ZKeys.

Position at 1^st cell in the excel, Row 1 and Column A using Select Cell action

Select ZKeys action and input %HSF in Keys textbox of Parameters section

• Execute above mentioned 2 steps. Column filters are applied as shown below.

2. Applying Excel formula: Consider Addition of all values in column B of below excel sheet.

Below are the Certify steps to use Excel formula:

• Specify the range for which addition is required. In this case, entire column is considered. Starting row =3 and Ending row = 256. So formula to input would be =SUM(B3:B256). Here B is the column name. Store this formula in a variable, here it is Excel ADD formula.

• Input the stored variable value in any of the empty cells preferably empty cell of column B which is at row 257.

• Send Enter Key after inputting the formula.

• SUM result is displayed. Value can be stored into any of the variables.

3. Creation of Pivot table to find unique values in a column

Below are the Certify steps to create a pivot table:

• Select range for which pivot table is to be created.

• Select ZKeys action and input %NV into ZKeys textbox.

• “Create PivotTable popup appears on excel sheet.

• Clicking on OK button creates a new sheet in excel showing unique values among the selected range.