excel

How To Split Data In Rows In Excel

Boost your productivity with Sourcetable's AI spreadsheet assistant. Work like a spreadsheet power user and answer all your questions in seconds.


Learn more
Jump to

Learn the art of dividing data into multiple rows in Excel with our straightforward guide. Excel, a powerful spreadsheet tool, can become cumbersome when you need to split data across rows for better organization and analysis.

Understanding the various methods to separate data in Excel is essential for data management and clarity. Our guide simplifies these techniques, ensuring you can apply them with confidence to your datasets.

In this guide, we'll also explore why Sourcetable offers a more user-friendly alternative for splitting data compared to the traditional methods in Excel.

excel

How to Split Data into Rows in Excel

Using Power Query

Power Query is a powerful add-in for Excel, available for Windows and included with Office 365 and Excel 2016. It provides enhanced tools for data transformation, such as splitting columns into new rows. To use Power Query, follow these steps:

  • Install Power Query for Excel 2010 and 2013 if necessary.
  • Open Power Query from the Excel ribbon.
  • Select the column you wish to split.
  • Choose 'Split Column' by delimiter.
  • After splitting, the data will appear in new rows.
  • Power Query remembers transformation steps for future use.

    • Using TEXTSPLIT Function

    The TEXTSPLIT function is a newer feature in Excel for Microsoft 365, Excel for the Web, and Excel for Mac. It functions similarly to the Text-to-Columns feature but allows for splitting text across rows or columns. Here's how to use it:

  • Identify the cell with text to split.
  • Use the TEXTSPLIT function specifying the delimiter.
  • The split data will automatically populate across rows or columns based on your settings.
  • Note that TEXTSPLIT is not compatible with older Excel versions and will cause an error if opened in such versions.
    • excel

    Common Use Cases

    • excel

      Use case 1: Separating a column of full names into separate columns for first names and last names

    • excel

      Use case 2: Dividing addresses into individual columns for street, city, state, and zip code

    • excel

      Use case 3: Splitting product codes into their constituent parts for better categorization

    • excel

      Use case 4: Organizing a single column of dates and times into two separate columns for independent analysis

    • excel

      Use case 5: Breaking down a list of email addresses into usernames and domain names

    sourcetable

    Excel vs Sourcetable: Spreadsheet Evolution

    Discover the future of data management with Sourcetable, an innovative spreadsheet tool that transcends traditional boundaries. Unlike Excel, Sourcetable seamlessly integrates numerous data sources, providing a unified platform for complex data queries.

    Enhance productivity with Sourcetable's AI copilot. This feature outshines Excel by offering real-time assistance in formula creation and templating, streamlining your workflow through intelligent automation.

    Experience the ease of Sourcetable's intuitive chat interface, a stark contrast to Excel's manual formula input. This groundbreaking AI simplifies data manipulation, making advanced analytics accessible to all users.



    Sourcetable Logo

    Work smarter, not harder

    Boost your productivity with Sourcetable's AI spreadsheet assistant. Answer all your questions about spreadsheets in seconds. Try for free to get started.

    Learn More
    Drop CSV