Excel Tips

Tips from the Finance Trenches: 2 Key Excel Functions for Organizing Data

Don’t get us wrong—data is a beautiful thing. But when you have lots of it, displayed in a format that is far from ideal, it presents challenges for those who are trying to view and manipulate that data. So how do you turn large datasets into useful, easy-to-read formats? Here are two commonly used functions that will help you clean up your (data) act.

Function 1: Combine Cells Using the “&” Key

Most databases split out data for accuracy’s sake—but this doesn’t always translate to the most user-friendly format. For example, instead of having data that shows a person’s full name, a database might display first and last name in separate columns. Similarly, an address may be separated by city, state and zip code. In Excel, you can combine cells containing different data elements into one cell by using the “&” sign in your function, allowing you to freely compose your text into the desired format.

How to Do It:

The following steps show how to combine first and last names into full names in a single column, as shown in the illustration.

  • Put your cursor in the blank cell where you would like the full name to appear.
  • Highlight one cell that contains a first name.
  • Type in an “&” sign; then highlight a cell with the corresponding last name.
  • To add that necessary space between first and last name, use the function =A2&” “&B2.The quotation marks around the space tell Excel to put in the correct spacing.

 Example:

Function 2: Split Text to Columns

Now, what if you would like to do the opposite? In other words, you have a bunch of names in one column that need to be separated into two different cells, split out by a person’s first and last name.

How to Do It:

  • Highlight the column that you want to split up.
  • Go to the Data tab and select “Text to Columns.” A module will appear with additional information, and now you need to select either “Delimited” or “Fixed Width.”
  • “Fixed Width” means you want to select the exact location on all the columns that you want the split to occur.
  • “Delimited” means you want to break up the column based on characters such as commas, spaces, or tabs. This is what we’ll use in our illustration below.
  • Select “Delimited” to separate the full name into first name and last name.
  • Choose your delimiter. This could be a tab, semi-colon, comma, space, or something else (i.e., the “@” sign used in an email address). In our example, let’s choose the space.
  • After choosing your delimiter, Excel will show you a preview of what your new columns will look like. When you’re happy with the preview, press “Next.” This page will allow you to select Advanced Formats if you choose to do so.
  • When you’re done, click “Finish.”

Example:

Like Excel shortcuts like this one? Take a look at some more Tips from the Trenches, including the following popular Excel posts:

Subscribe to Our Blog Today!

Categorized in:

similar articles

Learn to think and approach problems like our financial consultants.

Manufacturing Operations Finance

Time to Clean Out the Warehouse: Improving Inventory Management Processes in 2021

Inventory flows reveal a lot about the health of a company, and 2020 put many stresses on inventory management processes. There were demand shocks, rising transportation costs, supply chain shortages, labor constraints and many other impacting factors. Manufacturers and distributors were suddenly faced with a slew of underperforming SKUs while others were in short supply…. View Article

January 20, 2021Danelle Larsen

Financial Project Management

Notes from the Field: 5 Success Factors for Cross-Functional Project Management

Finance teams are often looked to for leadership of cross-functional projects. This is often the case because the ultimate project results can be measured by, or affect, financials. A budget or re-forecast process is a clear example of a cross-functional project led by Finance. From our experience in leading cross-functional projects as part of Finance… View Article

January 13, 2021Marco Moreno

Treasury and Cash Management

A 2020 Recap: Cash Management & Why Cash is Still King

Last year brought disruptions in almost every industry. We witnessed significant interruptions in order fulfillment and delivery systems, chaotic drops and soaring demand in certain categories, fresh liquidity for borrowers and cash crunches for others. And cash management underwent rapid change for those suddenly with too much or too little. Short-Term Cash Management Led to Underutilized… View Article

January 7, 2021Danelle Larsen

See All