How to remove formulas in excel but keep the values

NewArticle1
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 comma and space between the two quotation marks. Anything you put between the quotes will be shown in the Column C results. Here we have a comma and space separator. You can also use a hyphen or other numbers, letters, characters or symbols placed between the quotes.

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.
34.6528144, -98.4146529
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 tool-bar, 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:  ExcelCopyPaste

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.
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.

Advertisements

Tech Tips You Should Know

writer-cover

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 “ctrl” + “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.

Tech Support Error Codes

 

Human Error and Computer Error.

We have all heard the term “user error.” It’s the official-ish way of saying that your computer, phone or other device that appears to be having problems isn’t the source of the problem…you are.

In other words, user error is tech-speak for a mistake, and we all make them. Unfortunately, your pompous techie friend, IT help desk agent, or tech support rep will, on occasion, use his or her knowledge of your lack-of-knowledge to poke fun at your user errors.

You may not know the difference in an EEOC, HAL, or ID-10T issue, but the techie you ask for help from does… and knows you don’t. One of those is a real problem, and the other two are not-so-nice ways of making fun of you without you knowing!

Here’s a list of euphemisms for what the pretentious techie you’re talking to really wants to say: you’re an idiot. Consider yourself lucky or tech-savvy if you haven’t heard any of these.

ID-10T: The “IDIOT” Error

Pronounced as eye-dee-ten-tee, this is an “old favorite” among the tech savvy. It rolls off the tongue and sounds as legitimate as any other computer jargon you might hear.

The ID-10T joke has almost reached a point of common usage.

PEBKAC: Problem Exists Between Keyboard And Chair

This one is usually spoken as a word, pronounced as peb-kak.

Sometimes you’ll see this one as PEBCAK (swapping chair and keyboard). Other times you’ll see computer or monitor swapped for keyboard, making for all sorts of variations on this one, like PEBCAC or PEBMAC.

PICNIC: Problem In Chair Not In Computer

This one is easy to remember and has recently replaced PEBKAC.

EEOC: Equipment Exceeds Operator Capabilities

This one sounds so technical that it almost doesn’t feel mean.

The implication here is pretty clear: you’re not smart enough to use whatever you’re having trouble with.

RTFM: Read The Freaking Manual

This one seems to me like more an anger-filled reaction than a statement about your intelligence, but I have seen this used in support forums more than once.

This particular techno-insult has a variation on the ‘F’ part that I won’t spell out for you.

Code 18: The Problem is 18 inches Away From the Screen

Another “proximity” joke here, although I personally find 18 inches a bit close to sit to my screen.

The metric version of this joke is Code 40 or Error 40, so don’t let your centimeter-using friends slip one by you.

Please know, however, that there actually is a Code 18 error that you could see yourself – it’s a Device Manager error code. No, it’s not Bill Gates giving you a hard time – it means that you need to reinstall the device drivers for whatever hardware you see it on in Device Manager.

Layer 8: That’s You

The Open Systems Interconnection (OSI) model is a way of looking at how computer systems communicate. The “deepest” layer is Layer 1, the physical layer, and ends at Layer 7, the application layer – the one you and I interact with.

If you bring the OSI model a bit further out, you get Layer 8 (you), Layer 9 (your organization), and Layer 10 (your government).

This is certainly one of the more geeky ways to insult anyone without an IT degree. Now that you have a little more knowledge of these “error codes” you will be more prepared the next time you are on the phone with tech support.

More User Error Jokes

You can find a list of user error joke codes in the image “IT joke codes.” They are for your reference so you can respond appropriately when you hear one, but let’s be honest… they’re sometimes fun to dish out, too.

Yes, I did skip the offensive jokes for obvious reasons.

While no one deserves to have any of the above “jokes” hurled at them, there are a number of things you can do to make that interaction with tech support, or even your smarty-pants friend, a bit more successful. Reading these weekly tech tips is just one of them. You may also want to take a class at your local college or tech center.

1K Buffer Implies a low capacity for learning (1K is tiny)
C2K Chair 2 Keyboard issue
CBE Carbon Based Error
Code 18 The problem is 18″ away from the screen
EBCAC Error Between Computer And Chair
EBK Error Behind Keyboard
EEOC Equipment Exceeds Operator Capabilities
ESO Equipment Smarter than Operator
HKI Error Human Keyboard Interface Error
I/O Error Ignorant Operator Error (from legit Input/Output Error)
ID-10T Error The “IDIOT” Error
Layer 8 You are Layer 8 in the OSI model
OHE Operator Headspace Error
PEBKAC Problem Exists Between Keyboard and Chair
PICNIC Problem In Chair Not In Computer
RCSO Reboot Computer, Slap Operator
RTFM Read the Freaking Manual
TSTO Too Stupid To Operate
UPI User Perception Issue

 

Off-the-wall tech tips

bask-tech-tips

In past issues we have discussed tips, tricks and shortcuts that are pretty commonplace and somewhat easy for the novice computer users. This week we are going for some off-the-wall things you may not know you could do and how they can save you time and money.

Increase performance / save money

If you want to get more battery life from your laptop or want to modify performance to save energy costs, then you might want to look at one of Windows 7’s hidden (built-in) tools. The program ‘powercfg’ is designed to do just that.

  • First, let’s create a folder for our report to be saved in. Double-click on My Computer then double click on drive C and choose New Folder from the menu at the top. Name the folder whatever you choose such as ‘Report’.
  • Open a command prompt as administrator. To do this, click on the Start menu button, type ‘cmd’ in the search box. When the ‘cmd’ icon appears, right-click it and choose ‘Run as administrator.’
  • When the command prompt appears (a black box with some white text) type in the following exactly how you see it here without the quotes. “powercfg -energy -output \Report\Energy_Report.html” Remember, this is case sensitive. Press the ‘Enter’ key when done. You can change the name of the report and the folder to whatever you choose.
  • Windows 7 will take about a minute to examine the behavior of your computer while it analyzes and creates the report. Once it’s done, type “exit” to exit out of the command prompt. Now, simply double click the html report to open it and follow the recommendations to improve your computer’s performance.

Hide your computer search history

Searching through your computer using Explorer (double-clicking on My Computer) saves a history of what you have searched for in the past. This is a time saver if you look for the same type of documents frequently. However, if you share your computer with others, you may not want them to see what you search for. Here is how to turn off the ‘recent search feature.’

  • Click on the Start button and in the search box, type “gpedit” (without the quotes) and press ‘Enter.’
  • Double-click on User Configuration à Administrative Templates à Windows Components à Windows Explorer.
  • In the dialog box that appears, double-click “Turn off display of recent entries in the Windows Explorer search box” and click to select “Enabled” on the screen that appears.
  • Click “Apply” then click “Okay” and your search history is now turned off.

Customize the Shut Down Button

The default action of the Start Menu’s Shut Down button, as we all know, is to turn off your computer. What if you rarely shut down your computer but often switch users or just simply log off?

You can change the Shut Down’s default action to ‘Switch User’ or ‘Log Off’ or several other options provided by default in Windows 7.

  • To change your default, right-click the Start button and select Properties.
  • On the Start Menu tab, click the “Power button action” drop-down menu and select which action you want to be the default. Then click OK, and OK again.

Summary

As a general rule, it is not a good idea to leave a computer or monitor running for long periods of time between uses. Turning off your computer and monitor at night and for the weekend will save a bundle in electricity over a year’s time.

De-clutter your computer world

Cluttered computer station
Organized chaos

I am not asking you to throw away all your computers and other electronic devices, but isn’t it time to start syncing things into time-saving steps? Below are a few tips, tricks and shortcuts to help bring most of your digital devices to a more productive level.

Re-assign some of those unused function keys.

You see them every time you sit down to work with your computer, but how often do you actually use them. In a recent article, we discussed what they did and let’s face it, how many of those functions do you remember? It’s time to start using them for things you actually do. Here’s how;

  • Go to Netflix, Facebook, Twitter or wherever you frequently go then highlight and copy the address in the address bar of your browser.
  • Close or minimize your browser then ‘right click’ on the desktop and choose New à shortcut and paste the link in the box provided – then click Next.
  • Now, right-click on the newly created icon and choose ‘properties’ from the drop down menu.
  • On the dialog box that appears, select the “Web Document” tab and click in the “Shortcut Key” box then press the function key you would like to use for this application, such as the F7 key.
  • Now, to go to Twitter, simply press the F7 key on your keyboard.

Continue assigning various shortcuts to function keys. Remember to avoid using the F1 and F5 keys if possible. F1 is reserved for ‘help’ while F5 is reserved for refreshing your screen.

If you want to get rid of or hide all these newly created icons, simply create a folder on your desktop or somewhere else and move them to that folder.

Cross-posting on social media

Gone is the time when you had to post your comments, threads, resumes, blogs and everything else on social media sites one at a time. We are in an ever-growing social media world creating and maintaining our digital profiles whether it be for personal or business reasons.

Through a collection of different  apps to choose from, you can cross-post on multiple sites at the same time. TweetDeck, Buffer, HootSuite and IFTTT are some of the more popular examples of social media management applications that can do this for you. A simple Google search will list a larger selection of these apps and instructions on how to use them effectively.

Managing email, spam and newsletters

Even I have to admit to sometimes being overwhelmed with unwanted clutter in my email. Of all my accounts, only one has no clutter save for the daily email newsletters from http://www.codeacademy.com, which I want.

As I mentioned in a previous article, the best way to avoid all these spam emails and newsletters is to set up a ‘dummy’ email account. When you are visiting a site that asks for your email address, simply type in the dummy account, not your primary.

However, if it’s too late for that step, then try some online tools to help eliminate some of the daily clutter. Unroll.me is a great tool to help you unsubscribe from multiple newsletters with a single click . It also offers an option to combine your subscription into a daily digest email so you just receive one email per day instead of dozens. Currently, Unroll.me works with Outlook, Hotmail, MSN, Windows Live, Gmail, Google Apps, Yahoo Mail, AOL Mail and iCloud.

Managing time for emails is an issue within itself. Reading and replying to each one can be a daunting task if you simply go down the list in chronological order.  Start by spending some time to see the time of day your email is busiest. Do you get more emails between 2-3 p.m. as opposed to 4-5 p.m.?  Try to schedule just two times a day on email rather than every 10 minutes. Pick the two times per day your email is most active and set aside 30 minutes or so at those two times.

Read through all your new emails and respond to the simple ones first, then tackle the ones that may require more research or a lengthy response. You can also create a folder in your email program and call it ‘pending’, to address the more difficult emails. This will unclutter your inbox while giving these emails a priority so they can be addressed later, in the meantime, your email is more organized and your work day will be more effective.

Summary

There are as many solutions as there are problems when it comes to our digital life, making use of ‘free’ or ‘pay-for’ tools is a great way to start.

What to ask repair centers when your PC crashes

 

Computer repair center cautions.

Who do you trust with your beloved computer and all the data residing on it? It’s a question most of us take for granted as we drop off our computer at the local repair shop. Photo’s, legal documents and passwords are just a few of the things that we store on our computer thinking it is safely tucked away. Once the computer leaves your possession and is placed in a shop, how do you know all is safe?

Below is a list of important questions along with the expected answers you should receive when shopping for a repair center. If you are not comfortable with the answers your getting from a local computer repair shop or individual, you should consider other places.

How much do you charge per hour or service?

Most respectable computer repair centers will charge either by the hour or by task, depending on the issue. The hourly rate should range between $50 – $75 ‘depending on where you live.’ Too high of a price may mean that you are being overcharged and too low may mean that the repairman may not know what he is doing or he may be planning on billing for an excessive amount of hours.

Some services will be billed by “bench hours,” meaning, how many hours the computer is actually on the work bench whether attended or unattended. Expect to pay for unattended hours when your computer is using electricity and bench space while a program is being installed or updated.

Other services will be billed by  “product,” meaning, they charge a flat fee for installing a new video card or power supply.

How many billable hours will this take to repair?

The last thing you want to do is pay $60 hour for nine hours of labor, totaling $540 to repair a computer that can be replaced for three or four hundred dollars. This is where cost vs. reward comes into play. Is it worth it to fix the computer or just purchase a new one?

You should expect to hear from the repairman, “I can’t be totally sure, but these types of repairs in the past have cost an average of (x) dollars to repair. We can certainly call you if we see the cost is going to exceed the value of the computer.”

Don’t be alarmed if the technician can’t give you an exact amount over the phone, some computer problems may seem similar but are very different in reality.

Do you have a minimum charge?

Not all computer problems require nine hours on the work bench. If your issue takes only 15 minutes to repair, you may be slapped with a minimum bench time.

You should expect to hear “yes, we have a one billable hour minimum.” On part replacements, it is also common to have a flat rate for installing new hardware.

Will there be any additional charges?

While some companies stifle you with hidden fees, this simple question should clear the air. No one wants to get a quote only to find out the actual cost is much higher.

What you should expect to hear; “No. If it looks like it is going to cost more than we quoted, you will receive a phone call notifying you of the added expense and an explanation as to why.” At this point you may choose to go forward with the extra repairs or simply pick up your computer as-is.

Do you guarantee your work?

Guarantee’s and warrantees are a given in today’s society, but are they worth anything? Ask the repairman the conditions of the guarantee, if they fix the problem and it returns, what will they offer? If their response is “uhm or I’m not sure,” then try another repairman. No one wants to pay for repairs and have the same issue again two weeks later. This does not apply to all issues, for example; viruses. You may have your computer completely cleaned of all viruses and three days later be infected again.

You should expect to hear; “Yes, for most repairs we guarantee our labor and/or parts for 30 to 90 days.” Most hardware comes with a one year warranty from the manufacturer in conjunction with the local shops warranty. When replacing or upgrading parts, make sure you get new, not used if you want a warranty.

Anytime you have hardware or software programs installed on your computer, make sure you get the box, CD and literature for the product when you pick up your computer. Usually software will have a CD Key that you may need to register the product later.

Will you save my files?

Your computer is an assembly of parts that in most cases can be easily replaced. The digital items such as photo’s of your dog, video’s of your child’s first steps, legal documents and spreadsheets with your usernames and passwords are what’s really important to most of us.

The answer you should expect is; “yes, if they are retrievable.”

This also happens to fall under one of those items that are listed as a flat rate in most cases. Expect to have to pay $x per gigabyte of data retrieved. Most places will charge a flat rate of about $85 for the first gigabyte and $20 for each additional gigabyte. If your primary goal is to retrieve data from a dead hard drive, expect to send the drive to a professional lab. Generally, local computer stores do not have the ability to do this level of work. Professionals can most often retrieve data even if the drive has been formatted two or three times.

Summary:

When shopping for a computer repair center, do not hesitate to ask questions. Ask about certifications, knowledge level of the technicians and years of service. This is your computer and your sensitive data. Most often, your best option is to take it to a shop as opposed to a local guru’s house. On the reverse of that, some techies that work from home after their day job, are real brainiac’s and some are want-to-be’s, so beware.

Windows vs Mac vs Linux

LinuxWindowsMac

Move over Microsoft Windows and Mac OS X, Linux is gaining ground with speed and stability. In past articles, we have primarily dealt with the two mainstream operating systems (OS), but there is another that is dominating the world – Linux.

What is Linux

Linux is, in simplest terms, an operating system just as are Mac OS X and Microsoft Windows. It’s the software on a computer that enables the computer operator to interact between applications (software) and hardware. Many people are inclined to think that Windows is the standard while Mac OS X and Linux are rogue operating systems. Quite the contrary. Windows is a DOS (Disk Operating System) based OS while Mac and Linux are based on the very first OS – Unix.

Actually, there are number of OS’s that you may install on your home computer. A quick list of the other more popular ones are; Solaris Sun by Oracle, Free BSD, Chrome, Android, Os2 / Os2 Warp, React OS, AtheOS, SkyOS, and Free DOS. Only Mac and Windows use TV ads to promote their product, yet Linux is gaining a lot of market-share considering it has only word-of-mouth advertising.

As an operating system, Linux is developed collaboratively, meaning that its development and ongoing support is not the responsibility of one company, but rather all of them collectively. Companies participating in the Linux community share research and development costs with their partners and competitors. This would be the equivalent of Microsoft and Apple sharing technology, resources, and cost. We all know that doesn’t happen and probably never will.

Linux OS is comprised of two parts, the Kernel (code that makes up the core OS) and the GUI, (Graphical User Input.) There are only a handful of Kernels, but the GUI that makes the distribution (a combination of Kernel and GUI) are plentiful. The most popular are Mint, Ubuntu, Red Hat, Suse and Debian.

Where is Linux

Linux has grown remarkably since its first release in 1991, powering everything from the New York Stock Exchange to mobile phones to supercomputers and consumer devices such as smart refrigerators and washing machines. Linux began as a server OS and has become useful in desktop and laptop systems, gaining popularity with leaps and bounds.

Windows and OS X are predominantly found on personal computers, but Linux’s adaptability to work well on “wristwatches to supercomputers” gives the OS an edge over the other two. Quite simply put, most of the web pages that you visit each day are hosted by Linux servers, not Windows or Mac. Linux can also be installed at the factory when you purchase a new personal computer / laptop online from places such as Dell Computers.

Surprisingly, even Windows 10 contains binaries identical to the binaries running on an Ubuntu platform. (Binaries are the core function code found imbedded in the OS.)

Cost

Since Linux is an open operating system built by the masses, it costs nothing to the end user. That’s right, FREE. Unlike the bloated priced Microsoft Windows or Mac computers, Linux is free to download and install on as many computers as you like and there is no registration or activation required. Just to set things straight, I like Mac computers. After all they are Unix based like Linux, but the cost of purchasing a Mac is just too much for me. While the Linux distributions may be free, the biggest cost associated with Linux is the learning curve. The time a company devotes to training its employees on a new OS is often more than they are willing to pay at this time.

Final thoughts

The advantage of Linux over Windows is Linux is more stable and virtually virus free. It’s also much faster at starting up, running applications and shutting down. With Linux, there is no “blue screen of death” as associated with Windows. The list of major advantages could go on for pages, but you get the gist of it.

In my professional opinion, anyone wanting to migrate over to Linux should start with either Mint or Ubuntu. These seem to be the most Windows-like and user friendly of the more than 100 distributions I have worked with. If you would like more information, please email me at pailwriter@outlook.com.