Another Microsoft Excel Trick

I received an email query from a reader who wanted to know how to get Microsoft Excel to perform a specific task. He'd exported a report from QuickBooks that had subtotals for various categories in a single column. He wanted to know whether there's an easy way to get the total of all the individual items in the same column, instead of creating a second column, copying the subtotals, and then adding the amounts in the second column.

There sure is, and I use this trick when I have many columns of data and I don't want to create new columns and copy amounts in order to sum the subtotals.

For the columns that have subtotals, I go to the row below the last data row and enter the following formula: =SUM(Xy:Xz)/2, where X is the column, y is the first row in the calculation and z is the last row in the calculation.

Can you figure out why this works?

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.