How to remove formulas in excel but keep the values

(Update: 2022-10-03

I have been getting a lot of hits on this particular article. So, I thought I would update it by adding some additional images and a bit more clarity to the subject. I hope you enjoy this article and can benefit from it. Continue reading the main article below and my other articles listed on the left side of this page. And leave a comment if you don’t mind. Thanks.)

 

If you have used Microsoft Excel, or even Open Office Calc (the free alternative to Microsoft Excel) you have most likely used or even created formulas to return a desired value. An example would be;
Column A contains a latitude, Column B contains a longitude and Column C has a formula that concatenates the two together. Here is a sample of that formula in column C:
=CONCATENATE(A2,”, “,B2)

In the formula above, notice the three commas and space between the two quotation marks following the middle comma. Anything you put between the quotes will be shown in Column C, but the quotation marks will not appear. See the sample below.

In this sample, we will put the word “Hello” within the quotes.

And here are the results of that same cell in the Excel sheet.

 

Now, getting back to our real-world example. 

Given the latitude of 34.6528144 and the longitude of -98.4146529 in columns A and B respectively, you get the following result displayed in column C.

As you can see, the results contain the comma and space (between the two numbers) that was specified in the formula between the quotation marks.

 

Okay, I know this article is supposed to be about how to remove the formulas from a calculated cell (or cells) while retaining the resulting data. Instead, all I have discussed so far is how to create a formula in a cell. True, but to remove a stain, you must first understand what the stain is and how it got there. The same theory holds true in a substantial number of aspects of life.

With that said, let’s remove those pesky formulas now that we are done with them. There are many ways you can accomplish this, and I have seen countless chapters in Excel books and dozens of web sites that have you find a specific tool on the toolbar and navigate through a dozen or more options to get your desired results. However, the simplest way is to just select the cell or cells that contain the formulas and press “Ctrl” + “C” to copy them. Next, with the cells selected, press “Ctrl” + “V” to paste the resulting values in the same cells.

Before you just hit the “Enter” key or move on to other things in your life, notice the little “clipboard” looking icon that popped up. It should look something like this: (I know the image is small, but that’s the way it is in Excel, so bare with me.)

Click on it and scroll down the menu to the “Paste Values” section of the list and choose the “Values (V)” option.

Hint, as you mouse over the options (represented by small icons), a tool tip will appear telling you what that function is called. Once you click the “Values (V)” icon, all formulas will be erased and replaced with only the results that it calculated. See the results in the two images below.

Image 1, Before

Image 2, After

Mystery solved! It really is a lot simpler than a lot of web pages make it out to be. As you can tell, I am a firm believer in the K. I. S. S. (Keep It Simple Stupid) method of thought.

 

Thank you for viewing my blog.

Also visit my new “About me” blog at https://www.aubreywlove.com

Advertisement

Tech Tips You Should Know

Back in the saddle. Well, it’s been a while since my last article and some of my followers have been asking for more tech pointers, tips and tricks. After nearly a two-year break from writing, I have decided to get back in the game and have since opened two additional blogs. So, along with this weekly tech blog, I am also hosting a “C#” and a “SQL Server” blog site.
Here are the links for the two new sites:
C# – https://pailwriteroncsharp.wordpress.com/
SQL – https://sqlfundamentals.wordpress.com/

They may seem a little rough at the moment, but bear with me and I will get them straightened out and looking good.
The two new sites are much like this one, no particular order for the articles, just a random set of pages to help the average user understand how to accomplish things “here in the real world” as opposed to just practicing from a book.
With that said, let’s jump write into todays article with 7 new tech tips for the modern-day computer user.

1.) Re-open a recently closed tab:
Have you ever had the misfortune of accidently closing a tab in your browser only to realize that’s not the one you wanted to close? Fear not, there are a couple of ways to re-open that tab.
a. The easy way:
Click on your browser to open a new tab, now press the “ctrl”, “shift” and “T” keys on your keyboard and magically it re-appears.
b. The hard way:
In the browser tool bar click on “history” (you may have to look for this since it’s in various locations on different browsers). Now scroll through until you find the page you want to re-open.

2.) New tabs for me!
With the growing number of web developers and the massive quantity of websites, most of them have multiple links per page linking to dozens of different (but related) web pages. As the quantity grows, a majority of web developers are putting in a function / feature that opens a new tab when you click on a link. It’s advantages for them to do so, because it keeps their page open in your browser while you temporarily look around on other pages. However, not all links on a web page open in a new tab.
Fear not, there is a simple fix for this also. Simply mouse over the link and click the “middle” mouse button instead of the left mouse button and the link will open in a new tab.

3.) Paste plain text:
One of the problems I’m often asked about is, how to copy/paste text without any special format, font or color background associated with the text. Often people will copy/paste some text from a web page for example; and the formatting of that text gets copied as well.
Well, there’s a couple of ways of doing this:
Probably the easiest of the two is to use “ctrl” + “c” to copy the text, and instead of using the basic “ctrl” + “v” to paste it in a word document, email, etc. use “ctrl” + “alt” + “v”. This will paste the desired text without any special formatting, coloring, etc.
The other option would be to simply do your standard “ctrl” + “c” to copy the text, and “ctrl” + “v” to paste it in a word document or email. Next, highlight that text and find the “remove formatting” button in the tool bar and click on it. It should look like the upper-case letter “A” with a pink eraser over it.

4.) Delete an entire word with one stroke:
Instead of deleting a word one letter at a time with the “backspace” button, you can place your cursor at the end of the word and press “ctrl” + “backspace”.

You can also highlight an entire row by moving your cursor to the end of a sentence and pressing “shift” + “home” on your keyboard.

5.) Display the system (computer) information window:
Traditionally, we have used the manual method of navigating to this rarely seen window by clicking on the “Start” menu, then selecting “Control Panel” then clicking on “System”. Now, we can simply press the “Windows” key and press the “Pause/Break” button on the keyboard.

6.) Going Incognito:
While there are times you may not need hidden browsing, there are also times that you really should use “incognito” browsing. For example, when your browsing or shopping on a public WIFI (hot spot), or using a shared computer.
You can open an incognito browser tab via the menu in most browsers, but why go through the hassle of navigating through those exhaustive menus.
In Firefox, Internet Explorer and Microsoft Edge, simply press “ctrl” + “Shift” + “p”. In Google Chrome and Opera, press “ctrl” + “shift” + “N”.

7.) Read Installers: (No seriously, read them):
Ever wonder how you wound up with so many programs installed on your computer that you don’t recall downloading and installing? It’s become standard practice for a lot of companies to sell space on a program to install a totally different program provided by a different vendor. It’s what I call “piggy backing” and I hate it. I have had instances where I wanted to install a program and during the installation it would default check a box to install an additional third-party program.
Too often, we just click through an install process accepting the default settings, and for the most part, this is the best method. But, be careful when you click the “Next” button, and read the details of what it is going to do each step of the way.

Also visit my new “About me” blog at https://www.aubreywlove.com