Graphed LogoGraphed
Login

How to Manipulate Data in Excel

Cody Schneider•

Sorting, cleaning, and transforming data in Excel can feel like a chore, but it's the foundation of any meaningful analysis. This guide skips the theory and gives you the practical, step-by-step techniques you need to turn messy raw data into a clean, organized, and analysis-ready dataset. We'll cover everything from simple sorting and filtering to powerful functions like XLOOKUP and the game-changing capabilities of PivotTables.

What is Data Manipulation in Excel?

Data manipulation is simply the process of reorganizing raw data to make it easier to read and analyze. Think of it as tidying up a messy room before you decide where everything goes. In Excel, this involves tasks like:

  • Removing duplicate entries or unwanted spaces.

  • Combining or splitting columns.

  • Filtering out irrelevant information.

  • Summarizing large datasets to find key trends.

  • Standardizing formats (like dates or text).

Mastering these skills means you spend less time wrestling with data and more time finding valuable insights.

Sorting and Filtering: Your First Steps in Organization

Before you do any heavy-duty analysis, you need to bring some order to your data. Sorting and filtering are the quickest ways to see what you're working with.

Simple Sorting

Sorting arranges your data alphabetically (A-Z, Z-A) or numerically (Smallest to Largest, Largest to Smallest). This is great for quickly identifying top performers or outliers.

How to do it:

  1. Click any cell inside the column you want to sort.

  2. Go to the Data tab.

  3. In the "Sort & Filter" group, click the AZ icon for ascending order or the ZA icon for descending order.

Multi-Level Sorting

Sometimes you need to sort by more than one column. For example, you might want to sort sales data first by Region (alphabetically) and then by Revenue (largest to smallest) within each region.

How to do it:

  1. Click anywhere within your data range.

  2. Go to the Data tab and click the large Sort icon.

  3. In the dialog box, set your first sorting level. For our example, "Sort by" would be Region and "Order" would be A to Z.

  4. Click Add Level.

  5. Set your second sorting level. Here, "Then by" would be Revenue and "Order" would be Largest to Smallest.

  6. Click OK. Excel will now apply both sorting rules.

Filtering Data to Focus on What Matters

Filtering lets you temporarily hide rows you don't need, so you can focus on a specific subset of your data. For instance, you could filter a marketing campaign report to see only the results from "Facebook Ads."

How to do it:

  1. Select a cell within your data.

  2. Go to the Data tab and click Filter. Dropdown arrows will appear in each of your column headers.

  3. Click the dropdown arrow for the column you want to filter (e.g., "Campaign Source").

  4. Uncheck "(Select All)" and then check the box next to the value you want to see (e.g., "Facebook Ads").

  5. Click OK. Excel will hide all other rows.

Cleaning and Standardizing Data with Text Functions

Raw data is rarely perfect. Names might be in single columns, text might have unwanted spaces, or casing might be inconsistent. Text functions fix these issues fast.

Splitting Text into Separate Columns

The "Text to Columns" wizard is perfect for splitting data from one cell into multiple cells, like separating a full name into "First Name" and "Last Name" columns.

How it works:

  1. Select the column containing the text you want to split.

  2. Go to the Data tab and click Text to Columns.

  3. Choose Delimited if your text is separated by a character like a space, comma, or tab. Click Next.

  4. Select the delimiter that separates your text (e.g., check the box for "Space"). You'll see a preview of how the data will be split. Click Next.

  5. Choose a destination for your new columns and click Finish.

Combining Data with CONCATENATE and the Ampersand (&)

Need to do the opposite and merge columns? You can combine text from multiple cells into one with the CONCATENATE function or the even simpler & operator.

Let's say First Name is in A2 and Last Name is in B2.

Using CONCATENATE: =CONCATENATE(A2, " ", B2)

Using the & Operator (preferred for simplicity): =A2 & " " & B2

Both formulas produce "John Smith". The " " adds a space between the names.

Removing Messy Spaces and Fixing Capitalization

Inconsistent formatting can ruin your analysis. Here are three functions to standardize your text fields:

  • TRIM: This function removes extra leading or trailing spaces and reduces multiple spaces between words to a single space.

=TRIM(A2)

  • UPPER, LOWER, and PROPER: These functions change the case of your text.

    • UPPER converts all text to uppercase (e.g., JOHN SMITH).

    • LOWER converts all text to lowercase (e.g., john smith).

    • PROPER capitalizes the first letter of each word (e.g., John Smith).

Pro-tip: Create a new helper column to apply these formulas, then use "Paste Special" > "Values" to replace the original messy data with the new clean version.

Automating Analysis with Formulas

Formulas are the engine of data manipulation in Excel. Mastering a few key ones can automate calculations and logical decisions that would otherwise take hours.

Conditional Logic with the IF Function

The IF function performs a logical test and returns one value if the result is true, and another if it's false. It's great for categorizing data.

Syntax: =IF(logical_test, value_if_true, value_if_false)

Example: Let's say you want to categorize sales in cell B2. If the value is over $1,000, you want to label it "Large," otherwise, "Small."

=IF(B2>1000, "Large", "Small")

Summing and Counting with Criteria: SUMIFS and COUNTIFS

These functions let you sum or count data based on one or more conditions.

  • SUMIFS adds up cells that meet multiple criteria.

  • COUNTIFS counts the number of cells that meet multiple criteria.

Example: You have a sales report with "Sales Rep" in column A, "Region" in column B, and "Sale Amount" in column C. To calculate the total sales for "Sarah" in the "North" region:

=SUMIFS(C:C, A:A, "Sarah", B:B, "North")

Merging Data Tables with VLOOKUP and XLOOKUP

One of the most powerful things you can do in Excel is pull data from one table into another based on a matching value. VLOOKUP has been the classic tool for this, but the newer XLOOKUP is more flexible and powerful.

When to use it: You have a list of sales transactions with an Employee ID, but you need to add the Employee Name, which lives in a separate HR roster. You can use their shared Employee ID to connect the two.

While VLOOKUP is still common, we'll focus on XLOOKUP because it's simpler and avoids the common pitfalls of its predecessor.

XLOOKUP Syntax: =XLOOKUP(lookup_value, lookup_array, return_array)

Example: Let's say you have an employee ID in cell A2 of your sales sheet. The master list of IDs is in the 'HR' sheet in column A, and the names are in column B.

In your sales sheet, the formula would be:

=XLOOKUP(A2, HR!A:A, HR!B:B)

This formula looks for the ID from A2 within column A of the HR sheet and returns the corresponding name from column B. It's much more intuitive than VLOOKUP and can even look to the left!

Summarizing Data Effortlessly with PivotTables

If you're manipulating data in Excel, PivotTables are your best friend. They allow you to rapidly summarize, group, categorize, and analyze vast amounts of data just by dragging and dropping fields - no complex formulas needed.

How to create a PivotTable:

  1. Click any cell in your source data table.

  2. Go to the Insert tab and click PivotTable. Excel will automatically select your data range and suggest putting the PivotTable in a new worksheet. Click OK.

  3. A blank PivotTable and a "PivotTable Fields" list will appear on the right. This list contains all the columns from your data.

  4. Drag and drop these fields into the four areas at the bottom of the pane:

    • Filters: Filter your entire report by a specific field (e.g., Year).

    • Columns: Creates columns in your PivotTable (e.g., Product Category).

    • Rows: Creates rows in your PivotTable (e.g., Region).

    • Values: The bucket for fields you want to perform a calculation on, like sum, count, or average (e.g., a "Revenue" or "Quantity" field).

For example, to see total sales by region, you would drag the "Region" field into the Rows area and the "Sales Amount" field into the Values area. Excel instantly aggregates all the sales data for you.

Final Thoughts

Excel provides an incredibly robust set of tools for cleaning, transforming, and organizing your data for analysis. From simple sorting and reliable text functions to the powerful aggregations of formulas and PivotTables, you can turn any raw data export into a clean and insightful report by mastering these data manipulation techniques.

However, spending hours wrangling CSV files in Excel every week can become draining. The process is manual and prone to errors, and by the time you're done, the data might already be outdated. We built Graphed to eliminate this friction. By directly connecting your data sources like Google Analytics, Shopify, or Salesforce, you can skip the manual exporting and cleaning process completely. You simply ask in plain English for what you want to see, and we instantly build a real-time dashboard for you, turning hours of data manipulation into a 30-second task.