I have two ranges with data like this:

enter image description here

I want to create a Pivot Table that consolidates the data from both tables. However when I do this using ALT+D+P I end up with Country as Row label, with no way to change it's rows (the table fields only say Row, Column, Value and Page1):

enter image description here

What I would like to do is to have it like this (Group Cities by Country and see subtotals):

enter image description here

Is it possible to do this with Pivots from multiple consolidation ranges? If not is there another way to do this?


I tried doing this with microsoft query, however when I create a new sheet in my workbook, click on add data from Microsoft Query and select the same Excel I have open I get the error "Unrecognized database format". I noticed this error doesn't happen if I add another Excel file.

enter image description here


Great tips on Pivot Tables can be found on Contextures.com, and about multiple consolidation ranges on this page specifically. Quoting from that page:

If possible, move your data to a single worksheet, or store it in a database, such as Microsoft Access, and you'll have more flexibility in creating the pivot table.

These days, you have other options in Excel. If you have Excel 2010 or higher you can use Power Query to combine data from different sheets, workbooks, databases, web, whatever, into a single data source that you can then use as the pivot table source data.

Power Query is a free add-in from Microsoft and runs with Excel Professional Plus. The business grade editions of Excel 2016 have it built into the Data ribbon under Get & Transform.

  • hi @teylyn I already tried doign this with Power Query, but it only works on my PC and I have to handle this file to different employees on my company (and I can't ask them to install Power Query). I'm now trying with microsoft query but I'm unable to add the Excel to the query, I get the error "ODBC Excel Driver Login Failed". Do you have any idea why this happens? – Lisandro Vaccaro Apr 1 '16 at 13:30

Consolidation from multiple ranges automatically uses the leftmost column as the row. You can get around this:

Make the City column the leftmost, make a page field that is set to "Men" for the men's range and "Women" for the women's range. Then use grouping to combine the results for each country if you want to.

New contributor
Jessica is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.