In this article, we will see various techniques to transform data in **Excel**. We will explore some common **Excel** functions like **SUM**, **AVERAGE**, and **MAX**, which allow you to perform calculations on your data.

Weâ€™ll also look into **Power Query**, a handy tool for advanced data transformation, and learn how to use **VBA** macros for customized automation. Weâ€™ll provide practical examples of data transformation, like calculating **square roots**, **cubic roots**, **arcsine transformation**, and converting **columns** to **rows**.

Moreover, we will introduce you to the **Get and Transform** tool in **Excel**, which is designed specifically for data extraction, transformation, and loading (**ETL**) processes. We will explore the features and functionalities of this tool and show its usefulness in simplifying complex data transformation tasks.

Lastly, we will touch upon the ability to work with text strings in **Excel**, as manipulating and extracting information from text is important in data transformation.

**Download Practice Workbook**

Download the workbook here for practice.

**Table of Contents**Expand

## Introduction to Data Transformation

**Data transformation** in **Excel** means changing the way data looks without changing what it means. Itâ€™s like giving data a makeover so it can be used better. **Excel** lets you sort and filter data, so you can put it in order or choose only the parts you need.

Data transformation in **Excel** is important because it helps to make data accurate and organized. It also lets you bring together data from different places so it fits together nicely. When you transform data, it becomes easier to do calculations, make charts, and understand what the data is telling you.

The goal of data transformation in **Excel** is to make data easier to work with, so you can analyze it, make decisions, and share it with others. It gives you the power to make data look the way you want and makes sure itâ€™s in a good shape for whatever you want to do with it.

## Excel Features & Tools to Transform Data

In **Excel**, you can use different tools and functions to do things like removing columns, changing the type of data, or picking out specific rows. All of these actions are part of data transformation.

Itâ€™s impossible to cover all these functions and tools in a single article. So, we will explore some of them here.

### 1. Excel Functions to Transform Data

**1.1 Use of SUM Function**

You can use **the SUM function** to add multiple numbers in **Excel**. For example, I want to calculate the total marks using the below dataset.

- The formula I have used in
**G6**is,

`=SUM(D6:F6)`

- Then, use
**Fill Handle**to**AutoFill**up to**G15**.

**1.2 AVERAGE Function**

You can also get the average mark for any student in the above dataset. For this, you have to use **the AVERAGE function**.

- The formula in
**H6**is,

`=AVERAGE(D6:F6)`

- Then, use
**Fill Handle**to**AutoFill**up to**H15**.

**1.3 MAX Function**

In case you want to find out the maximum number a student has got, you can use **the MAX function**.

- The formula in
**I6**is,

`=MAX(D6:F6)`

- Then, use
**Fill Handle**to**AutoFill**up to**H15**.

### 2. Transform Data Using Power Query

Letâ€™s focus on **Power Query**, a smart tool that we use commonly to transform data in **Excel**.Â In the following dataset, I want to know who has scored the most number of goals. I can easily do that by using the **Sort** feature from **Power Query**.

- First, select the dataset
**>>**go to the**Data**tab**>>**select**From Table/RangeÂ**option.

- The
**Create Table**box will appear. - Mark
**My table has headers**and click**OK**.

**Excel**will open the**Power Query Editor**.- To sort the table based on the goal numbers, select the
**drop-down**shown in the image.

- Then, select the
**Sort Descending**option**>>**click**OK**.

**Excel**will sort the data.- Now, to transform the table into an
**Excel**table, click the**Close & LoadÂ**option.

**Excel**will create a new table in a separate workbook.

### 3. Apply VBA Macro to Transform Data in Excel

Perhaps, the most interesting way to work with data is to apply **VBA** macro.

Letâ€™s see how you can perform the sorting operation in the previous dataset using **VBA**.

- Press
**ALT + F11**to open the**VBA**window. - Then go to
**Insert****>>**select**Module**.

- A
**new module**will open. Write down the following code.

```
Sub SortRangeDescending()
Dim ws As Worksheet
Dim rng As Range
Set ws = ThisWorkbook.Worksheets("Macros and VBA")
Set rng = ws.Range("B5:D14")
With rng
.Sort Key1:=.Columns(3), Order1:=xlDescending, Header:=xlNo
End With
End Sub
```

**Code Breakdown**

```
With rng
Â Â Â Â Â Â Â .Sort Key1:=.Columns(3), Order1:=xlDescending, Header:=xlNo
Â Â Â End With
```

- Here, the
**With**statement is used to apply a sort operation to the range variable**rng**. The code sorts the range based on the**third column**(**Key1:=.Columns(3)**) in**descending**order (**Order1:=xlDescending**), without considering the first row as a header (**Header:=xlNo**).

- Now, press
**F5**to run the code.

## Examples of Data Transformation in Excel

### 1. Calculate Square Root of a Number

You can calculate the square root of any number using **the SQRT function**. I am going to calculate the square roots of some numbers in this section.

- The formula in
**C5**is,

`=SQRT(B5)`

- Then, use
**Fill Handle**to**AutoFill**up to**C13**.

### 2. Calculate Cubic Root of a Number

In a similar fashion, we can get the cubic root of any number. Here, I am going to use the **caret** (**^**) symbol.

- The formula in
**D5**is,

`=B5^(1/3)`

- Then, use
**Fill Handle**to**AutoFill**up to**D13**.

You can calculate the **Nth** root of any number by using this formula. Replace **3** with **N** in this formula.

### 3. Arcsine Transform in Excel

The arcsine, denoted as **sin^(-1)** or **asin**, is a mathematical function that represents the inverse of the sine function. It is used to find the angle whose sine is a given value. In other words, if you have a number between **-1** and **1**, the arcsine function will give you the angle (in **radians**) that produces that sine value.

We will use **the ASIN function **to perform the Arcsine transformation of a number in **Excel**.

- The formula in
**D5**is,

`=ASIN(C5)`

- Then, use
**Fill Handle**to**AutoFill**up to**D9**.

### 4. Transform Data from Column to Row

In case you want to alter the orientation of your dataset, you can easily do it by transposing your data.

- Select the range
**B4:F8**to transpose**>>**press**CTRL + C**to copy the dataset.

- Then, select cell
**B10**to paste the transposed data. - Right-click to get the
**Context menu**Â**>>**select the**Transpose**icon operation shown in the image.

**Excel**will transpose the range.

## Get and Transform Tool in Excel

The **Get and Transform** tool is a data transformation and data extraction feature in **Excel**. It provides a powerful set of tools and functionalities that allow users to connect to various data sources, import data, and transform it for analysis and reporting purposes.

Once the data is imported, the **Get and Transform tool** allows users to apply various transformations to cleanse, shape, and manipulate the data.

### Uses of Get and Transform Tool

The **Get and Transform** tool in Excel, also known as **Power Query**, offers several practical uses and benefits for data analysis and data preparation. Here are some key applications:

**Data Import and Connection:****Get and Transform**allows you to connect to various data sources, such as databases, spreadsheets, web pages, and online services.**Data Transformation and Cleaning:**This tool enables you to perform data transformations and cleaning operations easily. You can remove duplicates, filter rows based on specific criteria, split or merge columns, change data types, perform calculations, and apply custom transformations to shape the data according to your requirements.**Combining and Merging Data:****Get and Transform**allows you to combine data from multiple sources or merge tables based on common columns. This is useful when dealing with data stored in different files or databases, and need to consolidate and analyze it together.**Data Preparation for Analysis:****Get and Transform**helps you prepare data for analysis by cleaning, transforming, and structuring it in a way that facilitates easy analysis.**Automating Data Refresh and Updates:**Once a connection is established, you can schedule automatic data refreshes from the original data source. This allows you to keep your analysis up to date without the need for manual data updating.

### Load and Transform CSV Files in Excel

A **CSV** file, which stands for **Comma-Separated Values**, is a plain text file that stores tabular data in a structured format. In this section, I will show how you can import a **CSV** file using the **Get and Transform** tool.

- Go to the
**Data**tab**>>**select**From Text/CSVÂ**option.

**Excel**will open a**Dialogue box**named**Import Data**.- Browse and select the
**CSV**file you want to transform**>>**click the**ImportÂ**button.

**Excel**will open a**Query**window. Select**Transform Data**button.

- You will see that a
**Power Query Editor**appears with the contents of the**CSVÂ**file.

- Now, you can transform this into an
**Excel**file just like we have shown in the previous method.

### Working with Text Strings

Sometimes, you have to deal with text data in **Excel**. The **Get and Transform** tool comes in handy in such cases also. Suppose you have a dataset that contains the **Player ID** and **Name** in a cell, separated by a comma.

Letâ€™s split them using the **Get and Transform** tool.

- First, select the
**range B4:C14****>>**go to the**Data**tab**>>**select**From Table/Range**.

- A
**Create Table**box will appear. - Mark
**My table has headers**option and click**OK**.

**Excel**will open the**Power Query Editor**.- To split a column, select the column first
**>>**click**Split Column**option**>>**choose**By DelimiterÂ**option.

- A box will pop up.
- Choose the
**delimiter****>>**click**OK**.

- You will get the column splited.
- Rename the newly created columns. This will look like this.

- Now, follow the procedure shown in the previous section to transform the dataset into an
**ExcelÂ**worksheet.

## Things to Remember

**Power Query**is available in**Excel 2010**and later versions, and it is integrated into**Power BI**.**CSV**files store data in a plain text format with values separated by commas or other specified delimiters.- Be cautious with transposing large data sets, as it can affect the structure and formatting of your data.

## Frequently Asked Questions

**1. How important is working with text strings in data transformation?**

**Ans:** Working with text strings is crucial in data transformation as it allows you to extract and manipulate information from text-based data, such as extracting specific words, splitting text into columns, or combining text values.

**2. How is Power Query different from Excel functions?**

**Ans:** **Power Query** is specifically designed for data transformation and offers a more robust and scalable approach compared to traditional **Excel** functions. It provides a user-friendly interface and advanced capabilities for handling large datasets, complex transformations, and merging data from multiple sources.

**3. Can I automate data refresh in Power Query?**

**Ans:** Yes, **Power Query** allows you to automate data refresh so that your queries stay up to date. You can schedule refreshes at specific time intervals or set them to refresh upon opening the workbook or on-demand.

## Transform Data in Excel: Knowledge Hub

**Get and Transform Data****Difference Between Load and Transform Data****Transform Data to Normal Distribution****Arcsine Transform Data in Excel**

## Conclusion

To sum it up, **Excel** provides easy and simple techniques to transform data in Excel. In this article, we explored helpful **Excel** functions for performing calculations.

We also discovered **Power Query**, a useful tool for advanced data transformation, and learned about **VBA** macros for custom automation. Through practical examples, we learned how to calculate **square roots**, **cubic roots**, perform **arcsine transformations**, and **convert columns to rows**.

Additionally, we explored the **Get and Transform** tool, which simplifies data extraction and loading. Lastly, we emphasized the importance of working with **text strings** in data transformation.Â By using these techniques, you can utilize the power of **Excel** to transform your data effortlessly.

**<< Go Back toÂ Power Query ExcelÂ |Â Learn Excel**