Microsoft Excel 2007 to 2010

Object Linking and Embedding

Object Linking and Embedding (or OLE for short) is a technique used to insert data from one programme into another. We'll create a simple spreadsheet to illustrate the process, and place it in to Word document. When the Excel spreadsheet is updated, you'll see the Word version update itself as well.

If you don't want the data to update in Word, for example, it's called Embedding; if you do want the data to update, it's called Linking. We're going to do Linking. For this exercise, you need Word 2007 as well as Excel 2007 (or both 2010 versions).
First, create the simple spreadsheet below, and enter the formula shown in cell E3:

When you enter a number in cell E1, the answer is placed in cell E3 (don't do this yet).

With your spreadsheet created, highlight the cells A1 to E3. Click on the Home tab in Excel. On the Clipboard panel, click on Copy.
Now switch to Word 2007/2010. On the Home tab in Word 2007/2010, locate the Clipboard panel, and the Paste item:

Click on Paste. From the Paste menu, select Paste Special:

When you click on Paste Special, you'll see the following dialogue box appear:

Select Microsoft Office Excel Worksheet Object from the dialogue box. On the left hand side, select Paste Link. Click OK.

When you click OK, Word 2007/2010 will insert the spreadsheet from Excel:

It's even retained the cell formatting!

To check that it really does update in Word 2007/2010, switch back to Excel. Click inside Cell E1 and enter the number 7. Press the Enter key on your keyboard, and you should have the same answer as in the image below:

Now switch back to Word 2007/2010, and you should see that it too has the same answer:

Word 2007/2010 has successfully linked the data from Excel 2007/2010! If you don't want the updates, you would choose Paste from the Paste Special dialogue box instead of Paste Link.

You can link or embed things like Charts or Pivot Tables into Word 2007/2010, though, and it can come in really useful.

In the next part, you'll see how to reference formulas and data on other worksheets.

Microsoft Excel 2007 to 2010

How to Insert Hyperlinks in Excel

You can place Hyperlinks in the cells on your spreadsheet. To quickly go to a different worksheet or workbook, you would simply click the link. We'll see how to do that now.

• Click inside of cell A1 of a new spreadsheet
• From the Excel Ribbon, click the Insert tab
• From the Insert tab, locate the Links panel
• Click on Hyperlink:

When you click the Hyperlink item, you'll see the following dialogue box appear:

We're going to create a link to another worksheet in this same spreadsheet. So, under Link to on the left, click on "Place in This Document".

When you click Place in This Document, the dialogue box changes to this:

We'll try linking to Sheet3 on our spreadsheet. When the link is clicked on Sheet1, we want to jump to a specific cell on Sheet3.

• Under "Or select a place in this document", click on Sheet3
• Type some text in the Text to display box at the top. This is the text of your hyperlink, as it will display in the cell
• Click the Screen Tip button at the top, and type some text for when the mouse is over the link

Your dialogue box will then look something like this one:

Click OK when you're done, and you'll see cell A1 on your spreadsheet change:

Hold your mouse over the link and you should see your Screen Tip:

Try to click on your link, and you might find that nothing happens! To use the hyperlink, you have to click the link and hold your mouse down for a second or so. Let go of the left mouse button and you should jump to Sheet 3.

If you want to open up an existing spreadsheet, instead of jumping to a location in the current one, click the Hyperlink item on the Links panel to bring up the dialogue box again.

• Under Link to on the left, select Existing File or Web Page
• Navigate to the location of your spreadsheet from the Look in area
• Select the spreadsheet to open
• Type some text, and a Screen tip
• Then click OK

When you click your new link, the spreadsheet file you selected will open.

But we'll leave this brief introduction to the subject of Web Integration in Excel. There's a whole lot more you can do in this area: Upload your spreadsheet data to the web, instead of downloading like we did; save your spreadsheet as a web page; create a spreadsheet that others can interact with, email your spreadsheets, and a whole lot more besides. In fact, a whole book could be written on the subject!

In the next part, we'll take a look at Object Linking and Embedding in Excel.

Microsoft Excel 2007 to 2010

Web Integration

A Web Query is when you send a request to a web page and ask for some data to be returned. You'll see how to do that in this section, by importing data into your spreadsheet from a web page on our web site.
There are many reasons why you would want to do that. If, for example, you're a hard-working sales person out in the field, and a customer wants the latest prices, you could run a web query in Excel and pull the prices from your employer's website.

How to run a Web Query in Excel 2007/2010

You'll now learn how to use Web Queries in Excel. For this lesson, you'll need an active internet connection. We're going to connect to a web page, and download a product list straight into a spreadsheet. Off we go!

• Open Excel
• Connect to the internet, if you're not already online
• Click inside A1 on your new worksheet
• From the Excel Ribbon, click on Data
• From the Data tab, locate the Get External Data panel:

From the Get External Data panel, click on From Web. You'll then see the following dialogue box appear:

The idea is that you type the address of a web page and then click Go. Excel will then fetch the data for you.

So, in the Address box, where it says about:blank in the image, type the following address:

http://www.homeandlearn.co.uk/ME/webquery1.htm

Before you click Go, click the Options button in the top right of the New Web Query dialogue box. You'll see this dialogue box appear:

For this first web query, we're not going to change any of these settings. But the Formatting section is the one you'll use most. You can import the web page with all its current formatting, use just Rich Text formatting, or have no formatting at all. (Rich Text formatting will get you things like bold text, but won't give you any of the fancy stuff on the page.)

Click OK on the Options dialogue box to return to the New Web Query. Now click the Go button.
When you click the Go button, Excel will try to connect to the address you gave it. If it can't get through, you'll see a "Page Cannot be Found" error page:
If that's what you're getting, make sure you are connected to the internet. Check if you've typed the address correctly. Make sure that your firewall is not blocking Excel.
If Excel is successful, you'll see the data appear in the Web Query window:

Note the black arrows in the yellow squares. You can select the tables you want to import. Click the first yellow box, and it will turn green and have a tick in it. Like this one:

Once you have the data selected, click the Import button at the bottom of the New Web Query window. You'll get yet another dialogue box:

There's not much to do, here. But if you want to import the data to a different starting cell, or even a new worksheet, select the appropriate option. For this particular import, Excel is only giving us the option to view the data as a Table. Click OK and the import will begin. You should see this in cell A1 on your spreadsheet:

If the import is successful, your spreadsheet should look like ours below:

As you can see, the data from our web page has been imported into Excel! Let's try another one.

Web Query Two

The next web query we'll do will see an import of full HTML formatting. When you're finished, you'll see why this can be a problem.

• At the bottom of Excel, click on Sheet1
• On the fresh worksheet, click inside cell A1
• Click on the Data menu, then on click From Web on the Get External Data panel
• In the New Web Query Address box, type the following Address (don't click the Go button just yet):

http://www.homeandlearn.co.uk/ME/webquery2.htm

Click the Options button in the top right of the dialogue box:

This time, select Full HTML Formatting, as in the image above. Click OK, then click the Go button.

Excel will bring back your data. Click the yellow box with the arrow in it to select all the data:

Click the Import button at the bottom when your dialogue box looks like the one above.
When you see the Import Data dialogue box, just click OK. The data will then be imported into Excel:

The problem with importing full HTML is that some of that fancy formatting you did won't convert very well in Excel. In the image above, our Latest Prices heading has been mangled!
In other words, you may have to spend time re-formatting your spreadsheet.
To get the full heading back, for example, highlight the first row, from A1 to G1. Click on the Home menu, and then locate the Alignment panel. Click Merge and Centre.

But that's it for Web Queries. They are quite simple to do, and can come in handy if you're out on the road. In the next part, we'll take a look at Hyperlinks in Excel.

Microsoft Excel 2007 to 2010

Dropdown Lists in Excel

If you have to type the same data into cells all the time, then adding a drop down list to your spreadsheet could be the answer. In Excel, this comes under the heading of Data Validation.

In the example below, we have a class of students on a drop down list. We only have to click a cell in the A column to see this same list of students. You'll see how to do that now. Here's a picture of your finished spreadsheet:

In the image above, we can simply select a student from the drop down list - no more typing! We can also do the same for the Subject and Grade.

So, create the following headings in a new spreadsheet:

Cell A1 Student
Cell B1 Subject
Cell C1 Grade
Cell E1 Comments

We now need some data to go in our lists. So, type the same data as in the image below. It doesn't need to go in the same columns as ours. But don't type in Columns A, B, C or E:

The data in Columns F, G and H above will be going in to our list.

Now click on Column A to highlight that entire column:

With Column A highlighted, click on Data from the Excel Ribbon at the top. From the Data tab, locate the Data Tools panel. On the Data Tools panel, click on the Data Validation item. Select Data Validation from the menu:

When you click Data Validation, you'll see the following dialogue box appear:

To create a drop down list, click the down arrow just to the right of "Allow: Any Value" on the Settings tab:

Select List from the drop down menu, and you'll see a new area appear:

Source means which data you want to go in your list. You can either just type in your cell references here, or let Excel do it for you.

To let Excel handle the job, click the icon to the right of the Source textbox:

When you click this icon, the Data Validation dialogue box will shrink:

Now select the cells on your spreadsheet that you want in your list. For us, this is the Students:

Once you have selected your data, click the same icon on the Data Validation dialogue box. You'll then be returned to the full size one, with your cell references filled in for you:

Click OK, and you'll see the A column with a drop down list in cell A1:

However, you don't want a drop down list for your A1 column heading. To get rid of it, click inside of cell A1. Click the Data Validation item on the Data Tools panel again to bring up the dialogue box. From the Allow list, select Any Value:

Click OK on the Data Validation dialogue box, and your drop down list in cell A1 will be gone.

The rest of the column will still have drop down lists, though. Try it out. Click inside cell A2, and you'll see a down-pointing arrow:

Click the arrow to see your list:

Select an item on your list to enter that name in the cell. Click any other cell in the A column and you'll see the same list.

Adding a drop down list to your cell can save you a lot of time. And it means that typing errors won't creep in to your work.

Exercise
Add drop down list to the B and C columns. The B column should contain lists of Subjects, and the C column a list of Grades. Make sure that the cells B1 and C1 don't contain drop down lists. When you're finished, the Subject column should look like this:

And the Grade column should look like this:

In the next part, you'll see how to add an error message to an Excel spreadsheet

Free computer Tutorials

---

HOME	Search Home and Learn:
	Microsoft Excel 2007 to 2010 Data Forms in Excel If your spreadsheet is too big to manage, and you constantly have to scroll back and forward just to enter data, then a Data Form could make your life easier. To see what a Data Form is, we'll construct a simple spreadsheet. But a data form is just a way to quickly enter data into a cell. It is used when the spreadsheet is too big for the screen. To get a clearer idea of what a data form is, try this. Enter January in Cell A1 of a new spreadsheet AutoFill the rest of the months to December Now, highlight the columns A1 to L1 (click on the letter A and drag to letter L) On the Home tab in Excel, locate the Cells panel On the Cells panel, click the Format item From the Format menu, click Width Enter a value of say 20 for the Column Width, and click OK Some of your months should disappear from the spreadsheet The problem is, if you have to enter data under each month, you'd have to scroll across to complete the row. And then scroll back again to start a new row. Instead of doing this, we'll create a data form. You then enter data in the form to complete a row on your spreadsheet. No more scrolling back and forth! In the version of Excel 2007 we have, Data Forms have been hidden. They used to be sitting on the Data menu. Now they are not. In fact, quite a few menu options have disappeared in Excel 2007 and Excel 2010. To find Data Forms, click on the Office button in the top left of Excel, for 2007 users. From the Office button menu, click on Excel Options: For Excel 2010 users, click the File tab in the top left. From the File menu, click Options. When you click the Excel Options button, you'll see this dialogue box popping up: Click the Customization item on the left in Excel 2007. In Excel 2010 there is a Quick Access Toolbar item. Click that instead of Customization. The idea is that you can place any items you like on the Quick Access toolbar at the top of Excel. You pick one from the list, and then click the Add button in the middle. To add the Data Form option to the Quick Access Toolbar, click the drop down list where it says Choose Commands From. You should see this (we've chopped a few options off, in the image below): Click on Commands Not in the Ribbon. The list box will change: From the Commands Not in the Ribbon list, select Form. Now click the Add button in the Middle. The list box on the right will then look something like this one: Explore the other items you can add to the Quick Access Toolbar. You might find your favourite in there somewhere! When you click OK on the Excel Options dialogue box, you'll be returned to Excel. Look at the Quick Access toolbar, and you should see your new item: Back to the spreadsheet. Type any number you like in cell A2, under January. Then type a number in cell B2 for February. Now highlight the columns A to L again. This is so that Excel will know which is a column heading and which is the data. Click the Form item you have just added to the Quick Access toolbar: You should then see this: All the Columns in the spreadsheet are now showing. Enter numbers for the other months. To start a new row in your spreadsheet, you just click the New button on the right. In the next part, you'll see how to add drop down lists to an Excel spreadsheet. Drop Down Lists in Excel 2007/2010 --> <--Back to the Excel Contents Page View all our Home Study Computer Courses
Computer Tutorials List Beginners Computing Word 2007 to 2010 Word 2000 to 2003 > Excel 2007 to 2010 < Microsoft Excel to 2003 Web Design Cascading Style Sheets Javascript Visual Basic .NET Beginners PHP C# .NET Java for Beginners