Tips to fix errors when multiple Excel files are grouped together in one quick tab

Excel is one of the popular office software but also has many errors when used. In which 'many Excel files grouped together in one tab' is a big challenge. This article will be dedicated to guiding you on how to fix errors when multiple Excel files are grouped together into one tab, extremely quickly and easily . So don't miss the article with useful tips shared below!

What is the error of multiple Excel files grouped together into 1 tab?

When you open many different Excel files as usual, there will be 2 separate tabs. From there you can easily use the alt + tab key combination to convert files to each other. But no matter how you open it, it will still be combined into 1 tab. Only if you use Switch Windows to find and open hidden files will it work.

Tips to fix errors when multiple Excel files are grouped together in one quick tab Picture 1

The initial error cause as known by Trick Boss is usually due to:

1. Data conflicts.
2. Incompatible format.
3. Error while merging multiple Excel files or splitting Excel files into multiple sheets.

To fix this problem, you can try rechecking the steps in merging multiple Excel files into one Power Query file. To ensure that formats and data do not conflict or use professional Excel file merging tools to avoid errors.

The following are detailed troubleshooting instructions.

How to fix errors when multiple Excel files are grouped together in a common tab

Take the following troubleshooting steps:

File > Options > Advanced > Display > Show all windows in Taskbar

1. Step 1 : Go to File > Options to open Excel's settings panel.
2. Step 2 : Select the Advanced tab, scroll down and find the Display option .
3. Step 3 : Here you will see the item   Show all windows in Taskbar (Show all in the taskbar), check it.  Tips to fix errors when multiple Excel files are grouped together in one quick tab Picture 2

If not, use the mouse to drag the file and it's gone. Specifically, place the mouse on the file that is stuck on the tab, hold the mouse still with the left key and then take the mouse out with that file.

However, this is a general, fast and compact way. In some cases of data conflict errors or files being used to merge multiple files, you need to apply other methods such as:

1. Check the file merging process again.
2. Use Power Query
3. Check data format
4. Use professional pooling tools

Instructions for merging multiple Excel files into one Power Query file

The merged customizations will be different, so you need to pay special attention to editing in step 4. When editing files in the merged folder path box, the merged data file will also be customized.

Instructions for quickly merging files with 5 steps in Excel

1. Step 1: Put all the files into a folder you want to combine and remember the path to that folder.
2. Step 2: Open a new Excel file, on the Menu bar select Data > New Query (or Get Data > From File > From Folder .

Tips to fix errors when multiple Excel files are grouped together in one quick tab Picture 4

1. Step 3: Wait a moment then find the folder containing the files to be merged (in step 1) > Open.
2. Step 4: A list of files in the folder will be displayed. You can continue to try showing the data transformation steps by clicking Transform data or Combine & edit (optional).

Tips to fix errors when multiple Excel files are grouped together in one quick tab Picture 6

1. Step 5: When completing data transformation, choose to export to Excel.  Home >  'Close & Load' from the Ribbon to merge the data into a table in a new Excel file.

Instructions for customizing merged files in Excel

Customizations are made in step 4.

1. Add a new column to view and adjust the entire aggregate table: On the aggregate table, click on the upper corner and select Add Custom column .

Tips to fix errors when multiple Excel files are grouped together in one quick tab Picture 8

1. Column customization:
   1. Column name: New column name. 
   2. Customize column content: Custom column formula . Fill in after the sign with the formula: =Excel.workbook([content]) . (When opening the parenthesis, you can hover over the Available column to select content quickly!).

Tips to fix errors when multiple Excel files are grouped together in one quick tab Picture 10

1. After adding a new column, you can customize the data content as desired.
   1. Click on the 'left and right arrow' icon above the column. Uncheck ' Use original column name as prefix '. Click OK .
   2. Now the sheets from the merged files will be displayed. You can: merge multiple sheets into 1 sheet easily.

Tips to fix errors when multiple Excel files are grouped together in one quick tab Picture 12

1. Remove unnecessary columns:
   1. Select the Data column > 2 left and right arrows icon.
   2. Select OK . (Remember to uncheck Use original column name as prefix before!)
   3. At this point, the customization panel will release all data for you to customize.
   4. Left click + Ctrl to select the columns you want to remove. Right-click and select Remove Other columns. 

Tips to fix errors when multiple Excel files are grouped together in one quick tab Picture 14

1. Select general column headers: Click to select table: Use First Row as Headers.

Tips to fix errors when multiple Excel files are grouped together in one quick tab Picture 16

1. Remove unnecessary titles. Select the arrow mark to uncheck unnecessary combined column headers. Select OK .

Conclude

With Power Query aggregation, you can flexibly process and merge data from many different sources. When customizing data in the original data table, in the combined table you can quickly update it by selecting the table and clicking Refresh .

Tips to fix errors when multiple Excel files are grouped together in one quick tab Picture 18

The method of merging multiple files is also often accompanied by the method of splitting an excel file into many small files, splitting an Excel file into multiple sheets, separating data from 1 sheet into multiple sheets.

Popular examples include summarizing scoreboards, product tables for each zone, summary lists, etc.

Hopefully, the above method of fixing errors when multiple Excel files are grouped together in one tab will help you better understand the process of merging and splitting files on Excel without errors.

Micah Soto

Update 08 December 2023