I have had several requests for a simple, step by step post on using mail merge to print labels, envelopes, or letters. Mail merge is one of those functions which should be simple, but still manages to cause you to pull your hair out!
As I have stressed, the secret to effective operations such as this is to have the supporting documents properly prepared. To that end, let's spend some time reviewing names in Excel. Why Excel and not Word? Because most of the time, the names and addresses that we want for mail merging are stored in a spreadsheet.
We typically refer to an individual cell by that cell's Column and Row, such as B1. But if a spreadsheet regularly references a particular cell in formulas, such as a tax, discount rate, or WACC (for you MBA types) then we can name that cell exactly what it is. For a single cell or a multi-cell range the process is the same. Select the cell(s) and then type a name (such as Tax) in the Name Box:
Keep the names concise and descriptive. Spaces are not allowed so use underscores if required. Consider one of the primary advantages of such an arrangement before using long names. Because we have trained Excel that for this workbook, "Tax" = B1, then even if we have 100 worksheets that have formulas using the corporate tax rate, we can always refer to this as 'Tax' instead of 'Sheet1!$B$1'. So if we are going to type a Name instead of a cell / range then a name like Tax is better.
When creating, or even just manually entering formulas, it is always possible to make a mistake. That risk is mitigated by using names. Which is easier to spot check:
Value = FCF / (1 + WACC)
Value = Formulas!$D$4 / (1 + Formulas!$D$5)
Value = Formulas!$D$4 / (1 + Formulas!$D$5)
I mentioned named ranges on the Data Validation post. Data Validation requires that a list be named if the range is on a different worksheet. Named ranges are also appropriate when using VLOOKUP. As with any information technology project, names and lists are things that need to properly maintained. If the range is a list of active clients and project that will frequently be updated, then there is always a risk that the list will not match the definition saved as the named range. In such a case try to name entire row or column. Use Insert > Name > Create. This will bring up the Create Names dialog box.
The default assumption is that you will have names in the first row. A good practice is to leave the second row blank. This is because of a peculiarity in Excel - call it an undocumented feature. When you use a range as an incell drop-down list (data validation) the selection will default to the end if there are blank cells in the list. So imagine a list of the alphabet. When you drill down on the cell, it will default to the first blank list. Most people expect to see the beginning of the alphabet not the end.
There are two ways to handle this. On a list whose length is fixed, such as the alphabet you can eliminate the blank row 2 if you do not include any blank cells beyond 'Z.' For a range such as ProductNo that is not fixed, you'll need to use it. Remember, the problem would not be starting from the bottom of a list instead of the top - the potential problem is inconsistency. If one drop-down starts at the bottom of the list, and the next starts at the top, it is annoying for the users.
There are two ways to see a list of named ranges in the workbook. The quickest way is to drill down on the name box. Select any of the named ranges and Excel will take you to that location and select the cells that are defined by that name. This is particularly handy if you notice that there are two names: 'Tax' and 'TaxRate' and you can't remember which is which. If you find that you need to redefine or delete names, go to Insert > Names > Define which open a dialog box.
Click on a Name and the location will show in the field 'Refers to:.' You can manually adjust the definition here or you can click on the Range button to select or modify the range in place. Be consistent and careful with your names to ensure that there are not duplicates in a workbook. You will not get a warning if you create redundant definitions.
If you are working with an existing spreadsheet that already has formulas, but would like to apply names instead of references, click Insert > Name > Apply. For instance, if our worksheet contains a formula that is our net profit * tax rate to determine taxes due then the formula will use B1 as the reference.
After applying NetProfit and Tax, the cell references are replaced in the formulas with their respective names:
Comments