How to Copy a Format for Excel Worksheet Columns and Rows


Formatting permits viewers to differentiate values by type and even areas. Using the best format is crucial to readability, and the majority of users find out how to format cells and regions early on. After finding out the basics, users quickly carry on to using Format Painter to copy existing formats to other cells and even whole areas. You’ll work more efficiently utilizing Format Painter.

In this short article, you’ll find out how to utilize Format Painter with cells. Then, we’ll move on to utilizing Format Painter to quickly format whole rows and columns. You’ll even discover a trick for formatting whole rows and columns without utilizing Format Painter.

Keep in mind: It’s worth pointing out that you most likely do not wish to format an entire row or column often because doing so consumes a lot of memory. With today’s powerful systems, this isn’t the problem it when was, however you’ll still want to format whole rows and columns sparingly.

How to use Format Painter

Format Painter lets you use the very same set of formats from a cell or group of cells to another cell or group of cells. To illustrate, let’s utilize Format Painter to repair the formatting in the first date column displayed in Figure A. As you can see, various formats remain in usage. In this circumstance, you’ll wish to use the very same format to all of the cells with dates.

Use Format Painter to apply the same date format to the entire column of date values. Figure A: Use Format Painter to use the same date format to the entire column of date

  1. worths.
  2. Image: Susan Harkins/TechRepublic Now, let’s utilize Format Painter
  3. to copy the format from B31 to the rest of the

column: Select cell B31. Click Format Painter in the Clipboard group on the Home tab. Select the rest of the column, B32: B39. As you can see in Figure B, B32: B39 now shares the same format as B31. It’s important to keep in mind that this feature copies all formatting, not just the date format. Figure B: All date values in the Start column now share the very same format. Image: Susan Harkins/TechRepublic When you need to copy formatting to a variety of cells or regions that aren’t adjoining, you can lock the function. Doing so lets you format up until you turn the function off. Let’s utilize the locking function to reformat the dates in C32 and C36: Select C31, or any cell with the proper format. Double-click Format Painter. Click C32 (Figure C). Figure C: Click noncontiguous cells to format them at the same time. Image: Susan Harkins/TechRepublic Click C36. Click Format Painter to turn it off. You can continue to click cells up until you click Format Painter to turn it off. How to use Format Painter with rows and columns Now that you

  • understand the fundamentals, it
  • ‘s time to discover how to use Format Painter with whole columns and rows. It’s simple, actually; the routine is the same as reviewed above. The

    only difference is that you click a row or column header. A header cell is the gray cell to the left or the row or above the column. SEE: Check out these Excel pointers every user need to master. Let’s suppose you want to develop a brand-new table of worths using the same header format in row 29. You’ll do so as follows

    : Click the header cell for row 29 to choose the whole row. Click Format Painter. Scroll down to row 52 and click that row’s header cell (Figure D).

    Figure D: Format the whole row by clicking the header cell.

  • Format the entire row by clicking the header cell.Image: Susan Harkins/TechRepublic You simply copied an entire row’s format to another. Although it’s

    not easily visible, that indicates you copied A29 to A52, B29 to B52, C29 to C59 and so on. You might notice that there’s no formatting to the right of column G. That’s since there’s

    no formatting to the right of column G in row 29. You can do the exact same with columns: To copy the formatting from one column to another, do

    the very same thing, but choose column headers. If you can’t click the header cell While it’s not likely to be not able to click the header cell, it can happen if the workbook’s developer

    turns off the header cells. With the header cells off, it’s much easier to visualize how this next tip can be found in helpful. Nevertheless, you can utilize the Name Box to show the existing cell. Let’s switch off the header cell display screen as follows: Click the File tab. Click Options or More and after that

    Options. Click Advanced in the left pane. In the Display options for

  • this worksheet, uncheck the
  • Program row and column headers
  • choice (Figure E ).
  • Figure E: Turn off the header cells. Image: Susan Harkins/TechRepublic Now, let’s show this next method by broadening the format in G51 to the staying cells in row 51: Select the cell you wish to copy

    . In this case, click G51. Click Format Painter. Click Shift+Spacebar to copy the format from G51 to the entire row(Figure F). Figure F: Copy the format from a single cell to the entire row. Image: Susan

    Harkins/TechRepublic ACADEMY: Master Microsoft Excel with these 16 courses in this bundled deal. To copy the format from a cell to an entire column, repeat the above steps, however press Ctrl+Spacebar to

    pick the whole column in step 3. How to copy format to rows and columns without Format Painter Additionally, here’s how to copy format to a whole row or

    column without using Format Painter. We’ll work with row 51 once again, having actually

    pressed Ctrl+Z to reverse the last copy: Select the cell you wish to copy. In this case, click G51. Press Ctrl +C to copy the cell’s content and formats to the Clipboard.

    1. Press Shift +Spacebar to choose the whole row.
    2. Or press Ctrl + Spacebar to choose the entire column. Choose Paste Unique from the
    3. Edit menu. Click Formats in the Paste dropdown( Figure G).< img src=""

    Choose Formats to paste only the formatting.alt=”Pick Formats to paste only the formatting. “width=”770″height=” 284 “/ > Figure G: Select Formats to paste only the format. Image: Susan Harkins/TechRepublic Remember that paste choices (step 5) will have various outcomes. You should choose Formats to paste only the format.

  • Formatting whole rows and columns

    Regardless of what section of the sheet you’re working on, row and column headers are typically offered, making it easy to format the whole row or column utilizing Format Painter. In a pinch, you can copy the format to the Clipboard utilizing Ctrl + C and after that utilize shortcuts to pick the whole row or column before pasting the format from the Clipboard.


    Leave a Reply

    Your email address will not be published. Required fields are marked *