MS Excel is a repository of a wide range of formulae that help its users to compute calculations across a diverse set of fields from Engineering to Economics for example.
But within each of these formulae is a fundamental structure that renders it functional in the first place. This fundamental structure is called a ‘Syntax’ & contains the different elements that ought to be provided for the proper functioning of a formula. But the story doesn’t end there!
The nuances of referring to the cells within the formula is an article on its own, but in this article, we shall stick to the usage of ‘@’ within the formula in MS Excel & understand the situations when it is to be deployed.
When does @ appear?
Before jumping into analysing when @ shall appear in the formula & when it shall not, let us first understand the difference between a ‘Data Range’ & a ‘Data Table’ in MS Excel.
Data Range – A collection of data across rows & columns that aren’t formatted & organised. Data of this type can only be converted into slicers through a pivot table.
Data Table – Contains a set of data across rows & columns organised with uniform formatting. Data in this format can readily be converted into slicers without the usage of pivot tables.
Comparing the above two images with each other, one can right away deduce that the cell formatting, font colour and decimal places are uniform throughout in the latter than in the former. Another striking feature in a ‘Data Table’ is that if a formula is entered in any cell of a column & hit ENTER, the formula is applied across all the cells within that column.
This does not happen at all when the data provided is in the ‘Data Range’ format. So, those who’ve come this far would have figured out already the implications of having the data in a table.
Now, this brings us to the question of how the table knows to update/apply a formula to all the cells across the column & that is exactly where the ‘@’ comes into play!
Using @ Within an Excel Formula:
Let us consider the following table for instance where the requirement is to calculate the difference between the order date & the dispatch date.
To get things started, a formula is constructed to calculate the difference. The cell AP2 is double clicked using the left mouse button & the formula is begun by hitting the ‘equals’ (=) sign. Once done click the cell AO2 which contains the ‘Dispatch Date’ of the very first row & witness the miracle!
What witchcraft is this? We just clicked AO2 & for what good reason does [@Dispatch Date] appears within the cell?
Keep calm & there is no reason to panic! This is a unique feature of the ‘Data Table’ which refers to the column header rather than an individual cell when any formula is constructed.
Why? you ask. It’s for the Automatic Updates!
When a formula is constructed or modified in one cell, the ‘Data Table’ leverages this ‘@’ feature which conveys the limits of the columns used in the formula, thereby aiding it in applying the formula across all the other entries in a column. (A table in which each column would be of a different size would defeat the very purpose of analysing it!)
We shall demonstrate it with the above table by completing the formula which we started.
Hit ENTER & witness the next miracle!
The lightning symbol bears evidence that the autofill option has been exercised by the ‘Data Table’ in filling the column using the formula.
Summary
Now that we have reached the end of this article, hope it has elaborated on what is ‘@’ in the formula & how to use it within a formula in MS Excel. Here’s another article which details the ways to deal with the #N/A error. There are numerous other articles too in QuickExcel that can come in handy for those who are in looking to level up their skills in MS Excel. Cheers!