Glide Training Logo

Confused by the symbols used in Excel formulas?

  

Written by Jane Hames.

Last week, one of my Excel workshop participants said they were confused when looking at Excel formulas with quotes, exclamation marks, colons and commas. I thought that my response was worth sharing as I’m sure he’s not the only one to be confused by this.

The formula below, is calculating the average of the values in cells in a different WORKBOOK across two different WORKSHEETS.

=AVERAGE(‘[Sales Report.xlsx]1st qtr’!B4:D4,’[Sales Report.xlsx]2nd qtr’!B4:D4)
  • The workbook name is within square brackets [ ]
  • The workbook and the worksheet names have the single quotation marks ' around them.
  • The exclamation mark ! always follows the name of the worksheet.
  • The colon : between the cell references means that B4, D4 and all cells in between are included.
  • The commas , separate the two different ranges used.

 


previous post: A Clever Use of Slicers in an Excel Pivot Table

next post: PowerPoint – Exporting Notes Pages to Word

Why choose Glide?

Our Unique Approach

Our Unique Approach

We work on a bespoke, requirements-led basis rather than offer a set range of courses. We will work with you to identify your specific requirements and objectives before any training is delivered. That way you can be sure that training will always deliver exactly what you're looking to achieve.

View details »

Our Low Prices

Our Low Prices

We deliver all our on-site training at our customers' premises. Not only does that avoid any travel inconvenience for your team, but also it means we don't have the overheads required to maintain a dedicated training centre - and in turn we are able to pass these savings on to you.

View details »

Our Expertise

Our Expertise

Our trainers have designed and delivered IT training for a wide range of organisations and people including accountants, lawyers, data analysts, sales consultants and buyers. Clients benefit from our our ability to find creative solutions to their problems based on many years of experience.

View details »

Balderton Logo
Kreston Reeves Logo
National Institute of Health Research
England Rugby
Caravan and Motorhome Club Logo
Bird and Blend Logo
Ropetackle Logo
RBLI Logo
Farnborough Airport Logo
Kroll Logo
The Apprenticeship College Logo
Shoreham Port Logo
Adur and Worthing Councils Logo
Balderton Logo
Kreston Reeves Logo
National Institute of Health Research
England Rugby
Caravan and Motorhome Club Logo
Bird and Blend Logo
Ropetackle Logo
RBLI Logo
Farnborough Airport Logo
Kroll Logo
The Apprenticeship College Logo
Shoreham Port Logo
Adur and Worthing Councils Logo