Calculate data in rows and columns in Excel without displaying a bunch of excess information

 

by Julie Duncan

Application: Microsoft Excel 2000/2002/2003/2004/2007/2010/2013

Operating Systems: Microsoft Windows

Download: http://download.elijournals.com/excel/201812/transpose.zip

When you need to combine a selection of row-based data with column-based data in Excel, you may worry that transposing your column-based data into a row will needlessly clutter your worksheet. We’ll show you that using the TRANSPOSE function behind the scenes allows you to display your results without extra data from intermediate steps.

To include row- and column-based data in a single formula, we’ll:

  • Combine TRANSPOSE and SUMPRODUCT in an array formula that pulls together data from rows and in columns.
  • Determine subtotals after the fact without losing any extra time.

Obviously, we all go to great lengths to ferret out incorrect data in our worksheets, but often we forget how much of a problem unnecessary information can cause. It may keep your boss from getting the point of all your data, which can land you in hot water. When you need to use row- and column-based data in a calculation, you can always use the TRANSPOSE feature to switch the column-based data to a row (or vice-versa), but why display your data in two places and risk creating unnecessary confusion? Instead, we’ll show you how to transpose data behind the scenes and use it to create a clear and concise calculation.

Calculate employee wages

Let’s say your boss wants you to report the amount your company paid for employee wages in February. One worksheet lists the total number of hours each employee worked, and another sheet lists the pay rate for each employee. The total hours are listed in a row, and the pay rates are listed in a column, as shown in Figure A. It’s pointless to copy the employee pay rates into a row in the worksheet, since there’s really no reason to display them again. To find the total wages paid in February, we’ll combine a SUMPRODUCT formula that includes a behind-the-scenes TRANSPOSE function.

IME18c05a1 IME18c05a2

A: Data in columns and rows doesn’t lend itself to use in a single formula.

How SUMPRODUCT works

As its name suggests, SUMPRODUCT calculates the sum of multiple products. The syntax of SUMPRODUCT is:

=SUMPRODUCT(array1,array2,array3...)

where each array argument denotes a selection of cells you multiply against each other. For example, in Figure B, cell B3 is multiplied by cell B10, and the product is added to the product of C3*C10, D3*D10, B4*B11, and so on.

IME18c05b

B: Two or more arrays combine for a SUMPRODUCT function.

Arrays and array formulas

An array is simply a group of items. An array formula in Excel is a formula that can perform more than one operation on any of the values in [...]

 
Join NowClose
Return to the ExcelSkillsSociety's homepage