Data Validation’s Most Powerful Tool: Drop Down List

Validation2-1Last week we talked about Data Validation and the fun and excitement one could have restricting cells with it. Today, we are going to talk about one specific tool in Data Validation, the List option.

This option is great because it limits what the user can input in a cell while giving them options from a predefined list you create. This can greatly reduce input error and can streamline data input. Here is how you do it!

To start, create a list of items in cells A1 through A5, as shown below.

validation2-2

Next, click on cell B1. Open up the Data Validation menu from the Data ribbon. Choose ‘List’ and under Source type: “=A1:A5”

Validation2-3

Now when you select cell B1, a drop down arrow will appear. When you click on it, you should be able to choose from the list you created in cells A1:A5. You can change this list at anytime and it will automatically update in B1.

validation2-4

And there you have it! Be the life of the party this weekend by showing your friends this cool trick. Stay tuned for our next Excel tip post!

Data Validation: Limiting Others’ Inputs w/o Limiting your Outputs

Validation1Are you ready to learn one of the slickest tricks in Excel? Then read on, my fellow Excelerites! Today we are going to learn about Data Validation.

The goal of Data Validation is to restrict a user to only inputting certain items into a cell. For instance, you can restrict the user to only put whole numbers into a cell.

To begin, select the area that you want to restrict. Next, go to the Data tab on your Ribbon and select Data Validation.

validation2

A form will appear. You will notice that it has 3 tabs on it. Under the Settings tab, select the dropdown arrow under Allow.

Validation3

The default is Any Value. In other words, the user can input any value into the cells. Be adventurous and try experimenting with some of the options. You can start by selecting Whole Numbers. To see if your restriction worked, try inputting a restricted value into your cell. For example, let’s say you restrict cell A1 to be only whole numbers between 1-10. If you try to put a 17 in that cell, an error message will appear.

Speaking of error messages, you can customize your error message.Under the Error Alert tab, you can change the error message Style, Title, and Message. Please note that the Style will determine what type of message and restriction is allowed. For instance, the Stop option will not allow any value outside of the restriction. On the other hand, the Information option will. Both, however, will produce a popup. I created the following error message to pop up if the user tries to put a 17 in cell A1. Because I selected the Information option, the user can still change the cell value to 17 but will get this warning before.

validation4

All of this is very easy to learn. Give it a whirl! Stay tuned for our next post where we delve into the most powerful part of Data Validation: the List!

How to Fix Errors: Remove #N/A and other Errors from your Spreadsheet

fixerrors1 If you have eaten food in the last 3 days then chances are you have come across the annoying #N/A or #DIV/0! error in Excel.

Although these errors can be useful to detect possible errors, for the most part they cause spreadsheets to look messy and raise unnecessary questions from end users. Excel has created a very easy way to get around this error. Forget 3 steps, this will only take 1!

Let’s say the formula A1/B1 you copied down Column C has some pesky zeros in Column B which inevitably causes the dreaded #DIV/0!. To fix this, try copying and pasting the following formula instead:

=IFERROR(A1/B1, 0)

fixerrors2

fixerrors3

This formula is saying the following: If A1/B1 causes any type of error, put a 0 there instead of an error message, else, put the result of A1/B1.

You are not limited to replacing the error with a number. You could also put text:

=IFERROR(A1/B1, “You cannot divide by zero!”)

You can use IFERROR for any formula. For instance, here is how you would use it for a VLOOKUP:

=IFERROR(VLOOKUP($A$1:$B$8, 5, 2), 0)

Take a few minutes this week and apply the IFERROR to your formulas!

Get your mother something special next Sunday by giving her a clean-looking spreadsheet. You are bound to be hailed as the greatest son/daughter in the family.

Checking Formulas: Tracing Dependents & Precedents

checking1“Is this formula in cell A10 used for anything?” “Where are these inputs in cell Y54 coming from?” If you ever asked yourself these questions, read on.

 

1. Tracing Precedents
“Precedent cells are cells that are referred to by a formula in another cell. For example, if cell D10 contains the formula =B5, cell B5 is a precedent to cell D10.” Microsoft

To easily see which cells are referred to in a particular formula:

  1. Click on the cell you want to evaluate
  2. Click on the Formulas -> Formula Auditing -> Trace Precedents

checking2

When you click on this, Excel will create blue lines from the cells that make up the formula in the cell back to the cell you’re analyzing.

checking3

2. Tracing Dependents
“Dependent cells contain formulas that refer to other cells. For example, if cell D10 contains the formula =B5, cell D10 is a dependent of cell B5.” Microsoft

To easily see which cells use a particular formula:

  1. Click on the cell you want to evaluate
  2. Click on the Formulas -> Formula Auditing -> Trace Dependents

checking4

When you click on this, Excel will create blue lines from the cells that are dependent on the analyzed cell back to the cell you’re analyzing. In the image below we see that cell F2 is dependent on the Payment cell in C5. Trace Dependent is especially helpful when you are trying to clean up a spreadsheet. Many times people will put random calculations to the side or have redundant calculations. You can quickly see which formulas and cells are actually being used and delete the others.

checking5

If there are no Dependents or Precedents, the following message will appear:

checking6

To remove the blue arrows, click on Remove Arrows:

checking7