function to compare two columns in excel

Double-click East to insert it into the formula. This brings up East and West in the Items list box. In the Fields list box, Region is already selected. You need to click in the box and backspace to remove the zero. The Formula box always starts out with a formula of = 0. Since the column is relatively narrow, I went with “E-W” as the field name instead of “East minus West.” In the Insert Calculated Item dialog, give the new item a name. Had you chosen a field that contains a product name, you would be inserting a new calculated item along the product dimension.įrom the PivotTable Analyze tab, open the drop-down menu for Fields, Items, & Sets. The selected cell tells Excel that your new calculated item belongs to the Region dimension. Before starting to add a calculated item, you should choose either the East or West cell in the pivot table column headings. This will add columns for both East and West to the report.

Because East and West are two items within the single Region column, you would want to create a new calculated item to do any comparisons between the two.Ĭreate a pivot table with Product in the Rows area, Region in the Columns area, and Revenue in the Values area. Calculated items are used when the calculations are within a single pivot table field. Calculated fields are used when performing calculations involving other fields in the pivot table. Pivot tables offer two similar-sounding features: calculated fields and calculated items. The question involves finding out the differences by region for each product. The spreadsheet contains columns for Product, Region, Sales Rep, and Revenue. The example involves data on sales by region.

Function to compare two columns in excel