Copying cell formatting from another cell and apply on another range or some ranges can be useful in several scenarios. It can save time, ensure consistency, and reduce the chance of errors when working with large sets of data in Excel. This post shares two tips to format ranges as same as the format from another cell in the Excel worksheet.
Format Ranges from Another Cell’s Formatting by Format Painter Tool
You can use the “Format Painter” tool in Excel to apply the same formatting from one cell to a range of cells.
Here are the steps:
Step1: Select the cell that has the formatting you want to copy. In this example, you can select cell “B2” or any cell in the range “B5:B9” that has the same cell format.
Step2: Click on the “Format Painter” button in the “Home” tab of the Excel ribbon. The cursor will change to a paintbrush.
Step3: Select the range of cells that you want to apply the formatting to. The copied cell’s formatting will be applied to the entire range.
Format Ranges from Another Cell’s Formatting by “Paste Special” Feature
Alternatively, you can also use the “Paste Special” feature to apply formatting from one cell to a range of cells. “Special Paste” -> “Format” enables you to copy the cell style and formatting from another cell and paste it into the target cell or cell area without copying the values and formulas.
Here are the steps:
Step1: Select the cell that has the formatting you want to copy. You can select cell B2 in this example because it contains the style and format for a price.
Step2: Press “Ctrl+C” to copy the cell.
Step3: Select the range of cells that you want to apply the formatting to. In this example, select the range E5:E9 where you want to keep consistency with the price’s format.
Step4: Right-click on the selection and choose “Paste Special“-> “Formatting” from the context menu. The formatting from the original cell will be applied to the entire range.
Formatting a range as same as a given cell’s formatting is easily achieved by above two ways. After running the above steps, the values in the SALES column match the style and formatting of cell B2. You can choose either one you like to format your ranges in work.