The most straightforward way of entering text and formulas in your workbooks is to type it in. But no matter how fast you type, it pays to know all the alternative ways of getting data and formulas into your workbooks. These range from importing the data from an existing file, connecting to an external data source, speeding up data entry by maxing out AutoCorrect, taking advantage of the powerful AutoFill feature, and using copy and paste in all its flavors. You can even using the Replace feature to enter text.
If you have the data you need in an existing file, you can usually import it into Excel. Excel can import files in these five formats:
NOTE: Most spreadsheet applications and database applications can export data in CSV format or TSV format. You can also create these files manually if necessary. For example, if you need to create some data on a mobile device that doesn't have a spreadsheet application, you can create it as a text file with a comma separating each field.
NOTE: Importing works best when you need all the data from a file. When you need only some of the data, copy and paste is usually more effective. Alternatively, you can import all the data and then delete what you don't need.
To import data from an existing file, follow these steps:
File
Import
to display the Import dialog box (see Figure 1–13).
Figure 1–13. In the Import dialog box, select the appropriate option button based on the type of file you want to import.
NOTE: To import data from a FileMaker Pro database, you must have FileMaker Pro installed on your Mac. Without FileMaker Pro, Excel can't read a FileMaker Pro database.
When you import data from a comma-separated values file or a text file, the Text Import Wizard walks you through the import process.
On the wizard's first screen (see Figure 1–14), start by making sure that Excel has selected the Delimited option button if your file is delimited with commas, tabs, or another character. If it's a text file that uses spaces to create fixed-width columns, select the Fixed width option button.
Figure 1–14. Make sure that the Text Import Wizard has made the right choice between the Delimited option button and the Fixed width option button. You can also decide at which row to start importing.
If you need to skip some rows at the beginning of the file, increase the value in the Start import at row box. To start from the beginning, make sure this box is set to 1.
If the preview in the Data preview box looks wrong, open the File origin pop-up menu, and choose the encoding the file uses. For example, if the file was created on a Windows PC, you may need to choose the Windows (ANSI) item to make the text display correctly. If the file was created on a Mac, choosing Macintosh in the File origin pop-up menu should do the trick.
When the preview looks okay, click the Next button to move to the second screen (see Figure 1–15).
Figure 1–15. On the second screen of the Text Import Wizard for a CSV or TSV file, make sure that Excel has identified the correct delimiters.
NOTE: For a text file laid out with fixed-with columns, the second screen of the Text Import Wizard lets you create, delete, and move break lines to make the fields the right widths. The options on the third screen of the Text Import Wizard are the same for a text file laid out with fixed-width columns as for a delimited text file.
In the Delimiters box, make sure Excel has selected the check box for the delimiter the file uses—for example, the Tab check box.
Select the Treat consecutive delimiters as one check box if you want Excel to treat two or more consecutive delimiters as a single delimiter. Using this setting has the effect of collapsing blank fields in the data. Normally, you won't need to use this setting.
If your data file uses single quotes or double quotes to mark strings of text, make sure that Excel has selected the right type of quotes in the Text qualifier pop-up menu. If not, select it yourself.
Check that the data in the Data preview box looks okay, and then click the Next button to display the third and final screen of the Text Import Wizard (see Figure 1–16). On this screen, you can set the data format of any column by clicking the column in the Data preview box and then selecting the appropriate option button in the Column data format box:
Figure 1–16. On the third screen of the Text Import Wizard for a CSV or TSV file, you can set the data format for each column. You can also tell Excel not to import particular columns you don't need.
NOTE: If you need to set Excel to use a different character than the period as the decimal separator or the comma as the thousands separator, click the Advanced button on the third screen of the Text Import Wizard. In the Advanced Text Import Settings dialog box that opens, choose the appropriate setting in the Decimal separator pop-up menu and the Thousands separator pop-up menu, and then click the OK button.
When you finish choosing text-import settings, click the Finish button. The Text Import Wizard imports the data into the active worksheet.
When you import data from a FileMaker Pro database, the FileMaker Pro Import Wizard (see Figure 1–17) walks you through the process of choosing the right fields.
Figure 1–17. On the first screen of the FileMaker Pro Import Wizard, choose the layout or table you want to import, pick the fields, and then arrange them into your preferred order.
On the first screen of the FileMaker Pro Import Wizard, follow these steps:
When you've chosen the fields you want and arranged them into your preferred order, click the Next button to display the second and final screen of the FileMaker Pro Import Wizard (see Figure 1–18). On this screen, you can set the criteria to pick only the records you want from the table or layout you chose.
Figure 1–18. On the second screen of the FileMaker Pro Import Wizard, set up the criteria needed to limit the import to only the records you want.
If you want all the records in the table or layout, you can simply click the Finish button. But if you want to strip the table or layout down to only records that match particular criteria, follow these steps:
When you've nailed down your criteria, click the Finish button to close the FileMaker Pro Import Wizard. The Wizard displays the Returning External Data to Microsoft Excel dialog box (see Figure 1–19).
Figure 1–19. In this dialog box, choose the Existing sheet option button or the New sheet option button as appropriate. Then click the OK button.
In the Where do you want to put the data area, select the Existing sheet option button if you want to put the incoming data on the worksheet you've selected. Usually, this is the best bet. The text box shows the active cell; if you need to change it, you can type a different address, or click in the workbook and then click the cell you want to use as the upper-left corner of the range. Otherwise, if you want to create a new worksheet and put the data on it, select the New sheet option button.
Click the OK button to close the Returning External Data to Microsoft Excel dialog box. The Wizard queries the database, returns the records that match your criteria, and then enters them in the worksheet you specified.
When you import data from an HTML file, Excel simply opens the file without displaying any Import Wizard screens. You can then manipulate the contents of the file as needed.
If you have an external data source that contains data you need to use in your Excel worksheet, you can connect the worksheet to the data source and pull in the data so you can work with it in Excel.
To get the data into the worksheet, you use the same tools we just discussed for importing data—for example, you use the FileMaker Pro Import Wizard to import data from a FileMaker Pro database. After importing the data, you can refresh some or all of it as needed, updating the worksheet with the latest data from the data source.
Chapter 11 explains how to work with external data.
As you work in a worksheet, the AutoCorrect feature analyzes the characters you type and springs into action if it detects a mistake it can fix or some formatting it can apply. This feature can save you a lot of time and effort and can substantially speed up your typing, so it's well worth using—but you need to set it up to meet your needs. AutoCorrect also has some features that can cause surprises, so you'll want to choose settings that suit the way you work.
To set up AutoCorrect, choose Tools
AutoCorrect
from the menu bar to display the AutoCorrect preferences pane in the Excel Preferences dialog box (see Figure 1–20).
Figure 1–20. You can use AutoCorrect to enter text quickly in your worksheets as well as to correct typos and create hyperlinks.
With the AutoCorrect preferences pane displayed, select the check box for each option you want to use:
www.apress.com
) or a network path (such as \server1users
). This option is helpful if you want live hyperlinks in your workbooks—for example, if you're making a list of products with URLs, and you want to be able to click a link to open the web page in your browser.If you select the Correct TWo INitial CApitals check box or the Capitalize first letter of sentences check box, you can create exceptions that tell AutoCorrect not to fix particular instances of two initial capitals or sentences.
To create exceptions, click the Exceptions link under either the Correct TWo INitial CApitals check box or the Capitalize first letter of sentences check box. Excel displays the AutoCorrect Exceptions dialog box; the left screen in Figure 1–21 shows the First Letter tab, and the right screen in Figure 1–21 shows the INitial CAps tab.
Figure 1–21. Use the First Letter tab and INitial CAps tab of the AutoCorrect Exceptions dialog box to set up exceptions to the rules for capitalizing the first letter of sentences or lowercasing the second letter of a word.
On the First Letter tab, list the terms that end with periods but after which you don't want the next word to start with a capital letter. Office starts you off with a list of built-in terms, such as vol. and wk. To add a term, type it in the Don't capitalize after text box, and then click the Add button.
On the INitial CAps tab, list the terms that start with two initial capital letters that you don't want AutoCorrect to reduce to a single capital—for example, IPv6. Type a term in the Don't correct text box, and then click the Add button to add it to the list.
On either tab, you can remove an existing term by clicking it in the list box, and then clicking the Delete button.
When you've finished setting up the exceptions, click the OK button to close the AutoCorrect Exceptions dialog box.
Inserting missed capitals and creating hyperlinks automatically helps a bit, but the main point of AutoCorrect is to correct typing errors.
AutoCorrect comes with a list of standard errors, such as “abbout” for “about,” that you'll probably want it to correct automatically. But where you can really take advantage of AutoCorrect is by creating entries that aren't errors but rather abbreviations that you type deliberately and have AutoCorrect expand for you. For example, you can create an entry that changes “vpm” to “Vice President of Marketing” or one that changes “ssf” to “Second Storage Facility, Virginia (Security Grade II)”.
NOTE: Excel shares its AutoCorrect entries with Word, PowerPoint, and Outlook, so no matter which application you create an entry in, you can use it in the others as well. Each entry must be unique—you can't use “vpm” for “Vice President of Marketing” in Word and “virtual processor module” in Excel. In Word, you can also create formatted AutoCorrect entries that contain formatting, paragraph marks, and other objects (such as graphics or tables); these entries work only in Word.
To create an AutoCorrect entry, follow these steps:
TIP: When naming an AutoCorrect entry, you'll usually want to avoid using any words or names you may need to type in your workbooks. The exception is when you always want to change a particular word. For example, if your boss has a pet hate of the word “purchase,” you could set up AutoCorrect entries to change “purchase” to “buy,” “purchased” to “bought,” and so on.
NOTE: You can enter AutoCorrect entries only into a single cell. Even if you select multiple cells and paste them into the With box in the AutoCorrect pane, AutoCorrect strips out the cell divisions.
If you need to delete an existing entry, select it either by clicking in the Replace box and typing its name or by scrolling down to it and clicking it. Then click the Delete button.
Once you've set up your AutoCorrect entries, Excel automatically replaces an entry after you type it and then press the spacebar or a punctuation key or move to another cell.
In many workbooks, you'll need to enter a series of data, such as a list of months or years, a sequence of numbers, or a progression of dates (such as every Monday). In many cases, you can save time and effort by using Excel's AutoFill feature.
AutoFill works by analyzing the contents of one or more cells you select, and then entering the relevant series of data in the cells to which you extend the selection by dragging. AutoFill figures out mathematical sequences or date sequences on the fly and uses built-in custom lists to fill in the days of the week or the months of the year. You can also create your own custom lists for data you want to teach Excel to fill in.
The best way to grasp what you can do with AutoFill is to try it. So open a workbook you're comfortable experimenting with and try the examples in this section.
First, have AutoFill fill in the days of the week. Follow these steps:
Figure 1–22. Drag the AutoFill handle down or across to fill in a series of data derived from one or more existing entries. In this case, AutoFill fills in the days of the week and repeats the series if you drag farther.
NOTE: If AutoFill doesn't work, you need to turn it on. Choose Excel
Preferences
to display the Excel Preferences dialog box, and then click the Edit icon in the Authoring area to display the Edit pane. Select the Enable fill handle and cell drag-and-drop check box, and then click the OK button to close the Excel Preferences dialog box.
Now drag through the range to select it, then press Delete to clear the data. Then follow these steps to enter a sequence of months:
Figure 1–23. To reach more AutoFill options, Ctrl+drag or right-drag the AutoFill handle, and then click the fill option you want on the context menu.
Now clear your data again and then create a sequence of numbers. Follow these steps:
Delete the data that AutoFill entered, leaving 5 in cell A1 and 25 in cell A2. Then follow these steps:
NOTE: The AutoFill context menu also contains items for filling the cells with formatting only (copying the formatting from the first cell), filling the cells without formatting (ignoring the first cell's formatting), and filling in days, weekdays, months, and years.
Those are the basics of AutoFill. But you can also create custom AutoFill lists to quickly enter exactly the data you need.
If you need to enter the same series of data frequently, follow these steps to create your own AutoFill lists:
Excel
Preferences
or press Cmd+, (Cmd and the comma key) to display the Excel Preferences dialog box.Figure 1–24. You can supplement Excel's built-in AutoFill lists by creating your own data series.
TIP: If you need to create another list from worksheet data at this point, you don't need to close the Excel Preferences dialog box. Instead, click the Collapse Dialog button to the left of the Import button to collapse the Custom Lists dialog box to a shallow dialog box, then drag through the list on the worksheet. Click the Collapse Dialog button to restore the Excel Preferences dialog box, where the range appears in the Import list from cells text box. Then click the Import button to bring in the data.
Often, you can enter text more quickly in your workbooks by pasting it in from other sources. For example, if you receive sales figures in an e-mail message, you can copy and paste them into a worksheet. To paste, select the upper-left cell of the range of cells into which you want to paste the data, and then give a Paste command—for example, click the Paste button on the Standard toolbar or press Cmd+V.
Excel lets you paste data in with or without all its formatting. Usually it's easiest to go ahead with a straightforward paste operation and then use the Paste Options action button (see Figure 1–25) to change the result if it's not what you want.
Figure 1–25. Use the Paste Options action button to switch pasted text from using its original formatting to matching the formatting of the destination.
When you paste in data you've copied from another application and you know you don't want to paste the formatting, use the Paste Special command like this:
Edit
Paste Special
from the menu bar or Home
Edit
Paste
Paste Special
from the Ribbon (clicking the Paste pop-up button rather than the main part of the button) to display the Paste Special dialog box (see Figure 1–26).
Figure 1–26. Select the Text item in the As box in the Paste Special dialog box to paste in data from another application without pasting its formatting.
TIP:If pasting using the Text item in the Paste Special dialog box puts all the text in one cell rather than in multiple cells, try using the Unicode Text item instead.
NOTE: The options in the Paste Special dialog box vary depending on the data you've copied. As you'll see in Chapter 4, when you paste data copied from Excel, the Paste Special dialog box provides many more options.
Often when you're laying out a worksheet, the best way to arrange the data isn't obvious at first—so you may find you've laid out your data out in rows when a column arrangement would work better.
Instead of spending hours retyping the data or using drag-and-drop to move it, you can fix the problem in moments using Excel's Transpose command. Follow these steps:
Home
Edit
Paste
Transpose
from the Ribbon, clicking the Paste pop-up button rather than the main part of the button. Excel pastes in the data, transposing its rows and columns.NOTE: If Excel displays the This Selection Is Not Valid dialog box (see Figure 1–27) when you give the Transpose command, make sure you're not trying to paste the data back into its original location. Even though the dialog box implies that you can paste the data back into the original location or an overlapping location as long as you've selected the right number of rows and columns, pasting the data back like this simply doesn't work at this writing.
Figure 1–27. The This Selection Is Not Valid dialog box may indicate that you're trying to paste the transposed data back into its original location, an overlapping location, or a location that's the wrong shape.
If you have data in a table in Word, you can paste it straight into a worksheet in Excel. Excel places each table cell in a different worksheet cell, as you'd want.
You'll get the best results from a table that has a regular layout, with the same number of cells in each row. If some rows have more cells than other rows, you may need to move some cells in Excel after pasting them. In most cases, it's easiest to make the table regular in Word (for example, by making a copy of it in a spare document and the changing the copy) before pasting it into Excel.
If you have data in comma-separated format, you can get it into an Excel worksheet in three ways:
File
Import
, then use the Text Import Wizard to control exactly how Excel imports the text.File
Open
command and the Open dialog box to open a comma-separated file in Excel. Excel automatically puts each comma-separated item in a separate cell. You can then copy and paste the data as needed.Table
Convert
Convert Text to Table
from the menu bar to display the Convert Text to Table dialog box.As you know, the Mac OS X Clipboard lets you store only one item at a time, so each time you copy something to the Clipboard, you overwrite its previous contents. But often you'll want to store various copied items so you can paste them whenever you need to. For example, you may want to store a graphics file containing your company logo or sections of boilerplate text that you need in many workbooks.
To let you store clippings and files in a single handy location from which you can paste them anywhere, Office:Mac provides the Scrapbook. The Office applications share the Scrapbook, so you can collect items in one application and use them in another.
To open the Scrapbook, choose View
Scrapbook
from the menu bar or click the Toolbox button on the Standard toolbar and then click the Scrapbook button. Figure 1–28 shows the Scrapbook with several items stored in it. The left screen shows the Scrapbook's List view, the middle screen Detail view, and the right screen Large Preview.
Figure 1–28. The Scrapbook can display its contents as a list (left), with details (middle), or as large previews (right). Use the View pop-up menu to switch views so that you can easily identify the item you want to paste.
You can add an item to the Scrapbook in any of these ways:
TIP: To make your clippings easier to find, you can add keywords to them. Click a clipping, type one or more keywords in the Keywords pane, and then click the Apply button. You can then open the pop-up menu in the upper-left corner of the Scrapbook palette, choose Keyword contains, and type the keyword by which you want to search.
To insert an item from the Scrapbook, select the cell or range in which you want to paste it. Then click the item in the Scrapbook palette and click the Paste button.
If you want to paste the item as plain text (without formatting) or as a picture, click the item and then click the Paste pop-up button. On the menu, click Paste as Plain Text or Paste as Picture, as needed.
Chances are you'll want to keep some items permanently in your Scrapbook, but you'll need to get rid of others when you no longer need them. Here's how to get rid of items:
Excel includes a powerful Replace feature that you can use to replace text items in either the active worksheet or every worksheet in the active workbook. You can limit the search to matching the case you specify or by finding the match only as the entire contents of a cell rather than within a cell's contents.
To work with Replace, follow these steps:
Edit
Replace
.Figure 1–29. You can use the Replace dialog box to quickly make changes throughout a worksheet or all the worksheets in a workbook.
Just by typing, you can easily insert any characters that appear on your keyboard—but many documents need other symbols, such as letters with dieresis marks over them (for example, ë) or ligatures that bind two characters (for example, Æ).
You can quickly insert one or more symbols in a workbook by using the Symbol Browser pane in the Media Browser.
NOTE: When you insert a symbol using the Symbol Browser, the application inserts the symbol character in the same font you're currently using—if that font contains that character. If not, the Symbol Browser substitutes a font that does have the character. By contrast, when you use the Symbol dialog box, you can see exactly which symbols are available for a specific font.
You can use the Symbol Browser pane in the Media Browser to insert a symbol in Word, Excel, or PowerPoint. To insert a symbol, follow these steps:
Insert
Symbol
from the menu bar to display the Symbols pane of the Media Browser (see Figure 1–30).
Figure 1–30 . To insert a symbol in a workbook, click the symbol in the Symbol Browser. Choose the All Symbols item in the pop-up menu to browse all symbols (left), or select only the set you're interested in (right).
TIP: Drag the slider at the bottom of the Symbol Browser to zoom in to enlarge the symbols, or zoom out to see more symbols at once.
13.58.51.228