How to Wipe and Clean Excel Spreadsheets Thoroughly

Microsoft Excel is an extremely useful tool that has revolutionized the way we crunch data. Now, data analysis can be performed at home, work or school by the average person who owns a computer. A person using Excel for the first time can be up and running in a manner of minutes, entering data and formulas into cells and finding answers to questions they have about their data.

Not only is MS Excel useful, it is also a very widely-used piece of software. A copy of the software ships standard with most PCs today and is also in common use on many Macs. Excel has been around for over 20 years and has gone through a number of new versions along the way. Each new version has introduced more features and a more user-friendly interface.

Excel has a large number of uses and areas of application. In general terms, the most common uses of Excel are to perform what-if scenarios, store data as if in a database, and to perform simple or complex calculations for financial or engineering applications.

A Common Problem: How to Wipe Clean a Spreadsheet or Range of Cells

Given the increasing availability of large amounts of data, Excel users are now processing larger and larger data sets within individual files (or workbooks, as they are called in Excel). In addition, people may use the same file over and over again over a period of months or even years, adding, moving, changing and deleting data on a weekly or even daily basis. These, among other factors, can lead to the need to effectively wipe and clean a spreadsheet (or a range of cells).

There are different techniques you can employ to clean your spreadsheet. In some cases, you may want to delete the data but preserve the formatting. In other situations, you may want to completely delete everything in the cell, including data and formatting. Also, there are certain situations that advanced users will encounter whereby certain functions (e.g., VLOOKUP) are not performing as they should. In many such case, the culprit is dirty the data needs to be cleaned up by removing some "dirty data" not visible to the eye but that is affecting your functions.

Here are common techniques for to wipe and clean Excel. Let's start with the most common techniques and work our way to down to the heavy duty techniques for stubborn worksheets.

1. To remove data only, use the Delete key

This one is known to most any Excel user, but it's worth reviewing because it will do the trick for any situation whereby you just want to delete the data in a cell but leave the formatting in place. Just highlight the cell or range of cells in question and press delete. Or, you can right-click and select Delete from the menu.

2. To delete only formats or only data but not both use the Clear function

If you want to remove just data (values) or just formatting but not both, the Clear function comes in handy. Just highlight the cell or cells in question and, from the Home tab in Excel, find the Editing section (far right side of the Excel ribbon) and find the little eraser. Select the down arrow next to the eraser and select the function you want. Select Clear Formats to remove formats but keep the values and select Clear Contents to remove values but preserve the formatting.

3. Paste as Values into a new worksheet

If some of the data in the worksheet is giving you problems and you just want to start fresh, try copying the entire worksheet into a worksheet in a new workbook (file). To do so, just highlight the entire worksheet (ctrl + A will do the trick) and then copy the contents (ctrl + C). Then, open a new file and select all cells (ctrl + A again). Instead of pasting, however, you want to select Paste as Values (this will keep you from copying over any uncooperative or unwanted formatting). Just right-click and select Paste, then Paste as Values from the Paste dialog box. Be sure to save your new workbook!

4. Copy and paste into a text editor and remove unwanted characters

This one is a last resort, but it has saved my skin on more than one occasion. Use this if you still suspect there is unseen (or "dirty") data in the background that is messing up your day. (Note: you will find this a lot when you bring in data from unknown sources, for example). First, select the entire worksheet and copy the contents, then paste the contents into a text editor program like Notebook (ships for free with Windows) or Text Pad (not free but worth the price). If you notice any odd characters that weren't there before, you have found your culprit. Just use "Find and Replace" to eliminate them, then copy/paste the resulting data back into Excel - preferably in a fresh file.

At least one of these methods to wipe and clean Excel should help you out in a time of need! Download a FREE Excel cheat sheet at: www.Untangle-Your-Thoughts.com

Grab a Copy of My Excel - Hands On Guide!

Microsoft Excel Functions And Formulas Guide

Discover more about using Functions and Formulas - CLICK HERE!