Suppose you have a spreadsheet where you are entering a list of unique values, such as invoice numbers, customer names, etc. You don’t want to duplicate any of the data, as this would cause problems down the road, but at the same time the list might be too large for you to manually verify that no duplicate values have been entered.
You can use Data Validation to ensure that no duplicate values can be entered into your list. If a user accidentally enters a duplicate, an error message will pop-up telling them that they have entered an invalid value.
To do this, follow the steps below.
- Select the column where you want your list of unique values to appear (for illustration purposes we’ll assume it is column A).
- Click Data | Validation.
- On the Data Validation window’s Settings tab, select Allow: Custom.
- In the Formula box, type the following formula: =MATCH(A1,$A:$A,0)=ROW(A1) (if you’re using this in a different column than column A, change the “A1″ and “$A”‘s in the formula to whichever column you’re using)
- Click OK.
Now begin to type your list of unique values. If you should happen to accidentally enter one twice, you’ll see the following error message pop-up:
You can click “Retry” to re-enter a value in the same cell, or “Cancel” to delete the duplicate value.
**UPDATE**
To have a “one click” availability to this tip, use the following macro code. Again, like the formula above, change the “A1″ and “$A”‘s to whichever column you’re using.
Sub Duplicate_Validation()
'
'MACROS BY EXCELZOOM.COM
With Selection.Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=MATCH(A1,$A:$A,0)=ROW(A1)"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub
As discussed in a previous tip, adding a drop down list to your worksheet allows the users of the worksheet to enter information in a neat, consistent format.
The example in the previous tip emphasized the importance of having consistent information when summarizing information in a PivotTable. Another use is to store lists of information, which could be anything, but in this example, I’m going to use website addresses.
Say you do a lot of research on several different topics, and summarize the results in a different spreadsheet for each topic. You could store all the bookmarks in your web browser, and chances are you already do. The problem with this is that if you’re only using a handful of bookmarks for one topic out of the many you have saved, you could end up searching through a lot of bookmarks to find the sites you need.
If you instead take the time up front to compile a list of sites you use frequently for a particular topic, you can use data validation to display them in one cell on your spreadsheet with another “clickable” cell to open the site in your web browser.
To do this, follow the steps below.
- Select the cell where you want your list of websites to appear (for illustration purposes I’ll assume your list will appear in cell A1).
- Click Data | Validation.
- On the Data Validation window’s Settings tab, select Allow: List, and make sure the In-cell dropdown box is checked.
- In the Source box, refer to the range of cells in the same worksheet that contains your website list, or refer to a named range elsewhere in your workbook.
- Click OK.
When you select cell A1, you should now see the list of websites when you click the arrow to the right of the cell. Now select cell B1. This is where we are going to put some text that becomes “clickable”, sort of like a link on a website.
In cell B1, type =HYPERLINK(A1,”GO”). Now whenever you change the website in cell A1, using the drop down list, you can click the words “GO” in cell B1 go to that site. It’s like having your own bookmark system in Excel!
Often times it’s useful to be able to display your worksheet’s name in a cell in the worksheet. This could be tedious if you have a file with a lot of sheets, whose names could change frequently. Luckily there are a couple ways around this problem.
If you want to display this information in a cell in your file, enter the following formula in the cell where you want the name to appear:
=MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,256)
Anytime you change your sheet’s name, the formula will automatically update, so you never have to edit the name in the worksheet as well.
If you only need the information on a printout and don’t want it displayed in a cell, you can edit the sheet’s header/footer. Click View | Header and Footer | then Custom Header or Custom Footer, depending on where you want the name to appear. Determine where you want the name to appear (Left, Center, or Right) and click the worksheet button (third from the right) that looks like a worksheet with three tabs below it.
Sometimes, it’s useful to be able to remove personal information from your workbook, in the event that you’re sending the file to someone else, whether it’s someone from another company, posting it to a forum, or sending it via email.
Excel will, by default, save information such as the author, file title, comments, etc. This information, could be potentially damaging if it was put in the wrong hands.
Luckily, Excel 2007 makes it easy to remove this information. Click the Office Button | Prepare | Inspect Document. Check all the options on the menu that appears, and click Inspect. Excel will then check each category to see if there is any document information that can be removed and will show a Remove All button next to each that has any information. Click the button, and the information is removed.
Adding comments to your worksheet allows you to share your thoughts with other users, or to serve as a reminder for yourself.
It’s also useful to be able to add comments to a formula, so that you can let other users of your workbook know if you’ve made a change to the formula, let them know what the result of the formula represents, or share other information about the formula.
After entering your formula, type +N(“Your comment here”)
For example if you have a formula that produces a class average, you can type:
=AVERAGE(A1:A25)+N(“Period 1 Class Average”)
”For” loop is used in a macro whenever you have an action that needs to be performed a set number of times. For example, if you want to change the fill color of the first 20 cells in a list, you could have the macro select the range and simply fill as desired. But what if you wanted to only fill the cells if they were blank, for example? Telling the macro to select the whole range and fill it wouldn’t work.
This is where a “For” loop is useful. You can have the macro to go down your list, and determine if the cell is blank. If it is, then have the macro fill the cell with a color, otherwise go to the next cell, until it has run through the whole list.
The following code will do exactly what was mentioned above.
Sub FLoop()
'MACROS BY EXCELZOOM.COM
For x = 1 To 20
Cells(x, 1).Select
If Selection.Value = "" Then
With Selection.Interior
.Color = 65535
End With
End If
Next x
End Sub
- The first line identifies the range of rows to be evaluated. It also tells the macro, that if ‘x’ is less than 20, keep on going.
- The next line tells the macro to select the cell in column 1.
- The ‘If’ section determines if the selected cell is blank, and if it is, it will fill it with a color (in this case 65535=yellow), and if it isn’t blank then just move on and evaluate the rest of the cells until the end of the range.
Just to have a little fun with the new Twitter account, I thought I’d put together a little Excel tool to make fitting into the 140 character limit a little easier.
Simply type what you want to say, and if it exceeds the 140 character limit, it will append “…” to the end, so that the last “.” will be the 140th character. That way you’ll know how your post will look before putting it online.
To make this more than just a formula that chops off anything in excess of 140 characters, also included is a macro that will open up your Twitter home page and copy the text that is already pre-formatted to 140 characters or less. Now all you’ll have to do is paste the text into the “What are you doing?” box and click “Update”.
Download the file here.
You can apply conditional formatting to your sheet that will allow you to format every other row however you specify.
Select the area where you want to highlight alternating rows.
Go to the Format menu and select Conditional Formatting.
For Condition 1, select “Formula Is”, and type in “=MOD(ROW(),2)=0″ if you want to highlight every even row, or type “=MOD(ROW(),2)=1″ if you want to highlight every odd row.
Click on the Format button, and choose the format you want your highlighted rows to have (i.e. font, color, background, patterns, etc.).
Click OK, and you should have every other row highlighted.
If you have a list with cells of all different colors, you can apply a filter to those cells to have Excel filter them by color, and then apply a formula to count how many of those cells are visible.
First, select your list. Right click and choose Filter | Filter by Selected Cells Color. You can then select the filter drop down in your list’s first cell and choose which color to filter. Next, in a row that is outside your list, type in the following formula: =SUBTOTAL(103,A1:A100), where A1:A100 are the cells in your list. Whenever you filter the list, this formula will tell you how many cells are not hidden.
You can remove the filter at anytime, by choosing the Data tab, and clicking the Filter button, and the formula will give you the count of your whole list (i.e. 100 in the example of A1:A100).
Using the fill handle is easy. Simply start typing a list of consecutive numbers, dates, etc. and drag the rest. You can easily fill 100 cells in no time at all. Well what if you wanted to fill the alphabet down a list of cells? Unfortunately, Excel doesn’t have this functionality built in. If you were to start typing the alphabet down column A, so that A1=A, A2=B, A3=C, and then drag the rest, you would only get A, B, C repeated as far as you dragged the fill handle.
There are two ways of making this work. The first is through a formula, and the second is by using a custom list.
To get the alphabet with a formula, type the number 65 in cell A1, and drag down to cell A26. Now column A should have the numbers 65 through 90. In cell B1, type =CHAR(A1), and drag this down to cell B26. Column B should have the capital letters A through Z. If you want lowercase, simply start with 97 and fill to 122 in column A.
Alternatively, you could manually type your list A through Z in cells A1:A26, then select that range (A1:A26). Click Tools | Options | Custom Lists (or in Excel 2007 Office Button | Excel Options | Popular | Custom Lists). On the bottom of the menu, click Import, where it says $A$1:$A$26. You’ll see A through Z appear under custom lists. Now whenever you type A, then drag down it will fill through the letter Z then loop back to A and start all over again as far as you drag.