- Types of Data You Enter into Excel
- Entering Different Types of Data into a Cell
- Controlling the Next Cell Selection
- Using Copy, Cut, Paste, Paste Special to Enter Data
- Using Text to Columns to Separate Data in a Single Column
- Inserting Symbols and Equations into a Cell
- Using Web Queries to Get Data onto a Sheet
- Using Series to Quickly Fill a Range
- Editing Data
- Working with Tables
- Fixing Numbers Stored as Text
- Spellchecking Your Sheet
- Finding Data on Your Sheet
- Using Data Validation to Limit Data Entry in a Cell
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Using Copy, Cut, Paste, Paste Special to Enter Data
You can copy or cut data from different sources, such as other workbooks, Word documents, or web pages, and paste the information onto a sheet. Depending on how the data appeared in the original source, you might have to modify it after you paste it in Excel.
One of the ways you can clean up data copied or cut from another source is to use the Paste Special command instead of just Paste. To access this special command, go to Home, Clipboard and click the arrow on the Paste button or right-click on a cell. Various Paste Special options will appear. If you place your cursor over one of the icons, a tip appears, as shown in Figure 3.3. You can access more options by clicking Paste Special at the bottom of the drop-down.
)
Figure 3.3. The Paste Special drop-down provides quick access to the more commonly used options. Click Paste Special at the bottom of the list to access the full dialog box.
Using Paste Special with Ranges
Figure 3.3 shows the Paste Special options available if pasting a range copied or cut from within Excel. The Paste area of the dialog box has different paste options you can choose from. For example, if you select Values, you will only paste the value of what you copied. The formatting and formulas will not be pasted. If you do want the original formatting but also the values, select Values and Number Formats. If you want a combination of values and comments, then you need to use Paste Special twice, selecting Values once and then Comments the second time.
The Operation area allows you to perform simple math on the selected range. For example, if you have a list of prices that need to go up by 1.5%, type .015 in a cell and copy the cell. Select your range of prices and bring up the Paste Special dialog box. From the dialog box, select Values (so you don’t lose any formatting you have on your prices) from the Paste area and Multiply from the Operation area. Click OK. Your prices will have increased by 1.5%.
Using Paste Special with Text
If you copy or cut data within a cell (versus the entire cell) or from a non-Excel source, such as a Word document or web page, the Paste Special options are limited. Depending on the source, text, or graphic, you may get the options shown in Figure 3.4.
)
Figure 3.4. When doing a Paste Special with text, there are fewer options available.
NOTE
The options available depend on the original source. For example, if you copied text from within a cell (versus the entire cell), you would only see pasting options for Unicode Text and Text. You wouldn’t have the option of pasting the text as a Word document object because it didn’t originate from Word.
As you select an option in the As list box, an explanation appears in the Result area at the bottom of the dialog box. If you select the Paste Link option to the left of the list, you’ll also be able to link the pasted data to its original source.
If available, the Display as Icon option lets you paste an icon instead of the text. Double-clicking on the icon opens the text in an editing application (for example, if pasting text from a Word document, you can edit the text in Word).
Using Paste Special with Images and Charts
When pasting images and charts, the dialog box is similar to that shown in Figure 3.4, but the As list box options differ, listing various image types. The different types can affect image resolution and workbook size.
Using Paste to Merge a Noncontiguous Selection in a Row or Column
If you try to copy/paste a noncontiguous selection from different rows and columns, an error message appears. But if the selection is in the same row or column, Excel allows you to copy and paste the data. When the data is pasted, though, it is no longer separated by other cells, as shown in Figure 3.5. You can use this method to create a table of specific values copied from another table.
){kind=link}
Figure 3.5. Data from rows 90, 91, and 94 was copied from the table on the left to create the list on the right using the method explained in this section.