Take a Guess to Win Windows 10 Pro Key!
previous arrow
next arrow
Slider

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.

How to Process Data with Units Using Functions in Excel

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

How to Process Data with Units Using Functions in Excel

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.

How to Process Data with Units Using Functions in Excel

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.

How to Process Data with Units Using Functions in Excel

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

How to Process Data with Units Using Functions in Excel

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

How to Process Data with Units Using Functions in Excel

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

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

How to Process Data with Units Using Functions in Excel

Click Text to Columns in Data Tools section.

How to Process Data with Units Using Functions in Excel

Select Delimited and hit Next.

How to Process Data with Units Using Functions in Excel

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.

How to Process Data with Units Using Functions in Excel

Then just click NextFinish to implement the setting.

How to Process Data with Units Using Functions in Excel

Now the units in selected column have been removed successfully.

How to Process Data with Units Using Functions in Excel

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.

How to Process Data with Units Using Functions in Excel

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.

How to Process Data with Units Using Functions in Excel

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

How to Process Data with Units Using Functions in Excel
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 *