How to Reformat Data in Excel

Wrangling messy data in Excel is a challenge almost everyone has faced, turning what should be a straightforward analysis into a tedious cleanup job. If you've ever tried to create a chart from inconsistently formatted data or run formulas that keep returning errors, you know the frustration. This article will walk you through the most powerful and efficient ways to reformat data in Excel, from simple text functions to more advanced automation tools.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Reformat Your Data in the First Place?

Reformatting might seem like a chore, but it's the foundation of any reliable analysis. Clean, well-structured data is essential for a few key reasons:

• Accuracy: Proper formatting ensures your formulas, pivot tables, and charts work correctly. Calculating totals or averages is impossible when numbers are stored as text or there are hidden spaces.
• Consistency: Uniform data is easy to read, filter, and sort. Consistent date formats, capitalization, and naming conventions prevent duplicate entries and make your dataset professional and manageable.
• Efficiency: Investing a little time upfront to clean your data saves you countless hours of troubleshooting later. A properly formatted dataset makes analysis much faster.

Common Data Formatting Headaches in Excel

Before jumping into solutions, let's identify the usual suspects. Most formatting problems fall into a few common categories:

• Extra Spaces: Leading, trailing, or double spaces between words can quietly break lookups and filters (e.g., " New York" vs. "New York").
• Inconsistent Casing: Mismatched capitalization ("new york", "New York", "NEW YORK") causes Excel to treat them as different items.
• Numbers Stored as Text: This classic issue prevents you from performing mathematical calculations. It often happens when data is exported from other systems.
• Mixed-Up Dates: Dates entered in various formats (MM/DD/YYYY, DD-MM-YY, Month DD, YYYY) can't be sorted or analyzed chronologically.
• Merged Cells: While they might look neat, merged cells are a nightmare for sorting, filtering, and applying formulas to a range.
• Clumped Together Data: Important information like first name and last name, or city and state, is often stuck together in a single column.

Let's fix these problems step-by-step.

Essential Tools for Reformatting Data in Excel

Excel has a fantastic set of built-in tools designed to fix exactly these kinds of issues. Mastering them will transform the way you handle data.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

1. Fix Data Types with ‘Format Cells’

The ‘Format Cells’ dialog box is your headquarters for changing how Excel displays and interprets data. If your numbers are acting like text or your dates look like random integers, this is your first stop.

How to use it:

1. Select the cells or column you want to reformat.
2. Right-click and choose Format Cells, or use the keyboard shortcut Ctrl + 1.
3. In the ‘Number’ tab, choose the correct category on the left.

Example: Converting Text to Numbers Sometimes you'll see numbers aligned to the left with a small green triangle in the corner, indicating they are stored as text. To fix this, select the cells, click the little warning icon that appears, and choose ‘Convert to Number’. If that doesn't work, use the Format Cells dialog to change the category to Number or General.

2. Clean Up Messy Text Data

Excel offers several functions and features specifically for standardizing text. You can use these in a new helper column to generate a clean version of your original data.

TRIM: Remove Extra Spaces

The TRIM function is your best friend for getting rid of those pesky leading, trailing, and double spaces between words.

• Formula: =TRIM(A2)
• How it works: If cell A2 contains " Product A ", this formula will return "Product A". Simply type the formula in an adjacent cell and drag it down to apply it to your entire list. Then, copy and "Paste Values" of the new column over the original if needed.

UPPER, LOWER, and PROPER: Standardize Case

Inconsistent capitalization can ruin sorting and filtering. These three functions solve that in a second.

• UPPER: Converts all text to uppercase (e.g., "new york" becomes "NEW YORK"). Formula: =UPPER(A2)
• LOWER: Converts all text to lowercase (e.g., "New York" becomes "new york"). Formula: =LOWER(A2)
• PROPER: Capitalizes the first letter of each word (e.g., "new york" becomes "New York"). This is perfect for names. Formula: =PROPER(A2)

Flash Fill: Let Excel Handle the Pattern

Flash Fill (available from Excel 2013 onwards) is a game-changer. It automatically recognizes patterns in your data entry and completes the work for you. It's ideal for extracting information or combining it in new ways.

Example: Splitting Full Names

1. Suppose you have full names in column A (e.g., "John Smith"). In column B, type the first name you want to extract ("John").
2. Move to the cell below it (B3). Start typing the next first name ("Jane").
3. Excel will instantly show a faint preview of all the other first names extracted down the column. Press Enter to accept the suggestion. It’s that easy.

You can use Flash Fill for many things, such as extracting street names from full addresses, formatting phone numbers, or combining first and last names.

Text to Columns: Split a Single Column into Several

When you have a column where data is separated by a specific character (like a comma, tab, or space), the 'Text to Columns' feature is the perfect tool for splitting it up.

Example: Splitting Comma-Separated Values

1. Select the column containing the data you want to split (e.g., a column with "City, State, Zip").
2. Go to the Data tab and click Text to Columns.
3. Choose Delimited because your data is separated by a comma. Click Next.
4. In the next step, select the delimiter your data uses - in this case, Comma. You’ll see a preview of how the data will be split. Click Next.
5. Finally, choose the destination for your new columns and click Finish. Excel will split the original column into three separate columns for City, State, and Zip code.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

3. Restructure Your Data’s Layout

Sometimes the issue isn't the data itself, but the way it's laid out on the sheet. Here's how to fix common structural problems.

Transpose: Flip Rows and Columns

Ever get data where the headers are in a column and the values are in rows, when you need it the other way around? Transpose lets you swap them.

1. Select the entire range of data you want to flip.
2. Copy it (Ctrl + C).
3. Right-click on a new, empty cell where you want the transposed data to start.
4. Go to Paste Special, and in the dialog box, check the Transpose box at the bottom right. Click OK.

Unmerge Cells and Fill Blanks

Merged cells create hidden problems. The data only truly exists in the top-left cell of the merged range, leaving the others blank. This breaks sorting, filtering, and formulas.

How to fix it:

1. Highlight the range with the merged cells.
2. On the Home tab, click the Merge & Center button to unmerge all cells. This will leave gaps in your data.
3. With the range still highlighted, press F5 to open the ‘Go To’ dialog. Click Special....
4. Select Blanks and click OK. Excel will now select only the empty cells in your range.
5. Without clicking anywhere else, type the equals sign “=”, then press the Up Arrow key, then hold Ctrl and press Enter. This tells each blank cell to equal the value of the cell directly above it, filling in all the gaps instantly.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

4. For Recurring Tasks, Use Power Query

If you find yourself performing the same re-formatting steps every week or month, it's time to meet Power Query. It’s an incredibly powerful data transformation engine built directly into modern versions of Excel (Go to Data > Get & Transform Data).

With Power Query, you can perform all the actions mentioned above - and many more - by recording your steps. When new data arrives, you just hit "Refresh" and it instantly runs through all your pre-defined formatting rules again. It's a massive time-saver for anyone dealing with regular reports.

Final Thoughts

By learning just a few key features like Format Cells, text functions (TRIM, PROPER), Flash Fill, and Text to Columns, you can take control of any messy dataset. These tools are straightforward to use and will dramatically cut down the time you spend on manual data cleanup, freeing you up to focus on the analysis itself.

For those of us working with data from multiple sources like Google Analytics, Shopify, or Facebook Ads, the process of downloading and reformatting spreadsheets is a constant drag. That’s why we built Graphed to automate this entirely. By connecting directly to your platforms, we pull in clean, real-time data so you can skip the manual formatting completely and get instant dashboards and reports just by asking a question.