To calculate a sum of squares in Excel, use SUMSQ: with your values in A2:A6, enter =SUMSQ(A2:A6). Excel squares each number in the range and adds those squares, so negative values contribute positive results.
What a sum of squares means
A sum of squares is the total you get by squaring each value in a set and then adding the squared values. Squaring means multiplying a number by itself: 3 squared is 9, and -3 squared is also 9. This operation makes every contribution nonnegative and gives larger absolute values more influence than smaller ones.
The term appears in several fields, including statistics, engineering, and data analysis. The calculation itself is simple, but the phrase can refer to different quantities depending on what values are being squared. A raw sum of squares uses the original data values. A sum of squared deviations instead squares each value’s distance from a mean or another reference point. Those are related, but they answer different questions.
In Excel, SUMSQ is the built-in function for adding the squares of numbers. It does not calculate a mean, variance, or standard deviation by itself. Those statistics may use a sum of squares as one part of their calculation, but they also involve centering the data or dividing by a count.
How to use SUMSQ
The function takes numbers, ranges, or other references as arguments. With measurements in A2:A6, the formula is =SUMSQ(A2:A6). For a small set of separate values, you can pass them as individual arguments; with values in A2, C2, and E2, use =SUMSQ(A2,C2,E2). The function accepts multiple arguments, making it possible to combine separate cells and ranges.
To enter it in Microsoft Excel for Microsoft 365 on Windows:
- Select the cell where you want the answer.
- Type
=SUMSQ(, select the cells or type their addresses, and close the parenthesis. - Press Enter. Excel displays the result in the selected cell.
For a contiguous block, use the first and last cell addresses separated by a colon, as in A2:A6. A range includes every cell between those endpoints. For separate ranges, use commas between the arguments. Check your selections carefully: if a cell is included twice through overlapping arguments, its square is added twice.
Excel for Mac uses the same function and formula syntax. In Excel for the web, you can enter the same formula in a worksheet cell. Ribbon locations are not needed for this calculation, and the result updates when included numeric values change.
Worked example with real values
Suppose A2:A6 contains 2, -3, 4, 0, and 5. With those values in A2:A6, enter =SUMSQ(A2:A6). The calculation is 2² + (-3)² + 4² + 0² + 5², which equals 4 + 9 + 16 + 0 + 25, or 54.
| Cell | Value | Value squared |
|---|---|---|
| A2 | 2 | 4 |
| A3 | -3 | 9 |
| A4 | 4 | 16 |
| A5 | 0 | 0 |
| A6 | 5 | 25 |
| Total | 54 |
This example shows why negative numbers do not reduce the total: squaring -3 produces 9. Zero contributes nothing. If you change a value in the range, Excel recalculates the result automatically, provided calculation is not set to manual.
SUMSQ versus squaring the total
A common mistake is to add the values first and square the total. That is a different operation. For the example values in A2:A6, their sum is 8, and squaring that total gives 64. With those values in A2:A6, =SUM(A2:A6)^2 returns 64, while =SUMSQ(A2:A6) returns 54.
The distinction matters because squaring a sum creates cross-products between different values. For example, (2 + 3)² equals 25, whereas 2² + 3² equals 13. Use SUMSQ when the instruction is to square each observation separately and then total the squares. Use the square of a sum only when the mathematical definition specifically calls for the total to be squared.
Another equivalent way to calculate the raw sum of squares is to multiply each value by itself and add the products. With data in A2:A6, =SUMPRODUCT(A2:A6,A2:A6) returns 54 for the worked example. This can be useful when building a larger calculation, but for a straightforward sum of squares, SUMSQ is clearer and easier to read.
Raw squares and statistical measures
In statistics, a raw sum of squares is often not the quantity people ultimately want. Variance is based on squared deviations from the mean. This removes the effect of the data’s overall location: a dataset centered near 100 can have the same spread as a dataset centered near 0, even though their raw sums of squares differ greatly.
For the five example values, the mean is 1.6. The deviations from that mean are 0.4, -4.6, 2.4, -1.6, and 3.4. Squaring and adding those deviations gives 41.2, not the raw sum of squares of 54. With the observations in A2:A6, this calculation can be made with =SUMPRODUCT((A2:A6-AVERAGE(A2:A6))^2), which returns 41.2.
Excel’s variance functions calculate the corresponding variance directly. For the five observations in A2:A6, the population variance formula =VAR.P(A2:A6) returns 8.24, because 41.2 is divided by 5. The sample variance formula =VAR.S(A2:A6) returns 10.3, because 41.2 is divided by 4. The choice depends on whether the data represents the full population of interest or a sample used to estimate a larger population; it is not a choice made by SUMSQ.
What happens with blanks, text, and errors
When a cell reference or range is supplied to SUMSQ, Excel uses numeric values in that reference and ignores blank cells and text entries. That behavior can make a result look reasonable even when some intended observations are stored as text. For example, a cell containing a number imported as text may not contribute to the total. If the result seems too small, check the source cells and confirm that intended values are actual numbers.
Errors in referenced cells can cause the formula to return an error rather than a numeric total. Find and correct the underlying error when possible instead of hiding it automatically: replacing an error with zero may change the meaning of the calculation. Also check for unintended values such as dates, which Excel stores numerically, and for formulas that return an empty text string. Review the selected range to make sure it contains precisely the observations you intend to include.
Large numbers deserve extra care. Squaring magnifies their size, so a single value much larger than the rest can dominate the result. This is mathematically expected, not necessarily an Excel fault. If the inputs are measurements with different units or scales, verify that they should be combined before interpreting the total.
Choosing the right method and checking results
Use the method that matches the quantity you need, not just the word squares in a prompt:
- Use
SUMSQfor the sum of each original value squared. - Use a squared-deviation calculation or a variance function when the question concerns spread around the mean.
- Use the square of a sum only when you need the total itself squared.
- Use
SUMPRODUCTwith matching ranges when you need to combine paired values or include the calculation as part of a more involved expression.
A quick manual check can catch mistakes: square a few sample cells separately, verify that negative inputs become positive contributions, and compare the total with a small hand calculation. For a long dataset, compare the range endpoints with the data boundaries and inspect whether numeric text or errors are present. If the worksheet has filtered or hidden rows, remember that a normal range passed to SUMSQ still includes numeric values in those rows; filtering does not automatically turn it into a visible-rows-only calculation.
Frequently Asked Questions
What is the Excel formula for sum of squares?
Use =SUMSQ(range), replacing range with the cells to include. For example, with values in B2:B10, enter =SUMSQ(B2:B10) to square each numeric value and add the squares.
Does SUMSQ include negative numbers?
Yes. A negative number is squared, so its contribution is positive. For instance, -4 contributes 16 to the sum of squares.
Does SUMSQ ignore blank cells?
Yes. Blank cells in a referenced range do not add anything. Text in referenced cells is also ignored, so check whether numbers that appear in a range are stored as numeric values.
How is SUMSQ different from SUM(A:A)^2?SUMSQ squares every number first and then adds the squares. SUM(A:A)^2 adds the numbers first and squares their combined total. These generally produce different answers.
Can I calculate sum of squares without SUMSQ?
Yes. For the same range, SUMPRODUCT(range,range) multiplies each value by its matching value and sums the products. For a simple raw sum of squares, SUMSQ usually communicates the intent more directly.
Related
- Excel Mail Merge: Create Letters, Labels, and Emails
- Excel Daylight Saving Time: How Dates and Hours Work
- Monthly Excel Budget Format: Build a Simple Template
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel File Format or File Extension Not Valid? FixesIf Excel says the file format or file extension is not valid, it usually means the workbook’s contents…
- Excel Cell Fill Pattern: How to Apply and TroubleshootAn Excel cell fill pattern is a hatch, stripe, or other repeating design placed in a cell’s background.…
- Google Sheets COUNTIF Between Two Values: A Clear GuideTo count numbers between two limits in Google Sheets, use COUNTIFS with the same range twice: if the…
- Google Sheets: Duplicate a Data Validation RuleTo duplicate a data validation rule in Google Sheets, copy a cell that already has the rule, select…
- Best Way to Set Out of Office in Outlook AutomaticallyThe best way to set out of office in Outlook is to use Automatic Replies on your Microsoft…
- Data Validation in Excel: Lists, Rules, and FixesIn Excel for Microsoft 365 on Windows, open the Data tab, select the cells to control, then choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.