Excel allows you to create your own "User Defined Functions" (UDF) that can be used the same way as any other built-in function in Excel (i.e. IF, SUM, VLOOKUP, etc.). With a little knowledge of VBA code, you can create your own function to do pretty much whatever you want. To illustrate how to create a UDF, we'll create a function that calculates fuel consumption in miles … [Read more...]
Data Validation to Avoid Duplicate Values
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 … [Read more...]
Drop Down Lists To Create Hyperlinks
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 … [Read more...]
Confirmation Before Execution
Often times when you execute a macro, the process isn't easily undone. You may have a macro that deletes certain information, or completely reformats your worksheet. In either case, you want to be sure that your workbook's users are absolutely certain that they are ready to execute the macro before running it. If the macro can be run by clicking on an object within the … [Read more...]
Return Worksheet Name in a Cell
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 … [Read more...]