MrExcel.com - Your One Stop for Excel Tips & Solutions

Fill Formatting

October 12, 2023 - by

Fill Formatting

Problem: I have a thousand rows of data. I want to apply red and blue to every other row. Nothing in the Format as Table looks exactly like I want it to look and I don’t want to define my own table style.

Strategy: This technique temporarily wipes out all of your data, but you get it back. It is a fast way to go.

You want to build a specific alternating color format. A2:H2 is burgundy. A3:H3 is blue.
Figure 1364. Copy this formatting.

  • 1. Select the data in rows two and three. You will see a fill handle in the bottom right corner of the selection.

Select A2:H3. Drag the Fill Handle down.
Figure 1365. Double-click the fill handle.

  • 2. Double-click the fill handle. All of your data is destroyed. Don’t panic. Open the icon at the bottom right.

  • 3. Choose Fill Formatting Only. All of your data comes back. The formatting is copied throughout.

Result: the formatting is copied. Your data that was overwritten comes back.

When you get to the bottom, all of your data is screwed up!!! But open the on-grid drop-down menu and choose FIll Formatting Only. The original numbers are back, and the formatting is applied. When someone showed me this trick, I gasped at how dangerous it could be if you suddenly passed out or were otherwise removed from the spreadsheet (fire alarm? Insurrection? Your imagination is the limit...) before choosing Fill Formatting Only.
Figure 1366. Open the Fill Options icon.
The alternating-row formatting is applied.
Figure 1367. Only the formatting is copied!

This article is an excerpt from Power Excel With MrExcel

Title photo by Juan Cardenas on Unsplash