Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to perform non-contiguous columns values addition using the conventional and Paste Special methods. Let’s get started
In the caption below, we have a sample transaction data. We have Cost Price and Retail Price in columns E and G respectively. For this business, the Cost Price of each line of Product is increased by 200 which implies that the Retails Price also increases by the same amount.
Conventional Method
To effect the increase in Cost Price and Retail Price, we want to add the value in cell C1 to each values in the Cost Price and Retail Prices. To do that using conventional method:
- Create two columns and provided headings
- In cell F4, execute: =E4+$C$1 (Absolute Cell Reference is applied to C1 so that when the formula is copied down, cell C1 does not move down)
- In cell I4, execute: =H4+$C$1 (Absolute Cell Reference is applied to C1 so that when the formula is copied down, cell C1 does not move down)
In the caption below, we have the new Cost Price and Retail Price based on the 200 increase
Delete Cost Price and Retails Prices columns
The next thing we obviously need to do is to delete the Cost Price and Retail Price in columns E and H respectively. One caveat is that, if we delete columns E and H just like that, the newly calculated values in columns F and I will return #REF! error as seen in the caption below after deleting columns E and H
So, what do we do to forestall #REF! error? We need to break the formula in columns F and I. Therefore:
- Press CTRL + Z to undo the last command
- In cell F4, execute CTRL + SHIFT + Down Arrow and press CTRL + C to copy
- Execute ALT + HVV
- In cell I4, execute CTRL + SHIFT + Down Arrow and press CTRL + C to copy
- Execute ALT + HVV (The four steps break the formulas in each cells of the 2 columns)
- Then, we can delete Cost Price and Retails Price. See the caption below.
Paste Special Method
In this method, we will achieve the same result without the need to:
- Create helper columns.
- Use formulas
- Apply absolute cell reference
- Break formulas
- Delete helper columns
So, we have the same transaction data as seen in the caption below
- Select cell C2 and CTRL + C to copy
- Select cell E4 and execute CTRL + SHIFT + Down Arrow
- Hold down CTRL key
- Click on cell G4 and then execute CTRL + SHIFT + Down Arrow (This is non-contiguous columns selection)
- Execute ALT + HVSD on the keyboard (The shortcut opens the Paste Special dialogue box and select Add operation)
- Click OK
In the caption below, we have the 200 added to the Cost Price and Retail Price using the Paste Special. So easy!
Thanks for visiting our website. See you in the next tutorial