Introduction
Welcome to Excel Jet Consult blog post. In this video tutorial, we learn about when to use comma and semicolon to achieve the same result in different way.
Sample Data
In the caption below, we have sample data that has Account Manager and Total Sales. Note, the data is formatted in Excel Tables
Top 5: Spill To Cells Below
We want to return the Top 5 Total Sales by Account Manager. Therefore, we are going to deploy the LARGE Function
- In cell D1, execute the formula: =LARGE(Data[Total],{1;2;3;4;5})
Th above formula has Tables Structured Reference whereby Data is the name of the Table; Total inside square bracket is the column name. Then, I used semi-colon inside the curley braces to separate the numbers 1 to 5 which is the Top 5 we want to return. Note, I use Microsoft 365 (Insiders Fast). This implies that I have the Dynamic Array functions and functionality such as Spill to Neighboring cells.
- Click Enter
After I clicked enter, the result spills to the neighboring cells below. The spill to cells below is as a result of the semi-colon used inside the curley braces
Top 5: Spill To Cells To The Right
We want the Top 5 sales to spill to the neighboring cells to the right. All we need to do is to replace all the semi-colon with comma
- In cell D7, execute the formula: =LARGE(Data[Total],{1,2,3,4,5})
- Click Enter
In the caption below, we can see the result spills to the neighboring cells to the right basically because we used comma inside the curley braces
You can watch the video below