4.5.2.4 Lock Filter with Analysis

Lock-Filter

Summary

Generally, when graphing or analysis is done on data with a filter, changes in the filter condition of the source worksheet trigger recalculation for the analysis results and graph.

However for Copy Columns and Pivot Table, in the Recalculate lock icon context menu, there are three worksheet filter options. These are used to control whether the results will be affected by further filter changes.

Minimum Origin Version Required: Origin 9.1 SR0

What you will learn

• How to copy columns to a new sheet or new workbook
• How to lock a Data Filter condition on copied columns
• How to push back the Data Filter condition to the source worksheet

Steps

1. Import the Origin sample data automobile.dat which is located in <Origin Program Folder> \Samples\Statistics.
2. Highlight the Make column then from the menu choose Column: Filter: Add or Remove Filter or select the Filter button from the Worksheet toolbar.
   
3. To show only the Honda data, click on the filter icon that appears on the top left of the column's first cell. Choose Select All to clear all the options. Select Honda. Click the OK button.
   
4. To copy columns to a different sheet, hold down the CTRL key and select the Make, Power and Engine Displacement columns. Next, right-click and select the Copy Columns to... in the context menu.
5. In the colcopy dialog, select Auto in the Recalculate drop-down list. Make sure the Destination Columns is set to a new worksheet in the same workbook (see Tip, below). A new sheet with the copied columns will be created.
   
6. Click and drag the sheet tab of the copied worksheet to blank Origin workspace. This will create a new workbook to hold the copied data so the copied data and original data can now be compared side by side.
7. Click on the green Recalculate lock icon on the top left of the first cells' in the columns of this copied sheet. Select Worksheet Filters: Lock in the context menu. The filter conditions will be saved into the operation.
   
8. In the source worksheet, add Data Filter to Power column, and click on the filter icon to select Greater Than.... In the Simple Number Filter dialog, set Value to 100, and click OK. The filter will show only the Power data above 100 in the original workbook but since the filter was locked in the copied workbook the data in it will no longer update.
9. The filter conditions on the original workbook were changed and in order to set them back to the way they are in the copied workbook, use Push Back. In the copied sheet, click on the green lock in column(A) and select Worksheet Filters: Push Back from the context menu. This will result in the data filter condition in the original workbook being pushed back so that the filter condition set on Power no longer applies.

The lock filters options are only available in the new sheet created by column copy. If you copy columns to the same worksheet, the options will not show in the Recalculate lock icon's context menu.