Transform Data in Excel: A Visual Guide

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.

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,

• 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,

• 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,

• 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

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

• 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,

• 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,

• 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,

• 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