Use array constants to store static values so you can save time—and sanity!
by
Application:
Microsoft Excel 2000/2002/2003/2004/2007
Operating Systems:
Microsoft Windows, Macintosh
You’re probably unaware of the fact that most of the data in your workbooks are unnecessary and at times can hinder readability, open up more opportunity for error and bog down the workbook’s performance. When you need to use a range of static values in a formula, array constants can cut down on clutter and save your workbook’s memory, leading to faster calculations.
To effectively utilize array constants when we need to work with a static range of data, we’ll:
• Distinguish array constants from array formulas and discuss how to correctly enter an array constant.
• Create both a one- and two-dimensional array constant for ultimate flexibility.
• Transform the array constant into a named range so you only need to type it once.
• Calculate sales tax for a large amount of data with one formula and the help of an array constant.
Arrays are a staple for any Excel power user. The simple processes of opening, closing and calculating the workbook’s formulas can take much longer than necessary if you aren’t using arrays to streamline. You can use arrays to designate groups of constants — that is, static values that won’t change, such as tax rates or interest percentages. This is useful if you want to use these static values in a formula — since they reside in your workbook’s memory, they take up less space. Also, naming a frequently used array constant saves you valuable data entry time. Otherwise, you may end up typing dozens of tax rates over and over!
Array constants vs. array formulas
Understanding array formulas can help you make wise decisions about when they’re useful for streamlining the formulas in your workbook. Close cousins of array formulas are array constants. Array constants are a group of static values that you want to store in memory for use in array formulas.
Know the dimension of your array constants
There are two types of array constants: one-dimensional and two-dimensional. Let’s say you have a data range that’s always multiplied by the same set of integers. For example, you need to apply a predetermined tax percentage to each value in the data range. The numbers in your data range may change, but the tax percentages are static. This is a perfect situation for an array constant.
In our sample worksheet, we’re creating a tax chart based on state sales tax, as shown in Figure A. We need to multiply each state’s sales tax by the value at the top of the chart. Let’s look at how array constants can make this a three-step task.
Download: You can follow along with our example by downloading and extracting the file taxchart.xls from the URL given at the beginning of this article.
A:
With the help of an array constant, we filled in the sales tax figures for this entire chart by entering only one formula.
Use an array constant with only one dimension
The simplest type of array constant only contains one dimension — meaning that it contains either a row of data or a co
