EDUCBA

EDUCBA

MENUMENU
  • Blog
  • All Courses
  • All in One Bundle
  • Login
Home Excel Excel Resources Excel Tips CTRL Shift-Enter in Excel

CTRL Shift-Enter in Excel

Madhuri Thakur
Article byMadhuri Thakur

Updated August 22, 2023

Ctrl Shift Enter in Excel

Ctrl Shift-Enter in Excel (Table of Contents)
  • Explanation of Ctrl Shift-Enter in Excel
  • Examples of Excel Control Shift-Enter

Excel Ctrl Shift-Enter

Ctrl Shift-Enter is one of the shortcuts used in Excel to perform calculations with array formulae. It supports performing complex calculations using the standard Excel functions. It is widely used in array formulae to apply functions and formulas to a data set. Using Ctrl Shift-Enter together helps convert the data into an array format consisting of multiple data values in Excel. It also supports differentiation between the regular formula and the array formula. This shortcut has two major advantages: dealing with a set of values and returning multiple values at a time.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Explanation of Ctrl Shift-Enter in Excel

  • Before we use the shortcut CTRL SHIFT-ENTER, we need to understand more about the arrays. Arrays are data collection, 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 mentioned above, the formula encloses braces around the range of cells, resulting 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 requiring data in various cells. It is effectively used in various operations like determining sum and performing matrix multiplication.

How to Use Ctrl Shift-Enter in Excel?

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

You can download this CTRL Shift-Enter Excel Template here – CTRL Shift-Enter Excel Template
  • Creation of array formula in matrix operations such as multiplication.
  • Creation of 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.
  • Counting the number of characters or values in the range of data in Excel.
  • Summing the values presented in every nth column or row within low and upper boundaries.
  • Performing the task of the creation of simple datasets in a short time.
  • Expanding the array formula to multiple cells.
  • Determining the Average, Min, Max, Aggregate, and array expressions.
  • Returning the results in multiple and single cells by applying the array formulas.
  • Use of CTRL SHIFT-ENTER in IF functions.

Examples of Ctrl Shift-Enter in Excel

Below are examples of Excel control shift-enter.

Example #1 – To Determine Sum

This example illustrates the use of Ctrl SHIFT-ENTER in calculating the sum of sales generated for different products. We consider the following data for this example.

Ctrl Shift Enter in Excel Example 1

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

Example 1.1

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

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. The result is obtained as shown in the image.

Ctrl Shift Enter in Excel Example 1.4

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

Example 1.5

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 column of the subtotal.

Example #2 – Determine the Sum using Condition

This example illustrates using 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 below screenshot.

 Example 2.1

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

=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 shown in the image.

Example 2.3

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, one can automate the calculation of generated sales by using the shortcut CTRL SHIFT-ENTER.

Example #3 – Determining the Inverse Matrix

For this example, consider the following matrix A.

Let A = 10    8

4      6

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

Example 3

The range of the matrix is B1: C2.

Step 2: Highlight the range of cells to position the inverse matrix 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

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

Ctrl Shift Enter in Excel Example 3.3

Step 5: Once the MINVERSE function is entered successfully, use the shortcut CTRL SHIFT-ENTER to generate the array formula to have the results of all four matrix components 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

Things to Remember

  • 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 must press the shortcut CTRL SHIFT-ENTER again since the braces are removed every time we make changes.
  • While using the shortcut requires selecting the range of cells to result in the output before entering the array formula.

Recommended Articles

This is a guide to CTRL Shift-Enter in Excel. Here we discuss 3 ways to use Ctrl Shift-Enter in Excel to determine the sum, inverse matrix, and sum using condition, examples, and a downloadable Excel template. You may look at our following articles to learn more –

  1. Excel CTRL D
  2. Inverse Matrix in Excel
  3. Excel Correlation Matrix
  4. MINVERSE in Excel
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
MS Excel & VBA for Data Science Course Bundle - 24 Courses in 1 | 10 Mock Tests
87+ Hours of HD Videos
24 Courses
10 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
All-in-One Financial Analyst Masters Training Program Bundle - 550+ Courses | 300+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.9
All-in-One Data Science Bundle - 400+ Courses | 550+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.7
Primary Sidebar
Popular Course in this category
MS Excel Course Bundle - 36 Courses in 1 | 13 Mock Tests
 137+ Hours of HD Videos
36 Courses
13 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.9
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

Download CTRL Shift-Enter Excel Template

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

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

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

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download CTRL Shift-Enter Excel Template

EDUCBA

डाउनलोड CTRL Shift-Enter Excel Template

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more