Article: Excel cheat sheet

This Excel cheat sheet focuses on dynamic ranges, named values, and simple automation — the parts of Excel that turn a static sheet into a self maintaining system. This sheet will just touch those and give a few examples; it may grow over time. Note: some of this needs "Developer support", which is an Excel setting, not a workbook setting. To enable it, go to File Menu, Options, Customize Ribbon, and check "Developer". This will add a "Developer" menu (which you won't need to use explicitly as I suggest using keyboard combo's for it).

Hotspots Enable Developer (once per machine) Use .xlsm for macro enabled files Use CTRL/F3 for Name Manager Use ALT/F11 for VBA

Built-in functions

Functions are documented alphabetically as well as by category.

Some useful ones include (with simplified descriptions):

COUNTA(range)	Counts the non-empty cells in a range
INDEX(cell, dx, dy)	Points to the cell that is at position (cell.x+dx, cell.y+dy) Warning: there also is an INDEX variant that takes more parameters)
OFFSET(cell, dx, dy, h, w)	Returns a range that starts at INDEX(cell, dx, dy) and has size(w,h). Often used with dx=dy=0. Note the order of h and w!

 

Named values

The "Name Manager" can be reached by hitting CTRL/F3. It acts like a store of key-value pairs, where the key is a named variable, the value any cell, constant, or calculation. The association is live, i.e. value gets evaluated whenever required.

Warning: Excel may stubbornly remember names that are no longer visible in the Name Manager, and force its user to come up with new names.

 

Example: a line chart

An elegant way to define the data of a line chart consists of these steps, assuming sheet1 with an ever growing table with columns A=date, B=value1, C=value2:

• Create your data cells
• Enter Name Manager and define (using absolute cell coordinates $$):
  • RowCount = COUNTA($A$2:$A$9999)
  • DateRange = OFFSET($A$2, 0, 0, RowCount, 1)
  • Value1Range = OFFSET($B$2, 0, 0, RowCount, 1)
  • Value2Range = OFFSET($C$2, 0, 0, RowCount, 1)
• Prepare your chart's layout and enter in "Select Data…":
  • Horizontally: =sheet1!DateRange
  • Vertically:
    • Series 1 = sheet1!Value1Range
    • Series 2 = sheet1!Value2Range
  • Make sure to use the sheet name when entering chart definitions, Excel needs this (and may substitute the file name, don't worry about that).

Some pro's for this approach are:

• you overcome some limitations of the charting capabilities (charts don't offer the full range of Excel's calculation capabilities)
• you avoid the need for constantly editing the chart's data definitions.

Macro's

Macro's are user-defined functions that can be associated to buttons. They take three steps:

1. Use ALT/F11 to enter the VBA development environment, and enter the VBA code as a subroutine.
2. Insert a button
3. Use its context menu to "Attach Macro…"

Warning: Excel will not save macro's in an .xlsx file; save as .xlsm and when asked, make the file a "Trusted Document" (which will have to be repeated when moving the file elsewhere).

Example VBA subroutine:

Option Explicit

Sub InsertColumnRelativeToButton()
    Dim btn As Shape
    Dim ws As Worksheet
    Dim targetCol As Long

    Set ws = ActiveSheet

    ' Identify the button that called this macro
    Set btn = ws.Shapes(Application.Caller)

    ' Determine the column where the button is located, then
    ' add 1 since the button is in the right-most column that should NOT move.
    targetCol = btn.TopLeftCell.Column + 1

    ' Insert a new column to the right of the button
    ws.Columns(targetCol).Insert Shift:=xlToRight

    ' Copy formulas + formatting from the old column (now shifted right)
    ws.Columns(targetCol + 1).Copy
    ws.Columns(targetCol).PasteSpecial xlPasteFormats
    ws.Columns(targetCol).PasteSpecial xlPasteFormulas

    ' Clear constants (keep formulas)
    On Error Resume Next
    ws.Columns(targetCol).SpecialCells(xlCellTypeConstants).ClearContents
    On Error GoTo 0

End Sub

An interesting article (in Dutch) on VBA performance: https://www.exhelp.be/vba/optimaliseer-de-snelheid-van-je-excel-macros-vba/