To save data-entry time, you can copy and paste the values in cells, including ranges and formulas, into other cells in the worksheet. There are several copy and paste options to choose from, as outlined below.
You can use the keyboard shortcut of CTRL+Z to undo actions and CTRL+Y to redo actions, including bulk actions, such as copy forward.
Copy and paste
To copy a value from one cell in the worksheet and paste it in another, click the cell and press Ctrl+C, then click the destination cell and press Ctrl+V.
To copy the values in a range of cells from one location in the worksheet and paste it into another:
Left-click the first cell in the range and drag your cursor over the cells you want to include in the range.
Right-click a cell in the selected range and select Copy.
Click the destination cell, then right-click > Paste.
Drag and copy
The drag and copy method allows you to drag a value from one cell across the row or down the column.
Click the cell, then click and drag the bottom corner of the cell across or down the row.
To copy the value in cell all the way across the row or all the way down a column, use the Copy Forward or Copy Down option, as outlined below.
Copy forward
The Copy Forward feature allows you to copy a value in one cell, or the values in multiple cells, all the way forward across their respective rows until the end of the period.
To copy forward one cell, right-click the cell and click Copy Forward.
To copy forward multiple cells in different rows:
Left-click the cell in the first row and drag your cursor down over the cells in the subsequent rows you want to include.
Right-click a cell in the selected range and select Copy Forward.
Copy down
The Copy Down feature is a short cut to the drag and copy method above. It allows you to copy down a value in one cell, or the values in multiple cells in a row, in one of three ways: Rows in Section, Expanded Rows and All Rows.
The following image illustrates the outcome of each of the Copy Down options. Note how the value of the Total UK changes when each option in selected.
When the Rows in Section option is selected, the cell value (100) is copied down into the rows in the Branch 2 section (level), which is just one row (Account 104790).
When the Expanded Rows option is selected, the cell value is copied down into all the expanded (visible) rows in the worksheet, which includes the rows in Branch 2 and 3. This option is particularly useful when you use it alongside the Search feature, as outlined in the example below.
When the All Rows option is selected, the cell value is copied down into all the rows below it, regardless of whether those rows are expanded (Branch 2 and 3) or not (Branch 5 and 6).
Using the bulk undo keyboard shortcut of CTRL+Z, you can easily try out each option, to see the impact of each in your worksheet.
Example: Copy down rows in section
Suppose you want to apply a formula (last year’s actual x 10%) to all the rows in the Cost section of the worksheet.
Enter the formula in the first cell of the row.
Copy that cell forward to the end of the row. All the values in the subsequent cells in the row are overwritten with the formula.
Select that row.
Right-click the selected row, then click Copy Down > Rows in Section. All the values in the subsequent rows in that section are overwritten.
Example: Copy down expanded rows
Suppose you have a driver-based budget with Cost and Quantity measures and you want to apply a formula (last year’s actual x 10%) to just the Cost rows.
Enter the formula in the first cell of the row.
Copy that cell forward to the end of the row. All the values in the subsequent cells in the row are overwritten with the formula.
In the Search box, enter a search term (Cost in this case) to filter the rows in the worksheet and display only the rows you want to copy the values into.
Right-click the selected row, then click Copy Down > Expanded Rows. All the values in the subsequent filtered (Cost) rows are overwritten*.
*This option does not overwrite the values in working or sum rows.
Example Copy down all rows
Suppose you want to apply a formula (last year’s actual x 10%) to all the rows in the Cost section of the worksheet.
Enter the formula in the first cell of the row.
Copy that cell forward to the end of the row. All the values in the subsequent cells in the row are overwritten with the formula.
Select that row.
Right-click the selected row, then click Copy Down > All Rows. All the editable values in the subsequent rows in the worksheet are overwritten.