Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am using Excel 2010 and retrieving data from SQL analysis service.

When I refresh the data from Excel, the rows all refresh as they are bound to an external datasource. I am adding a separate column with a formula to sum the totals.

With an increment or decrement of these rows, the alignment of custom columns goes out.

How can I resolve this problem that summing of values become dynamic with adding and removal of rows?

share|improve this question
    
For this one we need to see your formula for the sum. –  Lance Roberts Jun 30 '11 at 5:30
    
=sum(B1:B7) , in my case the B7 value is dynamic –  Gainster Jun 30 '11 at 5:31
    
OK, I guess I'm misunderstanding precisely what's going on. Good Luck. –  Lance Roberts Jun 30 '11 at 5:45
    
What is an increment or decrement of the rows? –  soandos Jun 30 '11 at 5:56

2 Answers 2

I think I understand you. You are trying to calculate the total of a column but the number of rows may vary.

Firstly, make sure your data is in a proper Excel Table. When you click on a cell in the table, you should get "Table Tools" appears a section on the Ribbon bar.

Add a Total row to the table (Right click somewhere on the table then select Table -> Totals Row). This row will automatically move up/down as the number of rows in your data source changes.

Then add an autocalculated total (you can pick sum, count, average, whatever you need) to the relevant column.

Even though the cell containing the total might change rows, you can always reference it. For example, if your table is called products and your column containing the total is called quantity, you can refer to the total cell by reference as products[[#Totals],[quantity]]

share|improve this answer

=sum(B:B)

(I wanted to just submit the formula to be totally minimalist, but apparently I need more characters, so I added this sentence)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.