How to Transform Data in Excel? Ultimate Guide
Excel is a popular and handy tool for storing mathematical, statistical, and other information. Does Excel provide any functionalities for data transformation? Of course, it does! To transform data in Excel, you can use multiple functions, as well as native and third-party tools to automate processes. Read on to learn about all the available options.
What is data transformation in Excel?
According to Wikipedia:
Data transformation is the process of converting data from one format or structure into another format or structure.
So, you basically change the format without changing the information content of the data. The goal of data transformation is to present the data so that it can be used most effectively.
Data transformation in Excel means the same process but implemented in Excel and with the help of Excel functions and tools. For example, you can remove a column, change the data type, or filter rows. Each of these operations means data transformation.
In what cases may data transformation in Excel be needed?
Often, simply arranging data alphabetically, in ascending order, or transforming non-normal data in Excel is not enough. To understand the information, sometimes more complex transformations are required.
After conducting any sociological research, the collected data needs to be grouped in one place, and a detailed analysis should be carried out. Entrepreneurs use Excel data transformation to create reports or optimize their work. These can be companies that are engaged in sales or even tour operators.
If we talk about mathematics or physics, the systematization of data allows them to simplify the work as much as possible. And if, when working with data, you can immediately calculate the square root or raise a number to a power, it makes the work faster. It is almost impossible to list all areas where data transformation in Excel can be used, but to name a few:
- Reporting
- Analysis of statistical data
- Performing mathematical operations with a large amount of data
- Compiling financial data
- Business analytics, and many others
Each specialty can use data transformation in different ways, depending on their goals and needs.
What can you use to transform data in Excel?
Suppose you are planning to transform data values in Excel. In that case, you can take advantage of the numerous Excel functions, as well as built-in and third-party tools.
Excel functions
As for data transformation, Excel itself has a vast number of functions that allow you to transform data, change the spreadsheet’s appearance, perform various mathematical operations, and many others. These functions are more than enough to help you structure and organize your data in most simple cases.
Power Query
Power Query is a popular Excel tool used to extract, transform, and load data. It can be used as part of a self-service ETL solution to perform the following tasks:
- Extract data from the source.
- Transform your data to prepare it for analysis.
- Load the transformed data into a worksheet or data model.
Its massive popularity is in part due to its easy use. All steps are performed automatically, and the final results are recorded in a spreadsheet already familiar to you.
Power Pivot
This is an Excel-like user interface to a full-fledged SQL database installed on your computer and is a powerful tool for processing vast amounts of data. Power Pivot allows you to:
- Link imported spreadsheets by key columns.
- Filter and sort them.
- Perform mathematical and logical operations using more than 150 functions of the built-in DAX language.
Power View
Power View made its way into Excel 2013 from SharePoint. It primarily provides the user with tools for quickly creating live visual reports using pivot spreadsheets and database-based charts.
You can add totals to the report in a simple spreadsheet, a pivot spreadsheet, and various types of charts. Power View allows you to link data from spreadsheets even to Bing maps.
Coupler.io
Coupler.io is not a data transformation tool but a solution for importing data from different apps and sources to Excel. However, you can use it for data transformation purposes in many ways since it allows you to:
- Merge data from different spreadsheets.
- Select columns or a data range for import.
- Import queries (for specific data sources), and many more.
Check out the list of available Excel integrations and find the one you need.
Note: if you did not find the desired source, please fill out this form to let us know which application you would like to import data from.
What are the data transformation functions in Excel?
There are a considerable number of Excel data transformation functions. Here are 10 of the most popular ones:
| Function | Description |
|---|---|
| LOG10 | Allows you to calculate the logarithm to base 10. |
| ASIN | Allows you to calculate the arcsine. |
| SQRT | Allows you to calculate the square root. |
| ABS | Returns the absolute value of a number |
| LET | Assigns names to the results of calculations to allow storage of intermediate calculations, values, or name definitions in a formula. |
| ARABIC | Converts Roman numerals to Arabic numerals as a number. |
| DATE | Returns the sequential serial number that represents a particular date. |
| DAYS | Returns the number of days between two dates. |
| DEGREES | Converts radians to degrees. |
| ISO.CEILING | Rounds a number up to the nearest integer or multiple. |
Each function can perform one or more actions. You are unlikely to have to use all the existing functions. On average, one person uses 3 to 10 different functions, depending on what field of activity they work in.
Example of transforming data in Excel
One of the simplest examples of data transformation in Excel is removing duplicates. In Excel, you can do this either with Power Query if you’re getting data from an external source or with a button if your data set is already in Excel. Here is an example of a data set with duplicate records.
To remove these duplicates, click the Remove Duplicates button on the Data tab, then select the columns that contain duplicates.
There you go!
Examples of transforming data using Excel functions and tools
How to transform data into log in Excel?
Users working with stats or scientists or other researchers need to calculate the logarithm of a certain number. It can be quickly done if you use a calculator, but what if there is a large amount of data, and you need to do everything as soon as possible? Then, use log transform data in Excel. To convert the required data to a logarithm, you can use two functions: LOG and LOG 10.
- LOG10 is the function to return the base-10 logarithm of a number.
=log10(number)
- LOG is the function to return the logarithm of a number to the base you specify. The default base value is 10.
=log(number,base)
For example, here are two log transform formulas:
How to square root transform data in Excel?
To apply the square root transformation to a data set in Excel, you can use the SQRT function.
=SQRT(number)
For example:
How to make cube root Excel data transformation?
There is no dedicated function in Excel for cube root transformation. However, you can apply the following formula:
=[value]^(1/3)
For example:
According to this principle, you can perform root transformations of any degree, replacing 3 with the degree you need.
How to arcsine transform data in Excel?
The arcsine is the angle whose sine is the number. The arcsine transform can be used to stretch points between 0 and 1, for example, when working with proportions or fractions.
The arcsine transformation or the angular transformation is calculated as two times the arcsine of the square root of the proportion. To transform such data, use a combination of two functions: ASIN, which returns the inverse sine of a number, and SQRT.
=ASIN(SQRT())
For example, it looks like this:
The formula only works for the values in the range from 0 to 1. For example, it returned a #NUM! error for value 2. To avoid this, you will need to convert values to fit the 0 to 1 range. For this, divide each value by the max value into the data set using the following formula:
=[value]/MAX([data-set])
For example:
Nest this formula with the arcise one and you’ll get the proper result, for example:
=ASIN(SQRT(A2/MAX(A2:A6)))
How to transform data in Excel from columns to rows?
Use the transpose function if you have a worksheet with data in columns that need to be rotated to change its row order. With it, you can quickly switch data from columns to rows or vice versa.
Suppose you had a data set like this:
The transpose function rearranges the data set so that it looks like this:
To perform this transformation, you should select the range of data you want to reorder, including any row or column labels, and press Ctrl+C.
Next, you need to select a new location on the worksheet to insert the transposed data set (make sure you have enough space). The new data set that you insert here will completely overwrite any data/formatting already there.
Right-click the top-left cell to paste the transposed data set, then select Transpose.
Once you’ve transformed data in Excel, you can delete the original data set, and the data in the new one will remain intact.
How to transform into numeric data in Excel?
Sometimes, data in Excel is textual, which can lead to some problems. Fixing this is easy enough. You should select the cells and click on the diamond icon with an exclamation mark to select the conversion option to get started. You can perform such actions if this button is not available.
You should select the column where this problem occurs (if you don’t want to convert the entire column, you can select multiple cells instead).
On the Data tab, click Text to Columns.
The Text to Columns button is typically used to split a column, but it can also be used to convert a single column of text to numbers.
The remaining steps of the Text to Columns functionality are best for splitting a column. Since you’re just converting text in a column, you can click Apply right away, and Excel will convert the cells.
Press CTRL + 1 (or ⌘ + 1 on Mac). Then choose any format.
How to transform data range in Excel?
At any moment, you can convert the table to a normal range of data on a worksheet.
Right click on the table, select Table => Convert to range.
Main ideas for data transformation in Excel
The value of data transformation in Excel is difficult to overestimate. It can be used in various fields, such as sociology, mathematics, physics, and many other sciences. It solves several problems:
- Transforms data so that it is easier to do calculations
- Allows you to modify tables so that it is more convenient to work with them
- Simplifies data analysis
- Facilitates the creation of various reports and monitoring
Sometimes, it is impossible to perform calculations correctly since the values can be measured in different physical quantities (for example, radians and degrees), or vice versa; we need to perform some rounding. To not do all the conversions manually, you can use the special features of Excel. It allows you to save a lot of time, effort, and energy to deal with more complex and important processes, while computer technology is doing monotonous work.
Basic Excel data transformation rules
When transforming data in Excel, you need to remember the rules for using this spreadsheet:
- Any action can be reversed, so you should never panic.
- Using formulas when working with Excel simplifies working with a spreadsheet, but if you wish, you can do without them in some cases.
- If you work with Google Excel, there is no need to save permanently, but the interface and functions may differ slightly.
- Some add-ons may already be built into your version of the spreadsheet, while others may need to be downloaded. In the second case, use only trusted sites to avoid downloading a computer virus.
The above rules are not complex, and you can easily remember them even before working with a spreadsheet.
What to do if transform data in Excel fails?
In the process of data transformation, you may lose the data partly or entirely, and this may be very problematic. To mitigate losses, it is good to have a backup of the data.
You can store your backup copy in another Excel workbook stored locally or in the cloud. It would also be great to have copies in another spreadsheet app, for example, Google Sheets, or even in a data warehouse, such as Google BigQuery. You can easily automate backups of your data to any of these destinations using Coupler.io. The schedule for data refresh can be customized, which is more than awesome! Try Coupler.io for free and good luck with your data.
