Skip to main content

New Article

How to SUM by matching partial text in Excel

Post a Comment

Value Paste and Formula Paste

In many case, in our professional life, we need to do Copy and Value Paste or Copy and Formula Paste in all most always. Those employees, who works under MIS Department, have to do it lots of time in a single day to prepare reports. It is very time costing task in Office. Many of us use Excel Menubar to do it, others are using Keyboard Shortcut by pressing ALT key (like for Value Paste Press ALT+H+V+V and for Formula Paste Press ALT+H+V+F).

But did you noticed that, these two ways strongly need your attention to do this task. More clearly, if you choose Value Paste from Menubar, then what you need to do?
  1. First you should take the mouse in Hand
  2. Wheel the mouse in Home menu
  3. Then Click on Paste
  4. Then you pressure your eyes to find the Value Paste icon
  5. And then Click on it to paste it
These all steps can take more than 1 second. Am I right? Below is an image of this process:

Image 1: Value Paste from Home Menu


On the other hand, if you choose the shortcut from Keyboard for Value Paste, then what you need to do?
  1. First press ALT
  2. Then press H
  3. Then press V
  4. Then again press V
Image 2: Keyboard Shortcut of Value Paste command

These all steps can also take more than 1 second.

If you use any of these two ways, then I can challenge you, you have to stay attention all the time. In a Corporate Office where you have to always stay under pressure, then why you take more pressure like these simple things? Try to keep yourself relax whenever you are free.




So what am I suggesting you to do Value Paste or Formula Paste?

Oh Yea. I've found a nice way to do Value Paste and Formula Paste in seconds. Do you remember QAT (Quick Access Toolbar)? Just add the Command here and use Keyboard Shortcut by Pressing ALT+1, ALT+2 etc. Here is the way to add Value Paste command and Formula Paste command in QAT bar. You can't realize how powerful is QAT.

Step 1: Click on Customize Quick Access Toolbar drop down icon and Click on More Commands.

Image 3: Quick Access Toolbar customizing

Step 2: While Excel Options dialog box appeared, Click on Choose Commands From drop down list icon and Select the option All Commands.

Step 3: Now all the commands used in Excel will display in left pane. Press P to go to Command List starting with P and choose Paste Values and then Click the Add button to Add this command in Customize Quick Access Toolbar.

Image 4: Paste Values and Paste Formulas need to add


Step 4: Again by this way Add Paste Formulas. Use Move Up and Move Down button to move your Paste Values in Top and Paste Formulas in 2nd Top after adding them in Customize Quick Access Toolbar. For more details of these dialog box see my previous articles of QAT labels.

Finally it will looks like below:

Image 5: QAT in Customize Mode

In Excel Window it will looks like below:

Image 6: Value Paste and Formula Paste in QAT

Now Copy something, and use ALT+1 for Value Paste and ALT+2 for Formula Paste. Hope this article will help you a lot in your daily task. Keep using this Alt+1 and Alt+2 daily, then you will become an expert.
Labels:

Popular posts from this blog

How to display an image in worksheet based on a List or based on IF condition?

Excel can show image on worksheet based on a specific IF condition. So, how to do it? Simple follow the below steps: Step by Step: Step 1: Insert images in your Excel Worksheet. Here I've inserted 5 different types of balls, Football, Cricket, Pool, Basketball, Tennis ball. Note that, All balls are placed into different cell. These are placed in Picture sheet. Image 1: 5 balls placed in 5 different cells and covered photo's wide and height Step 2: In the report sheet, design the report as you wish. I've designed in my way like below: Image 2: Kids asking to Donald Duck, which ball need to throw now Step 3: Make a drop down list "Games" in E6 cell in Report sheet from Data Validation. which is as below: Football Cricket Pool Basketball Tennis You can do an IF function here in E6 in Report sheet, which will meet a certain condition and returned Football, Cricket, Pool, Basketball or Tennis. Step 4: Now the tricky part is...
Read more

Cumulative Closing Balance like a Bank Statement in PivotTable

A Bank Employee in many case needs to calculate the Closing Balance after each transaction in PivotTable like a Bank Statement . But with the help of Calculated Field of a PivotTable , you can only calculate Field with Field . It's not possible to use a formula in Calculated Field where you can mention a single cell with Relative Reference Cell . Because a PivotTable acts like a Table format where you can work with Field or Column Name . A Helping Column (A regular column, that helps to get a partial result where formula applied) can be a good idea. But there is an option in PivotTable by which you can Calculate Cumulative . In this article I will show you, how you can do it. Calculating with Formula: Assume that, you have an Excel file with Debit and Credit transaction of an Account . Image 1: Sample Bank Statement In a Bank Statement , Month wise Cumulative Balance is important. In the Excel sheet, as above, it is very easy to use a formula and calc...
Read more