Search code examples
excelfinance

Advice on how best to arrange and analyse data stored in 150 tabs in an excel spreadsheet

I am analyzing the debt of around 150 companies and really need some help/advice as I've hit a brick wall in terms of what to do next!

I have created a spreadsheet that has a tab for each company, so 150 tabs. In each tab there is a row for each debt instrument a company has (some companies have up to 100 debt instruments). I have several columns that show various items including: The maturity date of the debt Percentage of overall company debt that this instrument represents The interest rate of that instrument What sector that particular company is from What country that particular company is from (these last two are the same all the way down the column in each tab and are there to label each debt instrument, so I can analyse debt across countries and sectors.)

I'd like to consolidate the data so that I can create various matrices; e.g. countries on the X axis, Sectors on the Y axis, and have the average Interest rate of all the debt instruments in each combination of country and sector. Or a similar matrix with the average maturity instead of the average interest rate.

I tried using AVERAGEIFS but it didn't like doing it across 150 tabs. The next idea was therefore to consolidate all the tabs into one tab so that I could then use AVERAGEIFS within one tab, but I couldn't work out how to efficiently consolidate all the tabs, into one tab with all the data below each other.

I'm therefore looking for advice on where to turn next? Whether to manually link the data into a single tab , try and write a macro to do this, use pivot tables, Access or is there something I'm missing!

Any help would be very much appreciated!

Thanks!

Sonski

Solution

  • This is a job for Power Query. If the sheets are all structured the same and the data in all sheets is in Excel Tables (created with Insert > Table), you can pull the data into Power Query and combine all sheets.

    Start a new, blank query, without any data connection. In the formula bar, type

    =Excel.CurrentWorkbook()

    Filter out any tables you don't need and filter out any named ranges. Then combine the tables into one.

    For a video walk-through see https://www.youtube.com/watch?v=YOC-pEIuHpA