Excel Addition: Combining data – pasting with mathematical operations

YOUR OBJECTIVE

To record the monthly revenues at your company’s several business locations, you created a number of spreadsheets in an Excel workbook (file). Excel addition  can help you to combine data fast and effective. To be able to see the data more clearly, you decided to record the revenues for each month on a separate worksheet. So far, you have a worksheet for January, another for February and a third for March.

Excel addition can be so helpful for companies e.g. For analysis purposes you also created a table on a separate worksheet that you will be using to accumulate sales results for the running year, adding new data to existing data each time the sales data for another month is available. So far, you’ve accumulated sales figures from January and February. Now that the March data is in, you would like to add the results from March to the existing numbers in your summary table. The Paste Special function will help you accomplish this in a quick, straightforward fashion.

In this learning module, you will be shown how to carry out mathematical operations while you paste.

HOW DOES EXCEL ADDITION WORK?

In Excel, after copying data from a cell or a range of cells, you can use Paste Special options to carry out certain mathematical operations while pasting data. Assume you have three worksheets, each containing sales figures for various flavors of ice cream for the months of January, February and March. Now you would like to sum up these figures on a fourth worksheet in order to display sales per flavor for the quarter.

To do this, you would first copy the table from the January worksheet to the newly created Cumulated worksheet. Then, to add the February sales to the sales figures for January, go first to the February worksheet. There, select the cell range with the sales numbers, and then use the Copy function to copy it to the clipboard. Now go back to the Cumulated worksheet. After positioning the mouse pointer on the first cell of the target cell range, click once. Now, on the Home tab, in the Clipboard group, click the little black arrow on the Paste button. In the selection catalog that appears (shown below), choose the Paste Special… command.

The Paste Special dialog box will open. Here you can specify which mathematical operation you want to carry out when pasting.

The following options are available in the [mathematical] Operstion area:

Use this option to specify that no mathematical operation should be carried out when pasting.

Activate this option, to cause the copied data to be added to the data that already exists in the paste area.

This option specifies that the copied data is to be subtracted from the data in the paste area.

Here you cause the copied data is to be multiplied by the data in the paste area.

This option causes the values in the paste area to be divided by the copied data values.

Activate this option to prevent values in the paste area from being changed if there is a blank cell in the corresponding cell position in the copied data.

Activate this option to cause columns of copied data to be changed to rows and vice versa.

When copying All or All except borders, this option causes links to the copied data to be inserted into the pasted cell area.

In the present example, you want to add the values from the clipboard to the values in the paste area. To do so, select the Add option, and then click OK.

You can then proceed in the same fashion to add the totals from March to the summary table, resulting in a summary of all sales for all three months. As this example shows you excel addition is a good and effective mathematical operation for combining data.

Did you like it? Then share it or sign up!

You can share this article with your friends or sign up for our newsletter for further information.

Leave a comment

Connect with Facebook

*

Kein Banner zum Anzeigen