Then, go to Table Design Tab> click on Tools Dropdown > select Insert Slicer Option.Make sure you have clicked on any cell of the table to open this tab option. In the final step, we will use the Insert Slicer option from Table Design tab. After that, the following table will be created.Afterward, turn on My table has headers option.Then, the Create Table dialog box will appear.To start with, go to Insert Tab > click on Table Option.Next, we will show you how to insert a table to use Insert Slicer option. Secondly, copy the data table from the main sheet and paste it into these three different sheets.Firstly, create three sheets named after the three students’ names.We will create multiple sheets to split our dataset based on column value. Split Excel Sheet into Multiple Sheets Using Insert Slicer Option After Creating Tableįor splitting the main sheet into multiple sheets based on the Student Name column you can use the Table Option. Finally, you will get three different sheets for the three students Daniel Defoe, Henry Jackson, and Donald Paul respectively.ģ.Next, select the column Student Name which was in the Filters area.Now, the Show Report Filter Pages wizard will pop up.After that, go to PivotTable Analyze Tab> click on PivotTable Group > click on Options Dropdown > select Show Report Filter Pages Option.Again, from Design Tab > click on Layout Group > click on Grand Totals Dropdown > select Off for Rows and Columns Option.Afterward, go to Design Tab > click on Layout Group > click on Report Layout Dropdown > select Show in Outline Form Option.Then, drag down the Student Name in the Filters area (any column on the basis of which you want to split the main sheet into multiple sheets) and Subject and Grade to the Rows area.Now, a new sheet will open up which has two parts PivotTable1 and PivotTable Fields.After that, click on New Worksheet (it’s a good practice to place Pivot Table into a new sheet).Then, PivotTable from table or range Dialog Box will appear.In the beginning, go to Insert Tab> click on PivotTable Option.You can split the following sheet into three sheets for the three students based on the column Student Name by using Pivot Table. Apply Pivot Table to Split Excel Sheet into Multiple Sheets Based on Column Value After following the above steps of this method for the other two sheets for the students’ Henry Jackson and Donald Paul, you will get the following two tables in their respective sheets.Ģ.Finally, let’s type the header name above all of the columns and format this data table.Here, in the formula, Filter!B5:D16 is the data range without header in the main sheet which is named Filter, and Filter!B5:B16 is the range of the Student Name in the main sheet and it would be equal to “Daniel Defoe”.
0 kommentar(er)
