All Collections
Categories
Troubleshooting
Troubleshooting the Category dropdown in the Transactions sheet
Troubleshooting the Category dropdown in the Transactions sheet

Learn why your category dropdown might be broken, missing, or not showing all categories.

Heather Phillips avatar
Written by Heather Phillips
Updated this week

If the Category dropdown menu on your Transactions sheet is missing, broken, or not showing all the categories on your Categories sheet you will need to correct the data validation for affected cells.

We include steps for how to fix this issue and tips for how to avoid it in the future.

In Google Sheets

  1. Review the location of your Category column in the Transactions sheet AND Categories sheet. Take note of the column letter that corresponds to the Category column in both sheets.

    1. The default locations in the Foundation Template in the Transactions it's column D and in Categories it's column A.

    2. You'll need to adjust the range references below to reflect the location of your Category column if they are different than the defaults. 

  2. In your Transactions sheet, select the entire Category column by clicking the column header letter (click Column D)

  3. Open the Data Menu

  4. Choose Data Validation and select the Data validation rule

  5. In the field below Apply to range confirm it's set to Transactions!D2:D.

    1. Using D2:D, instead of D2:D355 (or some other row number) will ensure that all new rows added get the validation too.

    2. Make sure it starts with D2 so the header row is not included.

    3. If your Category column in the Transactions sheet is not in column D adjust the range to use the appropriate column letter.

  6. Set the criteria.

    1. Make sure "Dropdown (from a range)" is selected

    2. Then enter =Categories!$A$2:$A$201 into the field below the selection.

    3. If your Category column in the Categories sheet is not in column A change the formula to use the appropriate column letter.

    4. If your Categories sheet does not have 201 rows, modify the formula to match your last row number in the Categories sheet.

  7. Review Advanced options

    1. We recommend that you have it set to "Reject Input"

    2. We recommend the "arrow" style dropdown.

How to Avoid this Issue

  1. Do not insert new rows at the top or bottom of the Categories list - use the existing rows and add new categories to the bottom of the list in blank cells or overwrite example categories you're not using.

  2. If you are pasting manually added transactions into your Transactions sheet, right click and choose Paste Special > Paste Values Only to ensure the data validation gets added to new rows. 

Tip: You can sort the Categories sheet by the Category column (or any other column) by hovering over the column letter header and clicking on the small downward triangle then choose Sort Z>A or A>Z.

In Microsoft Excel

ℹ️ These steps are for the Tiller Foundation Template for Microsoft Excel. If you are still using the Tiller Money Tracker template (it has a "Tracker" tab along the bottom and only a Yearly Budget) these steps won't work and we recommend you migrate to the Tiller Foundation Template.

  1. Starting with row 2 select all the cells in the Category column to the bottom of the Excel table (look for the alternating shading to stop to see where the table ends).

  2. Choose the Data ribbon

  3. Choose Data validation

  4. Make sure the Allow: option has List selected

  5. In the source input =INDIRECT("Categories[Category]")

  6. Leave "Ignore blank and "In-cell dropdown" checked

  7. Click the "Apply these changes to all other cells with the same settings" option

  8. Click "OK"

How to Avoid this Issue

  1. Do not insert new rows at the top or bottom of the Categories list - use the existing rows and add new categories to the bottom of the list in blank cells or overwrite example categories you're not using.

  2. Use the next available/empty existing row in the Categories sheet to make sure that the Excel Table expands properly (do not skip or leave blank rows at the bottom of the Categories sheet

Did this answer your question?