admin, Author at Your Excel Partners

July 27, 2011April 30, 2013

How to Use Paste Special in Excel?

We use copy paste (Ctrl+C and Ctrl+V) commonly to copy data. But surprisingly, there are 17 different variations to paste data in an excel sheet! To Paste Special click on arrow below Paste and click Paste Special.
Hit Alt + E (Edit) followed by S (Paste Special) to display the paste special box. Select the appropriate options in the box.

Steps to Use Paste Special

Select the data to be copied and click Copy on Home tab.

To Paste Special, select the destination and click the arrow below Paste button on Home tab. Click Paste Special on the drop-down menu. A Paste Special dialog box appears.

Select appropriate options in the dialogue box.

Pastes everything (formulas, formatting, and text) . (Same as Paste).

Shortcut: Hit Alt + E (Edit) followed by S (Paste Special) and A (All) or simply Ctrl + V.

It enables pasting just the formulas ignoring everything else. This is useful, because it retains the formatting and comments of the destination cells.

Shortcut: Hit Alt + E (Edit) followed by S (Paste Special) and F (Formulas).

It Pastes only values without formatting. It doesn’t matter if the source cell has a formula in it. If you wish to paste data from one worksheet to another that contains links, this option is useful to

avoid links to another file.

Shortcut: Hit Alt + E (Edit) followed by S (Paste Special) and V (Values).

This option enables to paste only the formatting (and conditional formatting) from the copied cells to destination cells. This is useful if you have content formatted in a certain way that you would like to copy to another location.

Shortcut: Hit Alt + E (Edit) followed by S (Paste Special) and T (Formats).

This option enables pasting only comments from the copied cell to the new cell, without pasting any of the formats or data from the source cell. This option saves you the time of re-typing comments, while preserving the data and formats in the destination cell.

Shortcut: Hit Alt + E (Edit) followed by S (Paste Special) and C (Comments).

When creating Data Validation rules, you might want to apply those rules to other cells. You can do that with this option without copying any source data or formats, and preserving the destination data and formats.

Shortcut: Hit Alt + E (Edit) followed by S (Paste Special) and N (Validation).

All using Source theme:

This option pastes all the information from the source cell (numbers, formulas, text, formats, comments, etc.) and applies the source worksheets theme’s colors, fonts and graphics to the destination.

All except borders:

This option pastes numbers, formulas, text, comments and formats except borders from the source cell in the destination cell.

Column widths:

It allows you to copy one column’s width or more and apply it to whichever columns you select. If your spreadsheet has one wide column of data, and you want that wide column look normal, select the normal column and paste Column Width, so that all columns are of equal width.

Formulas and number formats:

This option pastes only the formula from the source cell and number formats applied to that cell. It only pastes number formatting, such as percentage, currency, accounting, scientific, etc., and other formats like border, fill color, font, etc., are not pasted.

Values and number formats:

This option pastes the same information as the Formulas and number formats option above, except it only pastes values and not formulas. This is similar to the Values option discussed above with the addition of the number formatting.

Operation Section

The Operation section allows you to perform a mathematical operation with the data you have copied.

For example, let us assume that a source cell contains number 5, and the destination cell contains number 10.
Let us assume that the “All” option is selected from the Paste section with Skip blanks and Transpose left unchecked.

Below are the results of each operation option

None:

This operation performs no arithmetic, and therefore resulting number in the destination cell will be 5.

Add:

By selecting 5 from the source cell and pasting it into the destination, which contains 10, Excel adds 10+5 to return 15.

Subtract:

Excel subtracts 10-5 to return 5.

Multiply:

Excel multiplies 10*5 to return 50.

Divide:

Excel divides 10/5 to return 2.

There are three more options on the menu: Skip blanks, Transpose and Paste Link.

This option works just as it says. The Skip blanks option tells Excel to paste only where there is data in the source cell. If the cells are blank, Excel will skip over them and not paste anything, which is why cells B4 and B5 had the values of 800 and 900 respectively instead of being blank.

The Transpose option takes a copied list of numbers from a column and pastes them across a row, or copies a list of numbers from a row and pastes them down a column.

If you want your destination cell to link to the source cell you can use this option. In cell C2, you can see the formula ‘=$A$2’. If you select multiple cells to paste, it won’t have the dollar($) signs. Paste Link option saves a lot of time if you’re working with multiple worksheets or multiple files.

—————————————————————————————————————————————————-

Vani is a Business Associate with p2w2, a Spreadsheet Solutions company. p2w2 has expertise in Excel Modeling, Excel Dashboards, Profitability Analysis, Excel Invoices and Excel Bid sheets. You can contact us by email: cs [at] p2w2.com or call us at 305.600.0950.

July 26, 2011May 1, 2013

How to Split Text into Different Columns?

A feature of Microsoft Excel ‘text to columns’ option under the Data menu allows information in a cell or range of cells to be split into different columns. We find this tends to occur on exported lists of address information.
Below is the data in column B which is to be split in two different columns:

Following are the steps to split data into different columns using ‘Text to columns’ :

Select the range of data you need to work at.

Select Data from menu and select Text to

cialis canadian pharmacy

Columns in the Data Tools group.

Check Delimited in the dialog box (1 of 3) and click Next.

In Step 2 of 3, check the box related to the specific separator in the information.
If the separator is tab, check tab or if the separator

is semicolon, check semicolon and so on.
If the separator is other than the specified check boxes given in the wizard, check Other box and mention the separator in the box for Other:

Select Other in the dialog box (2 of 3) and enter the delimiter used in your data (-) in the box for Other: and click Next.

Enter the Destination (C3) to paste the data after splitting and Click Finish.

Click Ok in the dialogue box.

Shown below is the output:

—————————————————————————————————————————————————-

July 25, 2011May 1, 2013

How to Enable Macros in Excel?

In Excel, macros are disabled by default for enhanced security. Below are the steps to

Enable Macros in two different versions of Excel:

Excel 2007 version

Excel 2010 version

Enabling Macros in Excel 2007:

The picture below shows a Security Warning in a worksheet in which macros are disabled.

Click Options button.

Check Enable this Content and Click Ok in the Security Alert-Macro box.

Now the macros are enabled.

Enabling Macros in Excel 2010:

The

viagra online

picture below shows a Security Warning in a worksheet in which macros are disabled.

Click Enable Editing button.

Just by clicking the Enable Editing button, Excel is ready to enable all the macros.

—————————————————————————————————————————————————-

July 18, 2011May 2, 2013

How to Remove Duplicates?

When we work

with Excel, many times you encounter a situation where you need to remove duplicate records/data, in the following article we tried explaining you ‘How to remove duplicates’ hope this articles helps you!

Data before removing duplicates

remove duplicates in excel

Data after removing duplicates

remove duplicates excel

Following steps will help us in removing duplicates:

Copy your range of data to a blank section of the worksheet.

Name every column in your data set.

Select a cell in your data set.

From the Data menu, in the Data Tools group choose Remove Duplicates.

how to remove duplicates in excel

Excel displays a dialog box, for selecting the columns that need to be considered. Check the boxes of required columns and check My data has headers box if the data has headers included.

excel remove duplicates

Click OK.

Excel deletes the duplicate records from your dataset. It also displays the number of records removed and the number of records remaining in the sheet.

remove duplicates from excel

Click Ok.

Below is the data after removing duplicates.

delete duplicates in excel

—————————————————————————————————————————————————-

May 20, 2011May 2, 2013

How to Create Dynamic Drop Downs?

Suppose we have items of different criteria like Fruits, Vegetables, Flowers, Animals, and Birds. Each criterion has a list of items as above:

Here is a process to create a drop down:

Drop Down No: 1

First select a cell to add the drop down, for the list of criteria.
Select ‘Data’ from the ribbon.
Select ‘Data validation’ from the menu.

4. Click on the ‘Data validation’ In the validation criteria, select ‘list’ in ‘allow’ box and type in the following formulae in ‘source’:

=$C$2:$G$2

The above formula indicates the range of cells in which the list of criteria is available.

5. When we click on the drop down, we will be able to see the list of criteria.

Drop Down No: 2

Now suppose we want to retrieve ‘Apple’ from criterion ‘Fruits’.

Select the items of ‘Fruits’ and click in the ‘Name box’ which is at the top, left hand side of the sheet.
Type the name of the criterion, ‘Fruits’ which we had selected. Then press enter key.

3. Select a cell to add the drop down, for the list of items.

4. Select ‘Data’ from the ribbon.

5. Select ‘Data validation’ from the menu.

6. In the validation criteria, select ‘list’ in ‘allow’ and type in the following formulae in ‘source’:

=INDIRECT ($D$9)

What this does is this:

=INDIRECT (ref-text, [a1])

Ref_text is a reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string. If ref_text is not a valid cell reference, INDIRECT returns the #REF! Error value.

A1 is a logical value that specifies what type of reference is contained in the cell ref_text.

Here, we are using the formula in drop down. So we need to give the reference cell number (D9).

Now the drop down for list of items is added. Now the required item

can be selected from the list.

In the similar way we can do for all the criteria, and select the required item. In this way we can create dynamic drop downs in excel.

Steps to Use Paste Special

Select the data to be copied and click Copy on Home tab.

To Paste Special, select the destination and click the arrow below Paste button on Home tab. Click Paste Special on the drop-down menu. A Paste Special dialog box appears.

Shortcut: Hit Alt + E (Edit) followed by S (Paste Special) and N (Validation).

All using Source theme:

All except borders:

Column widths:

Formulas and number formats:

Values and number formats:

Operation Section

Below are the results of each operation option

None:

Add:

Subtract:

Multiply:

Divide:

Following are the steps to split data into different columns using ‘Text to columns’ :

Select the range of data you need to work at.

Select Data from menu and select Text to cialis canadian pharmacy Columns in the Data Tools group.

Check Delimited in the dialog box (1 of 3) and click Next.

Select Other in the dialog box (2 of 3) and enter the delimiter used in your data (-) in the box for Other: and click Next.

Enter the Destination (C3) to paste the data after splitting and Click Finish.

Click Ok in the dialogue box.

Excel 2007 version

Excel 2010 version

Enabling Macros in Excel 2007:

The picture below shows a Security Warning in a worksheet in which macros are disabled.

Click Options button.

Check Enable this Content and Click Ok in the Security Alert-Macro box.

Enabling Macros in Excel 2010:

The viagra online picture below shows a Security Warning in a worksheet in which macros are disabled.

Click Enable Editing button.

Data before removing duplicates

Data after removing duplicates

Following steps will help us in removing duplicates:

Copy your range of data to a blank section of the worksheet.

Name every column in your data set.

Select a cell in your data set.

From the Data menu, in the Data Tools group choose Remove Duplicates.

Excel displays a dialog box, for selecting the columns that need to be considered. Check the boxes of required columns and check My data has headers box if the data has headers included.

Click OK.

Select Data from menu and select Text to

cialis canadian pharmacy

Columns in the Data Tools group.

The

viagra online

picture below shows a Security Warning in a worksheet in which macros are disabled.