ExtremeCPA - Technology for Business

Monday, June 20, 2005

Named Ranges

A quick, but powerful tip that I wanted to offer revolves around named ranges in Excel. Whenever you're creating a complicated spreadsheet, keep in mind that you may not always remember exactly what you were doing when you first created your masterpiece. Named ranges can be of tremendous use when you're later trying to rekindle that flame in a last minute effort to get your product out the door. For example, "=Sales-COGS" makes much more sense than "=B4-B7". While this example is clearly contrived and trivial, when you have a 2MB Excel file with 15 sheets, it will not seem so trivial.

To use named ranges, simply select the cell (or group of cells) that you want to refer to by name, and hit Ctrl-F3. This will bring up a dialog allowing you to name your range. Once saved, you can refer to the name later either by explicitly typing it in a cell, or by hitting F3 and selecting it from the list. This is also useful when creating VLOOKUP or HLOOKUP formulas (examples to be provided later).

1 Comments:

  • No problem for the articles :) As for the named ranges, here's a go:

    They are NOT the same as a print range. They're essentially very useful to either 1) Refer to a complicated structure (like a table) very easily or 2) To help you understand formulas and references better in complicated spreadsheets.

    I'll explain 1) in another article that I'll write over the weekend. As for 2), however, we can do that right now. Let's assume that you've created a workpaper that you use at month end to analyze your company's income statement for anything strange. Let's also assume that you have several sheets in this workbook: "Income Stmt", "Balance Sheet", "Stmt Cash Flows", "Ratios", "Adjustmnts", etc... Each sheet holds the relevant information you need to analyze.

    Now let's assume that as part of your analysis, you calculate several ratios and compare them to historical averages or competitors ratios. One of those ratios might be your Current Ratio. Presumably, you already know how to calculate your Current Ratio, but in case you forget: Current Ratio = Current Assets / Current Liabilities. In your "Ratios" sheet, you may have a formula to calculate this for you, and it probably looks something like this: "='Balance Sheet'!C24 / 'Balance Sheet'!C44". This would seem to indicate that cell C24 on the Balance Sheet tab holds the value of Current Assets and cell C42 holds the value of Current Liabilities. This is fine, but the formula on the "Ratios" sheet would make much more sense when someone new comes along or if you don't know how to calculate it if it said (instead): "=Curr_Assets / Curr_Liabs". It's much easier to read, and much easier to audit if you ever think there is a problem.

    This example isn't terribly complicated, but it does offer some idea as to how you would use the tool. In this one example, you eliminated any possible confusion, allowed anyone who wanted to audit the formula to easily see what it refers to, and ensured that the calculation is functioning properly. When you extrapolate this to calculate 20 ratios, as well as other parts of the spreadsheet hopefully you can see the clarity that comes along with using named ranges. You could even go further in the various financial statements to use names as well (e.g., "Gross_Income = Total_Sales - COGS").

    Note that it's also easier to create new formulas. For example if you never used Inventory Turnover as part of your analysis, you could now do so by simply typing "=COGS / ((Beg_Inv + End_Inv) / 2)" (assuming you named the appropriate areas as such), instead of searching in each sheet and hoping you pick the correct cells from the page. Hope this helps, but feel free to write again if it still isn't clear :)

    By Blogger ktr, at 6:50 PM  

Post a Comment

<< Home