This article will tell you how to split one excel worksheet data into multiple excel worksheets. It will also tell you how to split excel worksheet data set by column.
1. How To Split Excel Sheet Into Multiple Worksheets.
- To split an Excel sheet into multiple worksheets, follow these steps.
- Open the Excel workbook that contains the sheet you want to split.
- Select the entire sheet by clicking on the box with the column and row labels, located above and to the left of the cells.
- Copy the selected sheet by pressing “Ctrl” + “C” on your keyboard.
- Create a new worksheet by right-clicking on an existing worksheet tab and selecting “Insert.”
- Choose the type of worksheet you want to create (e.g. blank, table, pivot table) and click “OK.”
- Click on the new worksheet tab to make it active.
- Paste the copied sheet into the new worksheet by pressing “Ctrl” + “V” on your keyboard.
- Repeat steps 4-6 to create additional new worksheets as needed.
- Delete any unnecessary columns or rows from each worksheet by selecting the column or row and right-clicking it, then selecting “Delete.”
- Save the workbook with the new worksheets.
- Each new worksheet will contain a copy of the original sheet, split into separate sections.
- You can edit and format each worksheet as needed.
2. How To Split Excel Worksheet Data Set By Column.
- Suppose you have a worksheet contains the sales data like below.
Date Product Sales 2021-01-01 Product A $100 2021-01-01 Product B $150 2021-01-02 Product A $200 2021-01-02 Product B $175 2021-01-03 Product A $125 2021-01-03 Product B $180
- If you want to split the above data set into 2 worksheets, then you need first split the data set.
- For example, we want to split the above data set by the Product column, then we can follow the below steps.
- Select the column ( Product ) that you want to use to split the data.
- Click on the “Data” tab in the Excel ribbon.
- Find the “Sort & Filter” group.
- Select “Sort A to Z” to sort the data by the selected column.
- Then the data set will be changed to below.
Date Product Sales 1/1/2021 Product A $100 1/2/2021 Product A $200 1/3/2021 Product A $125 1/1/2021 Product B $150 1/2/2021 Product B $175 1/3/2021 Product B $180
- Select the “Data” tab again and click on “Subtotal” in the Outline group.
- In the “Subtotal” dialog box, select the Product item from the At each change in drop down list.
- Select the Count item from the Use function drop down list.
- Check the checkbox before the Product item in the Add subtotal to list.
- Check the checkbox before to “Replace current subtotals“.
- Click the “OK” button.
- The data will now be split into multiple sections based on the values in the selected column.
- Now, you can copy different data set to different worksheets.