Excel Magic Trick 1082


Power Query UnPivot Feature to Create Proper Data Set For Sales & Grade Data

DESCRIPTION

See how to add every other column using 1 or 4 formulas:
(00:08 min) Manual method using SUM. See how to highlight non-contiguous columns using a comma or the Ctrl key. This formula will work if you insert new columns before data set. This formula will work if there are text values in data set. Formula 1: =SUM(A2:A12,C2:C12,E2:E12,G2:G12)
(00:35 min) Formula to automatically add every other column using SUMPRODUCT, COLUMN, COLUMN with array operation, MOD functions and the math operator: *. This formula will work if you insert new columns before data set. This formula will NOT work if there are text values in data set. Formula 2: =SUMPRODUCT(A2:G12*MOD(COLUMN(A2:G2)-COL­UMN(A2)+1,2))
(03:19 min) Formula to automatically add every other column using SUMPRODUCT, COLUMN, MOD functions and the math operator: *. This formula will NOT work if you insert new columns before data set. This formula will NOT work if there are text values in data set. Formula 3: =SUMPRODUCT(A2:G12*MOD(COLUMN(A2:G2),2))
(04:08 min) Formula to automatically add every other column using SUMPRODUCT, COLUMN, COLUMN with array operation, MOD, ROW, Array operation with exponent zero, and MMULT function. This formula will work if you insert new columns before data set. This formula will work if there are text values in data set. Formula 4: =SUMPRODUCT(A2:G12,MMULT(ROW(A2:A12)^0,M­OD(COLUMN(A2:G2)-COLUMN(A2)+1,2)))

WORKBOOKS