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
You can copy and paste formulas and values from one cell to another.
To copy a formula or 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 formulas or 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 and select either Paste Formula or Paste Value, as required.
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 shortcut 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 four ways: Rows in Section, Expanded Rows, All Rows and All [specific] Rows.
The following image illustrates the outcome of each of the Copy Down options. Note how the values in the Total Revenue row change with each option.
Rows in Section - The cell value (1,000,000) is copied down into the rows in the AUS section (level), which is four rows.
Expanded Rows - The cell value is copied down into all the expanded (visible) rows, which includes the rows in AUS and UK sections. This option is particularly useful when you use it alongside the search (filter) functionality, as outlined in the example below.
All Rows - The cell value is copied down into all the rows below it, regardless of whether those rows are expanded (AUS and UK) or not (US).
All [specific]Rows - The cell value is copied down into all the rows of the same account name, regardless of whether those rows are expanded or not. In this case, the value is in the A100 Sales Revenue (Retail) account row in the AUS section, so it is copied down into that same account row in the UK and US sections.
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.
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.
Copy formulas and paste values to lock in the budget
If you are a budget owner and you have completed the budgeting process, you might want to take all the formulas in a budget and change them to the current values to lock in the budget. You can do this quickly using the copy and paste feature.
Select the entire grid.
Right-click any cell and click Copy.
Select the top-left cell and right-click > Paste Values.