Excel Sheets into a report

Copper Contributor

I have over 20 sheets with fields listed in the first column (first name, last, name, etc) and data in the second column.  Can I get a report with all 20 individuals listed plus their data?

3 Replies
best response confirmed by Hans Vogelaar (MVP)


You can create a report that consolidates data from multiple sheets in Excel.

Here's a step-by-step guide to accomplish this:

  1. Open a new worksheet where you want to create the report.
  2. Rename this worksheet to something like "Report" or "Consolidated Data."
  3. In the first column of the "Report" sheet, list the fields you want to include (e.g., First Name, Last Name, etc.).
  4. In the second column, enter the formula to retrieve the data from each individual sheet. Assuming your individual sheets are named "Sheet1," "Sheet2," and so on, the formula in cell B2 of the "Report" sheet would be: =Sheet1!B2

This formula retrieves the data from cell B2 of "Sheet1." Adjust the cell references accordingly to match your specific data layout.

5. Copy the formula in cell B2 and paste it down the column to retrieve data for all individuals from  each sheet.

6. Repeat steps 4 and 5 for each field and each individual sheet, ensuring that the formulas reference the correct cells in each respective sheet.

7. Format the report as desired, applying any necessary formatting, headers, or styling to improve readability.

By following these steps, you can consolidate data from multiple sheets into a single report in Excel. Whenever you update the data in the individual sheets, the report will automatically reflect the changes.

Thanks so much!
I'm glad the instructions helped.
I wish you continued success with MS Office!