If it is advisable conditionally cut up values into a number of columns in Microsoft Excel, think about using the IF() operate. Here is how.
Illustration: Lisa Hornung, Getty Pictures/iStockPhoto
All of us inherit Microsoft Excel sheets that do not swimsuit our working routine. If it is a easy monitoring sheet of some kind, you’ll be able to normally change issues to fit your working wants.
For example, it is not unusual to work in a sheet that follows conventional database design moderately than a monetary sheet construction. When this occurs, you’ll be able to normally revamp the construction by using a couple of expressions as an alternative of making the brand new construction from scratch. On this article, I am going to use two IF() features to separate a easy “database” sheet into a number of columns, primarily based on a worth in one other column.
Disclosure: TechRepublic could earn a fee from a few of the merchandise featured on this web page. TechRepublic and the writer weren’t compensated for this impartial evaluate.
I am utilizing (desktop) Workplace 365, however you’ll be able to work with earlier variations. To your comfort, you’ll be able to obtain the demonstration .xlsx file, however you can even work with your personal information. The expressions will work within the browser version.
LEARN MORE: Workplace 365 for enterprise
Why you may use the IF() operate
In database trend, a variety of information is outlined by kind. That manner, you solely want one discipline for the precise worth. You are able to do this with Excel, too, as proven in Determine A. This simplified desk makes use of just one worth discipline and defines every worth as both a debit or a credit score. In Excel, you may also see this as two worth columns, one for debits and one for credit, and no transaction kind discipline as a result of that is recognized by every column header.
We have to cut up the values in column D into two columns, primarily based on the transaction forms of debit and credit score.
If you happen to inherit a sheet that resembles the one in Determine A, however you desire a extra conventional sheet the place the 2 transaction sorts are in numerous columns, you are not caught. You may strive Textual content to Columns first, however on this case that function will not work as a result of it really works with a single column. You possibly can’t use it to separate values primarily based on a conditional worth in a second column. As a substitute, we’ll use two IF() features, one for debit and one for credit score.
The IF() operate makes use of the next kind:
the place situation is a worth or expression that equates to true or false and the true and false arguments determine what occurs when situation is true or false, respectively. Now, let’s put this operate to make use of.
Methods to make the debit expression
There are a number of string features for delimiting strings, however we want a conditional expression. For that cause, we’ll use two totally different IF() expressions: One which returns debit worth and one which returns credit score values.
Let’s begin with the debit values. First, create a brand new column by coming into Debit in cell E2. If you happen to’re working with a Desk object (as I’m), Excel will mechanically prolong the Desk. In E3, enter the next expression:
Copy the expression to the rest of the column. Determine B reveals the 2 debit values in column E.
We’ve got a brand new column of current debit values.
At this level, column E accommodates solely current debit values from column D. If the worth in column C equals “Debit,” the operate returns the true argument, which is the corresponding worth in column D. In any other case, the operate returns a clean string (“”). As you’ll be able to see, column E accommodates solely two values, the values the place the corresponding textual content in column C is Debit. Now, we want a column for credit.
Methods to make the credit score expression
Now that you’ve got cut up out the debit values with an IF() expression, you’ll be able to most likely do the credit score values by yourself. In F2, enter Credit score, and once more the Desk object will prolong the Desk. We’ll use the identical primary operate, however this time, we’ll use Credit score because the situation:
As you’ll be able to see in Determine C, we now have two columns: Column E shows debit values, and column F shows credit score values. However we’re not achieved but.
We have separated the debit and credit score values into two columns, E and F, respectively.
The distinction between formulation and values
Proper now, it appears to be like like we’re achieved, however we actually aren’t. The values in columns E and F are the outcomes of two IF() features, and we want literal values.The IF() features are solely a brief technique of getting the prevailing values into the construction we wish to use going ahead.
To transform the formulation, do the next:
Choose E3:F6 (the vary containing the IF() features). Within the Clipboard group (on the Residence tab), click on Copy. In the identical group, click on the Paste dropdown and select Values (the primary choice) within the Paste Values part (Determine D). After doing so, the values in columns E and F are actually literal values—the IF() features are gone.
Convert the IF() expressions to their literal values.
Methods to clear up your columns
Now that you’ve got the unique values cut up into columns, you’ll be able to clear issues up a bit. First, you may wish to format the brand new values in columns E and F. On this case, apply the Foreign money format. Second, it’s best to delete or disguise columns C and D—they’re not wanted. Determine E reveals the completed outcomes. Now, customers can enter debits and credit in columns E and F, respectively.
Format the brand new columns, and conceal the unique information.
Microsoft Weekly E-newsletter
Be your organization’s Microsoft insider by studying these Home windows and Workplace ideas, methods, and cheat sheets.
Delivered Mondays and Wednesdays
Join as we speak