Some of you might have to do spreadsheets more often than I do. And let me tell you, when I do them, it’s a project. In an effort to help us all, I went in search of the most helpful Excel hacks to make data entry and calculation easier. Here are some gems I bookmarked for my own use.
Tip #1 From Launch Excel
Entering values into multiple cells using CTRL + ENTER
Scenario 1 – quickly enter data in multiple cells
- To quickly enter data in multiple cells, first select all of the cells. Type in the data and press CTRL + ENTER, which enters the same value in all of the selected cells. Select all the cells you want to fill.
- Type ‘John’ then enter the same value in all selected cells using CTRL + ENTER. For non-contiguous cells, hold down the CTRL key and click to select each cell.
- To enter values in non-contiguous cells first hold down the CTRL key and click to select each cell then type in ‘John’ and use CTRL + ENTER to enter the same value in all selected cells.
Scenario 2 – quickly correct data in multiple cells
- To quickly correct the data in multiple cells, select all of the cells containing the names you want to fix. Let’s say you need to replace ‘Betty’ with ‘Britney’ in five cells.
- How do you quickly replace ‘Betty’ with ‘Britney’ in these five cells? First, select all five cells containing ‘Betty’. For non-contiguous cells, hold down the CTRL key and click to select each cell. You can then type ‘Britney’ and press CTRL + ENTER, which will enter ‘Britney’ in all of the selected cells.
Scenario 3 – quickly fill upwards
- If you would like to fill up instead of fill down, you can do this by a clever modification of the CTRL + ENTER trick. Let’s say you want to replace ‘Britney’ with ‘Charlie.’ There’s already a ‘Charlie’ entered in the cell below. Select the cells starting with ‘Charlie’ and ending with the topmost ‘Britney.’
- How do you fill ‘Charlie’ upwards to overwrite the five cells showing ‘Britney’? With the cells selected, press F2 to go into Edit mode, then press CTRL + ENTER to copy the correct name into all of the selected cells.
- With the cells selected, press F2 then press CTRL + Enter and ‘Charlie’ has been filled upwards to replace ‘Britney.’ Note, the first cell with ‘Charlie’ must be the active cell. You can see this in the screenshot as it has a white background instead of a blue background.
For more hacks, take a look at the full post on Intuit's Fast Track blog.