Excel Formula Syntax

I need to know when various symbols can appear in Excel (Conditional Formatting) Formulas, and when a letter character followed by a number character can appear in those formulas.

Here’s my best guess:

$ symbol – mixed cell references, absolute references; inside sheet names; inside strings

! symbol – after sheet names in cell references; inside strings

” symbol – around strings; inside strings as escape characters

‘ symbol – around sheet names in cell references; inside strings

() brackets – after function names; as part of maths formulas, whether with numbers, formulas or cell references; inside strings

Letter character (upper or lower case) followed by a number character:

  • cell references (relative and some mixed)
  • inside sheet names
  • inside strings
  • maybe a defined name but I’m not sure if you can use those in a formula, especially a Conditional Formatting formula
  • maybe a formula name, either built-in or custom formula

Can anyone confirm the above, or think of any other times?

I’ve looked for a syntax definition for Excel formulas (which would specify what characters could be used where) but can’t find one – does anyone know of one? Examples of what I’m looking for might be https://docs.microsoft.com/en-us/dotnet/standard/base-types/regular-expression-language-quick-reference or https://www.geany.org/manual/gtk/glib/glib-regex-syntax.html or https://docs.microsoft.com/en-us/windows-server/administration/windows-commands/chkdsk

This is related to Search formula for cell references in VBA

By: James Carlyle-Clarke

Leave a Reply

Your email address will not be published. Required fields are marked *