Excel Ctrl Shift-Enter Command

For example, we can calculate the sales and costs of various electronic gadgets over any given number of years.

Ctrl+Shift+Enter is one of the shortcuts used in Excel to perform the calculations with array formulas. It supports performing complex calculations using the standard excel functionsExcel FunctionsExcel functions help the users to save time and maintain extensive worksheets. There are 100+ excel functions categorized as financial, logical, text, date and time, Lookup & Reference, Math, Statistical and Information functions.read more. It is widely used in the array formulae to apply functions and formulas to a data set.

Explanation

  • Before we want to use the shortcut CTRL+SHIFT+ENTER, we need to understand more about the arrays. The array is the collection of the data, including text and numerical values in multiple rows and columns or only in a single row and column. For example, Array = {15, 25, -10, 24}To enter the above values into an array, we need to enter CTRL+SHIFT+ENTER after selecting the range of cells. It results in the array as:Array= {= {15, 25, -10, 24}}As shown above, braces are enclosed to the formula by resulting in the range of cells in a single horizontal array. This keyboard shortcutKeyboard ShortcutAn Excel shortcut is a technique of performing a manual task in a quicker way.read more works in different versions of Excel, including 2007, 2010, 2013, and 2016.CTRL+SHIFT+ENTER also works for vertical and multi-dimensional arrays in Excel. This shortcut works for different functions that require the use of data in a range of cells. It is effectively used in various operations like determining sums and performing matrix multiplication in excelMatrix Multiplication In ExcelThe MMULT function in Excel is an inbuilt function for matrix multiplication. It accepts two arrays as arguments and returns the product of the two arrays. read more.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: Ctrl Shift Enter in Excel (wallstreetmojo.com)

Examples of Ctrl Shift-Enter In Excel

Example #1 – To Determine the Sum

This example best illustrates the use of Ctrl+SHIFT+ENTER in calculating the sum of sales generated for different products. Therefore, the following data is considered for this example.

Step 1: First, we must place the cursor into the empty cell where we want to produce the worth of the product’s total sales.

Step 2: As shown in the figure, we need to insert the formula as total = sum (D6:D11*E6: E11).

Step 3: After entering the formula, we must press CTRL+SHIFT +ENTER to convert the general formula into an array formula.

Step 4: As a result, the opening and closing braces are added to the sum formula in excelSum Formula In ExcelThe SUM function in excel adds the numerical values in a range of cells. Being categorized under the Math and Trigonometry function, it is entered by typing “=SUM” followed by the values to be summed. The values supplied to the function can be numbers, cell references or ranges.read more. The result will be obtained, as shown in the figure.

Step 5: Now, we need to check the result determining the total with the general procedure we used.

The result obtained from the two procedures is the same. But, using the array formula with CTRL+SHIFT+ENTER is easy to determine total sales by eliminating the calculation of an additional subtotal column.

Example #2 – To Determine Sum using Condition

This example best illustrates the use of Ctrl+SHIFT+ENTER in calculating the sum of sales generated for different products using the conditions. Therefore, the following data is considered for this example.

Step 1: In the first step, we need to determine the details of the seller and product that want to calculate sales generated individually. Consider Seller =John and Product = PC and enter these details into individual cells, as shown in the figure.

Step 2: To determine the sales generated by John in selling PC products, the formula as:

=SUM (IF (((A2:A10=F2)*(B2:B10=F3)), (C2:C10)))

Step 3: We mustpress CTRL+SHIFT+ENTER to have the desired result, as shown in the figure.

Step 4: Now, we need to change the values of F2 and F3 cells to determine sales generated by other persons.

In this way, the calculation of sales generated is automated using the shortcut CTRL+SHIFT+ENTER.

Example #3 – To Determine the Inverse Matrix

For this example, consider the following matrix A.

Let A = 10    8

         4      6

Applications

CTRL + SHIFT + ENTER is used in many applications in Excel.

  • Initially, we must enter the matrix A into the Excel sheet, as shown in the figure below. The range of the matrix is B1:C2. Then, highlight the range of cells to position the inverse matrix in Excel A-1 on the same sheet. After highlighting the range of cells, we must input the formula of MINVERSE to calculate the inverse matrix. Ensure during inputting the formula that all cells are highlighted. First, we must enter the array or matrix range, as shown in the screenshot. Once the MINVERSE function is entered successfully, we must use the shortcut CTRL+SHIFT+ENTER to generate the array formula to have the results of all four matrix components without re-entering to other details. The converted array formula is shown as{=MINVERSE (B1: C2)} The resultant inverse matrix is produced as:

The range of the matrix is B1:C2.

Ensure during inputting the formula that all cells are highlighted.

The converted array formula is shown as{=MINVERSE (B1: C2)}

  • Creating an array formula in matrix operations such as multiplication.Creating an array formula in determining the sum of the set of values.Replacing the hundreds of formulas with only a single array formula or summing the range of data that meets specific criteria or conditions.To count the number of characters or values in the range of data in Excel.To sum the values presented in every nth column or row within low and upper boundaries.To perform the task of creating simple datasets in a short time.To expand the array formula to multiple cells.To determine the Average, Min, Max, Aggregate, and array expressions values.To return the results in multiple and single cells by applying the array formulas.To use CTRL+SHIFT+ENTER in the IF function in excelIF Function In ExcelIF function in Excel evaluates whether a given condition is met and returns a value depending on whether the result is “true” or “false”. It is a conditional function of Excel, which returns the result based on the fulfillment or non-fulfillment of the given criteria.
  • read more.

Things to Remember

  • The manual entering of braces surrounding the formula does not work in Excel. Instead, we should press the shortcut CTRL+SHIFT+ENTER.When we edit the array formula, we must press the shortcut CTRL+SHIFT+ENTER again since the braces are removed every time we change.Using the shortcut requires selecting the range of cells to result in the output before entering the array formula.

This article has been a guide to the Ctrl Shift-Enter in Excel. Here, we learn when and how to use Ctrl Shift-Enter in Excel, examples, and a downloadable template. You may learn more about Excel from the following articles: –

  • How Many Rows and Columns in Excel?Break-Even Point in ExcelUsing String Array in VBADeclare Array in VBA