Formula: Balance Reduces Total Another

In this article, we will discuss how to create a spreadsheet that functions as a checkbook register. Here are the steps to create the spreadsheet with the specified columns:

1. Open a new Google Spreadsheet.

2. Rename the spreadsheet to "Checkbook Register".

3. In cell A1, type "Date". In cell B1, type "Check Number". In cell C1, type "Payee". In cell D1, type "Balance". In cell E1, type "Debit". In cell F1, type "Credit". In cell G1, type "Ending Balance". In cell H1, type "Memo".

4. Format cells D1, F1, G1, and H1 as currency.

5. In cell A2, type today's date (you can use the "TODAY()" function).

6. In cell A3, type the next available check number.

7. In cell B3, type the payee's name.

8. In cell C3, type the description of the transaction.

9. In cell D3, if the transaction is a debit, enter the amount. If it's a credit, enter 0.

10. In cell E3, if the transaction is a debit, enter 0. If it's a credit, enter the amount.

11. In cell F3, if the transaction is a credit, enter the amount. If it's a debit, enter 0.

12. In cell G3, calculate the new balance by adding the credit amount (cell F3) and subtracting the debit amount (cell E3) from the previous balance (cell D3). Use the formula =D3+F3-E3.

13. Copy cells A3 through G3 and paste them into the rows below as needed for additional transactions.

14. To calculate the total debits and credits, use the SUM function. In cell I1, type "Total Debits" and in cell I2, type =SUM(E3:E[last row number]). In cell J1, type "Total Credits" and in cell J2, type =SUM(F3:F[last row number]).

15. To calculate the final balance, use the formula =G[last row number]. In this case, you would put this formula in cell G1 and press Enter.

16. You can use conditional formatting to highlight negative balances. Select cells D3 through G[last row number], click "Format" > "Conditional formatting", choose "Custom formula is", and enter the formula =D3<0. Set the formatting style to your preference, such as a red background.

That's it! You now have a basic checkbook register spreadsheet. You can add more columns if needed, such as categories for expenses and income.

References:

• Google Spreadsheets Documentation: https://support.google.com/docs/answer/3093294
• Google Spreadsheets Conditional Formatting: https://support.google.com/docs/answer/3093366

Summary:

• Created a checkbook register spreadsheet with the following columns: Date, Check Number, Payee, Balance, Debit, Credit, Ending Balance, Memo.
• Formatted cells D1, F1, G1, and H1 as currency.
• Used the TODAY() function to automatically update the date.
• Calculated the new balance, total debits, total credits, and final balance using formulas.
• Used conditional formatting to highlight negative balances.
• The spreadsheet can be easily modified to include additional columns or rows as needed.