Bạn đang xem bài viết 5 Keyboard Shortcuts To Paste Values In Excel / 2023 được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
Bottom line: Learn 5 different ways to copy and paste values to remove all formulas and formatting.
Skill level: Beginner
What is Paste Values?
Copying and pasting values is probably one of the most common tasks we do in Excel.
Paste Values will paste the values ONLY of the copied range WITHOUT formulas and formatting. This allows us to extract the numbers or text from cells.
There are a TON of reasons to paste values.
One common use is for scenario analysis where we want to “freeze” numbers that are results of formulas and place them in some blank cells.
Another common use is when we want to paste numbers or text into a range that already contains formatting. Pasting values will not change any existing formatting that is applied to the cell/range.
In the image above, the Scenario 1 column already contained both cell formatting (colors) and number formatting. When we paste values, any existing formatting in the paste range will NOT change.
In the first example above, the blank cells have the default General format, and that is why there is no number formatting applied when we paste values.
The Paste Special Menu
Paste Values is one of the many pasting options on the Paste Special menu.
The Paste Special… button on those menus opens the full Paste Special Menu.
Keyboard Shortcuts for Paste Values
There are keyboard shortcuts for all of the Paste Special commands. As I mentioned before, the most common we use is Paste Values.
In the video above I share 5 keyboard shortcuts (plus a bonus) to paste values. Here is a list of the shortcuts.
Alt, E, S, V, Enter (Mac: Ctrl+Cmd+V)
Alt, H, V, V
Menu Key + V
Custom Quick Access Toolbar (QAT) Button: Alt+1
Ctrl+V, Ctrl, V
Custom shortcut with The Paste Buddy Add-in.
In the video I also mention my article on the best keyboards for Excel keyboard shortcuts.
If you’re more of a mouse user then checkout my article on my favorite mouse for Excel.
How do you Paste Values?
Thank you! 🙂
Copying and pasting is a very frequently performed action when working on a computer. This is also true in Excel.
It’s so common that almost everyone knows the keyboard shortcuts to copy Ctrl + C and to paste Ctrl + V.
This can be frustrating as sometimes you’ll only want the values to copy and not any of the other stuff in the cells.
In this post, you’ll learn 8 ways to copy and paste only the values from your Excel data.
Cell formatting including font colour, fill colour, number formatting and borders.
A data validation dropdown list.
Paste Special Keyboard Shortcut
If you want to copy and paste anything other than an exact copy, then you’re going to need to become familiar with paste special.
My favourite method to use this is with a keyboard shortcut.
Copy the data you want to paste as values into your clipboard.
Choose a new location in your workbook to paste the values into.
Press Ctrl + Alt + V on your keyboard to open up the Paste Special menu.
Select Values from the Paste option or press V on your keyboard.
Press the OK button.
Paste Special Legacy Keyboard Shortcut
This keyboard shortcut is a legacy shortcut from before the Excel ribbon command existed and it’s still usable.
Once the Paste Special menu is open you can then press V for Values.
Paste Special from the Home Tab
If you’re not a keyboard person and prefer using the mouse, then you can access the Paste Values command from the ribbon commands.
Select and copy the data you want to paste into your clipboard.
Select the cell you want to copy the values into.
Go to the Home tab.
Select the Values clipboard icon from the paste options.
Paste Values with Hotkey Shortcuts
Since the paste values command is in the ribbon, that also means you can access it with the Alt hotkeys.
Notice when you press the Alt key, the ribbon lights up with all the accelerator keys available.
Paste Values with Quick Access Toolbar Command
If it’s a command you use quite frequently, then why not put it in the quick access toolbar?
Depending where in the quick access toolbar you place it, it will also get it’s own easy to use Alt hotkey shortcut too.
Check out this post for details on how to add commands to the quick access toolbar, or this post on other interesting commands you can add to the quick access toolbar.
Select All Commands from the drop down list.
Locate and select Paste Values from the options. You can press P on your keyboard to quickly navigate to commands starting with P.
Press the Add button.
Use the Up and Down arrows to change the ordering of commands in your toolbar.
Press the OK button.
If you place it in the 4th position like in this Example, then you can you Alt + 4 to access it with a keyboard shortcut.
Paste Values Mouse Trick
Select the range of cells to copy.
Hover the mouse over the active range border until the cursor turns into a four directional arrow.
Select Copy Here as Values Only from the menu.
This is such a neat way, and there are a few other options in this hidden menu that are worth exploring.
Paste Values with Paste Options
There’s another sneaky method to paste values.
Paste Values and Formulas with Text to Columns
I don’t really recommend using this method, but I’m going add it just for fun.
A few caveats with this method.
You can only copy and paste one column of data.
It will keep any formulas.
If that’s exactly what you’re looking for, then this method might be of interest.
Select a single column of data ➜ go to the Data tab ➜ select the Text to Column command.
You can also select Fixed width as we won’t be using the text to column functionality it doesn’t really matter.
Paste Values with Advanced Filters
This one is another not-quite paste values option and is listed for fun as well.
With your data selected go to the Data tab then select the Advanced command in the Sort and Filter section.
Select Copy to another location.
Leave the Criteria range empty.
Select a location to place the copied data.
Press the OK button.
You can then remove the cell formatting that’s left by going to the Home tab ➜ Clear ➜ and selecting the Clear Formats option.
Wow! That’s a lot of different ways to paste data as values in Excel.
It’s understandable there are so many options given it’s an essential action to avoid carrying over unwanted formatting.
You’re eventually going to need to do this and there are quite a few ways to get this done.
What’s your favourite way? Did I miss any methods you use?
Undo, Redo shortcut keys for MAC & Windows and other foremost needed Functions:
Easy and Essential keyboard shortcut functions of Excel you’ve been looking for.
There is a list of essential Excel Shortcuts which will help you get in your Comfort Zone. If you spend a lot of time in Excel then you must know about these Easy Excel Shortcuts. The Undo Shortcut in Excel is the must-have and the handiest shortcut that people Should learn.
Look at the following functions. These are not displayed as buttons in the toolbar of the text editor. Whereas, you can use these keyboard shortcuts for a copy. Learn about the Keyboard Shortcuts in Word & Keyboard Shortcuts in Excel.
What are the keyboard shortcut keys for undo?
The list given below guides about the list of Keyboard Shortcuts. Which is Undo Shortcut keys, that will help you know how you can undo on different operating systems? Let us see for PC Desktop, Laptop and the copy shortcut for mac
#1 The Undo Shortcut Key in Excel or the shortcut key in Word for PC desktop and laptop = Ctrl + Z
#2 The Undo Shortcut Key in Excel or the shortcut key in Word Apple desktop and laptop = ⌘ + Z
#3 The Undo Shortcut Key in Excel or the shortcut key in Word Google Chromebook = Ctrl + Z
Keyboard Shortcuts for UNDO & REDO in Windows & Mac
Keyboard shortcuts to perform UNDO & REDO in windows & mac will be as follows:
The Keyboard Shortcut Command for Undo is Ctrl + Z or Alt + Backspace in Microsoft Windows and Command + Zin Apple Macintosh.
Tip: Many of the programs even support pressing the above keys multiple times for multiple undo’s. Well, in this case where you’re using Adobe Photoshop and the other programs, you would need to add additional keys to perform a multiple undo. Does anyone know what happens in Adobe Photoshop? Here, you press Ctrl + Alt + Z in order to perform the multiple undo’s. This undo & redo can also be used in Microsoft Word.
These are the ways by which you’ll be able to make use of the UNDO Shortcut Key in Excel and the Redo Shortcut Key in Excel.
Summary: You can press Ctrl + Z to undo any changes in Excel. On the other hand, pressing Ctrl + Y will redo the undo command. These Undo-redo Shortcuts can also be used multiple time to perform undo-redo changes,
If you’ve undone something and you want to Redo your Action, you have got the Redo Shortcut key. In order to redo something, you’ve undone, you need to press Ctrl + Y which the keyboard shortcut key for Redo or press F4. (In case, if F4 fails to work, you’ll need to press the F-Lock key or the Fn Key and then Press F4).
In order to Redo an action or Redo something that has undone, you’ll need to press ⌘ + Y.
In this tutorial, you guys might have looked at the shortcuts for undo, redo.
You can even repeat the action by pressing F4.
In this blog, we have explained how you can use the Shortcut keys for the illustration of how you can undo and redo your work, by using the Undo Shortcut key and making use of the Redo Shortcut Key you can experiment or try the task on your Worksheet.
What is to be kept in mind is, with the support of the Quick Access Toolbar you’ll show the Undo, Redo, and Repeat commands. This will make it a lot easier for you to see and understand the history of your changes.
Whenever you try on a Formula, suppose you wish to Calculate the Total price of a Product.
In the Next step, you apply the currency formatting on the Unit Price and the Total columns.
And then finally, you plan to apply a light green border to the entire table.
Now when you’ll go and check on the Undo command on the ribbon tab, you’ll see each change listed there or you learn this from Advanced Excel Course. You can make use of the same drop-down menu to undo all the 4 changes. Then you’ll be able to use the drop-down option under the redo in order to redo all the 4 changes. Well, what Excel does is, Excel tries to keep a record of the last 100 changes in the file. And don’t you think this is the most powerful way to move back in time, in case if you end up making a mistake.
Steps for Undo & Redo Shortcut Keys
To reverse your last action, just by pressing CTRL+Z. You can reverse more than one action.
This is going to reverse your last Undo action. You just need to press CTRL+Y. Reverse not just one but more than one action that has undone by you. Make use of the Redo Command as the Keyboard Shortcut Key once you have to use the Undo command.
While you’re about to perform a function on all the content in the text editor, you’ll need to make the selection. Place the cursor anywhere in the text editor and then press CTRL+A.
In order to view the text editor in the full-screen mode. The shortcut key is F11. Press the F11. And in order to exit the full-screen mode, press F11 again.
A Quick Recap of Undo & Redo Shortcut Keys in Excel:
The shortcut key for the Undo option is Ctrl + Z on Windows and Command + Z on a Mac. Each and every time I use this shortcut, Excel will move back one step.
The shortcut for Redo is Ctrl + Y on Windows and Command + Y on the Mac.
Just like Undo, the times you’ll use this shortcut, you are definitely likely to move forward one step.
So, remember the point that you can use the shortcuts for Undo and Redo as many times as you wish to. You can step backward and forward both through your changes.
Then comes the repeat command. The Repeat Command works a bit differently. This Shortcut Key will allow you to perform the same command again and again with just a simple shortcut. The shortcut which is used for the repeat is F4 or you can even use Ctrl + Y on Windows. When using the same for Mac or Apple, you can use the Command + Y on a Mac.
Well, not all the commands can be used on repeat, but the ones that can be used will let you enable the repeat button in the quick access toolbar.
So, let’s consider an example, the last thing we did in the earlier steps was to apply a border, so that you’re able to use the repeat shortcut whenever you need to apply the same border to the summary table.
And if I plan to apply a fill into this header row, you’ll be able to make use of the repeat for that as well.
But, if you plan to add a formula with the aim to summarize the sales by the color using SUMIF function…
The Repeat command is not available.
Did you know that the repeat shortcut can be well utilized? and also, can be used from time to time for a variety of actions? This includes formatting, inserting of sheets, and then deleting of the rows.
When you want to Repeat an action
In order to repeat an action or to repeat a task, this could be a paste Operation. What are you going to do? You need to press Ctrl + Y or F4. In case, if F4 fails to work. You’ll need to press the F-Lock key or the Fn Key and then Press F4.
In order to Repeat action on Mac. Or to repeat something simple, this could be a paste Operation, press ⌘ + Y
Note: This can also be used in Microsoft Word 2000, 2006, 2010, 2013, 2016, 2019
Hàm VALUE là gì?
Trong Excel, hàm VALUE sẽ lấy số trong một chuỗi ký tự trong bảng tính Excel, hay hiểu đơn giản đó là đổi chuỗi ký tự thành số. Hàm VALUE này thường được kết hợp với các hàm khác như hàm LEFT, hàm RIGHT, hàm MID.
Cách sử dụng hàm VALUE trong
=VALUE(text) là cú pháp của hàm VALUE
Trong đó text là một chuỗi số được đặt ở trong dấu ngoặc kép hoặc công thức, tham chiếu đến ô chứa chuỗi số mà các bạn muốn chuyển đổi.
Text có thể là định dạng của ngày tháng, thời gian hay hằng số, nếu text không phải các định dạng này thì hàm VALUE sẽ báo lỗi.
Hàm VALUE thường được kết hợp với những hàm cắt chuỗi như hàm LEFT cắt chuỗi ký tự bên trái, hàm RIGHT cắt chuỗi ký tự bên phải và hàm MID cắt chuỗi ký tự ở giữa. Khi cắt chuỗi thì định dạng các chuỗi chưa phải là định dạng số nên cần hàm VALUE để chuyển sang dạng số.
Ví dụ về hàm VALUE trong Excel
Bước 1: Với bảng này thì chúng ta sẽ sử dụng hàm cắt chuỗi RIGHT để cắt ra 2 số cuối. Công thức sẽ là =RIGHT (B3,2).
Kết quả sẽ lấy được 2 số ở trong cột ký tự mã nhân viên. 2 ký tự này sẽ là định dạng Text.
Bước 2: Để lấy nguyên ký tự cuối cùng trong cột mã nhân viên, mà ở đây chỉ lấy số 1 định dạng số trong dãy ký tự, chứ không lấy 2 số là 01, bạn cần sử dụng thêm hàm VALUE.
Chúng ta có công thức là =VALUE (RIGHT (B3,2)) rồi nhấn Enter.
Bước 3: Ngay lập tức chúng ta sẽ thấy kết quả được trả về là số 1 với định dạng là số.
Kéo xuống các ô ở dưới để hiện các kết quả còn lại. Như vậy hàm VALUE đã kết hợp với hàm RIGHT sẽ giúp chúng ta trả về kết quả định dạng số.
Cập nhật thông tin chi tiết về 5 Keyboard Shortcuts To Paste Values In Excel / 2023 trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!