If you need to conditionally split values into multiple columns in Microsoft Excel, consider using the IF() function. Here’s how.
We all inherit Microsoft Excel sheets that don’t follow our work routine. If it’s a simple tracing sheet of some sort, you can usually change things up to suit your business needs.
For example, it is not uncommon to work in a sheet that follows the traditional database design rather than the structure of a security. When this happens, you can usually regenerate the structure by using some expressions rather than creating the new structure from scratch. In this article, I will use two IF() functions to split a simple ‘database’ sheet into multiple columns, based on a value in another column.
disclosure: TechRepublic may earn commission from some of the products featured on this page. TechRepublic and the author are not compensated for this independent review.
I’m using Office 365 (for desktop), but you can work with earlier versions. For your convenience, you can download the .xlsx demo file, but you can also work with your own data. Expressions will work in the browser version.
Learn more: Office 365 for business
Why would you use the IF() function
In database style, a lot of data is defined by type. This way, you only need one field for the actual value. You can do this with Excel as well, as shown in Figure A. This simplified table uses only one value field and identifies each value as either a debit or credit. In Excel, you might also see this as two value columns, one for debits and one for credits, and there is no field for transaction type because it is specified by the header of each column.
If you inherit a sheet similar to the one in Figure A, but you want a more traditional sheet where the two operands are in different columns, you’re not stuck. You can try converting text to columns first, but in this case this feature will not work because it works with a single column. You cannot use it to split values based on a conditional value in a second column. Instead, we’ll use two IF() functions, one for debit and one for credit.
The IF() function uses the following form:
if (the conditionAndcorrectAndFalse)
where the condition is a value or expression equal to true or false and correct And False Arguments define what happens when the condition It is true or false, respectively. Now, let’s use this function.
How does a debit expression work?
There are many string functions to define strings, but we need a conditional expression. For this reason, we will use two different IF() expressions: one that returns the debit value and one that returns the credit values.
Let’s start with the discount values. First, create a new column by entering the flow in cell E2. If you’re working with a Table object (like me), Excel will automatically expand the table. At E3, enter the following expression:
=IF(C3 = “Debit”, D3, “”)
Copy the expression to the rest of the column. Figure B Displays the two discount values in column E.
At this point, column E contains only the current discount values from column D. If the value in column C is ‘discount’, the function returns correct argument, which is the corresponding value in column D. Otherwise, the function returns an empty string (“”). As you can see, column E contains only two values, the values for which the corresponding text in column C is the discount. Now, we need a column for credits.
How does a credit expression work?
Now that you’ve divided the debit values using an IF() expression, you can probably do the credit values yourself. In F2, enter credit, and again the table object will extend the table. We’ll use the same basic functionality, but this time, we’ll use credit as a condition:
=IF(C3 = “Credit”, D3, “”)
as you see in shape c, We now have two columns: column E showing discount values, and column F showing credit values. But we are not finished yet.
The difference between formulas and values
For now, it looks like we’re done, but we’re not. The values in columns E and F are the results of two IF() functions, and we need literals.
To convert formulas, do the following:
- Select E3: F6 (the range containing the IF() functions).
- In the Clipboard group (on the Home tab), click Copy.
- In the same group, click on the Paste drop-down menu and choose Values (the first option) in the Paste Values section (figure d). After doing this, the values in columns E and F became literals – the IF() functions disappeared.
How to clean your columns
Now that the original values are split into columns, you can clean things up a bit. First, you will need to format the new values in columns E and F. In this case, apply the currency format. Second, columns C and D should be deleted or hidden – they are no longer needed. Figure E Shows the final results. Now, users can enter debit and credit operations in columns E and F respectively.