Clear Formatting in Excel Without Losing Data

What You'll Be Able to Do, and Why Clear Formatting in Excel Is Trickier Than It Looks

Why does your spreadsheet still look like a disaster after you've already cleared the formatting? If you've hit that wall, you're probably running into one of two scenarios that catch almost everyone off guard: formatting that arrived through a paste operation, or formatting applied to individual characters inside a cell rather than to the cell itself. The standard Clear Formats option doesn't catch either one, and if you don't know that, you'll waste time clicking the same ribbon button on repeat. This guide walks through how to clear formatting in Excel across all three situations, without deleting a single data point. All you need is a selection of cells and Microsoft Excel open in front of you.

Clearing formatting and deleting data are completely different operations. Your numbers, text, and formulas stay put. Only the visual layer (fonts, colors, borders, cell styles) gets stripped away. That distinction matters a lot, and we'll revisit it when we get to a mistake that trips up even experienced users.

---

Step 1: Use the Clear Formats Option on the Excel Ribbon to Strip All Cell Formatting at Once

This is the core method, and it handles the majority of cases. Select your range, or the entire sheet with Ctrl+A, and you're two clicks from a clean slate.

How to Find Clear Formats on the Excel Ribbon (Windows and Mac)

On Windows: Go to the Home tab on the Excel ribbon. In the Editing group on the far right, click the Clear button (it looks like an eraser). A dropdown appears with several options. Click Clear Formats. That's it. Your data stays exactly where it is, and all cell styles, borders, font formatting, and fill colors are removed.

On Mac: The path is the same (Home tab, then the Clear eraser button in the Editing group), but the button can sit in a slightly different position depending on your window size, since the Mac ribbon compresses at smaller widths. If you can't find it, try widening your Excel window first. (Confirmed in Excel 2016, 2019, and Microsoft 365.)

A word on what doesn't get touched: comments, hyperlinks, and your actual data all survive. If you want to wipe those too, that's what Clear All does, a different option in the same dropdown. Don't mix them up. I keep a dedicated tab in my test workbooks (I call it "GraysonBreakThis") specifically for checking edge cases like this one before I ever touch a live file.

The Keyboard Shortcut That Does the Same Thing Faster

On Windows, press Alt, H, E, F in sequence (not simultaneously). That's your Excel clear format shortcut via the ribbon's keyboard access keys. There's no single-keystroke shortcut built into Excel the way some other spreadsheet apps handle it; you need the four-key sequence. On Mac, there's no direct keyboard equivalent, so the ribbon path is your best bet.

Once you've stripped the formatting from your range, you've handled the clean-data-in-Excel scenario. But if any of that data was pasted in from another source, step two is what you actually needed first.

---

Step 2: Clear Formatting When Pasting Data into Excel So Imported Styles Don't Sneak In

Pasting is the most common way bad formatting gets into a spreadsheet in the first place. Copy a range from another workbook, a web table, or even a different sheet with heavy formatting, paste it normally, and Excel drags in every font choice and background color from the source. You haven't made a mistake; this is just how a standard paste works.

Paste Special: The Fix for Formatting That Arrives with Copied Data

The cleanest solution is to paste values only, right from the start. After copying your data, press Ctrl+Alt+V on Windows (or Ctrl+Cmd+V on Mac) to open the Paste Special dialog. Press V to select Values, then Enter. Only the raw data comes through: no formatting, no cell styles, no baggage.

If you've already done a standard paste and regret it, look for the small Paste Options button that appears near the bottom-right corner of your pasted range immediately after pasting. Click it and choose Values Only. That strips the formatting retroactively without requiring an undo. This is part of a broader set of habits worth building around data entry and formatting in Excel: paste behavior is one of the most overlooked pieces of that puzzle.

With paste-related formatting handled, there's still one edge case that surprises people: formatting applied not to a cell, but to specific characters inside it.

---

Step 3: Remove Formatting Applied to Individual Characters Inside a Cell (What Clear Formats Can't Touch)

The Clear Formats option operates at the cell level. If someone bolded a single word inside a cell, or changed the color of three characters in the middle of a string, those changes live inside the cell's text, not in the cell's formatting properties. The ribbon button won't see them.

The fix is manual. Follow these steps:

1. Double-click the cell to enter edit mode.
2. Select the affected characters directly, either by clicking and dragging or by using Shift+Arrow keys.
3. Go to the Home tab and reset the font properties: remove bold, change the font color back to Automatic, and reset the font size if needed.
4. Alternatively, open the Format Cells dialog with Ctrl+1 (Windows) or Cmd+1 (Mac) to see everything at once and reset it from there.

It's tedious. There's no batch fix for this one. But it comes up far less often than cell-level formatting, usually only in spreadsheets where someone was trying to get creative with in-cell labels. If you're building clean data structures from the start, this is covered well in the Excel for Beginners guide.

---

Common Mistakes When You Clear Formatting in Excel, Including One That Can Actually Grow Your File Size

Three stumbles I see regularly, including one that's counterintuitive enough that it tripped me up early in my career.

1. Selecting the entire sheet before clearing. Pressing Ctrl+A and then running Clear Formats applies the operation to every cell Excel considers "used", which, in a workbook with a messy history, can mean thousands of empty cells that carry ghost formatting from old data. Microsoft's own documentation warns that this can actually increase file size in some cases, because Excel rewrites those empty-cell formatting records. If you're trying to shrink a bloated file, use the Inquire tab's "Clean Excess Cell Formatting" tool instead; it's built for exactly that.

2. Confusing Clear Formats with Clear All. Clear All removes data, comments, hyperlinks, everything. Clear Formats only removes the visual layer. Read the dropdown label before you click, every time. I tested this across Excel 2016, 2019, and Microsoft 365, and the behavior is consistent, but the label difference is easy to skim past.

3. Forgetting that conditional formatting has its own removal path. Standard Clear Formats does not remove conditional formatting rules. For those, go to Home > Conditional Formatting > Clear Rules, and choose whether to clear from the selected cells or the entire sheet. This is also where you'd manage overlapping rules that are causing unexpected color behavior, something VBA or Office Scripts can automate if you're doing this cleanup regularly across multiple files.

Merged cells deserve a brief mention here too. If your selection contains merged cells, Clear Formats will unmerge them, which can shift cell references and break formulas downstream. Merged cells are, in my opinion, almost ideologically wrong in a data-entry context. But if you've inherited them, deal with the merge situation before you clear anything. You'll save yourself a debugging session.

For a broader look at keeping your data clean and structured as you build out a sheet, the principles behind accurate data entry in Excel apply here just as much as they do at the input stage.

---