Copy and paste values
User permission: Budgets & Forecasts
Limitation: You can only copy and paste cells within the same budget workbook. Copying between different budget workbooks isn't supported. If you need to transfer work from one budget workbook to another, consider cloning the original workbook instead. This will duplicate all the content, and you can then modify the cloned version as needed.
To save data-entry time, you can copy and paste the values in cells, including ranges and formulas, into other cells in the workbook. There are several copy and paste options to choose from, as outlined below.
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 formula or value from one cell or a range of cells:
Select the cell you want to copy. To select a range of cells, select the first cell and drag your cursor over the cells you want to include in the range.
Use one of these methods to copy the selected cell(s):
Press Ctrl+C.
Right-click the cell, or any cell in a selected range, to open the context menu and select Copy.
Click the Cell menu in the top bar and select Copy.
To paste the copied cell(s):
Go to the destination cell.
Use one of these methods:
Press Ctrl+V.
Right-click the cell to open the context menu and select Paste.
Click the Cell menu in the top bar and select 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.
Select the cell you want to copy. To select a range of cells, select the first cell and drag your cursor down over the cells in the subsequent rows you want to include in the range.
Use one of these methods to copy the selected cell(s):
Right-click the cell, or any cell in a selected range, to open the context menu and select Copy Forward.
Click the Cell menu in the top bar 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.
The Copy down options are also accessible via the Cell menu.
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*.
*Note: 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.
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.
Last updated