What clearing formatting does and when you need it

Clearing formatting removes colors, fonts, borders, and number styles from cells while keeping the data inside them. You keep the numbers and text; you lose the visual styling. This is different from deleting, which removes everything.

You need this when a spreadsheet you inherited has formatting that makes it hard to read, when you want to start fresh with a consistent look, or when you copied data from a website and brought along unwanted colors and fonts. Excel gives you several ways to do this depending on how much formatting you want to remove and whether you want to keep some of it.

Key Takeaways

  • Clear Formatting removes colors, fonts, borders, and number styles but leaves your actual data untouched.
  • The fastest method is to select your cells, go to the Home tab, find the Clear button in the Editing group, and choose Clear Formats.
  • You can clear formatting from a single cell, a range of cells, or an entire sheet depending on what you select first.
  • If you only want to remove specific formatting like color or borders, use Format Cells instead of Clear Formatting.

Selecting the cells you want to clear

Before you clear anything, you need to tell Excel which cells to work on. Click on a single cell if you only want to clear that one. Click and drag to select a range — for example, from A1 to D10. If you want to clear an entire column, click the column letter at the top. If you want to clear an entire sheet, press Ctrl+A (or Cmd+A on Mac) to select all.

You can also select multiple separate ranges by holding Ctrl (or Cmd on Mac) and clicking on different areas. This is useful if the cells you want to clear are scattered across the sheet rather than grouped together. Once you have selected what you need, move to the Home tab at the top of the screen.

Using the Clear button in the Home tab

On the Home tab, look for the Editing group on the right side of the ribbon. Inside that group is a button that looks like an eraser or says "Clear" with a small arrow next to it. Click the arrow to open a menu with several options.

Choose "Clear Formats" to remove all formatting from your selection. This is the most common choice and the fastest way to strip away colors, fonts, borders, and number formatting while keeping your data. The other options in that menu — Clear Contents, Clear All, and Clear Comments — do different things and are less commonly used for this task.

What happens to different types of formatting

When you clear formatting, Excel removes background colors, font colors, bold and italic styling, borders, and number formats like currency symbols or decimal places. If a cell was formatted to show two decimal places and you clear it, the number stays but the formatting goes.

Clearing formatting does not affect merged cells — they stay merged. It does not affect column width or row height. It does not remove data validation rules or conditional formatting rules that are still attached to the cells. If you need to remove those, you have to do it separately through the Data Validation or Conditional Formatting menus.

Clearing formatting from pasted data

When you copy data from a website or another spreadsheet and paste it into Excel, you often get unwanted colors and fonts along with the numbers. Instead of pasting normally, use Paste Special to control what comes in. Right-click where you want to paste and choose Paste Special, or press Ctrl+Shift+V.

In the Paste Special dialog, click "Values" to paste only the numbers and text without any formatting. This is faster than pasting everything and then clearing it afterward. If you have already pasted with formatting, select those cells and use Clear Formats as described above.

Removing specific formatting instead of everything

If you only want to remove the background color but keep the bold text, or remove borders but keep the font color, use Format Cells instead of Clear Formatting. Select your cells, right-click, and choose Format Cells. You can then go to the Fill tab to remove color, the Font tab to change the font back, or the Border tab to remove borders.

This takes longer than clearing everything at once, but it gives you control over exactly what goes and what stays. You can also use the formatting buttons on the Home tab — the paint bucket icon removes fill color, the font color dropdown removes text color, and the borders button removes borders — without touching anything else.

Undoing a clear formatting mistake

If you clear formatting and realize you made a mistake, press Ctrl+Z (or Cmd+Z on Mac) when ready to undo. Excel keeps a history of your recent actions, so you can undo multiple steps if you need to. Once you save the file, you cannot undo anymore, so be careful with Ctrl+Z before you save.

If you cleared formatting from the wrong cells, undo and try again with the correct selection. If you cleared too much formatting and want to keep some of it, undo and use Format Cells to remove only the specific formatting you do not want.

Frequently Asked Questions

Does clearing formatting delete my numbers and text?

No. Clear Formatting removes only the visual styling — colors, fonts, borders, number formats — and leaves your actual data untouched. The numbers and text stay in the cells.

Can I clear formatting from an entire sheet at once?

Yes. Press Ctrl+A to select all cells in the sheet, then go to Home, click the Clear button arrow, and choose Clear Formats. This removes all formatting from every cell while keeping all your data.

What is the difference between Clear Formats and Clear All?

Clear Formats removes only the visual styling. Clear All removes both the formatting and the data itself. Use Clear Formats when you want to keep your numbers and text. Use Clear All only when you want to delete everything from those cells.

How do I remove only the background color and keep everything else?

Select the cells, go to Home, find the paint bucket icon (Fill Color), click the arrow next to it, and choose "No Color". This removes only the background color and leaves fonts, borders, and number formatting alone.

Can I clear formatting from cells that have formulas?

Yes. Clearing formatting does not affect formulas — only the way they look. The formula stays and works the same way; only the colors and fonts change.