How to Process Data with Units Using Functions in Excel

Date:2019-9-17 Author:Cordelia

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.

Use SUM function

And it also shows “#VALUE!” when I input the formula =A1+A2+A3+A4+A5 manually.

Input formula 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.

Use proper formula to process data with units

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.

Press Ctrl+H

Input the specific unit you don’t want in Find what, and type nothing in Replace with. Then click Replace All.

Find and Replace

The units have been removed, you can apply any function you want to these data now.

Remove the units

The second method is to split the content in the cell.

Select the cells you want to split and go to Data tab.

Select the cells and go to Data tab

Click Text to Columns in Data Tools section.

Click Text to Columns

Select Delimited and hit Next.

Select Delimited

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.

Select Others and input the unit in the textbox

Then just click NextFinish to implement the setting.

Finish the setting

Now the units in selected column have been removed successfully.

The cells have been removed

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.

Click Format Cells... in the right-click 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.

Input the format you want in Custom

The units added in this way won’t affect the calculations in Excel, you can still use functions on these data.

The correctly added units won't affect the calculation
Copyright Statement: Regarding all of the posts by this website, any copy or use shall get the written permission or authorization from Myofficetricks.

Leave a Reply

Your email address will not be published. Required fields are marked *