🏆 Paycheck Deductions Generator - Excel

Microsoft Excel Show & Tell
1

Overview

It provides an easy way to generate transactions based on the deductions that are taken from your paycheck. These transactions, when properly categorized, can then give you better insights on where your money is going. I have previously made a Google Sheets version available here.

PaycheckDeductionsGenerator1
PaycheckDeductionsGenerator12229×598 63.1 KB

How did you come up with the idea for your workflow?
Many, many, many iterations of trying to simplify and clarify the process I started some time ago.

Installation

  1. Download the workbook containing the Paycheck Deductions Generator to your local hard drive.
  2. Follow these instructions to copy the downloaded template into your Excel workbook and to connect the formula references to your local workbook data.

At this point your new template should be functional and linked to your local workbook’s data.

Thanks to @randy for these steps via @yossiea . Docs: Account Filter (Excel) - Microsoft Excel / Show & Tell - Tiller Community (tillerhq.com)

Configuration

Section 1 is “Configure Paycheck Deductions”. This is where you enter the detailed information from your paycheck stub/statement.

  • Using your latest paycheck stub/statement, enter the date you got paid in F4. Note that columns C, D and E are optional. They aren’t used anywhere in the generation of transactions, but you may find them useful for keeping track of your past paychecks. As an example, my last paycheck had ‘JAN B’, ‘1/2/2022’ and ‘1/15/2022’ in those columns, and ‘1/28/2022’ in column F.
  • Put your Gross Pay (the large amount before they started taking out deductions) in column G. Column H should now show the negative value of your Gross Pay. As you add deductions, the PayNet value will decrease, showing you what remains.
  • Columns I through L hold the standard deductions that most people have taken from their Gross Pay. Enter them as negative values, since that’s how we want them to show up on our transaction sheet. If any of them don’t apply to you, leave it blank.
  • The D1-D20 columns are for other deductions that are probably specific to your company. Type in each deduction, again as a negative number. If you change the labels from ‘D1’ to something brief but descriptive, it can help down the road when you add more transactions.
  • If you do enter more than 1 paycheck, keep the oldest at the top, and add newer ones below, so the list is sorted from oldest to newest. Some features won’t work right unless they are sorted this way.

Once you’ve finished entering deductions, check the PayNet total and compare it to the Net Pay total on your paycheck (the amount you end up getting paid). If they don’t match, you’re missing or mistyped a deduction, go back and correct! Once they do match, you can copy this row, and paste it down to fill as many of the rows below as you like. Change the PayDate to the dates of your upcoming checks. If your Gross Pay will be different on any upcoming checks, adjust it now. Also, if any of your deductions will be different (eg. I’m paid bi-weekly, and twice a year there are three pay periods in a month. They don’t take some of my deductibles out of that third pay period). You can also add any past paychecks if you like as a way of keeping a record. I have all of mine back through January of 2019. Make sure they are sorted by PayDate to the formulas are able to find the most current check based on the current date. Conditional formatting will highlight the row of the most recent paycheck.

Now that you’ve completed this section, click the ‘-’ above column AG and it will close.

Section 2 is “Configure Individual Transactions”. This is where you determine the ‘Description’, ‘Category’ and optionally, ‘Tags’ for each deduction you’ll have. Click on the ‘+’ above column AM and it will expand showing the settings that need to be configured. You’ll see a list of columns that match those you just entered your deductions into. Column AI should show the totals for the information you added for the most recent paycheck.

  • Determine what you’d like to appear as the Description of each of your deduction transactions.
  • Determine what category you’d like each of your transactions to be classified as. Click the drop-down menu in the Category cell to see a list of your existing Categories. You’re Gross Pay transaction should be categorized as an ‘Income’ type category. Note that the Net Pay transaction should be categorized as a ‘Transfer’ (hidden) type category. More on that later. The rest of the transactions should be categorized as ‘Expense’ type categories.
  • Optionally, if you have a ‘Tags’ column on your Transactions sheet, you can enter any tags you’d like associated with the transaction.

Here’s an example of what I have in this section:

Description                                           Category		        Tags
ACME Paycheck - Gross                                 Wages			        Paycheck
ACME Paycheck - Net				                      Transfer Out		    Paycheck
ACME Paycheck - Fed OASDI/EE (Social Security)	      SocialSecurity		Paycheck
ACME Paycheck - Fed MED/EE (Medicare)		          Medicare		        Paycheck
ACME Paycheck - Fed Withholding (Federal Tax)	      FederalTax		    Paycheck
ACME Paycheck - WI Withholding (State Tax)	          StateTax		        Paycheck
ACME Paycheck - Bobs Health Insurance		          HealthInsurance	    Paycheck
ACME Paycheck - Bobs Life Insurance		              HealthInsurance	    Paycheck
ACME Paycheck - ACME Retirement System	              Pension		        Paycheck
ACME Paycheck - 401K				                  401K			        Paycheck

Once you’ve completed these settings, click the ‘-’ above column AM and it will close.

Section 3 is “Configure All Transactions”. This is where you determine what you want to call the ‘Account’, ‘Account #’ and ‘Institution’ that will be listed for all your paycheck transactions. None of these are necessary, and you don’t need an actual Tiller Account that matches this, but having them filled in could be a useful way to sort or filter your paycheck transactions. Click on the ‘+’ above column AP and it will expand showing the settings that need to be configured.

  • Leave “Date Override” blank for now, we’ll address that later
  • I named my Account “ACME Paycheck”, note that you don’t need a matching Tiller Account. You can also leave this blank if you choose, but having it filled in may make it easier to sort or filter your Transactions sheet if you’re looking for paycheck info.
  • I left the Account # blank. You can enter whatever you’d like.
  • I named my Institution “ACME”. Again, this can be left blank with no repercussions.
  • There is one other column you can configure called ‘Note’. This column doesn’t exist in the default template, but you can add it if you want the ability to add notes to transactions. If you don’t add the Notes column to your transactions sheet, you can ignore the configuration here. I have a Notes column, and I configured mine to add “Copied from Paycheck Deduction Transaction Generator”, which is filled in by default (but of course you can change it to anything you’d like).

You may not have all the columns listed here, that’s OK, they’ll just be skipped over. Once you’re done configuring these options, click the ‘-’ above column AP, and it will close.

Section 4 is “Paycheck Deduction Transactions”. You’ve done everything you need to, time to reveal your Paycheck Deduction Transactions! Click the ‘+’ above column CA and it will expand. If everything worked correctly, you should see a list that included all the column headings from your Transactions sheet. There should be the same number of transactions as there were in section 2 when you provided descriptions for each transaction. Note that if any deduction for this check had a total of $0, it is filtered out. Each column that we provided information for should be filled in, many others are left blank either because they don’t apply, or because they will automatically be calculated (eg. Group, Category Type, etc).

Usage

I usually add paycheck transactions when I notice the transaction for my paycheck deposit in my checking account, and I have the paycheck stub/statement. Open Section 1 and confirm that the totals for your new paycheck match those you entered in the chart. Correct any differences. Then close that section and open Section 4.

Note the three yellow boxes on the left side of the section. The first box tells you how many transactions you have. Note this number, and go to your Transactions sheet. Find your most recent paycheck deposit transaction, click on the row number and drag down, selecting the number of transactions you need to add. With the rows selected, right-click on the selection and choose “Insert XX Rows Above”. New, blank rows will be added. Now go back to the “Paychecks” sheet and note the second yellow box, telling you to select the cells from “$AS$4” thru the last cell of your transactions (will vary by number of columns you have, and number of deductions). Click on cell “AS5” and drag over to the last cell in the lower right corner of your transactions. Once selected, choose “Copy” from the “Edit” menu. Now switch to your “Transactions” sheet, and click in the “Date” column of the first blank row you created. From the Edit menu, choose “Paste Special” and then “Values Only”, and your paycheck transactions should appear! The reason we don’t do a normal paste is that formatting from the Paychecks sheet probably won’t match that of the Transactions sheet, and can cause problems, especially if you use Conditional Formatting.

IMPORTANT!:
Normally you wouldn’t need to include a transaction for ‘Net Pay’, since it’s just what’s leftover after the deductions are taken from your ‘Gross Pay’. However, now that you’re adding a “Gross Pay” transaction, and are presumably categorizing it as income, you have a problem. You probably already have the bank deposit categorized as income. With both now being called income, your budgets/reports are going to show you earned way more than you really did. To fix this, change your paycheck deposit to also be a ‘Transfer’ category that is hidden. The “Net Pay” deduction you created will offset the bank deposit (that’s why it needs to be negative), so only your ‘Gross Pay’ shows up in budgets and reports, as do the deductions themselves. Think of the ‘Net Pay’ deduction as the transfer out of the ‘ACME’ account, and your paycheck deposit as the ‘transfer in’ to your checking account (I actually use ‘Transfer In’ and ‘Transfer Out’ as categories in the ‘Transfer’ group to help make more obvious how money is moving around)…

  • Notes

  • If you’d rather not keep a list of every paycheck, you can optionally just fill in the paycheck information in section 1 and not include a date. The generator will use today’s date instead, which you could always change as necessary once the transactions have been pasted into the Transactions sheet. Each time you get a paycheck, you can just update the numbers as needed, and copy the generated transactions.

  • If you’re just starting out, and have entered information for past paychecks in Section 1 and you’d like to generate transactions for them, open Section 3 and use the “Date Override” dropdown to choose which PayDate you’d like to generate a transaction for. Once you’re done generating transactions for past paychecks, delete the date in that cell to get back to the default setting, which is to use the most recent PayDate.

  • Dates will likely appear as 5 digit numbers. This is normal, once pasted into the Transactions sheet, they will appear using your normal date formatting. You can add date formatting to appropriate columns in the Paycheck Deductions Generator sheet. It is not done already everyone’s column layouts can be different.

Permissions

Is it ok for others to copy, use, and modify your workflow?
Yes, please, and please provide feedback on any issues you encounter and/or suggestions on how to make improvements!

2

Looks good! Two quick things, some of the date columns weren’t formatted as dates and reverted to the numbers, so I changed the format and it looks good.
The data validation for the dropdown for date override needs to be changed to paychecklist.

3

I’ll have to update the documentation about the formatting. Since the dates and amounts will be in different columns for different people, I can’t pre-format them. As it turns out though, if you copy the generated transactions, and paste them into your Transaction tab without formatting (as I recommend), they will take on the formatting of that sheet, so in the end it shouldn’t matter. It’s more about if you want it to look nice on the Paycheck sheet, which each person will need to do themselves if they like.
Thanks for catching the Date Override. I changed the name of the table, and forgot that I used an Indirect, so it didn’t automatically change. Just updated. So many details!

4

I found something else that I think can easily be fixed. I work in several states so sometimes I’ll have a state tax deduction. This paycheck, I noticed that one row was missing from Transactions.
In Paycheck, CB had a blank row. Then, in AQ5 it only counts if <>0, so that’s where the one row went. I think if you exclude the 0 entries from AQ5 then everything should match up, or at the very least, sort the results by amount so the 0 will fall off anyway.

5

Another great migration and addition to the Excel community-template portfolio, @jpfieber! Sorry for the slow response here but…

We are excited to award another $200 spot-award prize within the context of the 2022 Microsoft Excel Builders Challenge. :clap:

Thanks for your continued efforts. Keep up the good work!

1 Like
6

Thanks to @carlpetrovsky , I located a bug that prevented the “Date Override” feature from working. I’ve updated the shared template to include the fix and added a version number, 1.2.1. You can delete your old template and start fresh, you can copy the range AI4:AI29 from the shared template and overwrite the same in your current template, or if you don’t use the Date Override feature, you can ignore!

7

Thanks again to @carlpetrovsky , I updated the shared template to behave better if you want to have more than one copy of the sheet (eg. one for you, another for spouse or one for each job). In Excel it seems that sheets with named ranges don’t work well if you duplicate that sheet, so the update removes named ranges from the formulas in favor of traditional column/row references. The version is now 1.2.2.

8

This is fantastic! Thanks for making it. Question: With my current paycheck, I’ve been manually splitting it into salary, commissions (when I get any), and reimbursement (for travel when I have any), each with its own category. Do you think I can modify this to support that, and do you have any guidance on how I would do that?

9

Seems it should do what you need as is. It’s designed to allow you to specify all the deductions, and assign them each to whatever category you like.

10

Yep. I figured that out after posting the question that I can get it to work. Thanks!

1 Like