Insert Drawing Objects into your Excel Spreadsheets

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'll see how to add simple shapes, and textboxes to your spreadsheet.

First, look at the spreadsheet below. Unless you know about Cosines, Adjacent angles, and Hypotenuse, the data below will be a bit bewildering:

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):

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

How to Draw a Shape on an Excel Spreadsheet

To insert a shape on your spreadsheet, do the following.

• From the Excel Ribbon, click on Insert
• Locate the Shapes panel:

For Excel 2013 users, locate the Illustrations panel instead. The Shapes item is on there:

On the Shapes panel, click the drop down arrow to see all the available shapes:

• Under Basic Shapes, select the Right Triangle
• 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:

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.

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:

You should see an outline, like the one above. Let go of your left mouse button when it is in position:

As you can see, the green circle is now on the left hand side.

If you look on the Excel Ribbon at the top, you'll notice that it has changed - aFormat tab has appeared. You'll see all the various options for shapes. LocateShape Fill on the Shape Styles panel, and click to see the Fill options:

Select a colour for your triangle. You'll also want to select a Shape Outline, underneath Shape Fill. Select the same colour as your Fill, and your triangle will look something like this one:

Add a Text Box to an Excel Spreadsheet

To get the letter B in the triangle, we'll add a text box. So, on the Insert Shapespanel again, you'll notice a Text Box option. Click on this to select it:

Excel 2013 users have a separate Text panel, on the left hand side. Click the Text Box item:

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:

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:

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):

Once your cursor changes shape, hold down the left mouse button and drag your Text Box on to the triangle:

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 Shape Outline just below Shape Fill. Set it to No Outline. It will then look like this:

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:

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:

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 Homemenu, Font panel.

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 Home > Font panel:

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:

=DEGREES(COS(Adjacent_Cell_Reference / Hypotenuse_ Cell_Reference))

An example of how to use is it this:

=DEGREES(COS(F18 / F10))

When the user types in a value for the Hypotenuse or the Adjacent, the Cosine number will change.

But you can add any shapes you want to liven up your spreadsheet. It doesn't have to look plain, white and dull!

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!

Read More

Object Linking and Embedding

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 to Word 2013 as well as Excel 2007 to 2013.

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. On the Home tab in Word, locate the Clipboard panel, and thePaste 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 will insert the spreadsheet from Excel:

It's even retained the cell formatting!

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:

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

Word has successfully linked the data from Excel ! 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, though, and it can come in really useful.

In the next part, you'll see how to spruce up an Excel spreadsheet with drawing objects.

Read More

How to Insert Hyperlinks in Excel

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. (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.)
• 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.

Read More

Web Integration Microsoft Excel

Web Integration Microsoft Excel

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 to 2013

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.

Read More

How to add an error message to an Excel Spreadsheet

How to add an error message to an Excel Spreadsheet

Data Validation - restricting what data can go in a cell. 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:

• Highlight the E column on your spreadsheet (the Comments column)
• From the Data Tools panel, click Data Validation to bring up the dialogue box again
• From the Allow list, select Text length:

When you select Text Length from the list, you'll see three new areas appear:

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.

The first of the new areas (Data) is exactly what we want - Between. 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:

To add an error message, click the Error Alert tab at the top of the Data Validation dialogue box:

Make sure there is a tick in the box for "Show error alert after invalid data is entered".

You have three different Styles to choose from for your error message. Click the drop down list to see them:

In the Title textbox, type some text for the title of your error message.

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:

Click OK on the Data Validation dialogue box when you're done.

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:

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!

Hiding Spreadsheet Data in Excel 2007 to 2013

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.

• Highlight the columns with your data in it (F, G and H for us)
• Click on the Home tab from the top of Excel
• Locate the Cells panel
• On the Cells panel, click on Format. You'll see the following menu:

Move your mouse down to Hide & Unhide and you'll see a Sub Menu appear:

Click on Hide Columns from the Sub menu. Excel will hide the columns you selected:

In the spreadsheet above, the columns F to H are no longer visible.

To get them back again, highlight the columns E and I. From the same sub menu, click Unhide Columns.

In the next part, we'll explore Web Integration in Excel.

Read More