Add Manual Transaction in Excel 2022


Always make a backup of your essential files, like a budget. Try the install on the copy and if it works as advertised, then go live with the update or revert if there is an issue.

Environment: I am running Windows 10 with O365 installed and using the Desktop Excel.

I like my budget to track manual transactions and instead of entering them manually into the Transactions sheet. I created an Excel sheet for automating adding manual transactions to the Transactions sheet, like we can already do in Google Sheets. Spoiler: This is a key feature needed to track the current balances of your accounts while waiting for the transactions to clear. (Coming soon to Excel).

Note to Tiller: Do not hate me, but this uses an Excel add-in (old school), which is another name for a Macro, but it is not embedded in your Tiller budget workbook so you can keep the Tiller Feeds workbooks as a Macro free Excel extension.

How did you come up with the idea for your workflow?

I wanted it to make my life easier and since Excel is modular, why not. Plus, a lot of people are asking for it in the community chat. Ok, challenges: Tiller, from what I understand, want a community-based budget spreadsheet that the community can edit, share, and create new functionality while they manage the core sheets and connectivity to the financial world. (I am glad they can do that as it is extremely helpful). Additionally, the spreadsheet should be dynamic and use the built-in formulas to add functionality to the spreadsheet. Challenge 1: The Excel template is saved in .XLSX file extension which by design disables any VBA (Visual Basic) Macros. So not helpful to provide a Macro to embed in Tillers provided template as everyone would have to save the file as an .XLSM (which allows Marcos). Challenge 2. No scripting, so external programs are a no go.
Solution: Create a Macro through VBA and save it as an Add-In with the file extension .XLAM which provides the functionality to the Tiller template without changing the template, so it maintains version control.


Here is the installation video if you get stuck.

YouTube installation directions

Installation: (Probably for moderately experienced Excel users but should be straightforward if you follow the directions).

Go to my One Drive Share: Mr. Signals One Drive Share

  • Right Click on each of the Excel files and choose Download, or download the Zip file.
  • Save them to the file folder of your choice.
  1. Open your linked Tiller Feeds workbook in Excel (the file that already contains your transactions).

  2. Open the workbook you just downloaded in Excel (the one containing the Add Manual Transaction template).

  3. Right-click on the Add Manual Transaction sheet in the workbook you just downloaded and click “Move or Copy”.

  4. In the “To book” dropdown, select your linked Tiller Feeds workbook.

  5. Leave the “Create a copy” checkbox unchecked.

  6. In the “Before sheet” dropdown, select “(move to end)”, then click OK. If you receive an alert about a name conflict, just click Yes.

  7. The new worksheet should appear in your Tiller Feeds workbook with the name “Add Manual Transaction”.

  8. Navigate to “Data / Edit Links…” in the menu.

  9. Select the link to the downloaded workbook in the box.

  10. Click the “Change Source…” button.

  11. Navigate to the active workbook (i.e., select your personal spreadsheet).

  12. Navigate to the Add Manual Transaction sheet to Setup Data validation Rules (The data validation rules do not copy so they must be created.)

  13. Select the Data Menu in the toolbar.
    a. Select the Type, Account, or Category input Cell.
    b. Select the Data Validation button.

    image OR image

    c.Select List from the dropdown menu Allow:
    d.Insert the Source from the Rules below.

image image image

                          Images of the Data Validation

e. Repeat for the other two Input Cells.

  1. Rules:
    a. Transaction Type: Deposit, Withdrawal
    b. Account: =Accounts!$H$2:$H$80
    c. Category: =Categories!$A$2:$A$55
  2. Probably a good time to save your workbook.

At this point, your Add Manual Transaction Sheet should be linked to your local workbook’s data.

Setup (The Magic)

  1. Install the download Excel Add-In: (This will install the Add-In to your Excel environment).
    |a.|Go to the File menu in Excel.
    |b.|Select Options in the lower left corner.
    |c.|Select Add-ins on the left-hand side.
    |d.|Select Excel Add-ins in the dropdown box Manage, and then select Go button.
    |e.|Choose the Browse Button.
    |f.|Browse to the location you saved the downloaded Add-In.
    |g.|Select the file and click the Open button.
    |h.|The new Add-In should show up in the Add-Ins window.
    |i.|Check the box for the new Add-in if not already and hit the Ok button.

  2. Add a Ribbon button so that you can run the Add-in.
    a. Right click on the Ribbon bar up top and select Customize the Ribbon.
    b. bIn the Choose commands from dropdown select Macros.
    c. Select Enter_Manual_Transaction
    d. Select the Data Tab and click new group.
    e. Select the group you just created and rename. (Pick a name and picture that you want)
    f. Then click the Add >> button.
    g. Finally, click the Ok button and you should see your new button on the Data tab.


Enter the transaction details in the correct cells. Select the button you created in the ribbon bar. The transaction should be inserted at the top of your Transactions sheet, the Add Manual Transaction cells should be cleared, and you are ready to insert another transaction if desired.


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


Anything else you’d like people to know?
Environment Windows 10 with O365 using Desktop Excel




First bug (Thank you, Microsoft): Shouldn’t be a problem, but here it is.

  1. When the Sheet was copied over, Excel updated three of the formulas.
  • Cell T2, U2, and AA2: ( You will need to unhide the columns to do this)
    – Remove ‘your path\Manual Transaction Shared Template.xlsx’! Before BalanceHistory.
    Example: As everyone’s path will be different

Before formula
=VLOOKUP(S2, UNIQUE(CHOOSE({1,2,3,4},‘D:\Budget\Manual Transaction Shared Template.xlsx’!BalanceHistory[Account],‘D:\Budget\Manual Transaction Shared Template.xlsx’!BalanceHistory[Account ID],‘D:\Budget\Downloads\Manual Transaction Shared Template.xlsx’!BalanceHistory[Account '#], ‘D:\Budget\Manual Transaction Shared Template.xlsx’!BalanceHistory[Institution])),4,FALSE)

After formula
=VLOOKUP(S2, UNIQUE(CHOOSE({1,2,3,4},BalanceHistory[Account],BalanceHistory[Account ID],BalanceHistory[Account '#], BalanceHistory[Institution])),4,FALSE)

I did get your sheet and add-in successfully installed. I had to do a bit of tweaking, because my Transactions sheet has a few more columns than your macro accounts for so I had to change the location of some of the data. I changed the Add Manual Transactions sheet to reflect the correct data in the correct columns, but it looks like the macro doesn’t look any farther than Column N (it needs to go to column P) It added the row successfully, but not anything after column N. Would it be possible to extend the macro to account for a Transactions sheet that goes beyond column N? Thanks. Great job. I’m looking forward to using it.

Hello Mr Signal,

I followed your Setup (Magic) to open Add-ins. I was prompted with a file name. I tried with any file name to open Add-ins and nothing happened. What went wrong with it? What file name I should use?

Once you selected options from the file menu in Excel, it should open up this window:

Make sure to click Add-Ins on the left menu box. Then at the bottom next to Manage: there is a dropdown that should say Excel Add-ins. Select the Go button.

This window should open up:

From there select browse and your Windows file browser should open.

Go to the folder where you saved the file you downloaded from my share, it was the file named: Mr_Signal_Add_Transaction_Excel_Tiller.xlam, this is the Add in file you should select. If you renamed it then choose that file name.

Hope this helps.

Let me see if I can make sense of the question.

  1. The Transactions sheet has more cells than the original template.
    – To mirror this I put dummy data in row 2 all the way to column Z. (I am still learning the # trick to select all filled cells but I am not exactly sure how to format the formula right now to make it more dynamic)
    Two formulas need to change:
    a. Cell M2 would need to change to: =TRANSPOSE(INDIRECT(“Transactions!$A1:$Z1”)) in my example.
    You should see all the column headers from Transactions in column M.
    b. Likewise Cell O1 would need to change to: =INDIRECT(“Transactions!$A1:$Z1”) for this example.
    c. (Optional) complete the formula fill in Column N.

From here you should be able to verify that all of the correct data is under the correct header from the Transactions sheet. I am assuming this is where you adjusted the formulas.

Once all of that is straight, then a small tweak to the macro. It should be line 72 : Range(“O2:AB2”).Select ’ MODIFY the range if a new column is added to Transactions
Just modify the second variable in the Range for my example I will change it to:
Range(“O2:AN2”).Select ’ MODIFY the range if a new column is added to Transactions

This should fix the copy and paste of the values into the Transactions sheet.

Happy coding.

Hi, I’m not that proficient at Excel, so I didn’t know I could edit the macro! I did a quick google search and figured out how to access it. I made the change I needed, and it worked like a dream. Now that I have a “template” I can learn more about how it works. I can usually figure out how code works and modify it. I’m just not good at creating it from scratch - yet.
Thanks for the help.

Glad it worked. I have been debating posting an article on how to work with VBA (Visual Basic). I did at least try to comment on everything in the code so that you can understand what is happening e.ven if you do not know the exact syntax of the code. If you need any more help or just want to bounce ideas off me just let me know.

Oh, I did not download Mr_Signal_Add_Transaction_Excel_Tiller.xlam and decided to use your UTUBE video you previously suggested. I see much better examples and was able to complete the magic setup successfully. I slightly modified to my preference. It was a good learning experience for me.

I checked the newly inserted transaction in Transactions table. I see you populated Full Description in K column. I removed the unnecessary Full Description column since Description column has the same data as Full Description. Is it possible for me to edit the macro to eliminate the column K population with Description?

I find this Add Manual Transaction template useful since I must insert some manual transactions monthly.

Thank you for sharing this interesting template with us and give us an opportunity to learn some advanced EXCEL features!


I agree the full description was redundant, but for simplicity’s sake, just copying an identical range in the Add Manual Transaction to the Transactions sheet made it easier to code the second time since my computer crashed after the first iteration which I manually selected each cell and then copied it to Transactions. Copying the entire range is also a faster operation. That being said, yes you can eliminate column K in both the Transactions sheet and the associated column in the Add Manual Transaction sheet and then still use the same Macro copy range function if I understand what you are asking.

However, should you want to copy individual cells or ranges of cells then here is a brief explanation:
In the Macro file line 72 like the reply to J,

’ Select Add Manual Transactions sheet, copy data, goto Transactions sheet and paste data in new row.
’ Paste values only to maintain cell formats and formulas.

Sheets(“Add Manual Transactions”).Select
Range(“O2:AB2”).Select ’ MODIFY the range if a new column is added to Transactions
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

The line: Range(“O2:AB2”).Select ’ MODIFY the range if a new column is added to Transactions
would need to be modified to select the values you want to copy. So for example change it to:

Then you would leave these lines alone:
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Now you would have to go back to the Add Manual Transaction sheet to select the next Cell or Cells.
Sheets(“Add Manual Transactions”).Select

Modify the cell range: ( so for my example we are skipping T2 in the Add Manual Transactions)
Range(“O2:AB2”).Select ’ MODIFY the range if a new column is added to Transactions

Change it to Range(“U2:AB2”).Select This selects the remainder of the columns then we need to copy the selection:


The rest you would need to adjust to your use case.

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

You would need to modify Range(“A2”).Select to something like Range(“K2”).Select this selects the location you want to paste the values. If you only copy for example 6 cells then the Range of your paste should also be 6 cells.

Hope this helps:

It seems to me that I’m not able to edit your file.

There are no protections on the file that should prevent you from changing it. That being said, the file has nothing in it except for the Macro in VBA. Alt+F11 opens the Microsoft Visual Basic For Applications Editor , in which you can create or edit a macro by using Visual Basic for Applications (VBA). It is under the modules folder.

Are you able to open up the module?

I had to unchecked Trust Center/Protected View for files originating from the Internet and I was able to open your VBA module. I updated the line 72 to simply change from “O2:AB2” to “O2:J2” and saved it. I tested it to add the manual transaction. The new transaction was added to Transaction table, but it does not look right. It’s ok with other folks whose transaction table has K column with Full Description that I deleted some weeks ago. I decided to postpone it until I have time to play with it later.

So perhaps a dumb question…

Why do we need this tool? Can’t we just insert a row and type a transaction in manually one cell at a time?

I inserted columns into the transaction sheet as well and it seems to have broken the macro. I started following along on the blog, but thought it would not be necessary if we just entered the row manually.

No question is ever dumb. By itself, the tool is not super useful. It simply gives someone a way to insert the manual transaction without going to the Transactions sheet and manually entering the data into the cells. I would probably bet that most people who enter the data manually into the Transactions sheet do not fill in several of the fields like Transaction ID, week, month, etc … Also like David, I guess they delete columns that are also not super useful. (That is the cool thing about this system is we can make it ours.) Those columns can be used in the future to add additional functionality to the spreadsheet. So this tool automates filling in those additional columns that may not be used now, but maybe later.

Another reason the tool exists is people were asking for it in the community for Excel and it was already available in Google Sheets. I was thinking I hadn’t worked with Excel formulas in a while and wanted to see if I could create the ability and share it.

Challenges with adding functionality to an open source project: The project needs to maintain a baseline configuration like the minimal sheets to make it functional like the Balance History, Categories, etc… Obviously the community can take the project wherever they want, and modify it the way they want. As a programmer contributor, I do not know what I do not know and can only work with the baseline to add functionality. On the flip side, I get to meet cool people like you that might want to tweak the system and need unique solutions or as I like to think about it new challenges to figure out. The toughest part is the input validation and error handling. While the creation of the Macro did take several days, there was so much more I wanted to do, but frankly, I do not know how to do it or if it is even possible in VBA or Excel formulas. Like matching header names in case, someone deletes a column or popping up a calendar to choose the date when you go to insert a date.

Personal reason: I have been looking for a budget program like this for a long time. It needs additional functionality to completely meet my needs but it is a great starting point and handles the connectivity to the financial institutions including the 2FA that most institutions are going to for security. So I started a list of additional features that need to be implemented and adding a manual transaction is one of them. Others include adding manual accounts, recurring expenses, and income. This will give me a great challenge as I am not an expert on VBA, javascript, Excel formulas, and I am sure much more.

Sorry, it didn’t work for your implementation. Picking and choosing individual cells to copy and paste programmatically is harder than it sounds. If you need any help in the future just let me know.

I’ve wondered why Tiller Money Team decided to include Column K Full Description in Transactions sheet in Tiller Foundation Template for Google Sheets and Excel. Column B Description has duplicate data as Column K. It’s redundant and unnecessary!
What is the purpose of this column? Do users and developers really depend on Column K Full Description? I removed Column K Full Description to save space and may cause some issues for us.

As I do not speak for Tiller, I am not sure. As a quasi-programmer, I am keeping the optional columns in mind for expanded capability, like using it for META data, but that column is already in Google sheets. The question I have is why the format of the Sheet is different between Google sheets and Excel. Some functions and javascript could be ported from one to the other, but with different formats, you have to change the formulas and coding. It is a lot more coding to find the header name and then craft the array to insert, but I will get it done.

Oh, it’s nice of you to expand your VBA program to include individual columns. I’m not familiar with VBA, but I can learn VBA from you.

I’m a retired programmer. I wrote business programs mostly in COBOL and SAS on mainframes. I do enjoy programming at home using C# scripts to control Home Automation. My son is a software engineer involved in video streaming.

Many thanks!

Hey @mr_signal! Thanks for putting this together and being so responsive on this thread.

I finally got around to taking your creation for a spin. It’s really simple to use and fills a (hopefully soon-to-be-filled) hole on the Excel add-in side.

The only technical suggestion I’d make is to change the manual Transaction Id format from:
To the convention used in Sheets:

As we build out workflows on the Excel side, this change will ensure transactions created by your add-in match up with the new tools. Would you mind making this change?

I think the seamless, one-click insertion is a great workflow once properly configured.

As an alternative, script-less implementation, I could also see a Manual Transaction Staging worksheet where the column order matches the Transaction sheet and most derived columns (e.g. Account Id, Week, Transaction Id) are set to autofill. From there multiple transactions could be configured and staged for quick & easy paste-as-values into the Transactions sheet. This approach would be more work day to day but column order could be easily configured to match by the user and would not require installing an add-in (though you made that process quite easy).

Responding to a few questions in this thread:

  • @mr_signal: In Sheets, we sometimes use the Metadata column to store additional data about a transaction in JSON that does cleanly fit in other Transaction-sheet columns.
  • @mr_signal: In Sheets, we sometimes use the Metadata column to store additional data about a transaction in JSON that does cleanly fit in other Transaction-sheet columns.
  • @david: The Full Description is the original description as it is pulled from Yodlee. That value can sometimes be different from Description depending on the bank and some clean up functions in our backend. I’d definitely keep Descriptions but the Full Description column is only lightly used.
  • @wegs220: You can definitely do this manually… but I personally find filling all those derived columns a drag so I see the value here.

We recommend users exercise caution before using third-party add-ins & scripts with their personal data.