OmniDocHow to Get Format Painter to Work in a Spreadsheet, Step by Step
Learn how Format Painter copies cell formatting in a spreadsheet, fix it when it seems to do nothing, and carry a Word-style look into an .xlsx file.
Format Painter is a fast way to make one part of a spreadsheet match another. Instead of setting the font, fill, borders and number format by hand, you copy them from a cell that already looks right. Many people click the tool, though, and nothing seems to change, or the formatting lands in the wrong place. If you are trying to get Format Painter to work in a spreadsheet, the problem is usually one of a few specific causes.
This guide explains what the tool copies, walks through the steps for painting cell formatting, and shows how to fix the common cases where it does not apply. It also covers a related question: how to copy a Word-style look into a spreadsheet, which the painter cannot do on its own. You will finish with a short checklist you can run on any workbook.
How do you get Format Painter to work in a spreadsheet? The short answer
Click a source cell that already has the formatting you want, turn on Format Painter, then click or drag over the target cells. If nothing changes, leave any cell that is still in edit mode, check whether the sheet is protected, and confirm that the formatting came from the spreadsheet itself rather than from another program.
| Your situation | What to do | Why |
|---|---|---|
| The format applies to only one area | Double-click the tool instead of single-clicking it | A single click applies the format once; a double-click keeps paint mode on. |
| Nothing changes when you click | Press Esc to leave edit mode, then try again | A cell you are still typing in blocks formatting changes. |
| You get an error or the change is blocked | Check whether the sheet is protected, and unprotect it if you have the password | Protected sheets stop formatting edits. |
| A Word font or color does not appear | Reapply the look inside the spreadsheet or use a cell style | The painter copies spreadsheet formatting, not the styles of a word-processing document. |
Before you start
- Confirm the file is a spreadsheet format. Use a workbook format such as .xlsx. A CSV file stores only values, so there is no formatting to copy.
- Make sure you can edit the file. A read-only copy or a protected preview blocks changes, so the painter will look broken even when it is working.
- Choose a source cell that shows exactly what you want. Click the cell and check its font, fill, borders and number format. Painting from a cell with a stray setting spreads that setting everywhere.
- Check whether the sheet is protected. Protection can block formatting changes on locked cells. If you do not have the password, ask the file owner.
- Note whether the formatting depends on cell contents. Rules that change a cell's look based on its value may need to be set up again on the new range, so plan to check the result carefully.
Painting cell formatting, step by step
- Open the .xlsx file and confirm you can edit it. If the file is read-only or protected, the rest of these steps will not change anything.
- Press Esc if a cell is still in edit mode. This leaves the cell without changing its contents, so the painter can apply formatting.
- Click the source cell that has the formatting you want. The painter reads the format of the cell you select at the moment you turn it on.
- Click the Format Painter tool on the toolbar. This turns on paint mode, so the next selection you make receives the copied formatting.
- Click a single target cell, or drag across a range. Every cell in the range receives the same font, fill, borders and number format.
- Double-click the tool if you need to paint several separate areas. Paint mode stays on until you turn it off, so you can move from area to area without clicking the tool again.
- Press Esc to turn off paint mode, then check the result. Leaving paint mode prevents accidental formatting on your next click.
Check the result
Once the formatting is in place, compare the target cells with the source. Painting copies whatever the source contains, so a small mistake in the source appears everywhere you painted.
| Check | How to see it | If it's wrong |
|---|---|---|
| Number format | Compare displayed dates, currency and decimals with the source | Reapply the number format to the whole range, and confirm the source cell is correct. |
| Fill and font | Look at color and size side by side | Click the correct source cell again and repaint the range. |
| Borders | Zoom in on the edges of the range, especially where it meets merged cells | Reapply borders to the full range; the painted edge may stop short of a merged area. |
| Contents unchanged | Click a target cell and read the formula bar | Undo with Ctrl+Z, then paint again; the painter should never change text or numbers. |
| "####" or cut-off values | Look for pound signs or truncated text | Widen the column so the formatted value fits. |
📊 At a glance
There is more than one way to copy formatting in a spreadsheet, and each suits a different job.
| Method | Good for | Watch out for |
|---|---|---|
| Format Painter | Copying one cell's look to a few areas quickly | Painted cells are not linked to the source, so repaint if the source changes. |
| Paste formats only | Bringing formatting from copied cells, including cells on another sheet | You must copy the source cells first. |
| Cell styles | Keeping a consistent look across a workbook | You need to set the style up once before it is useful. |
Copying a Word-style look into a spreadsheet
How to copy Word format to a spreadsheet? The short answer: the painter cannot read the styles of a word-processing document, so copy the text, then rebuild the look with spreadsheet formatting or a saved cell style. Painting from a cell you have already formatted works well as a starting point.
A few approaches work well in practice:
- Paste the text as plain text. Remove the word-processing styles first, then apply font, color and borders in the spreadsheet.
- Format one cell to match, then paint from it. Once that cell looks right, Format Painter can spread the look to the rest of the range.
- Start from a PDF table. If the table you need lives in a PDF, the desktop client at download.omnidoc.top can export PDFs to Word, Excel and PowerPoint. Export it to .xlsx, then clean up the formatting with the painter.
❓ FAQ
1. Why does Format Painter copy the formatting but not my formulas?
The painter only copies formatting, and it leaves cell contents alone. To copy formulas, use copy and paste, then check whether the references should change.
2. Why does a painted cell show "####"?
The column is too narrow for the formatted value, usually a long date or a currency amount. Widen the column until the full value shows.
3. Will the painted cells update if I change the source cell later?
No. Painted cells keep the formatting they received at the time you painted them. Repaint them if you want the new look.
4. Can I use Format Painter on a CSV file?
Not in a useful way, because a CSV file stores only values and no formatting. Save or open the data as a workbook format such as .xlsx first.
📝 Summary
Format Painter copies the look of a cell, not its contents, so it works well when the source is correct and the sheet is editable. If it seems to do nothing, leave edit mode, check protection, and remember that word-processing styles do not carry over on their own. Check the result against the source before you move on.
