What is the goal of your workflow? What problem does it solve, or how does it help you?
Though I see my net paycheck deposit as a transaction that Tiller pulls in from my credit union, I wanted the gross amount to show up in my balances, and to have all the deductions categorized as well (see how much I’m paying for insurance, taxes, etc). I started by copy/pasting one months transactions to the next, but wanted something a little nicer. There’s no way for me to automate that, but I found a way to simplify it so for each paycheck I could include these transactions.
How did you come up with the idea for your workflow?
I wanted a way to simplify the process of entering the paycheck information every time I got a check.
Please describe your workflow. What are the sheets? Does it use any custom scripts or formulas?
Here’s the setup and the process I use:
The Setup
- Paychecks Sheet - Create a new sheet called “Paychecks” with the first four columns holding the “Description”, “Category”, “Amount” and “Account”.
- Description Column - Using past paycheck stubs, list all the deductions in the Description column, with any that are periodic at the end of the list so you can more easily not select them to copy to the Transactions sheet if they are blank.
- Category Column - Add a category for each deduction, using “Data Validation” to include the same drop down menu that’s used in the Transactions sheet. Note that I have AutoCat set my actual paycheck bank deposit to “Transfer”, and then set the “Net” amount in the deductions so they zero out. This leaves the actual Net amount as my income.
- Paycheck Table - Since the Amount column can very each month (I’m currently paid monthly), I found it useful to create a table to enter the paycheck amounts, and the Amount column is automatically populated (and changed to a negative so it is seen as an expense in the transactions). This allows you to keep track of all your paychecks, provides a sum of the totals you’ve entered that can be compared against the Gross total to be sure nothing was missed, and always shows the most recent checks totals in the Amount column, so you can easily copy all four columns together at once.
- Amount Column - The Amount column simply copies all the numbers from the right-most column, making all the deductions negative while leaving the gross amount positive.
- Account Column - Though not necessary, including an Account name for these transactions can be useful for filtering, so prepopulating them here makes it easy to copy/paste along with the other data.
The Process
- After receiving a paycheck, copy the previous paychecks column and paste it in the next column to the right. Change the date, and then modify any of the deductions to properly reflect the current paycheck (enter them all as positive, the Amount column will properly add negatives where necessary).
- Once entered, confirm that the Gross amount is the same as that listed on the paycheck to be sure nothing was missed (If there’s a new deduction it may need to be added to the list).
- Copy the range that includes the first five columns of all the line items that aren’t zero (if there are items with a zero total that aren’t at the end you’ll just need to delete that row after pasting) and note the number of rows that will be needed to hold the data
- Go to the Transactions sheet and insert the number of necessary rows to hold the paycheck transactions
- Paste the data into the new rows starting in column A. **Note you need to “Paste Special/Paste Values Only” so the amounts get pasted in, not the formulas that grabbed the amounts.
- The date will be inserted into column A based on the date you entered in the Paycheck Table. Since I’m paid monthly, I like to put each Paycheck on the first of the month, even though the actual deposit sometimes may happen a day or two before or after (which can screw up budgeting when two checks show up in one month, and then none in another).
- The Group column should automatically populate based on the Category.
- If you want other columns filled in, like “Full Description”, you can manually enter them, or you can expand your Paycheck sheet to include these extra columns. You should avoid entering anything into the ‘Group’ or ‘Type’ columns though or you’ll break the formulas that auto-fill these values for all the other rows.
Anything else you’d like people to know?
I hope others find this useful and contribute ideas on how to make it even better!
Is it ok for others to copy, use, and modify your workflow?
Yes, I’m hoping others will use it and find ways to improve it!
If you said yes above, please make a copy of your workflow and share the copy’s URL:
Here’s a spreadsheet with a sample of the Paycheck sheet using dummy data, and a Categories sheet so the Categories column works properly:
