Office hacks: simplify the complexity of multiple worksheets merged with a trick

Many companies, especially large companies, have many departments, and for the convenience of bookkeeping, finance usually creates a worksheet for each department to record incoming and outgoing expenses (Figure 1). However, although this record is clear, it is not convenient for subsequent inquiries. For example, to view the expenditures of a particular department in a particular month, the conventional method to query back and forth in two worksheets. Now with the PowerQuery function of Excel 2016, we can easily combine multiple worksheets together.

201816gzbh01

Figure 1 Departmental expense table of a company

Create a new workbook file, then click “Data – New Query – from Excel file”, and then select the above Excel file containing multiple worksheets. After importing the file will automatically read the list of the above workbook, each worksheet will be automatically displayed (Figure 2).

201816gzbh02

Figure 2 Importing data sources

Tip.

If you need to combine worksheets saved in multiple workbook files (combined worksheets must have the same data structure), then you can Kutools for Excel add-in to achieve, launch Kutools for Excel in Excel, click “Enterprise / Summary”, in the summary worksheet wizard, select the “Copy information from multiple workbooks into one worksheet” option. In the Summary Worksheet Wizard, select the “Copy information from multiple workbooks to a worksheet” option. Then click the Add button to insert the files that need to be merged and specify the worksheets that need to be merged to combine multiple workbooks in one workbook. Finally, you can use the merged workbook as the data source to perform the above operations.

Select any of the worksheets in the above window and click “Edit” to enter the PowerQuery edit window. Since we only need to edit the “Source” data here, you can delete all the data except the “Source” data (only the source) in the right pane under “Apply steps” in the right pane to delete all data except “Source” (only the source is retained), right-click on the data and select “Delete” (Figure 3).

201816gzbh03

Figure 3 Retention of source data

After completing the above operation, as we also only need to contain the amount of “data” data for analysis, hold down the Ctrl key to select the DATA column data, the other columns of data will also be deleted. After completing the deletion of column data, click the Data column next to the “Filter” button, filter options select “Extended”, check all the Coloume click “OK” (Figure 4 ).

201816gzbh04

Figure 4 Filtering data

You can see that after the above operation, all the cost data of the original workbook is integrated in one worksheet. Select the first row and click “Start – Make the first row a header”. The first row of the table header content as the title, but because of the synthesis of each sub-table header all integrated over, so you need to hide the other rows of excess table header data data, hidden operation completed with the help of filtering tools, click the filter button to the right of “date” as prompted, in the drop-down window that opens to remove “Date” checkbox, so that the old only retain the first line of the table header content display (Figure 5).

201816gzbh05

Figure 5 Filtering hidden dates

Click “OK” after completing the above operation, and click “File – Close and Upload” in the returned edit window to upload all the integrated data to the new workbook (Figure 6).

Tip.

After saving and uploading the data to an Excel workbook, you can click the Refresh button in the right pane at any time and click Edit in the open window to return to the PowerQuery window for editing.

201816gzbh06

Figure 6 Upload and save

In the Save Uploaded Data window, click on “Insert – Pivot Table”, in the window that opens, select a list area, select all of the composite data, select the storage location and choose “Existing Worksheet” to insert a pivot table in the existing worksheet area (Figure 7).

201816gzbh07

Figure 7 Insert Pivot Worksheet

In the field settings of the pivot table, drag the date to the “row”, the amount to the “value”, the department to the “filter”, the cost dragged to the “Columns” to complete the layout of the pivot table, so that in a pivot table you can easily see the arrangement of the costs of each department and statistics (Figure 8).

201816gzbh08

Figure 8 Pivot table field settings

Continue to click “Insert – Schedule”, check the “Date” in the pop-up window, click “Insert – Slicer”, check the “Department” in the pop-up window. Department” to complete the pivot table settings (Figure 9).

201816gzbh09

Figure 9 Inserting the slicer

OK, after completing the above operation. In the future, if we need to view the expense data of each department, then just click on the inserted date or department slicer, it is easy to filter the view according to your needs. For example, if you need to view the cost breakdown of the first quarter of the Sales Department, then just set the date view to “Quarterly”, then select the first quarter, click “Sales Department” in the slicer, and the quarterly cost summary of the department will be displayed immediately. Of course, we can view any data according to our actual needs (Figure 10).

201816gzbh10

Figure 10 Display effect

Leave a Comment