PDF to Excel Conversion: How to Fix 7 Common Formatting Issues

You convert a PDF to Excel, open the file, and immediately spot the problems: columns are misaligned, cells are merged incorrectly, dates look like random numbers, and some data is in entirely the wrong place. Formatting issues are the most common — and most frustrating — aspect of PDF-to-Excel conversion. The data is there, but it needs cleanup before it's usable.

This guide covers the seven most common formatting problems after PDF-to-Excel conversion and provides exact, step-by-step fixes for each one. Every fix can be done in Excel in under two minutes.

Issue 1: Merged Cells Combining Multiple Columns

The problem: Data from two or three columns is crammed into a single merged cell. For example, "John Smith" and "Engineering" appear together in one cell instead of separate columns.

The fix:

  1. Select the merged cells
  2. Go to Home tab → Merge & Center (click to toggle off, unmerging the cells)
  3. If the data is still in one cell, use Data tab → Text to Columns
  4. Choose "Delimited" → select the separator (space, comma, tab) → Finish
  5. The data splits into separate columns

Issue 2: Broken Columns — Data Split Across Two Columns

The problem: Data that should be in one column is split across two. For example, a phone number "(555)" is in column A and "123-4567" is in column B.

The fix:

  1. Insert a new column next to the broken columns
  2. Use a concatenation formula: =A2&" "&B2 to combine them
  3. Drag the formula down to apply to all rows
  4. Copy the new column → Paste Special → Values to make it permanent
  5. Delete the original broken columns

Issue 3: Numbers Stored as Text

The problem: Numbers appear left-aligned in cells. The SUM function returns 0 or ignores them. A small green triangle appears in the cell corner. This is the most common formatting issue.

The fix (fastest method):

  1. Select the entire column (click the column header)
  2. Go to Data tab → Text to Columns
  3. Click "Finish" immediately (don't change any settings)
  4. Excel re-evaluates every cell and converts numeric text to actual numbers

Alternative fix: Select the column → click the green triangle warning → choose "Convert to Number."

Issue 4: Dates Displaying as Five-Digit Numbers

The problem: Dates appear as numbers like "45658" instead of "1/15/2025." The dates are actually stored correctly — Excel uses serial numbers internally — but the display format is set to "General" or "Number" instead of "Date."

The fix:

  1. Select the date column
  2. Right-click → Format Cells → Date
  3. Choose your preferred date format (e.g., MM/DD/YYYY)
  4. Click OK — the serial numbers display as proper dates

Issue 5: Dates Stored as Text (Won't Format as Dates)

The problem: Unlike Issue 4, these cells resist formatting changes. You change the format to "Date" but the display doesn't change. The dates are stored as text strings.

The fix:

  1. In an empty column, use the DATEVALUE function: =DATEVALUE(A2)
  2. Drag the formula down
  3. Format the new column as Date (right-click → Format Cells → Date)
  4. Copy → Paste Special → Values over the original column

Alternative: Data → Text to Columns → Step 3: select "Date" and specify the source format → Finish.

Issue 6: Extra Blank Rows and Columns

The problem: The conversion introduced blank rows between data rows, or blank columns between data columns. These break sorting, filtering, and chart ranges.

The fix for blank rows:

  1. Select any column in your data
  2. Data → Filter
  3. Click the filter dropdown → uncheck everything except "Blanks"
  4. Select all visible (blank) rows → Right-click → Delete Row
  5. Remove the filter — blank rows are gone

The fix for blank columns: Same approach but using column filters. Or manually select blank columns → Right-click → Delete.

Issue 7: Currency Symbols Missing or Misaligned

The problem: Dollar signs, euro signs, or other currency symbols have disappeared during conversion. Or they're stored as part of the text, preventing mathematical operations.

The fix:

  1. If symbols are part of the text: use Find & Replace (Ctrl+H) to remove "$", "€", "£" etc.
  2. Select the cleaned column → Format Cells → Currency
  3. Choose your preferred currency symbol and decimal places
  4. Click OK — proper currency formatting is applied

Prevention: Getting Cleaner Conversions

  • Use digital PDFs — Scanned PDFs always produce more formatting issues. If you have a digital version, always use it.
  • Check the source formatting — If the PDF table has inconsistent borders, merged cells, or irregular spacing, the Excel output will reflect those issues. Clean the source if possible.
  • Try PDF to Word first — For complex tables, converting to Word first (which preserves layout better), then copying the table to Excel, sometimes produces cleaner results than direct PDF-to-Excel conversion.

Frequently Asked Questions

Why does formatting break during conversion?

PDFs use fixed positioning while Excel uses a grid system. The converter must map visual elements to grid cells, which isn't always perfect — especially for complex layouts with merged cells, irregular spacing, or overlapping elements.

Is there a way to get perfect formatting every time?

No converter achieves 100% formatting accuracy on complex tables. Plan to spend 2-5 minutes on cleanup after conversion. For simple tables with clear borders, the conversion is usually perfect.

Can I automate the cleanup for recurring conversions?

Yes. Excel's Power Query can record and replay cleanup steps. Perform the cleanup once manually, then save it as a Power Query transformation. Future conversions can be cleaned with a single refresh.

Summary

Formatting issues after PDF-to-Excel conversion are common but fixable. Merged cells, broken columns, wrong data types, date display problems, blank rows, and currency issues each have specific solutions that take under two minutes. The PDF to Excel tool produces the cleanest output possible, and these fixes handle the remaining 5% of issues quickly and reliably.