Office hacks: Excel with Ctrl+Enter key combination

The Ctrl+Enter key combination in Excel can unify all the cells selected for operation. Using this function, we can solve some of the problems encountered in the daily production of statistical tables to improve efficiency.

1. Find the wrong value Quick operation

Usually we use Excel to deal with data tables, often use some query formulas, in the results returned, sometimes mixed with some of the wrong values (Figure 1). For these errors in the value of the deletion, assignment and other operations, the direct use of the “Find and Replace” function can not be achieved, one by one manual processing is too clumsy, seriously affecting efficiency. To quickly unify the operation of multiple results, you can try the following methods.

1903A-ZHJ-1

First, select the column with the formula, press the shortcut Ctrl + G, select “formula”, check the “error” and OK (Figure 2), at this time that is, select all the return value is the wrong cell. At this point, remember not to use the mouse to select the cell, or you need to rework.

1903A-ZHJ-2

Now, if you need to delete the error value, just press Backspace, see the first value deleted and then press the Ctrl+Enter key combination, and all the error values will be deleted. If you need to assign a new value, then enter the new value and press Ctrl+Enter, all the wrong values will be given a new value.

2. Split and merge cells to quickly assign values

The most headache of creating data tables is the trouble of merging cells, not only affect the filtering, but also can not create pivot tables. How can we solve this problem more quickly?

First of all, select the need to split the cell, click the “merge after the center” command in the menu bar; then press Ctrl + G, positioning conditions to select the “null” and OK (Figure 3), then enter “= C2 “(the top a non-null value), and then press the Ctrl + Enter key combination, all the null values are assigned to the correct value.

1903A-ZHJ-3

Note that the following cells are formulas rather than values, when the value of the first cell changes, it will affect the cells below. The solution is to select this column and copy, and then right-click and select “selective paste”, select “value” and OK.

3. complex logic formula fast copy

When dealing with very long data tables, bulk add formulas to pull up and down the table, rather tedious, what is a good way to do? First select the cells need to add the formula, directly in the name box after entering the formula, press Ctrl + Enter key combination, all cells are added to the formula, very efficient. This is a simple formula input.

1903A-ZHJ-4

For the formula logic is more complex, can not be directly analogous to the situation, you can take a special approach to deal with. For example, the need to number all personnel in the unit, the number is three digits, the first for the department number, the last two for the sequence number. Simply the number of the first person in each department, the use of the above-mentioned approach to locate the empty value, select the other unnumbered cells (Figure 4), enter “= D2 + 1” (the top a non-empty value), and then press Ctrl + Enter key combination, all the empty values are assigned the correct value, and then use the selective paste the formula into a numerical value can be.

Leave a Comment