Chapter 8 Advanced Excel 2007/2010

Create a Worksheet Template

Creating a worksheet template can really save you a lot of time. Templates are especially useful if you find yourself having to create the same spreadsheet over and over. For example, if a spreadsheet has sheets for each month of year it becomes a bit if a chore if you have to type out the heading and formula for each sheet. Instead, you only need to do it once. Then you can save it as a template. If you need a new month, you can then Insert your template. Here's how.
Open up a new workbook, with all three blank sheets at the bottom. Now delete two of the three sheets (If you have Excel 2013 you don't need to do this as there's only oneworkbook by default):
When you have deleted two sheets, the bottom left of your screen should look like this:
Now create the spreadsheet you want to use as a template. In the image below, we've just set up a simple spreadsheet with Week headings at the top and some labels down the left:
Notice how none of the data is filled in for the weeks. The reason there are zeros for the Weekly Totals and the Item Totals is because we have the formulas in place but no data for the weeks. Once it is saved as a template and inserted into a new workbook, then the data can be added. We won't have to add the formulas because they are already in place.
Once you have your spreadsheet looking the way you want it, click the Office button then Save in Excel 2007. In Excel 2010, click File > Save. In Excel 2013,click File >Save, then under Save As, select Computer. Under the Computer heading, click the Browse icon.
When the Save As dialogue box appears change the Save as type area at the bottom to Excel template. Type a name for your template:
Before clicking Save, notice the file name now ends in xltx, and that it is being saved to a Templates folder inside Excel (Custom Office Templates in later versions).
Click the Save button to save your template.
To use your template, close the current worksheet. Create a new blank workbook. Right click a sheet name at the bottom and select Insert from the menu that appears:
The Insert dialogue box should appear. Your template should be on the list, in the Generalsection:
Select your template, and click OK. Your template spreadsheet should then be inserted into your new workbook. You can delete any sheets your don't need, and rename the template. If you need a new sheet based on your template, right- click a sheet and select Insert from the menu again.

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
  • From A1 to L1, 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. (In Excel 2013, you'll see a menu when you click Format. From the menu, select Column Width.)
  • From the Format menu, click Width
Changbe the Column Width in Excel 2007
  • 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 and 2013 users, click the File tab in the top left. From the File menu, clickOptions.
When you click the Excel Options button, you'll see this dialogue box popping up:
Excel Options
Click the Customization item on the left in Excel 2007. In Excel 2010 and 2013 there is aQuick 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):
Choose Commands From
Click on Commands Not in the Ribbon. The list box will change:
Commands Not in the Excel 2007 Ribbon
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:
Customize Quick Access Toolbar in Excel 2007
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:
The Data Form on the Quick Access Toolbar
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:
A Data Form in Excel 2007
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.

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:
A Drop Down List in Excel 2007
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 for the lists
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:
Highlight the A 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:
The Data Tools Panel in Excel 2007
When you click Data Validation, you'll see the following dialogue box appear:
Data Validation
To create a drop down list, click the down arrow just to the right of "Allow: Any Value" on the Settings tab:
Click on List
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:
Select the Source for your lists
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:
Select 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:
The Source has been entered
Click OK, and you'll see the A column with a drop down list in cell A1:
The A Column now has drop down lists
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:
Select Any Value from the list
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:
The Drop Down List has been added
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:
The Subject Drop Down List
And the Grade column should look like this:
The Grade Drop Down List


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:
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:
The Error Alert Tab
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:
The error styles
In the Title textbox, type some text for the title of your error message.
Type a Title for your error
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:
Type the error message that the user will see
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:
Our Error Message
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:
Hide and Unhide in Excel 2007
Move your mouse down to Hide & Unhide and you'll see a Sub Menu appear:
Hide Columns in Excel 2007
Click on Hide Columns from the Sub menu. Excel will hide the columns you selected:
The columns have been hidden
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.

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 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:
The Get External Data panel in Excel 2007
External Data, Excel 2013
From the Get External Data panel, click on From Web. You'll then see the following dialogue box appear:
The New Web Query dialogue box
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:
Web Query Options
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:
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:
Import Data
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:
Excel is fetching the data
If the import is successful, your spreadsheet should look like ours below:
The web page has been imported into the Excel 2007 spreadsheet
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:
Some of the HTML formatting has not imported successfully
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. 

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:
Hyperlink is on the Links panel in Excel 2007
When you click the Hyperlink item, you'll see the following dialogue box appear:
Insert Hyperlink
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:
Place in This Document
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:
The Hyperlink has been inserted into the A1 cell
Hold your mouse over the link and you should see your Screen Tip:
The screen tip for the hyperlink
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.
A hyperlink to open up an existing spreadsheet
  • 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!

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:
Create this spreadsheet in Excel 2007
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:
Paste Special in Excel 2007
When you click on Paste Special, you'll see the following dialogue box appear:
The Paste Special dialogue box
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:
The Excel 2007 spreadsheet has been pasted into Word 2007
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:
Update the Excel 2007 spreadsheet
Now switch back to Word, and you should see that it too has the same answer:
The spreadsheet in Word 2007 has been updated
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.

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:
An Excel 2007 Spreadsheet
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):
An Excel 2007 spreadsheet with Shapes
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:
Shapes in Excel 2007
For Excel 2013 users, locate the Illustrations panel instead. The Shapes item is on there:
Shapes in Excel 2013
On the Shapes panel, click the drop down arrow to see all the available shapes:
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:
A triangle on the spreadsheet
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:
Rotate the Shape
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:
Shapes Fill
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:
Insert Shapes panel > Text Box
Excel 2013 users have a separate Text panel, on the left hand side. Click the Text Box item:
Text Box in Excel 2013
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 finished spreadsheet
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!

Comments

Post a Comment