The article “Excel - Table Optimization” stipulated that for correct operation with a table, each column should contain only logically indivisible data .

For example, in the employee table in Fig. 1, you need to sort the data by last name, and then by employee name (to sort namesakes). To do this, you should distribute last names, first names and patronymics by individual columns.

When you need to split data in one column, you can use the built-in Excel capability – splitting the contents of one column into several .

To do this you need to do the following:

1. To the right of the divided range, insert several empty columns according to the number of data to be separated. This is done to avoid replacing data in adjacent columns. In our case, we will insert two new columns (Fig. 2).

2. Select the column that contains the shared data (Last Name First Name Patronymic).

3. In the Working with Data panel of the Data tab, click the Text by Columns button.

In the dialog box that opens Text Wizard (parsing) – step 1 of 3 (Fig. 3) in area Source data format indicate the partitioning option:
— with delimiters – if the text in the cells is separated by spaces, commas, semicolons, tabs, etc. (this is our case - separated by spaces);
— fixed width – if we divide the data into equal (column) parts.

5. In the window Text Wizard (parsing) – step 2 of 3(Fig. 4) select separator character — space . If the required character is not included in the proposed set, check the box another and enter the required character in the field. In this case, a sample partition will be shown at the bottom of the window.

6. By clicking the Next button, we go to the following dialog box: Text Wizard (parsing) – step 3 of 3(Fig. 5), which allows you to set the data format for each new column. Button More details opens a dialog box in which you can install additional settings numerical data.
You can exclude a single column when splitting. To do this, select it in the parsing sample and enable the option Skip column.

7. After setting all the required parameters, press the button Ready, as a result of which the contents of the selected range will be divided into three columns (Fig. 6).

In the workspace table editor Microsoft Excel There are a huge number of cells and columns. The user can change their height and width, combine several fields into one and apply many more settings. We suggest you learn how to split a cell in Excel.

Step by step guide

If the cell to be split was created by combining several cells, then the procedure will be very simple. First you need to click on it with the mouse. The “Merge and Center” button will be highlighted in the menu. Click it or expand the drop-down list and select “Unmerge Cells”. In these cases, the result will be the same - the program will divide the field into two components.

If the cell is not composite, you will have to do a little more operations: make adjacent fields composite.

This is the last operation that will allow you to divide the selected field in Excel into a specified number of parts.

A worksheet in Microsoft Excel is itself a large table consisting of cells that are assigned a specific address (a number corresponding to a row and a letter corresponding to a column). At this address you can access the data, that is, to use it in formulas. Accordingly, dividing a cell directly, as we say in Word, will not work. Because the address A5 will not become A/1-5 and A/2-5. So what can be done?

If you have not yet created a table and the data has not been entered, then initially think everything through well, and then simply merge certain cells, leaving them, for example, separated only in the header or in some row.

But let’s tell you in this article what to do if the table is ready. Let's figure out how to divide a cell in Excel into two or more horizontally and vertically, as well as diagonally.

For example, we will have a table like this. In it you must also indicate the Manufacturer after the Name.

Since new columns are added to the left of the current selection, I select any value in column C and, on the Home tab, insert columns.

So, after the Name, another vertical column appeared, which I will call Manufacturer.

Since fruits can be brought from different countries, you need to make several opposite each one in the added column from one cell.

Let's start with Apple. Select the block immediately below this name, right-click and select “Insert” from the context menu.

Mark the “line” with a marker in the box and click “OK”.

An additional line will be added in the place we need. In the same way, more lines are added if there are more Manufacturers.

Now let's merge the cells in those columns where the data does not change. In the example, these are Number, Name and Quantity. First you need to select them, then click on the “Merge” button and select “Cells”.

You can read about various ways to combine cells in Excel in the corresponding article.

Fill in the Manufacturer and Price fields with data.

As you can see, by adding rows and merging certain cells, we achieved that in the desired column we managed to divide them horizontally into several rows. Thus, Apple has several various Manufacturers and different price.

You can do the same with Pear. We add rows, combine where Number and Name are, and only two rows in the Quantity column (since the value will be the same). Then we enter the data and get a sort of divided cell in the Manufacturer and Price field.

Please note that in merged blocks you need to indicate the upper left address in the formula. For example, from D2:D3 it will be D2, and from B4:B6 it will be B4.

Now let's look at how to split one cell into 2 or more vertically. This is necessary if you want to add several subnames to the column name, and only then fill them with data.

For example, let's divide the Quantity column. From the second row, select any undivided cell, it can be C2 or E2. Calling context menu and insert an additional line.

Using the appropriate button, combine D1 and E1, placing the value in the center.

In this cunning way, we divided the cell in the column under the word Quantity and gave them the names Imported and Sold. Where Sold, I still have previously merged cells with a value; if it doesn’t fit, I just need to cancel it by selecting the appropriate item.

As a result, we received a table in which, in the right places, the cells are divided into several rows or columns. And we did this not by dividing, but by adding horizontal and vertical rows, followed by combining some blocks.

To split diagonally, select the cell, call up the context menu by right-clicking and click “Format”.

The selected block will be divided.

You can do it differently. At the top, open “Insert”, click “Illustrations” - “Shapes” and select a straight line from them.

In the desired cell, draw it from one corner to another diagonally. If, as in the example, it is the wrong color, then on the tab "Drawing Tools" select the desired one either from ready-made styles, or by clicking on “Shape Outline”.

You can split cells in Excel that previously had a merge applied to them using the same menu item on the tab "Home". In this case, the information available in the merged cell will be placed in the upper left cell.

When you select a merged cell, the menu item is also selected "Merge and Place in Center". After you split cells, a group of previously merged cells remains selected.

It is not possible to split a cell in Excel that was not originally merged. In Excel there is not even such an item, but only "Unmerge Cells".

If you still need to split a cell in Excel into two or more, which was originally whole, you can simply achieve a similar effect by merging adjacent or top cells.

It is also possible to split a cell diagonally in Excel. To do this, right-click on the cell that needs to be divided diagonally and select from the menu "Cell Format". In the window that appears on the tab "Border" You can choose to split the cell diagonally, in two options.

But in this case, the cell is not divided into two parts, but only a line is drawn in the cell. To place text in such a cell in different corners, it should be written in the cell in two lines, and spaced using spaces.

The Split Table add-in will automatically split data from one sheet across several sheets:

• Quickly split table or range data into different sheets
• Selecting a division method: by column values ​​or by number of rows
• Choosing how to name result sheets
• Preserve headers and formatting in result tables
• Automatic splitting of merged cells with duplicate values

Add "Split Table" in Excel 2019, 2016, 2013, 2010, 2007

Suitable for: Microsoft Excel 2019 - 2007, desktop Office 365 (32-bit and 64-bit).

How to work with the add-on:

How to split a table into multiple sheets based on column values

You can split an entire table or range based on the values ​​in one key column. Thus, the data related to each unique value in the key column will be displayed on separate sheets.

1. Click the Split Table button on the XLTools panel > A dialog box will open.

• 
  Attention: for best results, make sure there are no empty cells in the header.

4. Select split by “Values ​​in this column” as the splitting method > From the drop-down list, find and select the key column:

• If your table has a header, find the column by its name in the header.
• If the table does not have a header, find the column by its general letter designation (A, B, C, etc.)

• Select Column Value to name the tabs based on the key column values.
  Note: If some cells in your key column are empty, please fill in the blanks or use a different way to name the sheets.
• Or: Select Number Row to name the tabs with sequential numbers (1, 2, 3...)
• If necessary, add a prefix or suffix. They will be repeated in the name of each tab.
  

6. Click OK >

Result: New sheets are placed in order immediately after the original sheet. Each tab contains a table of data associated only with a specific key value. The original data is intact and has not been modified.

How to split a table into several sheets for a given number of rows

You can split a table or range based on the desired number of rows per sheet, for example, split the data after every 5 rows. Thus, each next 5 lines will be placed on a separate sheet.

1. Click the Split Table button on the XLTools panel > A dialog box will open.

2. Select the table or range you want to split, including the header.
Tip: Click on any table cell and the entire table will be selected automatically.

3. Check the “Table with headers” checkbox if this is the case.

• If the table has a header, it will be duplicated in the result tables.
• If there is no header in the table, it will not be in the result tables either.

4. Select By Number of Rows as the splitting method > Specify a fixed number of rows to split the table.

5. Set the naming method for result sheets:

• Select Number Row to name the tabs using sequential numbers (1, 2, 3...)
• If necessary, add a prefix or suffix. They will be repeated on each tab.
  Tip: We recommend using meaningful prefixes and suffixes - it will be easier to search and switch between sheets later.

6. Click OK > Finish. Large tables may take some time to process.

Result: New sheets are placed in order immediately after the original sheet. Each tab contains a table with a fixed number of rows. The original data is intact and has not been modified.

How data is copied to new sheets

Spreading a table across multiple worksheets essentially means extracting and copying data from the original worksheet to new sheets in the workbook.

• Formulas and cell references:
  To avoid data distortion, instead of references to cells, functions or formulas in the source sheet, the XLTools Split Table add-in inserts their values ​​into the result sheets.
• Formatting:
  The Split Table add-in keeps cell and table formatting the same as in the original worksheet. This applies to cell format (number, date, text, etc.), column width, row height, fill color, etc. However, if your source table has a style applied, the result tables will be inserted as ranges.
• Merged cells:
  If there are merged cells in the table, the merge is automatically removed and the corresponding values ​​are duplicated.

How to save result sheets as separate files

After exploding a table or range by different sheets, you can quickly save these sheets as separate files using the XLTools Book Organizer add-on. It allows you to save sheets separate files, copy sheets to new book and manage multiple sheets at once.