Microsoft Excel- Paste Special, Format Numbers

Paste Special in Excel

If you are copying some data from any other database or worksheet you have variety of options to paste that data in the target database.

You can select to paste just the formula, value, formats, etc. only. 

You can specifically select what to paste and what not to paste.

Let us take an example:

• Select the cell or database you want to copy and press CTRL+C
• Move to desired location (it can be any other worksheet or somewhere in the same worksheet) and right click. 

• Now click on Paste Special, a dialog box will appear

In this box you can select the desired option which you want to paste.

For example, you can just copy the formula or just the values from the copied cell.

Or you can just copy the validation criteria (if any).

Also, there are other options to select from like pasting only the comments and skipping blanks or transposing the database.

• After selecting your option click on OK and the results will appear and desired value will be pasted in the selected cell or database.

In our example we have just pasted the formula.

See the formula has relatively been changed and due to non-availability of values in I1 to I3 cells.

Notice the background color is not copied.

Formatting Numbers in Excel

In Excel, you can format the style of numbers as per the data requirements of your workbook.

If you want your numbers to have a hyphen after every two digits (e.g. 00-00-00), you can use the inbuilt tool provided by Excel to customize the format of your numbers.

Create a custom number format

1. On the Home tab, in the Number group, click More Number Formats at the bottom of the Number Format list   .

2. In the Format Cells dialog box, under Category, click Custom.
3. In the Type list, select the built-in format that most resembles the one that you want to create. For example, 0.00.

The number format that you select appears in the Type box.

4. In the Type box, modify the number format codes to create the exact format that you want. For example, 000-000-0000.

Your changes will not alter the built-in format. Instead, your changes create a new custom number format.

5. When you have finished, click OK.

Apply a custom number format

1. Select the cell or range of cells that you want to format.
2. On the Home tab, in the Number group, click More Number Formats at the bottom of the Number Format list  .
3. In the Format Cells dialog box, under Category, click Custom.
4. At the bottom of the Type list, select the built-in format that you just created. For example, 000-000-0000.

The number format that you select appears in the Type box.

5. Click OK.

Delete a custom number format

1. On the Home tab, in the Number group, click More Number Formats at the bottom of the Number Format list  .
2. In the Format Cells dialog box, under Category, click Custom.
3. In the Type list, select the custom number format, and then click Delete.

Notes: 

• Built-in number formats cannot be deleted.
• Any cells in the workbook that were formatted with the deleted custom format will be displayed in the default General format.

SUBTOTAL FUNCTION

To understand and practice subtotal function, click here.