WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Ctrl Shift Enter in Excel

Ctrl Shift Enter in Excel

Excel Ctrl Shift-Enter Command

Use of Ctrl Shift-Enter together helps in converting the data into an array format which consists of multiple data values in excel. It also supports in differentiation between the regular formula and array formula in excel. With this shortcut, there are two major advantages such as dealing with a set of values at a time and also returning the multiple values at a time.

Ctrl Shift-Enter is one of the shortcuts used in Excel to perform the calculations with array formulae. It supports to performing the complex calculation using the standard excel functions. It is widely used in the array formulae to apply functions and formulas on a set of data.

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 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 the selection of 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 shortcut 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 sum and performing matrix multiplication in excel.

Ctrl Shift Enter in Excel

Examples of Ctrl Shift-Enter In Excel

You can download this Ctrl Shift Enter Excel Template here – Ctrl Shift Enter Excel Template

Example #1 – To Determine Sum

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

Ctrl Shift Enter in Excel Example 1

Step 1: Place the cursor into the empty cell where you want to produce the worth of the total sales of the product.

Example 1.1

Step 2: Enter the formula as total = sum (D6:D11*E6: E11), as shown in the figure.

Ctrl Shift Enter in Excel Example 1.2

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

Example 1.3

Step 4: The opening and closing braces are added to the sum formula in excel. The result is obtained, as shown in the figure.

Ctrl Shift Enter in Excel Example 1.4

Step 5: Check the result determining total with the general procedure we used.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Example 1.5

The result obtained from the two procedures is the same. But, using the array formula with CTRL SHIFT ENTER is easy in determining total sales by eliminating the calculation of an additional column of sub-total.

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. The following data is considered for this example.

Ctrl Shift Enter in Excel Example 2

Step 1: In the first step, 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 respective cells, as shown in the figure.

Example 2.1

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)))

Ctrl Shift Enter in Excel Example 2.2

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

Example 2.3.0

Step 4: Change the values of F2 and F3 cells to determine sales generated by other persons.

Ctrl Shift Enter in Excel Example 2.4

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

Step 1: Enter the matrix A into the Excel sheet, as shown in the below-mentioned figure.

Example 3

The range of the matrix is that B1:C2.

Step 2: Highlight the range of cells to position the inverse matrix in excel A-1 on the same sheet.

Ctrl Shift Enter in Excel Example 3.1

Step 3: After highlighting the range of cells, input the formula of MINVERSE to calculate the inverse matrix. Take care during inputting the formula that all cells are highlighted.

Example 3.2.0

Step 4: Enter the range of the array or matrix, as shown in the screenshot.

Ctrl Shift Enter in Excel Example 3.3.0

Step 5: Once the MINVERSE function entered successfully, use the shortcut CTRL+SHIFT+ENTER to generate the array formula to have the results of all four components of the matrix without reentering to other components. The converted array formula is shown as

{=MINVERSE (B1: C2)}

Example 3.5

Step 6: The resultant inverse matrix is produced as:

Ctrl Shift Enter in Excel Example 3.6

Applications

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

  • 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.
  • Count the number of characters or values in the range of data in Excel.
  • Sum the values presented in every nth column or row within low and upper boundaries.
  • Perform the task of the creation of simple datasets in a short time.
  • Expand the array formula to multiple cells.
  • Determine the value of Average, Min, Max, Aggregate, and array expressions.
  • Return the results in multiple and single cells applying the array formulas.
  • Use of CTRL+SHIFT+ENTER in IF function in excel.

Things to Remember

  • The manual entering of braces surrounding the formula doesn’t work in Excel. We should press the shortcut CTRL+SHIFT+ENTER.
  • When we edit the array formula, we need to again press the shortcut CTRL+SHIFT+ENTER since the braces are removed every time we make changes.
  • While using the shortcut, it requires to select the range of cells to result in the output before entering the array formula.

Recommended Articles

This has been a guide to the Ctrl shift-enter in excel. Here we learn when and how to use Ctrl Shift-Enter in excel along with 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 Excel
  • Using String Array in VBA
  • Declare Array in VBA
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Ctrl Shift Enter Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More