5.4 PowerPoint

‚   ‚   ‚  

5.3 Excel

Spreadsheet programs make it easy for anyone ‚ even the number-phobic ‚ to create everything from simple budgets to sophisticated financial analyses. This section explains how to get the most out of the most popular spreadsheet program on the planet: Microsoft Excel.

5.3.1 Automatically Importing Data from the Web

The Internet, as you know, is chock-full of astounding amounts of data ‚ stock prices, population figures, environmental statistics, and more. But if you need to work with these numbers on a regular basis, the thought of cutting and pasting them into your own document may be enough to cause spasms in your mousing arm.

Fortunately, you can import data from the Internet directly into Excel. In fact, you can even tell Excel to update certain data automatically ‚ sort of like having a personal statistician at your beck and call.

To import data into an Excel file, you have to be online. Then:

  1. Choose Data Import External Data New Web Query .

    The New Web Query dialog box appears. In the address bar at the top, type the URL of the Web site that contains the data you want.

  2. In the Web page that appears, click the yellow arrow next to the data you want to import .

    A thick blue box appears around the data you've chosen to import, and the arrow turns into a checkbox (see Figure 5-8).

    Figure 5-8. When you tell Excel to import data from the Web, it doesn't know precisely what you want. To expedite the process, Excel identifies a large section of data on a page that it can import. From this data, you can choose what you want imported. (Most likely you don't want everything it selects, so be careful when choosing.)


  3. Click Import .

    Excel's Import Data dialog box opens, asking where you want to place the data on your spreadsheet. Choose a location and click OK. The data appears in your spreadsheet, in separate cells , as shown in Figure 5-9. You may have to do a bit of cleanup, as sometimes Excel mistakenly identifies words or numbers and imports them along with the real data you want.

  4. To update the data in your Excel spreadsheet with the latest information from the Web, place your cursor in any of the cells whose data you imported and select Data Refresh Data .

    Excel updates the information, assuming it's still available online. You don't need to have your browser open , although you do need to be connected to the Web.


Tip: You can instruct Excel to automatically extract the latest information from the Web, without manually updating the data yourself. Open your Excel file and choose View Toolbars External Data. Then place your cursor in any cell with data you want Excel to automatically update from the Web. On the External Data toolbar, click the Data Range Properties button, and then check the box next to "Refresh every...minutes." Finally, from the drop-down box, choose how often you want the data refreshed.

Figure 5-9. Excel efficiently pastes data into your spreadsheet after you import it from the Web. However, only the data transfers ‚ not any corresponding spreadsheet formulas.


5.3.2 Creating a Watermark

Excel files often contain sensitive information ‚ staff salaries, next year's budget, home phone numbers for big- name clients , and so on. Some files simply aren't meant for public viewing, so it's important that everyone who sees a copy knows that the document is confidential.

One way to make that clear is to put a watermark stamped "Confidential" in the background of the report. Similar to an old-fashioned red stamp on printed stationery, this noticeable imprint doesn't obscure anything you've typed into the cells (Figure 5-10).

Here's how to add a watermark to an Excel file:

  1. Display the WordArt toolbar by choosing View Toolbars Word Art, then click the Insert WordArt button .

    It's the leftmost button in the toolbar, and it looks like a slanted , three-dimensional letter A.

  2. In the WordArt gallery that appears, click the style of WordArt you want to use and click OK .

    While it may be tempting to get creative here, keep in mind that it's a lot easier to read horizontal text than vertically stacked letters .

  3. In the WordArt Edit Text dialog box, type the word Confidential (or any other word you want), select the font and the font size , and click OK .

    Whatever you typed appears in your spreadsheet, blocking out other text, as shown in Figure 5-11.

    Figure 5-10. You can, of course, type anything you want into the watermark: "Draft," "1999 Data," "No Dogs Allowed," and so on.


    Figure 5-11. A font size of 36 or higher is probably easiest to read.


  4. Right-click the WordArt you've just inserted, and choose Format Word Art Colors and Lines .

    For color, select No Fill. That makes the body of the WordArt transparent. For Line Color ‚ the outline of the letters ‚ select a light gray or a medium-intensity color . Click OK.

  5. Resize the WordArt so it fits on the spreadsheet and doesn't obscure any of the data .

    Even though the watermark is transparent, its outline might make some of your data difficult to see. You can move or resize a watermark by selecting it (click around it until you see little boxes at its edges, as in Figure 5-11) and then dragging its corners.

5.3.3 Speeding Up Excel with Keyboard Shortcuts

When you're typing in numbers and working with formulas, a mouse just slows you down to a snail 's pace. Speed up your work in Excel by using keyboard shortcuts. Table 5-1 lists shortcuts for common Excel tasks .

Table 5-1. Excel Keyboard Shortcuts

Shortcut Key

Purpose

Ctrl+Z

Undo

Ctrl+Arrow

Move to edge of region

Ctrl+*

Select current region

Ctrl+A

Select all cells

Ctrl+Home

Select A1

Ctrl+End

Select last cell in used range

Ctrl+Shift+End

Select from active cell to last cell in used range

Ctrl+Shift+Home

Select from active cell to A1

Ctrl+Page Up

Move to the previous sheet

Ctrl+Page Down

Move to the next sheet

Ctrl+Tab

Move to the next open workbook

Ctrl+N

Open new workbook

Shift+F11

Insert new worksheet

Shift+F3

Open the paste function window

=+FunctionName+Ctrl+A

Insert a new function

Ctrl+F3

Define name

F3

Paste name

Ctrl+Space bar

Select columns

Shift+Space bar

Select rows

Ctrl+1

Format cells


5.3.4 Quickly Add New Data to a Chart

It's a breeze to create charts in Excel using the Chart Wizard, which walks you through the process step-by-step. In an Excel file, highlight the data you want to put into a chart, click the Chart Wizard button, and follow the simple instructions.

But if you've already created a chart and merely want to include new data from your spreadsheet, there's no need to bother with the entire wizard process again.

Here's how to add new data to an existing chart:

  1. Click the chart you want to update .

    A colored box appears around the chart's source data in the spreadsheet.

  2. Move your cursor over the corner of the colored box closest to the data you want to add to the chart. When the cursor becomes a double-headed arrow (Figure 5-12), drag that edge of the box to include the new data .

    When you release the mouse, the new data appears in the chart.

    Figure 5-12. If the cursor becomes two intersecting double-headed arrows, like a cross, keep moving it slightly until it becomes one double-headed arrow. (It won't work in the cross mode.)




Windows XP Power Hound
Windows XP Power Hound: Teach Yourself New Tricks
ISBN: 0596006195
EAN: 2147483647
Year: 2003
Pages: 119

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net