Transposing columns to rows in Excel

0 votes

I want to transpose my data in excel currently looks like this:

Data Set Now

And it should look like this: enter image description here

You see the problem is that I do not only need to transpose the columns with values, but also add rows for each consecutive year for each indicator and for a given country. Any help?

Sep 30, 2022 in Others by Kithuzzz
• 38,000 points
1,190 views

1 answer to this question.

0 votes

Due to the amount of your data, we are unsure if you can achieve that based on your photographs. But I believe the TRANSPOSE(array) cell formula is what you need with some filtering and rearranging:

Here is what we'll get: enter image description here


  • Select the destination range where you want to place your new/good data
  • Enter =TRANSPOSE( into the formula bar

    enter image description here


  • Select the source range of your old/bad data (or type the address manually)
  • Close the ) for the cell formula

    enter image description here

  • Hit Ctrl + Shift + Enter

answered Oct 1, 2022 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

Want to compare two columns in excel

Hello To compare two columns in excel ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
1,112 views
0 votes
1 answer

Convert Rows to columns using 'Pivot' in SQL Server

If you are using SQL Server 2005+, ...READ MORE

answered Jun 20, 2022 in Others by nisha
• 2,210 points
4,433 views
0 votes
1 answer

Is there any way in python to auto-correct spelling mistake in multiple rows of an excel files of a single column?

Use Spellchecker for doing your stuff: import pandas ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 63,600 points
2,075 views
0 votes
1 answer

Is there a function to unhide columns in excel through python

Excel file : df.to_excel('demofile.xlsx',index=False) import openpyxl py = openpyxl.load_workbook('demofile.xlsx') exlsheet = ...READ MORE

answered Oct 18, 2022 in Others by narikkadan
• 63,600 points
2,277 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,690 points
986 views
0 votes
1 answer

How to do multiple transpose in excel vba

Use Dictionary object Sub test2() With New ...READ MORE

answered Jan 22, 2023 in Others by narikkadan
• 63,600 points
669 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
1,246 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,692 views
0 votes
1 answer

Create unique rows in Excel with limited data to be used in multiple columns

This setup isn't readily generalizable, though since ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 63,600 points
751 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP