Unpivot Country and Year Data into Three Columns in Excel
Question details
Convert country codes and year columns into a normalized table with CountryCode, Year, and Value fields, while handling error values.

- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- A worksheet has country codes in rows and separate columns for each year.
- Observed behavior
- The desired result is one row per country and year, with the corresponding value in a third column.
Check that the source range has a header for the country code and a separate header for each year. Remove unrelated columns or identify them before unpivoting, since Unpivot Other Columns transforms every unselected column.
Unpivot the year columns with Excel Power Query
Use this method to create a refreshable three-column table from the source data.
Select the country and year data in Excel, press Ctrl+T, confirm that the table has headers, and select OK.
Click a cell in the table and select Data > From Table/Range to open the Power Query Editor.
Select the country-code column, then choose Transform > Unpivot Other Columns. Power Query creates Attribute and Value columns.
Double-click the column headers to name them CountryCode, Year, and Value. If needed, select Year and use its data type icon to set it to Whole Number.
If the Value column contains errors such as #N/A, select Value and choose Transform > Replace Errors. Enter 0 only if zero is an appropriate substitute for the missing or invalid value.
Select Home > Close & Load to return the result to Excel. After changing the source table, select Data > Refresh All to update the output.

Work with country and year spreadsheets in WPS Office
WPS Office is a free, lightweight Office alternative with a familiar interface and Excel format compatibility for an easier move between spreadsheet tools. Use WPS Spreadsheets to review and edit country and year data; it does not replace Excel's Power Query unpivot and refresh workflow.

Frequently Asked Questions
How do I keep CountryCode from being unpivoted?
In Power Query Editor, select the country-code column before choosing Transform > Unpivot Other Columns. The selected column stays in place while the year columns are transformed.
Why are some country and year combinations missing from the result?
Power Query unpivoting omits null values. Check the corresponding source cells for blanks and decide how missing values should be represented before unpivoting.
Should I replace #N/A errors with zero?
Only replace them with zero if zero is meaningful for your analysis. To do so, select the Value column in Power Query and choose Transform > Replace Errors.
How do I update the unpivoted table after adding data?
Add the data to the Excel source table, then select Data > Refresh All. Power Query reruns the transformation and updates the loaded result.




