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

Function Keys F1 – F12, what are they and how to use them

Welcome to the first edition of “Tech Made Easy.” This is where the rubber meets the road and tech short cuts are made easy.

Hello, my name is Aubrey Love and I am the newest edition to the Fort Sill Tribune as a contract writer. I have been working in the computer industry since the PC was invented back in the 70’s and am now a retired U.S. Army vet. In the past I have worked for such companies as Dell Computers in the Engineering Research and Development lab, NASA in the IT lab, Lockheed Martin in the Flight Simulator lab and various other businesses. I have written countless tech manuels/instructions, white papers and a book “Linux the Other Windows” and am currently writing “Linux Made Simple.” Over the years I have picked up a great deal of tips, tricks and shortcuts and look forward to sharing some of them with you.

Function keys are a time saver when you need to get a lot done with limited time to do so. Shortcut keys are a combination of two or more keys that simulate some functions of a mouse or other pointing devices.  On a side note, you may in some instances use the ctrl, alt and shift keys in conjunction with the function key as a functional shortcut.

In some work environments you are provided a cardboard template or a peel and stick label placed above or around your function keys to indicate what those keys will do within your specific work programs or software. This may be a database, a special word processer or personnel scheduling program designed specifically for your company. When you are working in those programs you will use the function keys in that manor but when you exit that program and open a more generic or commercial program such as Microsoft Windows or Excel your template should not apply and they will revert back to the default operations of the function and shortcut keys.

Taking a look at your keyboard you will notice a row of “F” keys at the very top. These are known as function keys. Most keyboards today are equipped with keys F1-F12. However some keyboards (especially the older IBM keyboards) may also have keys F13-F24.  The following is a list of the function keys and their basic definitions. Here we will be covering keys F1 through F12 only. Since keyboards with function keys F13 through F24 are no longer used, they will not be discussed in this article.

F1 – This one is almost always reserved as the help key. Most programs will display a help dialog box with a search option and index. Pressing the “Windows” key (located on the bottom row of your keyboard) plus the F1 key will open the Microsoft Windows help and support center.

 

F2 – In Windows this will highlight the name of any selected folder or icon on your desktop allowing you to simply start typing and rename the selected icon or folder. This is also used to enter the CMOS setup during the computer boot up process for some computer brands such as Dell Computers. It is not recommended to use this option unless you are quiet familiar with the internal settings of how your computer boots and runs. Pressing ALT + Ctrl + F2 will open a document window in Microsoft Word if that program is installed. Ctrl + F2 will display the “print preview” window in Microsoft Word.

 

F3 – This function key will open a search feature for most programs such as Microsoft Windows while you are at the Windows desktop.

While in MS-DOS mode or a Windows command line mode pressing the F3 key will repeat or retype the last command you typed in.

Pressing the shift key + F3 while in Microsoft Word and most other word processors will change the font from upper to lower case.

In Microsoft Outlook pressing the Windows Key + F3 will open the “Advanced” find/search window.

 

F4 – On older computers that are still running Microsoft Windows 95 through Windows XP will open the “find” window.

This function key will also open the address bar in Windows Explorer and Internet Explorer.

It will also repeat the last action performed within Microsoft Word version 2000 and newer.

ALT + F4 will close the program window currently active in Microsoft Windows.

For those of us who are constantly multi-tasking, pressing the Ctrl + F4 closes the open window within the current active window in Microsoft Windows.

 

F5 – In all current Internet browsers, pressing the F5 key will refresh or reload the page or document window that you are currently viewing. If you have multiple tabs open it will only refresh the tab you are currently viewing and has no effect on the others until you click on those tabs and press the F5 key.

In Microsoft Word the F5 key will open the find, replace and go to window.

If you are using Microsoft PowerPoint, F5 will begin your slide show.

 

F6 – In Internet Explorer, Mozilla  Firefox and most other browsers will move the cursor to the “address bar.”

Ctrl + Shift + F6 toggles between other open Microsoft Word documents. On a side note pressing the ALT + Tab will toggle you between all open applications.

 

F7 – This will be a real time saver for someone that does a lot work in Microsoft Word or emails in Outlook. Pressing the F7 key will start the spell check and grammar check of your word document or email.

If you need a thesaurus simply highlight the word you would like to check and press the shift + F7 keys.

Caret browsing – no this is not referring to diamonds in your computer, this feature will place a moveable cursor in your web pages allowing you to select text with the keyboard. If you are not familiar with exactly how this works then I would suggest doing a little more research on it before you use it. However, in the interest of getting the most from your computer and if you are so inclined to experiment, go ahead and activate Caret browsing and try it out. After all, turning it off is just as easy as turning it on.

 

F8 – This one doesn’t really have a lot of function on the desktop but it can be a real life saver in the event that you are not able to boot into Windows successfully. If your computer just will not boot into Windows or you constantly get the infamous “Blue Screen of Death” then the F8 key may cure your problems. Simply power off your computer then reboot, on first boot you should hear one beep. This is good, that is a system self test and the single beep indicates all is well so far. Just after the single beep press the F8 key to be taken to a menu screen. If you were unable to boot into Windows successfully in the past attempts then you should use the up arrow keys to highlight the “Safe Mode” option and press the enter key. Once you have reached the desktop in safe mode you can now reboot the computer as you normally would and all should be fine. If not, press the F8 key again after rebooting and choose one of the system restore options.

 

F9 – While using Microsoft Word this option will refresh the word document.

When in Microsoft Outlook it will refresh your email account sending and receiving any pending emails.

If you are using a program named Quark 5 or later this function key will open the measurements toolbar.

 

F10 – In Microsoft Windows this function activates the menu bar of any open application.

Pressing the Shift + F10 provides the same function as right clicking (or left clicking if you are using a left hand mouse) on a hyperlink or any highlighted file or icon.

This function key will also allow you access to the hidden recovery partition on most Compaq, HP, and Sony computers.

The F10 key is also used to enter CMOS on some computers.

 

F11 – This will allow you to enter and exit the full screen mode in all current/modern Internet browsers.

This function key will also allow you to access the hidden recovery partition on most Dell Computers. In some instances you may need to press the Ctrl + F11 keys to activate this hidden recovery partition on Dell Computers.

This function key will also allow you access to the hidden recovery partition on most eMachines, Gateway and Lenovo computers.

 

F12 – If you are working in Microsoft Word this function key open the “Save as” window.

Most of us are familiar with the shortcut “Ctrl + S” to save the changes to a word document and Shift + F12 will do the same.

Pressing the Shift + F12 will open a document in Microsoft Word.

The Ctrl + Shift + F12 will print the currently open document in Microsoft Word. It will also preview a page in Microsoft Expression Web, if you are using that program.

 

So there is the short and to the point facts of how to save a great deal of time by simply making use of your function keys. One of the more prominent things I have learned over the years is that no one knows everything about computers and I really don’t anticipate any one person ever achieving that goal. With that said, if you have any shortcuts, tips or tricks that you would like to share for upcoming articles please feel free to contact me pailwriter@outlook.com

 

 

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

Defining the Internet

Have you ever wondered what all those acronyms used on and about the Internet represent? Well, wonder no more. Below is a list of some of the basic and more commonly known acronyms that we use daily but may not know exactly what it stood for. There a lot more acronyms out there and we will cover them in later issues.

1. The Web vs. the Internet –

The Internet is a massive interconnection of computer networks. It is comprised of millions of computing devices that swap volumes of information back and forth. It’s not just computers, GPS units and cell phones also access the Internet as do car alarms, game consoles and even some home appliances such as washing machines and refrigerators.

The Internet started in the 1960’s as a military project, initially dubbed “packet networking” and later developed into “ARPANET.” During the 1970’s and 80’s while the “Internet” was still developing most business’ used the “Sneakernet,” a process where a file was saved to a floppy disk and then handed off to a “runner” who would deliver the file to the recipient. Since the runners wore sneakers “for running” the term sneakernet stuck. The Internet was NOT developed by Al Gore as he claimed some years ago.

The “Internet” houses layers of information, these layers are called “protocols”. The World Wide Web is the most popular of these layers. Other layers or “protocols” are; File Transfer Protocol (FTP), Telnet, Gopherspace, instant messaging and email. We will cover these additional protocols in later issues.

2. HTTP and HTTPS

HTTP is a technical acronym that means “HyperText Transfer Protocol”. This is the “language” of web pages. When your web page has one of these extensions, they should display your text, images and links in a web browser.

HTTPS is basically the same as above but the “S” indicates a secure connection. This means that the web page you are viewing has an added layer of protection/encryption to hide your passwords and other personal information. When you log on to a web site for your bank, email, or other data sensitive sights you should see the HTTPS preceding the web page name.

3. IP Address

Your computer’s Internet Protocol (IP) address will be either a 4 part or 8 part electronic serial number. An example of a 4 part would be Google’s IP address “74.125.224.72” (without the quotes) and an 8 part would be “21DA:D3:0:2F3B:2AA:FF:FE28:9C5A” which is a Microsoft Technet site. Any computer or other device that accesses the Internet is given at least one IP address. Regardless of what website you navigate to, or where you send that email to your computer and location are recorded with you IP address. It’s similar to your home address, when you mail a hard copy letter and put your return address on the envelope that would be like your IP address.

In the early days of the Internet, “www.whatever-website.com” did not exist. If you wanted to visit a web site you had to insert the actual IP address in the line. Like in the example above for Google the IP address would be “74.125.224.72” thus you had to input the number sequence to go to that web page. Just typing “Google” would not cut it. How did we keep it all straight? A “phone” book of course. Yes back in the day an Internet page phone book was published each year just as a home phone book was.

4. URL

URL stands for Uniform Resource Locator’s. These are the web browsers of the Internet pages we visit. Instead of putting in the IP address, as mentioned above, we can now simply input the URL such as Google.com and be directed to the correct web page. URL’s work with the IP addresses to allow us to name, locate and bookmark specific web pages. URL’s use three parts to address a web page or file. The protocol, which is the “//:”, the filename/pagename itself and the host computer which ends in “.com, .net, .org” or something of this nature.

5. ISP

ISP is your Internet Service Provider. This is the company that provides you access to the Internet whether it be through a private company or a government entity. Your ISP will offer a variety of packages with different price ranges offering web access, email, hosting your own web page or blog and so on. The Internet connection speeds will also vary depending on the package you sign up for.

These days we also have WISP’s available. WISP is an acronym for Wireless Internet Service Provider. Most often these are available through you cell phone provider and offer much the same packages as a home based ISP.

Hot-Spots are wireless broadcasts of a business or personal home based ISP. These can be found at most restaurants, hotels, banks and other businesses.

6. P2P

P2P stands for “Peer to Peer”. It appears to be the most voluminous Internet activity or process in current times. P2P is the trading or sharing of files among millions of users. This is accomplished by installing specific software on your computer and voluntarily sharing specific photos, files, music, movies, ebooks and software programs with others.

This file sharing is fully legal for the most part, as long as what you share is your own work or you have release rights. Web sites such as Pirate Bay would be a good example of this file sharing in action. Some of the content on Pirate Bay is fully legal to transfer while other files on their site are “bootlegged”, meaning they are illegal copies.

7. E-commerce

E-commerce stands for “Electronic Commerce”. In short, it’s an on-line store as opposed to a “brick and mortar” store. If you have ever purchased anything on-line then you have used E-commerce. Everyday there are billions of dollars transferred on-line through these stores. Buying on-line has its advantages such as wider selection, cheaper prices and no sales tax. In turn it has its disadvantages such as not knowing exactly what your getting or the quality of the item until it arrives at your door.

8. Keywords vs Tags/Labels

Keywords can be one or more words to start your initial search. They help you find general or specific things on the Internet. In order to more define your search you can use quotes, hyphens or a colon.

Tags (sometimes called Labels) are “keywords” that you link to your website or blog to assist Internet surfers find your site. Item specific tags will get you a few hits from surfers while more vague tags will bring in a broader range of surfers.

9. Blogs or Blogging

A blog (aka web log) is an on-line version of a writers column. Both amateur and professional writers can publish blogs on any topic. A blog doesn’t have to be a written article, it can also be a collection of photos, art work or anything else you would like to post on-line. Anyone can start a blog and some people actually make a reasonable income by posting advertisement links on their blog page. You can create a blog for free at a number of locations such as WordPress.

10. Addons and Plugins

Addons are custom enhancements or modifications for existing/pre-installed software. A user will install addons to improve a particular program’s functions. An example would be adding a custom Ebay toolbar to your Internet browser or a new tool option in Excel. Most addons are free and can be downloaded from the Internet.

Plugins are typically for web browsers allowing special features to work or work better while surfing th e Internet. Examples include: Adobe Flash or Shockwave player, Microsoft Silver-light or Adobe Acrobat reader.

So here you have a quick breakdown of some of the more popular terms and their meanings regarding the Internet. Now you know a little history as well as the actual meaning of these acronyms.

If you have any short-cuts, tips or tricks that you would like to share for upcoming articles please feel free to contact Aubrey Love via email at pailwriter@outlook.com

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