This module describes how you can make data tidy with Microsoft Excel. You can try all the data transformations in this module yourself with the sheets in the Excel file below.
The name of the sheets in the Excel correspond to the different sections of this module.
Separating a column in Microsoft Excel can be done with the “Text to columns” button under the Data tab. Select the column you want to separate, click the Text to columns button, and select the separator you want to use.
Source: Maarten Lambrechts, CC BY SA 4.0
Pivoting a data table in Excel can be done by using a pivot table. To do so, select the table you want to pivot, click “Insert” on the menu bar and select “Pivot Table”.
Source: Maarten Lambrechts, CC BY SA 4.0
Next, you configure the columns of the pivot table. The column that contains the names of the columns you want to create should go under “Columns” (in this example, this is the type column) and the column containing the data for these columns should go under “Values” (here, this is the count column). The other variables (country and year) should go under “Rows”.
Source: Maarten Lambrechts, CC BY SA 4.0
Making data longer, or unpivotting data, in Excel requires the Power Query Editor. However, the Power Query Editor is not available in all versions of Excel.
If it is available in your version of Excel, you can follow the steps in the official documentation for unpivotting columns with Power Query.
In Microsoft Excel, transposing data can be done by copying the table, clicking right and selecting “Paste special” and checking the Transpose checkbox.
Source: Maarten Lambrechts, CC BY SA 4.0
Congratulations!
You have completed module 5. Continue with the next suggested module Geometric objects in detail or pick another module from the