Track Taxes in Google Sheets With Controls

Start by creating a workbook with exactly 2 tabs:

• Tax Log
• Summary

Enter these headers in Tax Log!A1:L1:

Amounts and distances must be nonnegative. To distinguish income, expenses, payments, and mileage, use Record Type instead of entering expenses as negative values.

This example prepares rows 2 through 1000 for data entry. If you need more capacity, extend the validation, status formula, conditional formatting, filter, and protection ranges before adding records below row 1000.

Next, create these reference lists on Summary:

These are organizational labels. They don’t establish legal tax treatment. Although the workbook checks whether a category appears in the approved list, it doesn’t enforce a relationship between each category and its record type.

Enter this illustrative fixture in Tax Log!A2:K6. Leave cells shown as blank empty instead of typing the word “blank.”

Enter dates as real Google Sheets date values, then apply these formats:

• Tax Log!A2:A1000: Format > Number > Custom date and time, displayed as yyyy-mm-dd
• Tax Log!E2:E1000: Format > Number > Custom currency, USD with 2 decimal places
• Tax Log!F2:F1000: Format > Number > Custom number format, using 0.0

The filenames are illustrative references. This guide doesn’t include the source documents.

For each range below, select Data > Data validation > Add rule and set invalid data to be rejected.

For Tax Log!A2:A1000, use this custom formula to accept dates from 1900 through 9998:

=AND(ISNUMBER(A2),A2>=DATE(1900,1,1),A2<DATE(9999,1,1))

Then add these dropdown rules:

• Tax Log!C2:C1000: dropdown from Summary!I2:I5
• Tax Log!D2:D1000: dropdown from Summary!J2:J15

For Tax Log!H2:H1000, require a whole-number year from 1900 through 9998:

=AND(ISNUMBER(H2),H2=INT(H2),H2>=1900,H2<=9998)

The upper limit is 9998 because the reporting formulas use January 1 of the following year as an exclusive end date.

For Tax Log!E2:E1000, use this formula. Mileage records must leave Amount blank, while other record types require a nonnegative numeric amount:

=OR(AND($C2="Mileage",E2=""),AND($C2<>"Mileage",ISNUMBER(E2),E2>=0))

For Tax Log!F2:F1000, use this formula. Mileage records require nonnegative numeric distance, while other types must leave Distance blank:

=OR(AND($C2="Mileage",ISNUMBER(F2),F2>=0),AND($C2<>"Mileage",F2=""))

For Tax Log!I2:I1000, require a deduction tag only for expenses and mileage:

=IF(OR($C2="Expense",$C2="Mileage"),COUNTIF({"Unreviewed","Candidate","Not claimed"},I2)=1,I2="")

Validation reduces direct-entry errors, but pasted or imported data can replace or bypass the rules. To catch those cases, the next step adds a formula-based check.

Enter this formula in Tax Log!L2:

=IF(
  COUNTA(A2:K2)=0,
  "",
  IF(
    NOT(ISNUMBER(A2)),
    "INVALID DATE",
    IF(
      OR(A2<DATE(1900,1,1),A2>=DATE(9999,1,1)),
      "INVALID DATE",
      IF(
        B2="",
        "MISSING DESCRIPTION",
        IF(
          COUNTIF({"Income","Expense","Estimated payment","Mileage"},C2)=0,
          "INVALID TYPE",
          IF(
            COUNTIF(Summary!$J$2:$J$15,D2)=0,
            "INVALID CATEGORY",
            IF(
              NOT(ISNUMBER(H2)),
              "INVALID TAX YEAR",
              IF(
                OR(H2<>INT(H2),H2<1900,H2>9998),
                "INVALID TAX YEAR",
                IF(
                  H2<>YEAR(A2),
                  "YEAR MISMATCH",
                  IF(
                    C2="Mileage",
                    IF(
                      NOT(ISNUMBER(F2)),
                      "INVALID KM",
                      IF(
                        F2<0,
                        "INVALID KM",
                        IF(
                          E2<>"",
                          "CLEAR AMOUNT",
                          IF(
                            COUNTIF({"Unreviewed","Candidate","Not claimed"},I2)=0,
                            "INVALID DEDUCTION TAG",
                            "OK"
                          )
                        )
                      )
                    ),
                    IF(
                      NOT(ISNUMBER(E2)),
                      "INVALID AMOUNT",
                      IF(
                        E2<0,
                        "INVALID AMOUNT",
                        IF(
                          F2<>"",
                          "CLEAR KM",
                          IF(
                            C2="Expense",
                            IF(
                              COUNTIF({"Unreviewed","Candidate","Not claimed"},I2)=0,
                              "INVALID DEDUCTION TAG",
                              "OK"
                            ),
                            IF(
                              I2<>"",
                              "CLEAR DEDUCTION TAG",
                              "OK"
                            )
                          )
                        )
                      )
                    )
                  )
                )
              )
            )
          )
        )
      )
    )
  )
)

Copy L2 through L1000 before entering live data. Every populated entry row must have the status formula in column L. Without it, the row will be omitted from summaries and control counts because the formulas require a status of OK.

All 5 sample rows should return OK. The 2025 record is structurally valid, but the 2026 summary formulas exclude it based on its date.

The status formula checks that:

• Date is numeric and within the supported date range.
• Description is present.
• Record Type appears in the allowed list.
• Category appears in Summary!J2:J15.
• Tax Year is a whole number from 1900 through 9998 and matches the date.
• Amount and distance follow the selected record type.
• Deduction tags are used only where required.

Now select Tax Log!A1:L1000 and choose Data > Create a filter. Add 2 conditional-formatting rules to Tax Log!A2:L1000.

Invalid populated row

=AND(COUNTA($A2:$K2)>0,$L2<>"OK")

Missing source-document reference

=AND(COUNTA($A2:$K2)>0,$J2="")

Use a warning color for each rule. A warning doesn’t exclude a row by itself. Only the explicit Calculation status = OK criterion in the summary formulas excludes invalid rows.

An OK status means only that the row passed these structural checks. It doesn’t confirm that the transaction is unique, supported, deductible, or assigned to the correct category for its record type.

Set up the reporting period in Summary!A1:B3:

Validate B1 as a whole number from 1900 through 9998.

Enter this guarded formula in B2:

=IF(NOT(ISNUMBER(B1)),"CHECK YEAR",IF(OR(B1<>INT(B1),B1<1900,B1>9998),"CHECK YEAR",DATE(B1,1,1)))

In B3, enter:

=IF(NOT(ISNUMBER(B2)),"CHECK YEAR",DATE(B1+1,1,1))

Format Summary!B2:B3 as yyyy-mm-dd. With B1 set to 2026, the displayed results should be:

• B2: 2026-01-01
• B3: 2027-01-01

These formulas use an inclusive start and exclusive end. A record dated January 1, 2026 is included, while one dated January 1, 2027 is excluded.

Enter these labels in A5:A10:

Next, enter the following formulas.

Summary!B5:

=IF(NOT(AND(ISNUMBER($B$2),ISNUMBER($B$3))),"CHECK YEAR",SUMIFS('Tax Log'!$E$2:$E,'Tax Log'!$C$2:$C,"Income",'Tax Log'!$A$2:$A,">="&$B$2,'Tax Log'!$A$2:$A,"<"&$B$3,'Tax Log'!$L$2:$L,"OK"))

Summary!B6:

=IF(NOT(AND(ISNUMBER($B$2),ISNUMBER($B$3))),"CHECK YEAR",SUMIFS('Tax Log'!$E$2:$E,'Tax Log'!$C$2:$C,"Expense",'Tax Log'!$A$2:$A,">="&$B$2,'Tax Log'!$A$2:$A,"<"&$B$3,'Tax Log'!$L$2:$L,"OK"))

Summary!B7:

=IF(NOT(AND(ISNUMBER($B$2),ISNUMBER($B$3))),"CHECK YEAR",SUMIFS('Tax Log'!$E$2:$E,'Tax Log'!$C$2:$C,"Expense",'Tax Log'!$I$2:$I,"Candidate",'Tax Log'!$A$2:$A,">="&$B$2,'Tax Log'!$A$2:$A,"<"&$B$3,'Tax Log'!$L$2:$L,"OK"))

Summary!B8:

=IF(NOT(AND(ISNUMBER($B$2),ISNUMBER($B$3))),"CHECK YEAR",SUMIFS('Tax Log'!$E$2:$E,'Tax Log'!$C$2:$C,"Estimated payment",'Tax Log'!$A$2:$A,">="&$B$2,'Tax Log'!$A$2:$A,"<"&$B$3,'Tax Log'!$L$2:$L,"OK"))

Summary!B9:

=IF(NOT(AND(ISNUMBER($B$2),ISNUMBER($B$3))),"CHECK YEAR",SUMIFS('Tax Log'!$F$2:$F,'Tax Log'!$C$2:$C,"Mileage",'Tax Log'!$I$2:$I,"Candidate",'Tax Log'!$A$2:$A,">="&$B$2,'Tax Log'!$A$2:$A,"<"&$B$3,'Tax Log'!$L$2:$L,"OK"))

Summary!B10:

=COUNTIFS('Tax Log'!$L$2:$L,"<>",'Tax Log'!$L$2:$L,"<>OK")

Apply these formats:

• Summary!B5:B8: USD currency with 2 decimal places
• Summary!B9: custom number format 0.0 "km"
• Summary!B10: whole number

Add these controls in D1:E4:

Enter this formula in E2:

=$B$10

In E3, enter:

=COUNTIFS('Tax Log'!$L$2:$L,"<>",'Tax Log'!$J$2:$J,"")

In E4, enter:

=$B$46

Format E2:E3 as whole numbers.

For the unchanged fixture, the annual dashboard should show:

Candidate is a user classification for review. It doesn’t make an expense or trip legally deductible.

Enter these monthly headers in Summary!A11:G11:

Add these formulas in row 12.

A12:

=IF(ISNUMBER($B$2),DATE($B$1,ROWS($A$12:A12),1),"CHECK YEAR")

B12:

=IF(ISNUMBER(A12),EDATE(A12,1),"CHECK YEAR")

C12:

=IF(NOT(AND(ISNUMBER($A12),ISNUMBER($B12))),"CHECK YEAR",SUMIFS('Tax Log'!$E$2:$E,'Tax Log'!$C$2:$C,"Income",'Tax Log'!$A$2:$A,">="&$A12,'Tax Log'!$A$2:$A,"<"&$B12,'Tax Log'!$L$2:$L,"OK"))

D12:

=IF(NOT(AND(ISNUMBER($A12),ISNUMBER($B12))),"CHECK YEAR",SUMIFS('Tax Log'!$E$2:$E,'Tax Log'!$C$2:$C,"Expense",'Tax Log'!$A$2:$A,">="&$A12,'Tax Log'!$A$2:$A,"<"&$B12,'Tax Log'!$L$2:$L,"OK"))

E12:

=IF(NOT(AND(ISNUMBER($A12),ISNUMBER($B12))),"CHECK YEAR",SUMIFS('Tax Log'!$E$2:$E,'Tax Log'!$C$2:$C,"Expense",'Tax Log'!$I$2:$I,"Candidate",'Tax Log'!$A$2:$A,">="&$A12,'Tax Log'!$A$2:$A,"<"&$B12,'Tax Log'!$L$2:$L,"OK"))

F12:

=IF(NOT(AND(ISNUMBER($A12),ISNUMBER($B12))),"CHECK YEAR",SUMIFS('Tax Log'!$E$2:$E,'Tax Log'!$C$2:$C,"Estimated payment",'Tax Log'!$A$2:$A,">="&$A12,'Tax Log'!$A$2:$A,"<"&$B12,'Tax Log'!$L$2:$L,"OK"))

G12:

=IF(NOT(AND(ISNUMBER($A12),ISNUMBER($B12))),"CHECK YEAR",SUMIFS('Tax Log'!$F$2:$F,'Tax Log'!$C$2:$C,"Mileage",'Tax Log'!$I$2:$I,"Candidate",'Tax Log'!$A$2:$A,">="&$A12,'Tax Log'!$A$2:$A,"<"&$B12,'Tax Log'!$L$2:$L,"OK"))

Fill A12:G12 through row 23, then apply these formats:

• Summary!A12:B23: yyyy-mm-dd
• Summary!C12:F23: USD currency with 2 decimal places
• Summary!G12:G23: custom number format 0.0 "km"

Expected results include:

For quarterly reporting, enter the same headers in A26:G26, followed by these formulas.

A27:

=IF(ISNUMBER($B$2),DATE($B$1,1+3*(ROWS($A$27:A27)-1),1),"CHECK YEAR")

B27:

=IF(ISNUMBER(A27),EDATE(A27,3),"CHECK YEAR")

C27:

=IF(NOT(AND(ISNUMBER($A27),ISNUMBER($B27))),"CHECK YEAR",SUMIFS('Tax Log'!$E$2:$E,'Tax Log'!$C$2:$C,"Income",'Tax Log'!$A$2:$A,">="&$A27,'Tax Log'!$A$2:$A,"<"&$B27,'Tax Log'!$L$2:$L,"OK"))

D27:

=IF(NOT(AND(ISNUMBER($A27),ISNUMBER($B27))),"CHECK YEAR",SUMIFS('Tax Log'!$E$2:$E,'Tax Log'!$C$2:$C,"Expense",'Tax Log'!$A$2:$A,">="&$A27,'Tax Log'!$A$2:$A,"<"&$B27,'Tax Log'!$L$2:$L,"OK"))

E27:

=IF(NOT(AND(ISNUMBER($A27),ISNUMBER($B27))),"CHECK YEAR",SUMIFS('Tax Log'!$E$2:$E,'Tax Log'!$C$2:$C,"Expense",'Tax Log'!$I$2:$I,"Candidate",'Tax Log'!$A$2:$A,">="&$A27,'Tax Log'!$A$2:$A,"<"&$B27,'Tax Log'!$L$2:$L,"OK"))

F27:

=IF(NOT(AND(ISNUMBER($A27),ISNUMBER($B27))),"CHECK YEAR",SUMIFS('Tax Log'!$E$2:$E,'Tax Log'!$C$2:$C,"Estimated payment",'Tax Log'!$A$2:$A,">="&$A27,'Tax Log'!$A$2:$A,"<"&$B27,'Tax Log'!$L$2:$L,"OK"))

G27:

=IF(NOT(AND(ISNUMBER($A27),ISNUMBER($B27))),"CHECK YEAR",SUMIFS('Tax Log'!$F$2:$F,'Tax Log'!$C$2:$C,"Mileage",'Tax Log'!$I$2:$I,"Candidate",'Tax Log'!$A$2:$A,">="&$A27,'Tax Log'!$A$2:$A,"<"&$B27,'Tax Log'!$L$2:$L,"OK"))

Fill A27:G27 through row 30 and apply these formats:

• Summary!A27:B30: yyyy-mm-dd
• Summary!C27:F30: USD currency with 2 decimal places
• Summary!G27:G30: custom number format 0.0 "km"

The expected quarterly view is:

The distance formulas include only Mileage records tagged Candidate. Valid Mileage rows tagged Unreviewed or Not claimed aren’t included in these summary or reconciliation measures.

For more complex groupings, see How to Use QUERY in Google Sheets for Finance.

The reconciliation covers quarterly income, expenses, estimated payments, and Candidate mileage. It doesn’t reconcile all mileage records or Candidate expenses as a separate metric.

For the illustrative fixture, the source-control schedule is:

In live use, derive source totals from actual statements, invoices, receipts, confirmations, and mileage records. Don’t copy totals from Tax Log, as that would make the comparison circular.

Create the Q1 and Q2 reconciliation table in Summary!A33:G37:

Use these sheet-total formulas:

Then add the variance formulas.

In D34, fill through D36:

=IF(AND(ISNUMBER(B34),ISNUMBER(C34)),ROUND(B34-C34,2),"CHECK INPUTS")

In D37:

=IF(AND(ISNUMBER(B37),ISNUMBER(C37)),ROUND(B37-C37,1),"CHECK INPUTS")

In G34, fill through G36:

=IF(AND(ISNUMBER(E34),ISNUMBER(F34)),ROUND(E34-F34,2),"CHECK INPUTS")

In G37:

=IF(AND(ISNUMBER(E37),ISNUMBER(F37)),ROUND(E37-F37,1),"CHECK INPUTS")

Now create the Q3 and Q4 reconciliation table in Summary!A40:G44:

Use these sheet-total formulas:

Add the remaining variance formulas.

In D41, fill through D43:

=IF(AND(ISNUMBER(B41),ISNUMBER(C41)),ROUND(B41-C41,2),"CHECK INPUTS")

In D44:

=IF(AND(ISNUMBER(B44),ISNUMBER(C44)),ROUND(B44-C44,1),"CHECK INPUTS")

In G41, fill through G43:

=IF(AND(ISNUMBER(E41),ISNUMBER(F41)),ROUND(E41-F41,2),"CHECK INPUTS")

In G44:

=IF(AND(ISNUMBER(E44),ISNUMBER(F44)),ROUND(E44-F44,1),"CHECK INPUTS")

Apply these formats so source inputs, sheet totals, and variances use the same displayed precision:

• Summary!B34:D36, E34:G36, B41:D43, and E41:G43: USD currency with 2 decimal places
• Summary!B37:D37, E37:G37, B44:D44, and E44:G44: custom number format 0.0 "km"

Enter Quarterly reconciliation status in A46 and this formula in B46:

=IF(COUNT(D34:D37,G34:G37,D41:D44,G41:G44)<>16,"CHECK INPUTS",IF(SUM(ARRAYFORMULA(ABS(D34:D37)))+SUM(ARRAYFORMULA(ABS(G34:G37)))+SUM(ARRAYFORMULA(ABS(D41:D44)))+SUM(ARRAYFORMULA(ABS(G41:G44)))=0,"TIED","INVESTIGATE"))

The expected result is:

• Every variance in D34:D37, G34:G37, D41:D44, and G41:G44 equals 0.
• B46 displays TIED.
• The dashboard control in E4 displays TIED.

TIED means the source controls and sheet totals agree at the reconciliation’s reporting precision: 2 decimal places for USD and 1 decimal place for kilometers. It doesn’t prove equality at a finer precision. For example, $80.500 and $80.504 produce a displayed variance of $0.00.

Enter source controls and transaction amounts at the precision required by your accounting policy. Also remember that a zero reported variance doesn’t prove a source document is genuine, complete, or correctly classified for tax purposes.

Protect the prepared status-formula range:

1. Select Tax Log!L2:L1000.
2. Choose Data > Protect sheets and ranges.
3. Restrict editing to the workbook owner or designated finance editors.

Protect the Summary sheet while leaving these input ranges editable:

• B1 for the tax year
• B34:B37 for Q1 source-control totals
• E34:E37 for Q2 source-control totals
• B41:B44 for Q3 source-control totals
• E41:E44 for Q4 source-control totals

Protected cells should include the period formulas, annual totals, monthly and quarterly summaries, reconciliation formulas, and validation reference lists.

Apply conditional formatting to Summary!B46 and Summary!E4:

• Green when the text is TIED
• Red when the text is INVESTIGATE or CHECK INPUTS

To create a printable one-page control dashboard:

1. Keep the visible dashboard in Summary!A1:G46.
2. Hide columns I:K if you don’t want the validation lists printed.
3. Choose File > Print.
4. Select the current sheet.
5. Set the scale to Fit to page.
6. Check the print preview before printing or saving.

The expected dashboard state for the unchanged fixture is:

These are reproducible cell-level views, not screenshots or results from an attached workbook.

ModelMonkey is an AI assistant for spreadsheet work in Google Sheets and Excel. In Google Sheets, it can use the active spreadsheet, sheet, and selected-range context to read tables, search content, analyze data, help with formulas, organize cells, and create or adjust charts.

In a test copy of the workbook, seed one error:

1. Replace Tax Log!E3, currently 80.50, with the text eighty.
2. If validation rejects the entry, paste it into an unvalidated test copy to simulate imported data.
3. Confirm that Tax Log!L3 changes to INVALID AMOUNT.
4. Confirm that Summary!B10 and Summary!E2 change from 0 to 1.
5. Confirm that annual and Q1 expense totals become $0.00 because the summary formulas include only rows whose status is OK.
6. Confirm that Summary!D35, the Q1 expense variance, becomes $80.50.
7. Confirm that Summary!B46 and Summary!E4 change to INVESTIGATE.

Then ask ModelMonkey:

Inspect Tax Log!A1:L1000 and Summary!A1:G46 without changing cells.

Find every populated Tax Log row whose Calculation status is not OK.
For each one, report the row number, source value causing the problem,
status, and effect on the annual or quarterly summaries.

Review all quarterly reconciliation variances and explain why the
quarterly reconciliation status is not TIED.

The expected inspection result for the seeded fixture is:

Tax Log row 3:
- Amount contains the text "eighty" instead of a number.
- Calculation status is INVALID AMOUNT.
- The row is excluded from expense and Candidate-expense summaries.
- Q1 source expenses are $80.50 while the Q1 sheet total is $0.00.
- The resulting $80.50 variance changes the quarterly reconciliation
  status to INVESTIGATE.

This is the expected result of the proposed inspection, not a claim that the test was run here. Check ModelMonkey’s findings against the formulas and source record. When the workflow presents a preview for proposed spreadsheet changes, inspect it before proceeding.

Once the check is complete, restore Tax Log!E3 to 80.50. L3 should return OK, Q1 expenses should return to $80.50, and the reconciliation status should return to TIED.

For related spreadsheet-assistant examples, see Google Sheets Artificial Intelligence for FP&A. To perform this specific review, install ModelMonkey and ask it to inspect Tax Log!A1:L1000 together with Summary!A1:G46.

Run each check in a copy, then restore the original fixture. The results below are expected outcomes, not claims that the tests were run.

Many negative tests intentionally violate the validation rules. These include invalid years, blank or invalid required amounts, invalid distance, unsupported types or categories, invalid dates, and invalid Tax Year values.

For each test, use an unvalidated test copy or a paste/import action that bypasses validation. Otherwise, Google Sheets may reject the input before the status formula can evaluate it.

The duplicate test shows why reconciliation remains necessary. Review repeated dates, descriptions, amounts, document references, refunds, transfers, and opening balances instead of assuming the formulas will identify them.

For each close or tax-review cycle:

1. Compare income, expenses, and estimated-payment records with bank or card statements and other source records.
2. Compare Candidate mileage entries with the independently derived Candidate subset of the vehicle or travel log.
3. Review Unreviewed and Not claimed mileage separately because the dashboard doesn’t reconcile those records.
4. Enter independently derived source totals for all 4 quarters.
5. Investigate every nonzero quarterly reconciliation variance.
6. Filter column L for statuses other than OK.
7. Confirm that every populated row has the status formula in column L.
8. Resolve missing source references and confirm that each reference still opens the intended record.
9. Review category and deduction tags with the person responsible for tax treatment.
10. Record the review date or unresolved explanation in column K.
11. Confirm that Summary!B46 displays TIED at the stated reporting precision before distributing the dashboard.

Keep original receipts, statements, confirmations, and mileage evidence outside the spreadsheet according to your organization’s document-retention and access policies.