Excel 2010 Spreadsheet: Removing Automatically Produced Email Address Box Pointing to SUM(Name) Cell
In Excel 2010, when you use the SUM function with a range of cells that includes a column with header "Name" or "E-mail", Excel may automatically add a drop-down list email address box linked to the SUM(Name) or SUM(E-mail) cell.
Why does Excel produce an email address box?
Excel is designed to make it easy for users to perform various tasks, and one of those tasks is sending an email to a list of recipients. When Excel recognizes a column header named "Name" or "E-mail", it assumes that the data in that column consists of email addresses. As a result, Excel may display an email address box to allow the user to quickly send an email to the listed recipients.
How to remove the automatically produced email address box?
To remove the email address box, follow these steps:
- Click on the email address box.
- Click on the
Datatab in the Excel ribbon. - In the
Data Toolsgroup, click onData Validation. - In the
Data Validationdialog box, under theSettingstab, set theAllowoption toAny valueorWhole number. - Click
OKto close theData Validationdialog box. - Click on the cell that contained the email address box, and press the
Deletekey on your keyboard.
Alternative method: removing the email address box using VBA
If you prefer to remove the email address box using Visual Basic for Applications (VBA), you can use the following code:
Sub RemoveEmailBox()
Dim rng As Range
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1") 'change "Sheet1" to the name of your sheet
For Each rng In ws.UsedRange
If TypeName(rng.Validation.Formula1) = "String" Then
If InStr(1, rng.Validation.Formula1, "=SUM(", vbTextCompare) > 0 Then
rng.Validation.Delete
End If
End If
Next rng
End Sub
This VBA code loops through each cell in the worksheet's used range, checks for the existence of an email address box linked to a SUM function, and deletes the email address box if found.
How to prevent Excel from producing the email address box?
To prevent Excel from producing the email address box, you can rename the column header or change the formula to not include the "Name" or "E-mail" column. For example, instead of using SUM(Name), you can use SUM(Column1) or any other column name that doesn't correspond to a column header that Excel may recognize as email addresses.
- Excel 2010 may produce an email address box for the
SUM(Name)orSUM(E-mail)cell when a column header is named "Name" or "E-mail". - To remove the email address box, either change the validation settings or delete the email address box manually.
- To prevent the email address box from appearing, rename the column header or change the formula to not include the "Name" or "E-mail" column.