How to Do Data Transformations in Excel: A Beginner's Guide

MS Excel is a spreadsheet editor developed by Microsoft. It is a powerful tool that can help you store and manage large volumes of structured data, including mathematical and statistical information. Apart from its basic functions, Excel also offers various features that allow you to execute complex data transformations and gain valuable insights.

The platform's user-friendly interface enables quick adaptability and a smoother learning curve. With Excel, you can organize, format, and visualize data for deeper analysis and reporting. This article provides a beginner’s guide on how to do data transformations in Excel while exploring its benefits. It also discusses the other alternatives you can leverage to achieve similar or better outcomes.

What Is Data Transformation in Excel?

Data transformation in Excel is the process of modifying your raw data using the tool and making it more accessible for further downstream processes. This involves cleaning, restructuring, and reformatting data to improve data quality and consistency.

You can use Excel to merge or split columns, remove duplicates, aggregate values, and pivot data to view it from different perspectives. Implementing these transformation techniques not only improves data readability but also ensures its accuracy and reliability.

Benefits of Performing Data Transformation in Excel

By learning how to transform data in Excel, you can considerably increase the productivity and efficiency of your data workloads. Some benefits of performing data transformation in Excel include:

• Ease of Use: Excel’s user-intuitive interface allows even beginners to navigate the platform easily and implement complex transformations.‍
• Data Pivoting: Pivoting data enables you to summarize and rearrange large datasets within a spreadsheet by grouping and aggregating them in different ways.‍
• Data Cleaning: Excel provides functions like TRIM, CLEAN, and UPPERCASE to help you eliminate inconsistencies and errors in data.‍
• Customizability: You have the flexibility to utilize Excel to create custom formulas and transformations based on your specific data needs.‍
• Integration: You can integrate Excel with other platforms and support various applications by facilitating quick data imports and exports.‍
• Data Visualization: With Excel's charting capabilities, you can generate visual representations of data and easily recognize trends, patterns, and outliers.‍
• Automation: You can automate repetitive data transformations by using macros and Power Query. This saves your time and effort while minimizing the risk of human error.
• ‍Data Consolidation: Excel enables you to combine data from multiple sources into a single worksheet using Power Query or pivot tables, simplifying data management.

Tools for Excel Data Transformation

Excel offers several built-in data transformation tools and functions to help you clean, organize, and manipulate data according to your specific needs. Below are the top three tools that you can use to learn how to do data transformations in Excel effectively:

Power Query

Power Query in Excel is used to perform ETL (extract, transform, load) operations. You can connect various data sources like CSV files, online services, or web pages, apply transformations, and load them into a worksheet or data model. Power Query also provides drag-and-drop functionalities for ease of use.

You can follow the steps below to get started with Power Query:

• Open the Excel editor and click on the Data tab. Then, click on the Get Data option available in the Get & Transform Data section.

• Select the source from which you want to extract data. Currently, around 40 data sources are available for you to choose from.

• Once you select the file you want to import, you will see the preview of that data. Then, click on the Transform Data button. This will redirect you to another interface—Power Query Editor—where you can begin transforming your data before loading it into a new worksheet.

Pivot Tables

Pivot tables are summarization tools in Excel that help you with data comparison and analysis. You can quickly group, sort, count, add, find averages, and perform other calculations on your data to generate simple graphs and charts for your report.

To create a pivot table, you can follow the steps below:

• Select your data, click the Insert tab, and choose Pivot Table from the Tables group.

• You will see a pop-up window, as shown in the image above. The Table/Range field will automatically display the range of your data or the table's name.
• Ensure the editor correctly identifies your data, then choose the location for your new pivot table.
• After you click the OK button, a blank pivot table will be created at the specified location.
• You can use the PivotTable Fields pane to select the fields you want to add to your report and organize them by rows, columns, values, and filters.

Excel Functions

Excel functions are pre-built formulas that allow you to perform calculations by taking inputs, processing them based on pre-defined rules, and returning a corresponding result. The spreadsheet editor provides arithmetic, date and time, statistical, and financial functions to support your data transformation and analysis needs. You will find these functions under the Formulas tab.

Performing Data Transformations in Excel

Now that you have familiarized yourself with the various tools available for data modification, let’s explore some practical examples of how to transform data in Excel using them.

How to Transform Text Case in Excel?

To change the case of text, you can use the following Excel functions:

• UPPER: Converts text to uppercase.‍
• LOWER: Converts text to lowercase.‍
• PROPER: Capitalizes the first letter of every word.

• Select an empty cell where you want the transformed text to appear.
• Type the formula UPPER(), click on the cell whose text case you want to change, and hit the enter key. This will change the text to upper case.

• When you hover on this cell, you will see a plus mark. Click on it and drag it to the cell you want to change the text case.

You can use the LOWER() and PROPER() functions in the same way as mentioned above.

How to Transpose Data in Excel?

Transposing data in Excel involves swapping the rows into columns and vice-versa. 

While you can use the paste special method to transpose your data (as shown in the image above), the TRANSPOSE() function enables dynamic data transposition. This implies that as your source data changes, your transposed data will be updated accordingly.

To use this function, first, highlight the empty cell range so that the number of columns and rows from your source data are flipped. Then click on the formula bar and type TRANSPOSE(), select your source data, and press Control + Shift + Enter keys (because it’s an array formula). This will transpose your data.

How to Transform Text to Dates in Excel?

If your data contains dates mentioned using a delimiter other than ‘/’ (forward slash) and ‘-’ (dash), Excel will not recognize them as dates. Another way of identifying if the dates are in text format is to check the alignment. The dates in Excel are right-aligned by default, and the value in the formula bar will not have an apostrophe leading it.

To convert text data into dates, select the text-formatted dates and click the Text to Columns feature present in the Data tab. This will open a wizard. Then, follow the steps mentioned in the images below:

Once you click on Finish, your text-formatted dates will be transformed into dates identifiable by Excel.

How to Remove Duplicates in Excel?

To perform data deduplication in Excel, you can implement the following steps:

• First, select your target data and then click on the Remove Duplicates feature in the Data Tools section of the Data tab.
• When the pop-up window appears, choose the columns that have duplicate values.
• Then, click on the OK button.
• Excel will now automatically identify and remove duplicate entries and display this information in a dialog box.
• You can click on OK and continue working on the editor.

How to Split Text into Columns?

When you import data into Excel from other applications, you might come across situations where too much information gets clubbed together in a single cell. This makes it difficult to perform downstream data analysis. By following this step-by-step guide on how to transform data in Excel, you will be able to split your text data into different columns.

Excel offers an intuitive tool, Flash Fill, that recognizes the patterns in which you want the data and populates the rest of the cells based on the samples you provide. To access this feature, you can press Ctrl + E or find it under the Data tab.

All you need to do is provide samples of which data you want in a particular column, click on Flash Fill, and hit Enter.

How to Filter Data in Excel?

Filtering data in Excel allows you to view only the rows that meet specific criteria, enabling you to focus only on relevant information within large datasets. Filtering data helps you identify trends and isolate records without changing the original data. You can follow the steps below to filter data in Excel:

• Select the required columns and click on the Filter icon available in the Data tab.
• Tap on the header arrow that appears beside the column header.
• You can find either text filters or number filters based on your data.

• Choose specific criteria such as ‘greater than,’ ‘below average,’ or ‘filter by color’ and enter appropriate values in the subsequent window that appears.
• Click on OK.

Excel Data Transformation Alternatives

While MS Excel is a widely used tool, there are other options that you can explore. These alternatives might offer additional functionality and flexibility to implement your data transformations.

Python

Libraries like Pandas allow you to work with efficient data structures and manipulation functions. With NumPy, you can optimize numerical operations and perform mathematical calculations and statistical analysis. For more advanced analysis and machine learning processes, libraries like Scikit-learn and TensorFlow can be integrated with Python to implement complex data transformations and modeling.

Airbyte

Airbyte is an AI-enabled data integration tool that you can use instead of Excel. It offers extensive features compared to Excel, making it a more efficient and effective platform for handling complex data operations. Airbyte’s open-source Python library, PyAirbyte, provides a set of utilities for using Airbyte connectors in Python and reduces the need for costly and error-prone custom ETL coding.

With PyAirbyte, you can extract data from hundreds of sources and load it into SQL caches such as DuckDB, Postgres, Snowflake, and BigQuery. This cached data is compatible with Python libraries (Pandas), SQL-based tools, and AI frameworks (LangChain, LlamaIndex), which provide a lot of scope for performing data transformations.

Some key features of Airbyte include:

• Custom Connector Development: Apart from the 550+ pre-built connectors, Airbyte provides the flexibility to build connectors from scratch using Connector Builder, a low-code Connector Development Kit (CDK), Python CDK, or Java CDK. The AI assistant available in the Connector Builder can help you speed up pipeline development. This helps you achieve faster data consolidation.
• ‍Data Transformation: Airbyte allows you to load unstructured and semi-structured data directly into vector data stores, streamlining your GenAI workflows. By integrating the platform with LLM frameworks (LangChain, LlamaIndex), you can perform RAG-specific techniques like automatic chunking, indexing, and embedding. You can also integrate Airbyte with dbt Cloud to create and run custom dbt transformations.
• ‍Refresh Syncs: Using refresh syncs, you can synchronize your data without creating any downtime. Airbyte supports two modes of data syncs: incremental and full refreshes. The former enables you to append data, while the latter lets you append and overwrite data completely. 

Airbyte has also announced the general availability of the Self-Managed Enterprise edition, which offers highly scalable data ingestion capabilities and centralized control over sensitive data. For more information about how you can benefit from incorporating Airbyte into your data flows, you can talk to experts or refer to the official documentation.

Final Thoughts

Data transformation is an important step in the data analysis process, and Excel provides a user-friendly interface for implementing it. By understanding how to transform data in Excel utilizing tools like Power Query, Pivot Tables, and Excel functions, you can effectively clean, organize, and analyze your data.

This article on how to do data transformations in Excel provides practical examples to help you interact comfortably with the editor. It also introduces alternative solutions, such as Airbyte and Python. Airbyte, in particular, when integrated with dbt Cloud, allows you to implement custom data transformations. Additionally, its Python library, PyAirbyte, enables you to streamline the development of ETL pipelines.

By understanding your requirements and leveraging the appropriate tools, you can optimize your data workflows and make data-driven decisions.