How to Edit an Imported Workbook in Excel

By Stephen L. Nelson, E. C. Nelson

When you import a workbook, sometimes you will see that the data, although neatly formatted, doesn’t appear as an Excel table. You will often encounter situations like this. You can use several workbook-editing techniques to clean up a workbook.

image0.jpg

Delete unnecessary columns

To delete unnecessary columns (these might be blank columns or columns that store data that you don’t need), click the column letter to select the column. Then choose the Home tab’s Delete command.

You can select multiple columns for multiple deletions by holding down the Ctrl key and then individually clicking column letters.

Delete unnecessary rows

To delete unnecessary rows, you follow the same steps that you do to delete unnecessary columns. Just click the row number and then choose the Home tab’s Delete command. To delete multiple rows, hold down the Ctrl key and then select the row numbers for each of the rows that you want to delete. After making your selections, choose the Home tab’s Delete command.

Resize columns

To resize (enlarge the width of) a column so that its contents clearly show, double-click the column letter box’s right corner or click AutoFit Column Width on the Format button’s drop-down (Home tab). For example, column H is too narrow to displays its values. Excel displays several pound signs (########) in the cells in column H to indicate the column is too narrow to adequately display its values.

image1.jpg

Just double-click the column letter label, and Excel resizes the column so that it’s wide enough to display the values or labels stored in that column. Here, Excel has resized the width of column H to display its values.

image2.jpg

You can also resize a column by selecting it and then choosing the Home tab’s Format→Column Width command. When Excel displays the Column Width dialog box, you can enter a larger value into the Column Width text box and then click OK. The value that you enter is the number of characters that can fit in a column.

image3.jpg

For you manually inclined fiddlers, you can also resize a column by clicking and dragging the left corner of the column letter label box. You can resize the column to any width by dragging this border.

In Excel 2007 and Excel 2010, select the column and use the Home tab’s Format→Width command to display the Column Width dialog box and change the column width.

Resize rows

You can resize rows like you resize columns. Just select the row number label and then choose the Home tab’s Format→Row Height command. When Excel displays the Row Height dialog box, you can enter a larger value into the Row Height text box.

image4.jpg

Row height is measured in points. (A point equals 1/72 of an inch.)

In Excel 2007 and Excel 2010, select the row and use the Home tab’s Format→Row Height command to display the Row Height dialog box and change the row height.

Erase unneeded cell contents

To erase the contents of a range that contains unneeded data, select the worksheet range and then choose the Home tab’s Clear→Clear All command. Excel erases both the contents of the cells in the selected range and any formatting assigned to those cells.

Format numeric values

To change the formatting of values in a workbook that you want to analyze, first select the range of what you want to reformat. Then choose the Home tab’s Number command. When Excel displays the Format Cells dialog box, choose from its tabs to change the formatting of the selected range.

For example, use choices from the Number tab to assign numeric formatting to values in the selected range. You use options from the Alignment tab to change the way the text and values are positioned in the cell, from the Font tab to choose the font used for values and labels in the selected range, and from the Border tab to assign cell border borders to the selected range.

image5.jpg

The buttons and boxes that appear just above the Number command button provide for several, convenient, one-click formatting options. For example, you can click the command button marked with the currency symbol to format the selected range using the accounting format.

Copying worksheet data

To copy worksheet data, first select the data that you want to duplicate. You can copy a single cell or range of cells. Choose the Home tab’s Copy command and then select the range into which you want to place the copied data. Remember: You can select a single cell or a range of cells. Then choose the Home tab’s Paste command.

You can also copy worksheet ranges by dragging the mouse. To do this, select the worksheet range that you want to copy. Then hold down the Ctrl key and drag the range border.

Moving worksheet data

To move worksheet data to some new location, select the range that stores the data. Choose the Home tab’s Cut command and click the cell in the upper-left corner of the range into which you want to move the worksheet data. Then choose the Home tab’s Paste command.

You can also move worksheet ranges by dragging the mouse. To do this, select the worksheet range that you want to copy and then drag the range border.

Replacing data in fields

One of the most common commands used to clean up a list is the Home tab’s Find & Select command. To use this command, first select the column with the data that you want to clean by clicking that column’s letter. Next choose Find & Select→Replace so that Excel displays the Find and Replace dialog box.

image6.jpg

Enter the incorrect text that you want to find in the Find What text box and then enter the correct text in the Replace With text box. Then click the Replace All button to fix the incorrect text.