Some people tend to input the data with units into Excel spreadsheet considering it will be more clear and intuitive. But unfortunately, the data with manually entered units can’t be calculated correctly with Excel functions. It means you can only do the summation or subtraction manually as well. To avoid this situation and use functions on these data with units, there are several tips you should know.
1. How to Use Functions to Sum the Data with Units
In Excel, there are lots of useful functions for all kinds of situations you may encounter. It’s no exception in this case.
For the data with units, you can also sum them with Excel function, but with a proper one.
Look at the GIF below, the SUM function doesn’t work out for the data with units.
And it also shows “#VALUE!” when I input the formula =A1+A2+A3+A4+A5 manually.
Are all the functions invalid for data with units? It’s not exactly so.
You can try this one: =SUMPRODUCT(1*SUBSTITUTE(A1:A5, “$”,””))&”$”
In this formula, A1:A5 refers to the range of cells you want to sum, $ refers to the units in these cell. You can change them according to your situation.
Type or copy it in the spreadsheet, you can see it works well in my case.
Please note you can not directly apply it to other cells with different units.
2. How to Get Rid of Unwanted Units
If you think the formula is too difficult to remember and use, you can also choose to remove these no longer wanted units in Excel.
To achieve this goal, there are 2 commonly used methods.
The first one is using Find and Replace to remove certain symbol.
Click [Ctrl+H] in the Excel spreadsheet.
Input the specific unit you don’t want in Find what, and type nothing in Replace with. Then click Replace All.
The units have been removed, you can apply any function you want to these data now.
The second method is to split the content in the cell.
Select the cells you want to split and go to Data tab.
Click Text to Columns in Data Tools section.
Select Delimited and hit Next.
Click Other in Delimiters and input the units you want to get rid of, and check Treat consecutive delimiters as one on the right, otherwise the data will be divided into two columns and overwrite the existing column beside it.
Then just click Next – Finish to implement the setting.
Now the units in selected column have been removed successfully.
3. How to Add Units in Excel Correctly
In addition to trying hard processing the data after manually entering units in Excel, you can enter the units correctly in advance to avoid all these troubles.
You don’t need to enter the unit after the value in each cell, actually. Just select the cells you want to add units, right-click them and choose Format Cells… in the menu.
Select Custom in Category and type the format in the textbox. Here I typed 0$, which refers to the regular numbers followed by $ symbol. Click OK to confirm the setting at last.
The units added in this way won’t affect the calculations in Excel, you can still use functions on these data.