Top 7 Applications of Ctrl Shift Enter in Excel

Performing single calculations in Excel is straightforward. There is no complication at all. But when multiple calculations are involved, the experience is never so direct. If you don’t know the way, you have to do calculations one by one and then combine the individual output manually to get the result you are looking for. However, Ctrl Shift Enter in Excel says hi and is here to change the game!

Standard functions in Excel do not allow you to perform intricate calculations. The formulas are incapable of doing complex calculations by themselves. This is where Ctrl Shift Enter comes in. It helps create an Array Formula instead of a regular one which allows you to execute complex calculations. It saves both your time and energy in your Excel operations!

In this guide, we discuss everything about Array Formulas. We are doing it through visual demonstrations of seven excellent applications of Ctrl Shift Enter in Excel. We are also sharing the practice file with you so that you can follow along and have a complete learning experience.

Let’s get going!

Ctrl Shift Enter and Array Formulas in Excel

In Excel, regular formulas work with a single value and then return a single value as a result. If more than one value is used for calculation in an argument of a regular formula, you will get an error. On the other hand, Array Formulas execute more than one calculation and based on your need, it may return single or multiple values as a result.

But what is the connection between Array Formulas and Ctrl Shift Enter? When you enter a regular or standard formula in Excel, you simply press the Enter button. But if you want to use an Array Formula instead of a regular one, you must press the Ctrl+Shift+Enter buttons to enter the formula. Therefore, Ctrl Shift Enter in Excel is the ultimate key to Array Formulas.

As an example, look at the picture below. In this dataset, the values in the second column have to be subtracted from the values in the first column. Usually, the calculation is done individually for each row. However, by turning the regular formula into an Array Formula by pressing the Ctrl+Shift+Enter buttons, we get to calculate it with just one instance of entering the formula.

Ctrl Shift Enter and Array Formulas in Excel

If you look at the dataset in the middle, you can see that the formula has arrays or selections of ranges for the arguments. When we entered the formula after writing it, we pressed the Ctrl+Shift+Enter buttons instead of pressing just the Enter button. It turned the formula into an Array Formula.

The way to recognize an Array Formula is simple. Take a look at the Formula Bar in Excel while a cell with an Array Formula is selected. You will see your formula held inside two curly braces. In Excel formulas, curly braces (also known as curly brackets) are the indications of being an Array Formula.

However, manually putting those curly braces around a regular formula without pressing the Ctrl+Shift+Enter buttons does not turn a regular formula into an Array Formula. You will always have to press Ctrl Shift Enter to enter the formula for it to work. Array Constant is the exception that we will see in Use 5.2 of this guide.

Now that we know what Array Formulas are, let’s get down to business and see some uses of Array Formulas that will take away extra work from you and thus, save your energy and time.

7 Important Applications of Ctrl Shift Enter in Excel

In this guide, we are discussing seven excellent uses of Ctrl Shift Enter. We will show how you can perform many calculations in one cell with Array Formulas. And as explained in the earlier section, regular formulas are turned into Array Formulas by pressing the Ctrl+Shift+Enter buttons when entering the formula.

Make no mistake, the seven applications discussed here are not the only calculations that can be performed by Ctrl Shift Enter. Rather, these seven uses will give you an excellent idea of how Array Formulas work through Ctrl Shift Enter. By learning these seven applications, you will develop the ability to perform hundreds of complex calculations by yourself using Array Formulas.

When you go through the steps of each application, see the corresponding pictures for a better understanding. They will help you learn faster.

Now let’s start with the first useful application of Ctrl Shift Enter.

Use 1: Maintaining Value Order with Ctrl Shift Enter in Excel

It is a pretty common scenario for an Excel user where the requirement is to put multiple values maintaining their order in a range of cells. Mostly, it is done when the need is to create an ordered numeric list beginning with a particular number. A growing serial number in a column is one of those most popular requirements.

Normally, it looks like the dataset shown below.

Maintaining Value Order with Ctrl Shift Enter in Excel 1

There are usually three ways it can be done. For convenience, we will discuss them all below. To understand how Ctrl Shift Enter is a better choice and makes a huge difference, non-Array methods should be demonstrated as well.

Maintaining Value Order with Ctrl Shift Enter in Excel 2

Now, from those three methods, using the Series Fill and Row Function top the list of popularity. However, there are some downsides in these two methods that are solved by the most incredible Ctrl Shift Enter. It offers two important plus sides.

  1. The Array Formula (Ctrl Shift Enter) does not let anyone change the order of the list without removing the formula itself,
  2. The whole list can be copied without messing up the order despite having a ROW formula.

To use the Series Fill option to create the list, you only need to put the first number in the first cell, then select the whole range, and go to the Fill option in the Editing group under the Home ribbon. From there, selecting the Series option lets you input the Step number and Stop number which then puts the maintaining values in the column. You can see this in the picture below.

Maintaining Value Order with Ctrl Shift Enter in Excel 3

With the ROWfunction, you simply write the formula for rows to have ordered numbers, =ROW(reference). In the reference, select the range of cells in the column which should have the ordered numbers. Look at the picture below.

Maintaining Value Order with Ctrl Shift Enter in Excel 4

However, you have to subtract rows that are above the selected range to have the ordered list start from number 1. The simplest way is to take a look at the number of the row just above the formula and subtract that number after the ROW formula, as shown below.

Maintaining Value Order with Ctrl Shift Enter in Excel 5

Now you have an order-maintained list of serial numbers.

Maintaining Value Order with Ctrl Shift Enter in Excel 6

Even so, if you are to copy your data along with the ordered list of numbers somewhere else, you will see that the list of numbers has gone rogue. See the picture below.

Maintaining Value Order with Ctrl Shift Enter in Excel 7

This is where the Ctrl Shift Enter gives you a great way to avoid the headache and, for one, protect your list of ordered numbers and, two, maintain the order even if the dataset is copied somewhere else!

Follow the steps below to see how fast it can be done with some amazing extra benefits!

1. To begin with, select your range of cells.

Maintaining Value Order with Ctrl Shift Enter in Excel 8

2. Then start writing the formula to use the ROW function. Write =ROW(reference) where, in place of reference, select your range of cells again for the ordered maintained list of numbers.

Maintaining Value Order with Ctrl Shift Enter in Excel 9

3. If you press Ctrl Shift Enter now, you will get a list that will begin with the row number you are currently in. Observe the picture below.

Maintaining Value Order with Ctrl Shift Enter in Excel 10

There is an excellent remedy for it. See the next step.

4. After the ROW formula, put in the subtraction operator and then, write an additional ROW formula. In the cell reference of this one, write the first cell of your selected range for this part of the formula. See the picture below for a clearer understanding.

Maintaining Value Order with Ctrl Shift Enter in Excel 11

After the minus sign, the ROW formula has cell G3 in it. It is the first cell as can be noticed in the first ROW formula. They are both highlighted in the picture.

However, subtracting rows from G3 also means that the series of numbers will begin on “0” (Zero) as it is the first value in the numeric series. To remedy it, simply put an arithmetic addition operator (“+”, the plus sign) and then put “1”. It means that the series will start from “1” because “0+1=1”.

When you are done writing the formula, press the Ctrl+Shift+Enter buttons.

5. Now you will see that your selected range has an ordered series of values starting from “1”.

Maintaining Value Order with Ctrl Shift Enter in Excel 12

6. Now you can copy your dataset and the serial values in that list will maintain their order.

Maintaining Value Order with Ctrl Shift Enter in Excel 13

Now say goodbye to all of the extra unnecessary manual work that you had to do because of changes in the list of numbers when you copied a dataset with an ordered numeric list.

Additional Tip:

  • You can notice in the formula bar that the whole formula is now existing between two curly brackets which indeed indicates that this is an Array Formula.

Maintaining Value Order with Ctrl Shift Enter in Excel 14

  • And If you manually want to change the numbers in your list, you will see that it is foolproof and cannot be changed. You will get a message box with a warning as shown below.

Maintaining Value Order with Ctrl Shift Enter in Excel 15

That’s it!

Use 2: Finding Total Cost with Multiple Array Calculation

Using the Ctrl Shift Enter to calculate the total cost is also an excellent demonstration of a multiple array calculation. To understand this requirement, let’s take a look at the picture below.

Finding Total Cost with Multiple Array Calculation 1

Usually, the way is to go with coupling the SUM function with the VLOOKUP function to find and calculate costs for each product with respective quantities in a selling list. Then all the derived values are added to find the total cost. A helper column is used in this case. This is without the Ctrl Shift Enter method.

You write a nested VLOOKUP formula within a SUM formula. The VLOOKUP picks up the lookup_value from the selling list, then the table_array is selected from the dataset that has the cost info. Next, the coumn_index_number is selected, followed by the closure of the VLOOKUP formula here. Finally, it is multiplied by the quantity number found in the selling list. The picture below shows all the information for this usual operation to find out the total cost.

Finding Total Cost with Multiple Array Calculation 2

Then the helper column is filled with the formula. Next, the SUM function is used for calculating the total cost by selecting all those individually calculated product costs for quantities.

Finding Total Cost with Multiple Array Calculation 3

Finally, you have found your total cost.

Finding Total Cost with Multiple Array Calculation 4

However, the Ctrl Shift Enter gives you a better choice. You don’t even need any helper columns. Inside just one cell, you can find out cost info and multiply them with the quantity, thanks to the Array Formulas!

Let’s see in steps how to do it.

1. [FORMULA EXPLANATION] We are going to use a different set of array formulas for this method (not SUM & VLOOKUP) that uses Ctrl Shift Enter to achieve the same result shown above.

For this Array Formulas approach, the whole set of formulas is: =SUM(SUMIFS(sum_range,criteria_range,criteria)*multiplication_array).

Here,

  • SUM & SUMIFS: Functions.
  • sum_range: The range of cells that have product costs.
  • criteria_range: Range of cells that have product names which will be found against the criteria to find out individual costs of those products.
  • criteria: Product names in the selling dataset.
  • multiplication_array: Array or range of cells with the quantity value of sold products which will be multiplied by the respective cost of products to find out the total cost.

The picture below has everything pointed out in the used formulas.

Finding Total Cost with Multiple Array Calculation 5

After writing your formula, press the Ctrl+Shift+Enter buttons.

2. Now you will see that the Total Cost has been calculated. In the picture below, both approaches without the Ctrl Shift Enter and with the Ctrl Shift Enter results are shown.

Finding Total Cost with Multiple Array Calculation 6

3. Even if a value is changed in the dataset, the Total Cost gets updated at once. You can see in the picture how the total value changed when a value in the Cost column was altered.

Finding Total Cost with Multiple Array Calculation 7

It’s simply amazing and straightforward!

Use 3: Calculating Total Sales with Multiple Array

Multiple Array calculation with the Ctrl Shift Enter option offers you a way to calculate your total sales without using the helper column. It is faster and occupies only a single cell to complete the job!

The dataset we are going to use for this demonstration is shown below.

Calculating Total Sales with Multiple Array 1

Now, when Total Sales are calculated, the usual approach is to take the help of a helper column to calculate the total amount of sales for each product individually. Then all the values are added together to find the Total Sales. The traditional process of this arithmetic calculation is shown below.

First, in the helper column, the selling price of a product is multiplied by the sold quantity of that product. This gives you the total amount of sales for that specific product.

Calculating Total Sales with Multiple Array 2

Then the helper column is filled with that multiplication formula. Next, the Total Sales are found by adding up all the values from the helper column by using the SUM function.

Calculating Total Sales with Multiple Array 3

Now you have the Total Sales.

Calculating Total Sales with Multiple Array 4

But this approach is lengthy and inefficient. Contrarily, the Ctrl Shift Enter approach allows you to calculate your total sales using just one cell. It only requires one step to complete your task. See the demonstration below.

1. Write the SUM formula, “=SUM(”. Then, for numbers, select the first range of cells (in this case, the Selling Price). Then put the multiplication operator “*” and then select the next range of cells (Quantity column, in this case). Finally, hit the mighty combination of buttons Ctrl+Shift+Enter. Watch the magic!

Calculating Total Sales with Multiple Array 5

2. Your Total Sales are now calculated! If you notice the formula in the Formula Bar, you will see that the formula is contained within two curly brackets. It means that this formula is an Array Formula!

Calculating Total Sales with Multiple Array 6

3. Like the previous method, you can even change any value in the range, and the total value will update automatically.

Calculating Total Sales with Multiple Array 7

How awesome is that?

Use 4: Finding Revenue without the Helper

The Ctrl Shift Enter also shows its power when you want to calculate the revenue. This is another arithmetic array calculation where Ctrl Shift Enter reigns supreme. Compared to the previous two methods, the calculation is slightly more complex. However, the Array Formula is ready to tackle all the obstacles.

The dataset below is going to be used for this demonstration. And we will discuss both with and without Ctrl Shift Enter methods for this operation. In that way, it is easier to understand the impact of doing calculations with Array Formulas that do not require too many steps or occupy too much space.

Finding Revenue without the Helper 1

In the most popular method, helper columns are used to find out the revenue. You take two helper columns like below where total sales and total cost of products are calculated. The total cost is then subtracted from total sales to find the revenue value.

Finding Revenue without the Helper 2

First, total sales are calculated for individual products. Then the values are added at the bottom.

Finding Revenue without the Helper 3

Then the total cost of products is individually calculated followed by adding up all those derived values.

Finding Revenue without the Helper 5

Then you have the total sales value and total cost value.

Finding Revenue without the Helper 6

Finally, you subtract the total cost from the total sale value.

And now you have your revenue calculated. How many steps did it take? How many minutes?

Finding Revenue without the Helper 8

Let us show you a better way that uses Ctrl Shift Enter and saves you minutes for this operation!

1. We are going to do a simple SUM equation to find out the revenue. All the calculations will happen in a single formula, =SUM((totalSales)-(totalCost)). The total sales value and total cost value will be calculated using the Array Calculation.

Now write down the formula in order. First, begin the formula with “=SUM(”. Then select the range of cells that has the selling price of products. Then multiply this range by the array or range of cells for quantity. Now close the bracket and then put in the subtraction operator. Next, input the open bracket again and then select the cell range for the cost, and multiply it by the array of quantity. Close the bracket and then hit the combination of buttons for Array Formulas Calculation, the Ctrl+Shift+Enter buttons.

Finding Revenue without the Helper 9

In this demonstration, array calculation arguments within brackets are defined in the picture below.

Finding Revenue without the Helper 10

2. As soon as you hit Ctrl Shift Enter, you will see that your revenue has been calculated! And it was done in under ten seconds without using any helper column with so many steps. Notice the final formula now in the Formula Bar to see the curly brackets. They show that this formula is indeed an Array Formula.

Finding Revenue without the Helper 11

That was so cool, wasn’t it? Saved you so many seconds!

Use 5: Calculating SUM of Best 3 Values from Array

Ctrl Shift Enter helps find out and calculate the largest or smallest multiple values in under ten seconds. When there are so many numbers and the SUM of best or worst values are to be found, usually, a lengthy approach is taken.

But, as always, Ctrl Shift Enter offers the best possible way that uses the shortest possible time even if there is too much data to pick up the numbers from!

For this demonstration, the dataset below is going to be used. In it, the SUM of the best three values from each row will be calculated.

Calculating SUM of Best 3 Values from Array 1

The traditional way is shown below. The LARGE function is used for it. The formula is: =LARGE(array,k).

Here,

  • LARGE: The function.
  • array: The range of cells with values from where the best values will be picked up from.
  • k: The position of the value in the array, from largest to smaller number.

Now, the most common method is to find the top 3 values separately within the same formula where multiple LARGE formulas are added up with the arithmetic addition operator (“+”). Only the k value varies. Let’s take a look at the picture below.

Here, for the SUM of three best values, the first LARGE formula has “1” for the k value, the second one has “2” and the third one has “3”. In all three, the array is the same (the range of cells in the row from where the best three values are to be picked up and added together).

Calculating SUM of Best 3 Values from Array 2

Then the formula is applied, and the column is filled up with that formula.

Calculating SUM of Best 3 Values from Array 3

However, Ctrl Shift Enter offers a better, faster, and more excellent approach. There are two ways it can be done with the Array Formula Calculation. The first method is to do it via Cell Reference for k value with Ctrl Shift Enter, and the second method uses the Array Constant that allows you to use multiple values for one number in an argument within a formula.

We are going to demonstrate both. But first, let’s see the one now where we will use Cell Referencing and the Ctrl Shift Enter combo.

5.1: With Ctrl+Shift+Enter

As already mentioned, this method uses Cell Referencing to find out the best values. The numbers for nth values are put in cells and then selected for the k value. The cells will indicate which nth values (from largest to smaller) will be picked up from the array. Moreover, as the arguments in the formula are using arrays or a range of cells, the formula will be an Array Formula.

Now let’s see how to find out and add up the best three values from an array.

1. [FORMULA EXPLANATION] First, for Cell Reference, put the nth values that you want to extract from the array to add up in your Total of Best 3 values (largest three values). Make sure that one cell has only one value in them. See the picture below. As we are going with the best three values, we put 1 in a cell, 2 in the next cell, and 3 in the further cell.

The formula for this operation is: =SUM(LARGE(array,k)). Here,

  • SUM & LARGE: Functions.
  • array: The range of cells from where the nth best values will be extracted.
  • k: The nth value. For Cell Referencing, an array with nth values will be selected.

Now, write the formula in the first cell of the column. As the calculation is about adding up the best three values, start with the SUM formula. Then nest the LARGE formula within that SUM formula. Next, select the array or range of cells from where you want to find out and add together the best three formulas, and then put a comma.

With Ctrl+Shift+Enter 1

As you can see in the picture shown above, the formula is “=SUM(LARGE(B3:F3,” at this moment. Here, B3:F3 is the range of cells or arrays for the LARGE formula.

2. Now, it’s time to include another array in the formula. This is for the k value. Select the range of cells or array that has your nth values, and then press the F4 button once to make it an Absolute Reference (if done correctly, you will see dollar signs before each column and row numbers for the cells selected as the nth value for k).

Finally, close the brackets, and press the Ctrl+Shift+Enter buttons.

With Ctrl+Shift+Enter 2

3. Now you will see that the best three values have been picked up and added together.

With Ctrl+Shift+Enter 3

4. Now fill the column with the applied formula. You can see in the Formula Bar that the formula that has done the work here is an Array Formula, initiated by Ctrl Shift Enter!

With Ctrl+Shift+Enter 4

That is the way to do it if you go with the combination of Cell Referencing and Ctrl Shift Enter.

5.2: With Array Constant

Array Constant is another magical element in the calculation of arrays and Excel overall! Formulas usually don’t give you the option to enter multiple values for an argument when it supports one single value. Because, by default, those formulas are not Array Formulas and if you are to use Array Formulas like we have been doing so far in this guide, you have to press the Ctrl+Shift+Enter buttons to make it work.

However, Array Constant changes the game here! It allows you to enter multiple values as an array in a formula. For instance, in just discussed Use 5.1, we used Cell Referencing to include multiple nth values in the formula.

Array Constant allows you to do so without Cell Referencing. You enter your array of values as a single value within curly brackets. Therefore, the formula becomes an Array Formula, and you get to derive your result rapidly! And instead of turning the formula into an Array Formula with Ctrl Shift Enter, we are doing so manually by putting the values inside curly brackets, inside the formula itself!

Let’s go through the demonstration below and see how to do it!

1. First, write the formula till the argument of the k value.

With Array Constant 1

2. Now, within curly brackets, write your nth values {1,2,3} for the best three values as the k value. Then close the brackets and press only Enter (NOT Ctrl+Shift+Enter).

With Array Constant 2

3. You can see that the summation of the best three values from the selected range of cells has been done!

With Array Constant 3

4. Now fill up the column with the formula. You will see that all the calculations have been done properly. You can compare your final best three values for each row against the manual calculation or the 5.1: With Ctrl+Shift+Enter method’s calculated values.

With Array Constant 4

And if you see the formula in the Formula Bar, the numbers for k values are indeed within the curly brackets as you write them in the formula. And curly brackets inside a formula mean it is an Array Formula!

That’s how easy it is!

Use 6: Minimum Value from an Array with Logical Function

Finding the smallest value from a range of cells is probably the easiest task with formulas in Excel. You simply write the MIN formula and select your range of cells from where the lowest value is returned. That’s it.

Let’s make it interesting. What if the requirement is to find the minimum value in an array or selected range based on criteria? We can combine the MIN and IF functions together and amplify their power further with Ctrl Shift Enter. In the later part of this application (from Step 5), there is even a surprise for you that will help cut down your work furthermore. Keep on reading to find out.

Now, we are going to use the dataset shown below for this demonstration. The goal is to find the minimum value in each column where the criteria would be the column header.

Minimum Value from an Array with Logical Function 1

The formula we are using for this operation is: =MIN(IF(logical_test,value_if_true)). Here,

  • MIN & IF: Functions.
  • logical_test: The criterion against which the MIN value will be found.
  • value_if_true: The MIN value that is found against the logical_test or criterion.

We will use an array or range of selections for both the logical_test and value_if_true in our formula for this demonstration.

Check this out!

1. First of all, select a cell where you will write your criterion. For instance, we want to find out the smallest score in a specific test (mentioned in the criterion cell). Therefore, our criterion here is the test name, and the cell next to it is where we will write the formula. The cell for criteria must be chosen first because, in the formula, that cell is referred to via Cell Referencing.

Minimum Value from an Array with Logical Function 2

2. Now write down the formula. For the logical_test or criterion, select the range of cells where the criteria will be looked for. We selected cells B2 through F2 because this is where our test names are. Then put an equal sign and click the cell where you will write the criteria. Next, put in a comma.

As you can see in the picture below, we have chosen cell I3 because this is where we will put the criteria (test name, in this case).

Minimum Value from an Array with Logical Function 3

3. Now select the array or range of cells from where the minimum or lowest value will be picked for each column (according to the criterion name). Finally, press the Ctrl+Shift+Enter buttons.

Minimum Value from an Array with Logical Function 4

4. You will see that the minimum value according to your criterion is shown in the cell where you wrote the formula. Take a look at the picture below for a demonstration.

Minimum Value from an Array with Logical Function 5

Here, we put T.1 under Test as the criterion. The lowest value we got from that column is 23.

5. Similarly, by changing the criterion to T.4 to find out test T.4’s lowest mark, we found out the minimum or lowest value for that test.

Minimum Value from an Array with Logical Function 6

If you don’t want to write down criteria again and again and want to change them instantly to match them against your array, we can show you an excellent option. You can create a drop-down list with the criteria from your array so that whenever you select a criterion from the list, the respective value will be shown.

6. To do it, select the cell for your criteria. Then go to the Data ribbon. Inside the Data Tools group, click the marked (3) Data Validation button.

Minimum Value from an Array with Logical Function 7

7. You will see the Data Validation window now. From there, click the little button with the down arrowhead under Allow:. Select List from the drop-down list.

Minimum Value from an Array with Logical Function 8

8. Now, for Source, click the little up arrow button marked in the picture below.

Minimum Value from an Array with Logical Function 9

9. Now select the column headers as a range. Then click the marked (2) arrow button to go back to the earlier option window for Data Validation.

In this demonstration, we selected test names because those are our criteria.

Minimum Value from an Array with Logical Function 10

10. Now click the OK button.

Minimum Value from an Array with Logical Function 11

11. You will be taken back to your initially selected cell for criteria. Now you will see that there is a drop-down list for you to choose the criteria from.

Minimum Value from an Array with Logical Function 12

12. Now click the little arrow button next to the criteria cell and select whatever criteria you want to choose to see the respective minimum or lowest value.

Minimum Value from an Array with Logical Function 13

So cool and quick, isn’t it?

Use 7: Dynamic Array TRANSPOSE with Ctrl Shift Enter

Converting columns to rows and vice-versa is a well-known requirement in any Excel job. The most common way for it is to copy the column or row data and then paste it as Transpose from the Paste menu. It is also done by using the TRANSPOSE function.

However, both of these options can be destructive. The data can be changed for different reasons while copying. It usually happens more when there are formulas within the data. To tackle this problem, you may use Pivot Table as a solution. But we suggest using the TRANSPOSE function with Ctrl Shift Enter for this task more than anything.

The TRANSPOSE formula as an Array Formula is highly effective for this operation. When a column or row of data is transposed through this method, the data is protected at the destination array. If any changes are needed to be made, they can only be changed at the source.

Therefore, it is extremely useful when you need to transpose your data but have to keep it protected from any changes. In this case, the Dynamic Array TRANSPOSE formula offers a non-destructive method. The dataset below is going to be used for this demonstration. The header of the columns will be turned into row headers after transposing.

Dynamic Array TRANSPOSE with Ctrl Shift Enter 1

Now let’s see the process of how to use an Array TRANSPOSE Formula with the use of Ctrl Shift Enter!

1. First of all, find out how many cells you are going to need from your current data. In this demonstration, we have four columns and five rows. So, we took four rows and five columns as the destination for transposed data.

Dynamic Array TRANSPOSE with Ctrl Shift Enter 2

2. Now select the whole area where you want your data to be transposed. As you can see in the picture below, we selected those five columns and four rows for the original four columns and five rows of data.

Dynamic Array TRANSPOSE with Ctrl Shift Enter 3

3. While the destination columns and rows are selected, write the formula =TRANSPOSE(array). Here, select columns and rows (range of cells) in your dataset from where you want your data. You can see below that we selected the source data after writing =TRANSPOSE(”.

When you are finished writing the formula, simply press the Ctrl+Shift+Enter buttons.

Dynamic Array TRANSPOSE with Ctrl Shift Enter 4

4. Now you will see that your columns and rows data have been transposed into rows and columns, respectively.

Dynamic Array TRANSPOSE with Ctrl Shift Enter 5

Isn’t it great? Now you don’t have to fear the chance of a modification of data when you transpose them.

Additional Tip:

  • If you want to change any data inside your transposed data, you will see a warning message shown in the picture below.

Dynamic Array TRANSPOSE with Ctrl Shift Enter 6

  • If anything needs to be changed, simply go to the data source and then change values from there. It will change instantaneously in your transposed array dataset.

Dynamic Array TRANSPOSE with Ctrl Shift Enter 7

  • If you double-click on anywhere inside the transposed array dataset, you will see the same formula that you used here. It means that the whole range of cells in your transposed data is considered as a single value and so, it will protect your data from any changes.

Dynamic Array TRANSPOSE with Ctrl Shift Enter 8

  • To check whether it is an Array Formula, see the Formula Bar. As you can see here in the picture, curly brackets are holding the formula between themselves. Therefore, it is an Array Formula for sure!

Dynamic Array TRANSPOSE with Ctrl Shift Enter 9

That’s all.

Closing Words

Ctrl Shift Enter in Excel has many applications. If we were to discuss all of them, this guide would have been ten times the size. For this reason, we chose the most useful seven uses of Ctrl Shift Enter to give you an idea about the magnificent world of Array Formula in Excel.

The seven applications that we have discussed in this guide are the most used and practical ones in any Excel user’s daily tasks. We recommend that you take your time to learn all of these uses and apply them whenever needed. If you can grasp all the ideas behind the calculation using Array Formulas shown in this article, you will be able to recognize the opportunity of using them.

And after you become familiar with this Ctrl Shift Enter, you will succeed in doing complex calculations using just a single cell. So, practice and learn these important uses of Ctrl Shift Enter and take yourself to the next level.

Further Reading:

Leave a Comment