Look Who it Was Random video

Excel - Master Excel with This Game-Changing Paste Special Values Trick - Episode 394

730 views · Published 12 October 2009 · 2:53 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial:  Master Excel with This Game-Changing Paste Special Values Trick.

Welcome back to the MrExcel netcast, where we bring you the latest and greatest tips and tricks for mastering Excel. I'm Bill Jelen, and today I have a game-changing tip for using the Paste Special Values feature. But before we dive into that, let me set the stage for you.

In this video, we have a table with first names in column A and last names in column B. If we want to combine these two columns into a single column, we would typically use the concatenation operator. However, I have a better solution for you. Instead of using the operator, we can use the PROPER function to automatically convert the names to upper and lower case. Simply enter =PROPER(A2&" "&B2) and drag it down using the fill handle.

Now, let's say we have the combined names in column C and we want to delete columns A and B. If we do that, all of our formulas will change to reference errors. But fear not, I have a trick that will save you time and frustration. Instead of using the traditional method of copying and pasting values, we can simply right-click on the border of the range containing the formulas, drag it to the right one cell, and then drag it back to its original position. A context menu will appear, allowing us to choose "Copy Here as Values Only." This will instantly convert our live formulas to static values without the need for multiple clicks and keystrokes.

I learned this amazing trick from a fellow Excel pro named Dave during a seminar in Columbus, Indiana. It may seem like a small and obscure tip, but trust me, it will save you a lot of time and hassle in the long run. So next time you need to convert live formulas to values, remember this trick and thank Dave for sharing it with us. Thanks for tuning in to this netcast from MrExcel, and be sure to join us again on Monday for more Excel tips and tricks.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

Table of Contents:
(00:00) Introduction
(00:18) Setting up the tip
(00:28) Concatenation operators
(00:45) Using the PROPER function
(01:01) Using Paste Special Values
(01:25) A quicker way to use Paste Special Values
(02:01) Repeating the trick
(02:13) Thanking Dave for the trick
(02:33) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Concatenation operators
Converting formulas to values in Excel
Copy Here as Values Only
Copying and pasting in Excel
Deleting columns in Excel
Excel tips and tricks
Joining columns in Excel
Paste Special Values
PROPER function in Excel
Using right-click in Excel

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152681/



Today's tip comes from Dave in Columbus, Indiana. Dave was at my Power Excel seminar last week and passed along a better way to invoke the Paste Special Values command. In the process of showing Dave's trick, I also talk about joining text using the concatenation operator, changing text from upper case to proper case, copying a formula by double-clicking the fill handle, using Ctrl z to undo. Episode 394 shows you how.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

More from this channel