Search

×

How to equip static templates for future changes

Last week, three new hotel brands launched into the market: Tru by Hilton, Tapestry Collection by Hilton, and Trademark Collection by Wyndham. While hoteliers’ reactions to these announcements are likely to vary, some analysts (like myself) are undoubtedly thinking: “How will I manage updating all of my Excel templates?!”

New brands, new rate codes, new room types — you name it — always will pose a threat to our static reporting templates. Fortunately, we can program Excel to act more dynamically in preparation for these changes. Here are a few of my favorite tricks for creating dynamic Excel reports.

Creating dynamic named ranges

Dynamic named ranges automatically expand and contract when new data is added or deleted. Named ranges can be used as a “helper” for situations when you refer to a specific set of cells (for example, VLOOKUP(), MATCH(), OFFSET(), and even when creating charts).

To create a dynamic named range, follow the steps below (Figure 1):

  1. Navigate to Formulas > Defined Names > Define Name in the menu bar
  2. Name your range
  3. In the “Refers to” box, type the following:

=OFFSET(Reference, Rows, Columns, Height, Width)
=OFFSET(MySheet!$A$1, 0, 0, COUNTA(MySheet!$A:$A), 2)

  • Reference = the cell from which you want to base the offset. We are starting our range from cell A1.
  • Row = the number of rows that you want to move up or down from the reference cell. We want the reference cell to remain at A1, so we enter 0.
  • Columns = the number of columns that you want to move left or right from the reference cell. We want the reference cell to remain at A1, so we enter 0.
  • Height = number of rows in your range. We want the height to expand automatically when we add more rows, so using the COUNTA formula, we count all of the non-blank cells in column A, and make that number the height.
  • Width = number of columns in your table. Our table has 2 rows – brand name and property count, so we enter 2.

Now, when I add a new row to this table, my named range will update automatically to include the new row, and I can refer to this named range in other formulas.

Automatic shading with banded rows

Conditional formatting using the ISEVEN() or ISODD() formula checks the row number, and shades the even or odd rows, respectively.

To apply an automatic shading for every other line, follow the steps below (Figure 2.1):

  1. Highlight your table
  2. Conditional Formatting > New Rule > Use formula to determine which cells to format
  3. In the formula box, enter =ISEVEN(ROW())
  4. Choose the color you want to use for your shaded row

Now, when I add a new row to the table in between existing values, every other row will be updated automatically with the shading (Figure 2.2). By utilizing these two formulas, you will be better prepared to handle changes in your Excel templates.

Figure 1 – Configure dynamic range
Figure 1 – Configure dynamic range
Figure 2.1 – Set up automatic banded rows
Figure 2.1 – Set up automatic banded rows
Figure 2.2 – Result of automatic banded rows
Figure 2.2 – Result of automatic banded rows

 


Kathleen Ayers is manager of information architecture at Kalibri Labs, based in Rockville, Maryland.

Comment