Monday, September 5, 2011

Microsoft Excel 2007 to 2010

Dropdown Lists in Excel

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

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

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

So, create the following headings in a new spreadsheet:

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

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

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

Now click on Column A to highlight that entire column:

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

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

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

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

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

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

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

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

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

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

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

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

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

Click the arrow to see your list:

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

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

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

And the Grade column should look like this:

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

Free computer Tutorials

HOME

Microsoft Excel 2007 to 2010

Data Forms in Excel

If your spreadsheet is too big to manage, and you constantly have to scroll back and forward just to enter data, then a Data Form could make your life easier. To see what a Data Form is, we'll construct a simple spreadsheet.

But a data form is just a way to quickly enter data into a cell. It is used when the spreadsheet is too big for the screen. To get a clearer idea of what a data form is, try this.

Enter January in Cell A1 of a new spreadsheet
AutoFill the rest of the months to December
Now, highlight the columns A1 to L1 (click on the letter A and drag to letter L)
On the Home tab in Excel, locate the Cells panel
On the Cells panel, click the Format item
From the Format menu, click Width

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

The problem is, if you have to enter data under each month, you'd have to scroll across to complete the row. And then scroll back again to start a new row. Instead of doing this, we'll create a data form. You then enter data in the form to complete a row on your spreadsheet. No more scrolling back and forth! In the version of Excel 2007 we have, Data Forms have been hidden. They used to be sitting on the Data menu. Now they are not. In fact, quite a few menu options have disappeared in Excel 2007 and Excel 2010.

To find Data Forms, click on the Office button in the top left of Excel, for 2007 users. From the Office button menu, click on Excel Options:

For Excel 2010 users, click the File tab in the top left. From the File menu, click Options.
When you click the Excel Options button, you'll see this dialogue box popping up:

Click the Customization item on the left in Excel 2007. In Excel 2010 there is a Quick Access Toolbar item. Click that instead of Customization. The idea is that you can place any items you like on the Quick Access toolbar at the top of Excel. You pick one from the list, and then click the Add button in the middle.
To add the Data Form option to the Quick Access Toolbar, click the drop down list where it says Choose Commands From. You should see this (we've chopped a few options off, in the image below):

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

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

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:

Back to the spreadsheet. Type any number you like in cell A2, under January. Then type a number in cell B2 for February. Now highlight the columns A to L again. This is so that Excel will know which is a column heading and which is the data.
Click the Form item you have just added to the Quick Access toolbar:
You should then see this:

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

In the next part, you'll see how to add drop down lists to an Excel spreadsheet.

Drop Down Lists in Excel 2007/2010 -->

<--Back to the Excel Contents Page

View all our Home Study Computer Courses

Computer Tutorials List

> <

Saturday, September 3, 2011

You don't really understand human nature unless you know why a child on a merry-go-round will wave at his parents every time around - and why his parents will always wave back.