Excel: How to Add Columns to Total Sum of Selected Cells (With Examples + VBA Code)

Adding columns to sum selected cells in Excel is key. It's vital for managing budgets, tracking sales, or making reports. Knowing how to sum data well boosts productivity and accuracy.

In this blog, we'll show you how to add columns in Excel to total the sum of selected cells. We'll cover manual methods, formulas, and VBA code for automation. Let's get started.

📌 Why Add Columns to Sum Selected Cells in Excel?

Adding columns for summing allows users to:

• Create dynamic reports
• Analyze large datasets efficiently
• Automate repetitive calculations
• Save time and reduce manual errors

✅ Method 1: Use the SUM Formula

👉 Example 1: Add Values in a Column (Manually Selected)

Let's say you have numbers in cells A2 to A10. You want to find the total.

Formula:

=SUM(A2:A10)

This formula adds all values from A2 to A10. Just place it in an empty cell to see the total.

👉 Example 2: Add Multiple Columns

To sum values from multiple columns (e.g., A2 to C10):

Formula:

=SUM(A2:C10)

This formula totals all values from A2 to C10 across the selected columns.

✅ Method 2: AutoSum Button

1. Click on the first empty cell below a column of numbers (e.g., A11).
2. Go to the Home tab > Editing group > AutoSum (Σ).
3. Press Enter.

Excel will automatically insert the

=SUM(A2:A10)

formula for you.

✅ Method 3: Sum Only Selected Cells (Non-Contiguous)

Sometimes, you only want to sum certain cells, not the whole column or row.

👉 Example:

Sum cells A2, A4, and A6 only:

Formula:

=SUM(A2, A4, A6)

You can manually select these cells while typing or click them while holding Ctrl.

🧠 Pro Tip: Use Table References for Dynamic Sums

Convert your range into a table (Ctrl + T). Then use structured references:

=SUM(Table1[Sales])

This ensures your formula updates automatically when new data is added.

🛠️ Method 4: VBA Code to Sum Selected Columns

VBA (Visual Basic for Applications) is great for large datasets or automating tasks.

📌 VBA Example 1: Sum All Values in Column A

Sub SumColumnA()
    Dim total As Double
    total = Application.WorksheetFunction.Sum(Range("A:A"))
    MsgBox "Total of Column A is: " & total
End Sub

📌 VBA Example 2: Sum Selected Cells Only

Sub SumSelectedCells()
    Dim cell As Range
    Dim total As Double

    For Each cell In Selection
        If IsNumeric(cell.Value) Then
            total = total + cell.Value
        End If
    Next cell

    MsgBox "The sum of selected cells is: " & total
End Sub

To use:

1. Press Alt + F11 to open the VBA Editor.
2. Insert a new module.
3. Paste the code.
4. Run using F5 or assign to a button.

🧮 Bonus: VBA to Sum Each Column and Output Totals

Sub SumEachColumn()
    Dim lastRow As Long, lastCol As Long
    Dim col As Integer
    Dim ws As Worksheet
    Set ws = ActiveSheet

    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

    For col = 1 To lastCol
        ws.Cells(lastRow + 1, col).Formula = "=SUM(" & ws.Cells(2, col).Address & ":" & ws.Cells(lastRow, col).Address & ")"
    Next col

    MsgBox "Sums added below each column."
End Sub

 

This macro will add a total at the bottom of each column with data.

📝 Final Thoughts

Summing columns and selected cells in Excel can be simple or powerful, depending on your approach. Whether you're manually adding values, using smart formulas, or writing VBA scripts, Excel offers flexible tools to manage your data.

Want to automate your Excel workflow even more? Explore our [VBA automation tutorials] or sign up for our free Excel cheat sheet!