To copy a formula down a column in Google Sheets, select the cell with the formula plus the empty cells below it and press Ctrl + D (⌘ + D on a Mac) to fill down; you can also drag the small blue box at the corner of the selection. Relative references move one row at a time, while references with $ stay fixed. ProfitDrawer's bookkeeping spreadsheets already carry their formulas down all 1,000 income and 1,000 expense rows.
Fill down (computer):
- Type the formula in the first cell, for example
=C2*D2in E2. - Click E2, then hold Shift and click the last cell you need, for example E500.
- Press Ctrl + D (Windows, ChromeOS) or ⌘ + D (Mac).
Other shortcuts from Google's list: Ctrl + Enter fills a selected range, Ctrl + R fills right.
Lock a reference: =C2*$H$1 keeps H1 fixed in every row while C2 becomes C3, C4 and so on. While editing a formula, press F4 to switch between absolute and relative references.
One formula for the whole column: ARRAYFORMULA shows results in many rows from one cell, for example =ARRAYFORMULA(IF(C2:C500="","",C2:C500*D2:D500)). Pressing Ctrl + Shift + Enter while editing adds ARRAYFORMULA( for you. Google notes array formulas cannot be exported.
Check it worked: click a cell lower down and confirm its references point to its own row.
When you clear the example rows, select only the typed cells (Income Date to Paid?, Expenses Date to Amount, Mileage Date to Miles) and press Delete, so the formula columns stay in place. In Google Sheets the Excel locks are not kept, so a formula cell you select is cleared too.
Source: Keyboard shortcuts for Google Sheets: Google Docs Editors Help · Updated
Tools and spreadsheets for this
Related questions
All questions on ProfitDrawer Answers →
General information, not tax, legal or financial advice. Check the rules for your state and your situation.