Sometimes when you are creating a spreadsheet that is going to be updated by more than one user, you only want a specific set of values to be available for a cell. One good example of this is a status spreadsheet, where your team members will update the status of their work daily. However, you only want to accept the values of “Not Started”, “In Progress”, and “Complete”. You could just tell them that those are the values to use, but there is always the chance that they will forget and input something else. What you need to do is give them a drop down list of those available choices. The way to do this is by using List Validation on that cell.

Validation on a cell allows you to specify what kind of values are allowed to populate that cell. In particular, List Validation will allow you to specify a range of cells from which to provide input values that the user can select from via a drop down list when the user clicks on the cell. To do this, follow the steps below:

1. Create your list of input values and give it a Name. Let’s start with our example list, shown below:

   

   As you can see, we have our input values in cells A1-A3. To give them a Name, simply highlight the cells (1) and then type the name you want in the Name input box (2).

   

2. Now that the input values are set, we can add the validation to the cell. To accomplish this, first click on the cell you want to put the validation in. Then, go to Data and then Validation. This menu will be displayed:

   

   Under the Allow: drop down, select List (1). The Source: text box will now be enabled. In this field, type “=Status” (2). Click “Ok” (3). Basically, this is setting the list of possible input values to the range of cells defined by “Status” that we set previously. These steps are illustrated below:

   

3. The Validation List is now setup on the cell. The sample cell should now have a drop down arrow on it. If you click on it, the following screen should be displayed:

   

   The user will only be able to input a value into this cell that is on the list shown.

Usually when setting up validation like this on cells, I’ll put the input values on a separate spreadsheet that I will hide and lock. This way only I can change the set of input values.

This is only a basic lesson in setting up Validation Lists. In the Data Validation dialog box, there are options to configure error messages, ect. that respond to user input. Play around with it until you find something that works for you. There are also other types of validation available that will be covered in future articles.

I wrote an article today about zipping files that you attach to an email for a number of reasons, one of which was to keep the size of both your mailbox and your audience’s mailbox low. Along these lines, if you find that you are having a mailbox size problem, one trick that can help is to go back to your sent mail and delete any large attachments that you don’t need anymore from those messages. All you have to do is open the message, remove the attachment, and then save the message. You keep the message text, but lose the attachment.

This works really well for me using Microsoft Outlook, you should take a look on your local email application to see if there is a way to do this as well.

New article today on Zipping your Email Attachments, be sure to check it out and harness the power of Zip today!

Check back later today for additional article updates.

Today, email is one of the prime means of communication at the workplace. Consequently, it can be disastrous if you don’t have access to it. How could this happen? The answer is simple: you go over the size limit on your account. This usually leads to you not being able to receive or send email until the size of your account goes back under the limit. What could cause this? The usual culprit here is large file attachments. This is why it is so essential to Zip your attachments before sending out your email.

Zip refers to a process by which you can compress files into a smaller archive, sometimes cutting the size of contents of the Zip File in half. For example, if I had two Microsoft Word documents and a Microsoft Excel document that I was going to send out to my team, I would add all three to a Zip File then attach this file to the email instead of attaching the three documents separately.

This has the following advantages:

• As stated above, this will usually drastically decrease the size of the email. This is especially true in documents that may contain a lot of pictures, charts, or process flows (such as process flows from Microsoft Visio). As a rule of thumb, I Zip any document that is half a Megabyte or more (this might show as 500kb as the file size, which is the size in Kilobytes).

• This will also keep groups of documents better organized. In the above example, this would allow the audience to keep track of one file instead of three. Also, if you were to have multiple groups of documents in an email, you could Zip each group.

• A smaller email will obviously download faster, so if you Zip your attachments your audience will appreciate the fact that didn’t have to sit there staring at their Inbox for 10 minutes while your message downloaded. You don’t always know the connection speeds of your clients, so this can be a big deal for those on dial-up connections.

The argument some people make for not zipping their files is that their email accounts have a very large size limit (which is pretty true today across the board). However, when sending attachments, you must also take into account the audience you’re sending it to. Much like their connection speed, you don’t always know how their email is configured, and it probably isn’t a good idea to kill their account because you sent unzipped attachments.

Now that you’re ready to Zip your attachments, how do you do it? This is another easy answer. In order to Zip a file, you can use any number of free programs out there. The program I use is called WinZip. You must purchase this software, but I also believe that windows has a native utility to Zip files, although I have not used it. If you do a search on “free zip programs”, you should get plenty of options.

In the end, it takes very little time to Zip your attachments, and the benefits explained above are well worth it.