What it does
Automatically adds two useful summary sheets for analyzing data points from multiple identically-structured sheets – a row-by-row view and a side-by-side view.

When to use it
When you have a workbook full of identically-structured sheets, this adds two useful summary sheets to analyze data points side-by-side that would normally be spread across multiple sheets.  Generally a workbook like this would be exported from a reporting system and contain a different location, region, department, etc. on each sheet.

Why to use it
It’s a very quick and easy way to add centralized analysis to a workbook with multiple identically-structured sheets offering two powerful views: row-by-row and side-by-side.

Default shortcut
None

Other Details

• Category: Sheets / Folders and Files
• Difficulty: 3/5
• Usage/frequency: 3/5
• Automation factor: 5/5 (estimated 300 seconds saved each time used)
• Type: Bulk
• Date added: 11/10/2023
• Tags: Side-by-side, row-by-row, summarize, sheets, analysis

Related Macros
None

Other Articles
None

Summarize Sheets – Example File

Prerequisites
Optional: in the active sheet, set freeze panes above and to the left of your data, with the header rows above the freeze panes and the label columns to the left of the freeze panes.  This helps the macro know what data you’ll summarize (you can always change the default).

Select all the sheets you’d like to summarize.  To quickly select many sheets, use the Sheet Action Picker – option E (it selects all sheets from the active sheets to the end of the workbook).

Instructions
With the sheets you’d like to summarize selected, run the Summarize Sheets macro.  You will be prompted to enter the data range to be summarized, which is below header rows and to the right of label columns.  If the active sheet has freeze panes set, the used range below and right of freeze panes is used as a default range. 

Two sheets are created: Row-by-Row and Side-by-Side.  These summarize the selected sheets by showing two separate views that compare them using dynamic formulas leveraging the INDIRECT function.

In the Row-by-Row sheet, you can view the same single row of data from all the selected sheets, with the same column structure as all the selected sheets.  The row displayed is driven by the input at the top.  Update it to dynamically change the row referenced from all the selected sheets.

In the Side-by-Side sheet, you can view the same single column of data from all the selected sheets, with the same row structure as all the selected sheets.  The column displayed is driven by the input at the top.  Update it to dynamically change the column referenced from all the selected sheets.