MULTI IN ONE: Microsoft Excel

Insert Drawing Objects into your Excel Spreadsheets

<div dir="ltr" style="text-align: left;" trbidi="on">
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
A drawing can liven up a dull spreadsheet. Some good line art, or even simple shapes, can help illustrate your data. In this lesson, you'll see how to add simple shapes, and textboxes to your spreadsheet.</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<br /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
First, look at the spreadsheet below. Unless you know about Cosines, Adjacent angles, and Hypotenuse, the data below will be a bit bewildering:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="An Excel 2007 Spreadsheet" height="160" src="http://www.homeandlearn.co.uk/excel2007/images/2Shapes.gif" width="410" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
However, add a few shapes, along with some colour, and it becomes clearer what the data is for (the Cosine in the image below has been formatted to 2 decimal places):</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="An Excel 2007 spreadsheet with Shapes" height="441" src="http://www.homeandlearn.co.uk/excel2007/images/2Shapes3.gif" width="603" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
We'll now show you how to produce a spreadsheet like the one above. Don't worry if you haven't a clue about Cosines - it's not important for this lesson. (We'll show you the formula, though.)</div>
<h3 align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
How to Draw a Shape on an Excel Spreadsheet</h3>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
To insert a shape on your spreadsheet, do the following.</div>
<ul style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<li>From the Excel Ribbon, click on <b>Insert</b></li>
<li>Locate the <b>Shapes</b> panel:</li>
</ul>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Shapes in Excel 2007" height="85" src="http://www.homeandlearn.co.uk/excel2007/images/2ShapesPanel.gif" width="144" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
For Excel 2013 users, locate the <b>Illustrations</b> panel instead. The Shapes item is on there:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Shapes in Excel 2013" src="http://www.homeandlearn.co.uk/excel2007/images/2013/section3/shapes.gif" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
On the <b>Shapes</b> panel, click the drop down arrow to see all the available shapes:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Available Shapes" height="575" src="http://www.homeandlearn.co.uk/excel2007/images/2ShapesPanel2.gif" width="261" /></div>
<ul style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<li>Under <b>Basic Shapes</b>, select the <b>Right Triangle</b></li>
<li>Hold down your left mouse button on your spreadsheet, and drag to create your shape. Let go when you have a decent sized triangle. You'll see something like this:</li>
</ul>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="A triangle on the spreadsheet" border="1" height="239" src="http://www.homeandlearn.co.uk/excel2007/images/2Shapes4.gif" width="253" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
The green circle (white in Excel 2013) allows you to rotate the shape. The other circles (and squares) are sizing handles. Hold your mouse down over one of these and drag to resize your shape, if it's not the size you want it.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
But we'd like the triangle pointing the other way. So hold your mouse down on the green circle, and drag to rotate your triangle:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img border="1" height="238" src="http://www.homeandlearn.co.uk/excel2007/images/2Shapes5.gif" width="254" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
You should see an outline, like the one above. Let go of your left mouse button when it is in position:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Rotate the Shape" border="1" height="219" src="http://www.homeandlearn.co.uk/excel2007/images/2Shapes6.gif" width="255" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
As you can see, the green circle is now on the left hand side.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
If you look on the Excel Ribbon at the top, you'll notice that it has changed - a<b>Format</b> tab has appeared. You'll see all the various options for shapes. Locate<b>Shape Fill</b> on the <b>Shape Styles</b> panel, and click to see the <b>Fill</b> options:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Shapes Fill" height="311" src="http://www.homeandlearn.co.uk/excel2007/images/2ShapesShapFill.gif" width="174" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Select a colour for your triangle. You'll also want to select a <b>Shape Outline</b>, underneath Shape Fill. Select the same colour as your Fill, and your triangle will look something like this one:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img border="1" height="212" src="http://www.homeandlearn.co.uk/excel2007/images/2Shapes7.gif" width="255" /></div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<br /></div>
<h3 align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Add a Text Box to an Excel Spreadsheet</h3>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
To get the letter B in the triangle, we'll add a text box. So, on the <b>Insert Shapes</b>panel again, you'll notice a Text Box option. Click on this to select it:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Insert Shapes panel > Text Box" height="85" src="http://www.homeandlearn.co.uk/excel2007/images/2ShapestextBox.gif" width="239" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Excel 2013 users have a separate Text panel, on the left hand side. Click the Text Box item:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Text Box in Excel 2013" height="92" src="http://www.homeandlearn.co.uk/excel2007/images/2013/section3/text_panel.gif" width="249" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Now move back to your spreadsheet, hold down your left mouse button, and drag out a Text Box. Let go of the left mouse button and you'll have something like this:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img border="1" height="73" src="http://www.homeandlearn.co.uk/excel2007/images/2ShapesTextBox2.gif" width="73" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
With the cursor inside of the Text Box, simply type the letter B. Because it's text, you can highlight your letter and format it. In the image below, we've increased the font size:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img border="1" height="69" src="http://www.homeandlearn.co.uk/excel2007/images/2ShapestextBox3.gif" width="76" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
We now need to drag our Text Box onto the shape. Move your mouse over the Text Box until the cursor changes shape to four arrowheads (this can be tricky):</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img border="1" height="90" src="http://www.homeandlearn.co.uk/excel2007/images/2ShapestextBox4.gif" width="125" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Once your cursor changes shape, hold down the left mouse button and drag your Text Box on to the triangle:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img border="1" height="219" src="http://www.homeandlearn.co.uk/excel2007/images/2Shapes8.gif" width="254" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
With the Text Box selected, use the arrow keys on your keyboard to nudge it in to position. Fill the Text Box in the same way as you did for the triangle. To get rid of the text box border, click <b>Shape Outline</b> just below <b>Shape Fill</b>. Set it to <b>No Outline</b>. It will then look like this:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img border="1" height="219" src="http://www.homeandlearn.co.uk/excel2007/images/2Shapes9.gif" width="255" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
If you need to move your triangle and Text Box, you can select them both at the same time, and drag them as one. Click on your Triangle to select it. Now hold down the CTRL key on your keyboard. With the CTRL key held down, click on your Text Box. Both will now be selected:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img border="1" height="219" src="http://www.homeandlearn.co.uk/excel2007/images/2Shapes10.gif" width="255" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
With both the triangle and the Text Box selected, hold your mouse over the selected shapes. When your cursor changes to the four arrowheads, hold down the left button and drag your shapes to a new position:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img border="1" height="218" src="http://www.homeandlearn.co.uk/excel2007/images/2Shapes11.gif" width="252" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
You can finish off the formatting in the normal way. In the image below, we selected all the cells surrounding the shape, and added a background colour from the <b>Home</b>menu, <b>Font</b> panel.</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img border="1" height="237" src="http://www.homeandlearn.co.uk/excel2007/images/2Shapes12.gif" width="379" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
If you look again at the finished version, you'll see the rest of the colours we chose. These are just filled cells from the <b>Home > Font </b>panel:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="The finished spreadsheet" height="441" src="http://www.homeandlearn.co.uk/excel2007/images/2Shapes3.gif" width="603" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
The text in the cells is just entered in the normal way. The formula for the Cosine in cell G22 of our spreadsheet has this syntax:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>=DEGREES(COS(Adjacent_Cell_Reference / Hypotenuse_ Cell_Reference))</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
An example of how to use is it this:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>=DEGREES(</b>COS(F18 / F10)<b>)</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
When the user types in a value for the Hypotenuse or the Adjacent, the Cosine number will change.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
But you can add any shapes you want to liven up your spreadsheet. It doesn't have to look plain, white and dull!</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<br /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
And that completes this beginners course on Excel. It may have a little taxing along the way, but if you've finished all of it, you should have quite a few new skills to show off!</div>
</div>

Insert Drawing Objects into your Excel Spreadsheets

A drawing can liven up a dull spreadsheet. Some good line art, or even simple shapes, can help illustrate your data. In this lesson, you...

Object Linking and Embedding

<div dir="ltr" style="text-align: left;" trbidi="on">
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
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.</div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<br /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
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 to Word 2013 as well as Excel 2007 to 2013.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
First, create the simple spreadsheet below, and enter the formula shown in cell E3:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Create this spreadsheet in Excel 2007" border="1" height="145" src="http://www.homeandlearn.co.uk/excel2007/images/2ole.gif" width="351" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
When you enter a number in cell E1, the answer is placed in cell E3 (don't do this yet).</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
With your spreadsheet created, highlight the cells A1 to E3. Click on the <b>Home</b> tab in Excel. On the <b>Clipboard</b> panel, click on <b>Copy</b>.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Now switch to Word. On the <b>Home</b> tab in Word, locate the <b>Clipboard</b> panel, and the <b>Paste</b> item:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img height="109" src="http://www.homeandlearn.co.uk/excel2007/images/2ole2.gif" width="149" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Click on Paste. From the Paste menu, select <b>Paste Special</b>:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Paste Special in Excel 2007" height="136" src="http://www.homeandlearn.co.uk/excel2007/images/2ole3.gif" width="152" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
When you click on Paste Special, you'll see the following dialogue box appear:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="The Paste Special dialogue box" height="355" src="http://www.homeandlearn.co.uk/excel2007/images/2ole4.gif" width="527" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Select Microsoft Office Excel Worksheet Object from the dialogue box. On the left hand side, select Paste Link. Click OK.</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<br /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
When you click OK, Word will insert the spreadsheet from Excel:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="The Excel 2007 spreadsheet has been pasted into Word 2007" border="1" height="220" src="http://www.homeandlearn.co.uk/excel2007/images/2ole5.gif" width="516" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
It's even retained the cell formatting!</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
To check that it really does update in Word, switch back to Excel. Click inside Cell E1 and enter the number 7 (If your cells are still highlighted, just press the enter key on your keyboard). Press Enter, and you should have the same answer as in the image below:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Update the Excel 2007 spreadsheet" height="108" src="http://www.homeandlearn.co.uk/excel2007/images/2ole6.gif" width="325" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Now switch back to Word, and you should see that it too has the same answer:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="The spreadsheet in Word 2007 has been updated" border="1" height="220" src="http://www.homeandlearn.co.uk/excel2007/images/2ole7.gif" width="516" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Word has successfully linked the data from Excel ! If you don't want the updates, you would choose <b>Paste</b> from the Paste Special dialogue box instead of Paste Link.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
You can link or embed things like Charts or Pivot Tables into Word, though, and it can come in really useful.</div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<br /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
In the next part, you'll see how to spruce up an Excel spreadsheet with drawing objects.</div>
</div>

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 sim...

How to Insert Hyperlinks in Excel

<div dir="ltr" style="text-align: left;" trbidi="on">
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
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.</div>
<ul style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<li>Click inside of cell A1 of a new spreadsheet. (If you're using Excel 2013, you only get one worksheet. Add two more by clicking the plus button just to the right of Sheet1 at the bottom of Excel.)</li>
<li>From the Excel Ribbon, click the <b>Insert</b> tab</li>
<li>From the Insert tab, locate the <b>Links</b> panel</li>
<li>Click on <b>Hyperlink</b>:</li>
</ul>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Hyperlink is on the Links panel in Excel 2007" height="85" src="http://www.homeandlearn.co.uk/excel2007/images/2Hyperlinks.gif" width="58" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
When you click the Hyperlink item, you'll see the following dialogue box appear:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Insert Hyperlink" height="329" src="http://www.homeandlearn.co.uk/excel2007/images/2Hyperlinks2.gif" width="534" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
We're going to create a link to another worksheet in this same spreadsheet. So, under <b>Link to</b> on the left, click on "Place in This Document".</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<br /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
When you click <b>Place in This Document</b>, the dialogue box changes to this:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Place in This Document" height="329" src="http://www.homeandlearn.co.uk/excel2007/images/2Hyperlinks3.gif" width="480" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
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.</div>
<ul style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<li>Under "Or select a place in this document", click on Sheet3</li>
<li>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</li>
<li>Click the Screen Tip button at the top, and type some text for when the mouse is over the link</li>
</ul>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Your dialogue box will then look something like this one:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img height="329" src="http://www.homeandlearn.co.uk/excel2007/images/2Hyperlinks4.gif" width="475" /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Click OK when you're done, and you'll see cell A1 on your spreadsheet change:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="The Hyperlink has been inserted into the A1 cell" border="1" height="119" src="http://www.homeandlearn.co.uk/excel2007/images/2Hyperlinks5.gif" width="318" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Hold your mouse over the link and you should see your Screen Tip:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="The screen tip for the hyperlink" border="1" height="119" src="http://www.homeandlearn.co.uk/excel2007/images/2Hyperlinks6.gif" width="318" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
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.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
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.</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="A hyperlink to open up an existing spreadsheet" height="328" src="http://www.homeandlearn.co.uk/excel2007/images/2Hyperlinks7.gif" width="579" /></div>
<ul style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<li>Under <b>Link to</b> on the left, select <b>Existing File or Web Page</b></li>
<li>Navigate to the location of your spreadsheet from the Look in area</li>
<li>Select the spreadsheet to open</li>
<li>Type some text, and a Screen tip</li>
<li>Then click OK</li>
</ul>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
When you click your new link, the spreadsheet file you selected will open.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<br />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!</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<br /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
In the next part, we'll take a look at Object Linking and Embedding in Excel.</div>
</div>

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 ...

Web Integration

<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
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.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
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.</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<br /></div>
<h3 align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
How to run a Web Query in Excel 2007 to 2013</h3>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
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!</div>
<ul style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<li>Open Excel</li>
<li>Connect to the internet, if you're not already online</li>
<li>Click inside A1 on your new worksheet</li>
<li>From the Excel Ribbon, click on <b>Data</b></li>
<li>From the Data tab, locate the <b>Get External Data</b> panel:</li>
</ul>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="The Get External Data panel in Excel 2007" border="1" height="84" src="http://www.homeandlearn.co.uk/excel2007/images/2webQueryExtDatPanel.gif" width="277" /></div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="External Data, Excel 2013" height="92" src="http://www.homeandlearn.co.uk/excel2007/images/2013/section3/external_data.gif" width="278" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
From the Get External Data panel, click on <b>From Web</b>. You'll then see the following dialogue box appear:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="The New Web Query dialogue box" height="412" src="http://www.homeandlearn.co.uk/excel2007/images/2webQuery.gif" width="584" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
The idea is that you type the address of a web page and then click Go. Excel will then fetch the data for you.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
So, in the Address box, where it says about:blank in the image, type the following address:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>http://www.homeandlearn.co.uk/ME/webquery1.htm</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
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:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Web Query Options" height="296" src="http://www.homeandlearn.co.uk/excel2007/images/2webQueryOptionsDB.gif" width="290" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
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.)</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Click OK on the Options dialogue box to return to the New Web Query. Now click the Go button.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
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:</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
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.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
If Excel is successful, you'll see the data appear in the Web Query window:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="The Web Query Window" height="467" src="http://www.homeandlearn.co.uk/excel2007/images/2webQuery4.gif" width="620" /><br />
<div align="left">
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:</div>
<div align="center">
<img border="1" height="157" src="http://www.homeandlearn.co.uk/excel2007/images/2webQuery5.gif" width="525" /></div>
</div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
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:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Import Data" height="245" src="http://www.homeandlearn.co.uk/excel2007/images/2webQuery2.gif" width="290" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
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:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Excel is fetching the data" border="1" height="125" src="http://www.homeandlearn.co.uk/excel2007/images/2webQuery3.gif" width="322" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
If the import is successful, your spreadsheet should look like ours below:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="The web page has been imported into the Excel 2007 spreadsheet" height="241" src="http://www.homeandlearn.co.uk/excel2007/images/2webQuery6.gif" width="620" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
As you can see, the data from our web page has been imported into Excel! Let's try another one.</div>
<h3 align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Web Query Two</h3>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
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.</div>
<ul style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<li>At the bottom of Excel, click on Sheet1</li>
<li>On the fresh worksheet, click inside cell A1</li>
<li>Click on the Data menu, then on click From Web on the Get External Data panel</li>
<li>In the New Web Query Address box, type the following Address (don't click the Go button just yet):</li>
</ul>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Click the Options button in the top right of the dialogue box:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img height="296" src="http://www.homeandlearn.co.uk/excel2007/images/2webQuery7.gif" width="290" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
This time, select Full HTML Formatting, as in the image above. Click OK, then click the Go button.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Excel will bring back your data. Click the yellow box with the arrow in it to select all the data:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img height="475" src="http://www.homeandlearn.co.uk/excel2007/images/2webQuery8.gif" width="630" /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Click the <b>Import</b> button at the bottom when your dialogue box looks like the one above.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
When you see the Import Data dialogue box, just click OK. The data will then be imported into Excel:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Some of the HTML formatting has not imported successfully" height="235" src="http://www.homeandlearn.co.uk/excel2007/images/2webQuery9.gif" width="445" /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
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!</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
In other words, you may have to spend time re-formatting your spreadsheet.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
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.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<br /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
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.</div>
</div>

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, ...

How to add an error message to an Excel Spreadsheet

<div dir="ltr" style="text-align: left;" trbidi="on">
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
In the previous part, you saw how to add drop down lists to your Excel spreadsheets. In this part, we'll display an error message for our users. If you haven't already done so, you need to do the previous tutorial first.</div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<br /></div>
<h3 align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Data Validation - restricting what data can go in a cell</h3>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
You can also restrict what goes in to a cell on your spreadsheet, and display an error message for your users. We'll do this with our Comments column. If users enter too much text, we'll let them know by displaying a suitable error box. Try the following:</div>
<ul style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<li>Highlight the E column on your spreadsheet (the Comments column)</li>
<li>From the <b>Data Tools</b> panel, click <b>Data Validation</b> to bring up the dialogue box again</li>
<li>From the <b>Allow</b> list, select Text length:</li>
</ul>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Select Text Length" border="1" height="155" src="http://www.homeandlearn.co.uk/excel2007/images/2DataValidText.gif" width="163" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
When you select Text Length from the list, you'll see three new areas appear:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img border="1" height="164" src="http://www.homeandlearn.co.uk/excel2007/images/2DataValidText2.gif" width="271" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
What we're trying to do is to restrict the amount of text a user can input into any one cell on the Comments column. We'll restrict the text to between 0 and 25 characters.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
The first of the new areas (Data) is exactly what we want - <b>Between</b>. For the minimum textbox, just type a 0 (zero) in there. For the maximum box, type 25. Your dialogue box should then look like this:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img border="1" height="164" src="http://www.homeandlearn.co.uk/excel2007/images/2DataValidText3.gif" width="269" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
To add an error message, click the<b> Error Alert</b> tab at the top of the Data Validation dialogue box:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="The Error Alert Tab" border="1" height="250" src="http://www.homeandlearn.co.uk/excel2007/images/2DataValidText4.gif" width="386" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Make sure there is a tick in the box for "Show error alert after invalid data is entered".</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
You have three different Styles to choose from for your error message. Click the drop down list to see them:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="The error styles" border="1" height="125" src="http://www.homeandlearn.co.uk/excel2007/images/2DataValidText5.gif" width="159" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
In the <b>Title</b> textbox, type some text for the title of your error message.</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Type a Title for your error" border="1" height="149" src="http://www.homeandlearn.co.uk/excel2007/images/2DataValidText6.gif" width="359" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Now click inside the error message field and type some text for the main body of your error message. This will tell the user what he or she did wrong:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Type the error message that the user will see" border="1" height="147" src="http://www.homeandlearn.co.uk/excel2007/images/2DataValidText7.gif" width="359" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Click OK on the Data Validation dialogue box when you're done.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
To test out your new error message, click inside any cell in your Comments Column. Type a message longer than 25 characters. Press the enter key on your keyboard and you should see your error message appear:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Our Error Message" height="114" src="http://www.homeandlearn.co.uk/excel2007/images/2DataValidTextError.gif" width="428" /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
As you can see, the user is prompted to Retry or Cancel. But our title (Too many characters) is at the top, our Stop symbol is to the left, and our Error message is displaying nicely!</div>
<h3 style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Hiding Spreadsheet Data in Excel 2007 to 2013</h3>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
The data that went in to our lists doesn't need to be on show for all to see. You can hide this text quite easily.</div>
<ul style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<li>Highlight the columns with your data in it (F, G and H for us)</li>
<li>Click on the <b>Home</b> tab from the top of Excel</li>
<li>Locate the <b>Cells</b> panel</li>
<li>On the Cells panel, click on <b>Format</b>. You'll see the following menu:</li>
</ul>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Hide and Unhide in Excel 2007" height="438" src="http://www.homeandlearn.co.uk/excel2007/images/2HideCells.gif" width="258" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Move your mouse down to <b>Hide & Unhide</b> and you'll see a Sub Menu appear:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Hide Columns in Excel 2007" height="138" src="http://www.homeandlearn.co.uk/excel2007/images/2HideCells2.gif" width="145" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Click on Hide Columns from the Sub menu. Excel will hide the columns you selected:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="The columns have been hidden" border="1" height="261" src="http://www.homeandlearn.co.uk/excel2007/images/2HideCells3.gif" width="379" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
In the spreadsheet above, the columns F to H are no longer visible.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
To get them back again, highlight the columns E and I. From the same sub menu, click <b>Unhide Columns</b>.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<br /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
In the next part, we'll explore Web Integration in Excel.</div>
</div>

How to add an error message to an Excel Spreadsheet

In the previous part, you saw how to add drop down lists to your Excel spreadsheets. In this part, we'll display an error message for ...