Chapter 8 Advanced Excel 2007 2010

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. Heres 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 dont need to do this as theres 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, weve 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 wont 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 dont 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, well 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, youll see a menu when you click Format. From the menu, select Column Width.)
• From the Format menu, click Width

• Enter a value of say 20 for the Column Width, and click OK
• Some of your months should disappear from the spreadsheet

The problem is, if you have to enter data under each month, youd have to scroll across to complete the row. And then scroll back again to start a new row. Instead of doing this, well 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, youll see this dialogue box popping up:

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 (weve chopped a few options off, in the image below):

Click on Commands Not in the Ribbon. The list box will change:

From the Commands Not in the Ribbon list, select Form. Now click the Add button in the Middle. The list box on the right will then look something like this one:

Explore the other items you can add to the Quick Access Toolbar. You might find your favourite in there somewhere!

When you click OK on the Excel Options dialogue box, youll be returned to Excel. Look at the Quick Access toolbar, and you should see your new item:

Back to the spreadsheet. Type any number you like in cell A2, under January. Then type a number in cell B2 for February. Now highlight the columns A to L again. This is so that Excel will know which is a column heading and which is the data.

Click the Form item you have just added to the Quick Access toolbar:

You should then see this:

All the Columns in the spreadsheet are now showing. Enter numbers for the other months. To start a new row in your spreadsheet, you just click the New button on the right.

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. Youll see how to do that now. Heres a picture of your finished spreadsheet:

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

So, create the following headings in a new spreadsheet:

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

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

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

Now click on Column A to highlight that entire column:

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

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

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

Select List from the drop down menu, and youll see a new area appear:

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

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

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

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

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

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

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

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

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

Click the arrow to see your list:

Select an item on your list to enter that name in the cell. Click any other cell in the A column and youll 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 wont 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 dont contain drop down lists. When youre finished, the Subject column should look like this:

And the Grade column should look like this:

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. Well do this with our Comments column. If users enter too much text, well 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, youll see three new areas appear:

What were trying to do is to restrict the amount of text a user can input into any one cell on the Comments column. Well 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 youre 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 doesnt 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. Youll see the following menu:

Move your mouse down to Hide & Unhide and youll 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

go to link download