Create a Pivot From Multiple Sheets in Excel with Simple Methods

Find out how to create a pivot from multiple sheets in Excel to combine and analyze data in one place. When managing data across multiple sheets becomes time-consuming, Kimi Sheets helps streamline the process and deliver faster, cleaner insights.Try Kimi Sheets

10 min read·2026-04-29

Managing data across multiple Excel sheets can get messy fast. Pivoting from multiple sheets into a single unified view speeds up analysis and reduces the need to constantly switch between tabs. This guide walks you through how to do it with ease.

Table of contents

• Quick overview: 4 methods for pivot tables from multiple sheets in Excel
• How to combine multiple sheets using Power Query?
• How to create relationships between multiple sheets using Power Pivot?
• How to create a pivot table from multiple sheets using Excel Copilot?
• How to make pivot tables from multiple sheets in Excel using Kimi Sheets?
• Core features of Kimi Sheets
• Conclusion

Quick overview: 4 methods for pivot tables from multiple sheets in Excel

Creating pivot tables from multiple sheets in Excel can be done in different ways depending on your data and skill level. Some methods are more technical and give full control, while others are faster and more automated. Here's a quick overview to help you choose the right approach:

If you want full control and structured data preparation, Power Query is the right choice. For relational analysis across datasets, Power Pivot works best. If you prefer AI-driven creation in Excel, Copilot can help you get quick results. But if you want something quick, simple, and highly automated without technical steps, Kimi Sheets is the easiest option for handling multiple sheets and generating pivot-style insights instantly.

How to combine multiple sheets using Power Query?

Power Query helps you bring data from multiple sheets into a single clean table before creating a pivot table. Instead of manually copying and pasting data, it stacks and organizes everything automatically. It also keeps your original sheets unchanged, which makes the process safe and easy to update later. Here's how you can combine multiple sheets using Power Query.

Step 1: Convert sheets into tables

Select your data in each sheet and press Ctrl + T to turn it into an Excel Table. Power Query works best with structured tables, so this step is important. Make sure each table has clear headers and consistent column names across sheets to avoid errors during combining. You can also rename each table for easier management.

Step 2: Load tables into Power Query

Go to the "Data" tab and click "From Table/Range" to open Power Query Editor. Repeat this for each sheet so all tables are loaded as separate queries.

Inside Power Query, you can:

• Check and adjust data types (such as date, number, or text).
• Remove unnecessary columns or blank rows.
• Ensure column names are consistent across tables.

Step 3: Append and create PivotTable

In Power Query, go to "Home" > "Append Queries" to combine all tables into one dataset. If you have multiple tables, choose "Append Queries as New" to keep original queries unchanged.

After combining:

• Click "Close & Load" to load the final table into Excel.
• Insert a PivotTable from the new combined table.
• Start analyzing data from multiple sheets in one view.

How to create relationships between multiple sheets using Power Pivot?

A simple way to build a pivot table from multiple sheets in Excel is to use Power Pivot relationships. This method connects multiple tables without merging them into a single sheet. Instead, Excel links them through a common column so the data can "talk" to each other. It is useful when you want to keep your original sheets separate but still analyze them together in a PivotTable. Here's how you can do it in simple steps:

Step 1: Convert data into Excel tables

Select your data in each sheet and press Ctrl + T to turn it into an Excel Table. Power Pivot works only with structured tables, so this step is important. Make sure each table has clear headers and no blank rows. Also, ensure the key column (such as ProductID or StoreID) is consistent across tables.

Step 2: Add tables to the Data Model

Go to the "Power Pivot" tab and click "Add to Data Model" for each table. This loads your tables into Excel's Data Model, where relationships can be created between them.

Once added:

• Tables become available in Power Pivot.
• You can manage them centrally in the Data Model environment.

Step 3: Create relationships and build a PivotTable

Open "Power Pivot" > "Manage" > "Diagram View," then drag a common key column (such as ProductID or StoreID) from one table to another to create a relationship.

Make sure:

• One table has unique values (lookup table).
• The other table contains repeated values (data table).

After that:

• Go to "Insert" > "PivotTable".
• Select "Use this workbook's Data Model".
• Build your PivotTable using fields from multiple related tables.

How to create a pivot table from multiple sheets using Excel Copilot?

Another way to build a pivot from multiple sheets in Excel is by using Excel Copilot. It helps you skip complex steps and creates PivotTables using plain instructions. You just describe what you need, and Excel does the rest automatically. This makes data analysis faster and easier for beginners and professionals. Here's how to create an Excel pivot table for multiple sheets using Copilot:

Step 1: Prepare your sheets as Excel tables

Select your data in each sheet and press Ctrl + T to convert it into an Excel Table. This gives your data a clean structure that Copilot can easily understand. Make sure all sheets have clear and similar column names so the data can be combined without confusion.

Step 2: Open Copilot in Excel

Go to the "Home" tab and click Copilot to open the side panel in Excel. Your file should be saved in OneDrive or SharePoint with AutoSave turned on. Once opened, Copilot can access your workbook and start working with your data instantly.

Step 3: Enter a prompt to create a PivotTable

Type a clear command like: "Create a PivotTable from Sheet1 and Sheet2 showing total sales by product." Copilot will understand your request and process the data automatically. It will generate a PivotTable on a new sheet, which you can further adjust by asking follow-up instructions.

Working with multiple sheets in Excel can take time, especially when data is spread across different files. Even with tools like Copilot, users may still need a simpler and faster way to manage everything in one place. This is where modern tools like Kimi Sheets come in, enabling smoother data handling. It helps bring information together easily, so analysis feels more organized and less stressful.

How to make pivot tables from multiple sheets in Excel using Kimi Sheets?

Kimi Sheets is a smart data tool designed to organize and combine data from multiple sheets into a single, clean structure. It supports quick merging and transformation of datasets without needing advanced Excel skills. The tool helps prepare data for analysis by keeping it well-arranged and consistent. It also makes it easier to create summaries for PivotTable-style reporting and decision-making.

Step 1: Upload your Excel and enter the prompt

Access "Sheets" on Kimi and upload your Excel file with multiple sheets. Enter a prompt asking the tool to combine all sheets and create a pivot table-style summary.

Example prompt:

Step 2: Let Kimi process and analyze your data

Now, let Kimi Sheets automatically combine all sheets into a single clean dataset. It also removes errors, fixes structural issues, and prepares data for analysis. It will generate a pivot table based on your instructions.

Step 3: Download Excel

After analysis, you can download the updated Excel file from the platform. The file includes clean data and ready-to-use pivot-style insights.

Core features of Kimi Sheets

• Automated pivot table creation: Kimi Sheets automatically creates pivot-style summaries from your data in seconds. It removes manual setup and gives quick analytical views in a structured format for better understanding.
• Raw text structuring: It converts unorganized or raw text into clean, usable data tables very quickly. This makes it easier to analyze information without extra formatting work or confusion.
• Multi-sheet data modeling: Kimi Sheets can efficiently combine data from multiple sheets into a single connected model. This helps analyze large datasets in a single, unified view for clear insights.
• Instant data tabulation: It quickly turns scattered data into well-structured tables with proper alignment. This saves time and improves overall data readability for smoother analysis.
• Auto data extraction: Kimi's AI Excel agent accurately and efficiently extracts useful, relevant information from large datasets. It reduces manual searching and helps in faster data processing for better results.
• Dynamic table styling: Kimi Sheets automatically applies clean, adjustable styles to tables in real time. This improves presentation and makes reports easier to understand and visually clear.

Conclusion

There are many ways to manage and analyze data coming from multiple sheets, and each method fits different needs and skill levels. Some approaches offer full control, while others prioritize speed and automation for faster results. A pivot from multiple sheets in Excel can be created much more easily when you choose the right method for your workflow and goals. Try Kimi Sheets today to combine your data instantly and turn it into clear, simple insights without any extra effort.