Frequently Asked Questions
Q: What version of Excel is required to use these spreadsheet templates?
A: Microsoft Excel version 2016 or higher is required. You can use Microsoft Windows or the Mac version of Excel. Google Sheets is not supported. Microsoft also offers an online (web browser) version of Excel which unfortunately does not support macros or Power Query. However, you can use the basic version of the checkbook register with the online version of Excel.
Q: Is this checkbook template a subscription? Is there a recurring charge?
A: No. All of the checkbook templates on this site are ONE TIME purchases. There are no recurring charges. Please note that your bank statement might possibly show RECURRING PYMT on the charge which the payment processor (Stripe) is required to show, as other websites that use Stripe are sometimes set as a recurring payment. But you will never be charged more than once for these spreadsheet templates. (Stripe article link).
Q: Is it possible to download my bank transactions and get them into this spreadsheet?
A: Yes. Nearly all banks will allow you to download your checking or savings account transactions to a CSV file which Excel will open. The trick is to then reformat that spreadsheet from the bank so that it has a similar layout of columns as the Excel Checkbook Register. In other words, you’ll need to add/remove columns from the bank file so that you have columns for: Date, Trx Type, Check#, Payee/Description, Withdrawal and Deposit. I frequently download my bank transactions, and after a quick reformat, I then copy/paste those columns (being careful to NOT include the balance values) over to my checkbook. I created a YouTube video to demonstrate this process along with how to easily record an Excel macro to automate it.
Q: Can I delete or rename the column headings in the register worksheets?
A: Please don’t! If you rename any of the existing column headings (e.g., change “Deposit” to “Credit” or change “Chk#” to something else), the register worksheets will still function okay, but the Dashboard will stop working. Similarly, if you delete a column that you don’t need, the Dashboard will break. However, you can safely hide a column that you don’t use using Excel’s standard feature to hide a column.
Q: How can you insert a transaction (row) in the register in order to add a missing entry?
A: Using standard Excel features, you can insert a row anywhere you’d like. A few ways to do so include: (a) Right-click your mouse on a row number, and choose Insert. (b) With your cursor or mouse in one of the cells of an existing row, look on the main ribbon bar for Insert and click that button. A new empty row will be inserted above. Here’s a short YouTube video to demonstrate.
Q: Can you rename the tabs (sheet names) at the bottom of the spreadsheet so they say something other than Register1, Register2, etc.?
A: Yes, just point your mouse at a bottom tab and right-click it and choose Rename. Type your preferred name and press Enter.
Q: Can you safely delete (remove) the extra Register sheets that won’t be needed? For example, can I delete Register4, Register5, etc.?
A: No, please don’t delete any of the register worksheets. If you do, when you attempt to Refresh the dashboard, you’ll see this error: “[Expression.Error] The key didn’t match any rows in the table.“
That error occurs because the dashboard uses an Excel Power Query that has been programmed to look for transactions in all of the register worksheets. If you experience this error, I recommend re-downloading the Excel checkbook and then copy your transactions and categories over to a fresh copy of the spreadsheet, but don’t delete the unused register worksheets. Alternatively, you could edit the Excel Power Query and remove the reference to the register worksheets that were deleted. Contact me for additional info on this.
Q: I bought the new 2024 version which has a nice dashboard but I’m getting an error and/or it’s not updating.
A: See this page for a video walk-thru for initially setting up the spreadsheet and in particular, see the section about updating the Data Source Setting (if you downloaded the version before November 21).
Q: When I click to refresh the dashboard charts, I get an error like: “datasource.error: The process cannot access the file (xxx) because it is being used by another process“. What does that mean?
A: This happens with Microsoft OneDrive. If you store (save) the spreadsheet in a OneDrive folder, the OneDrive program puts a special lock on the spreadsheet which blocks Excel’s power query from having full access to the file so that the charts can be refreshed. As of November 21, an updated version of the Excel Checkbook has been released to resolve this. For previous users, see this page for how to fix this error.
Q: I get a date-related error when I click to Refresh the Dashboard. The error I see is: [dataformat.error] We couldn’t parse the input provided as a date value.
A: When this happens, there’s one or more entries in the date column in your register causing the issue. It could be some random text or a number instead of a valid date. You can use Excel’s filter feature to help find those odd entries. Note: you’ll need to do this on each register sheet until you find the entry (entries) that need correction.
INSTRUCTIONS: Click the little down-arrow on the DATE column which will reveal a list of date filters. In my screenshot below (Figure 1), I have good valid dates and they are all grouped by year. But when you have entered a number or some random text in the date column of your register, it will show up as a separate filter choice. When you see that oddity, you can uncheck all of the valid date entries so that only your bad entry remains check marked; click OK, and then your bad entry should be the only transaction that appears in the register. You can then fix it, and re-visit that down-arrow on the DATE column and choose the “Clear Filter” choice so that all of your entries are once again visible. Repeat that process on your register sheets, and be sure to save your file before attempting to Refresh the dashboard.
Q: If I have been using a previous version, how can I upgrade or move my data to a newer version?
A: Please see the YouTube video and instructions on this page for a demonstration on how to do so.
Q: Can you split a transaction into separate entries for categorizing?
A: At this point in time, we don’t have a feature to automate the splitting of a transaction. Quicken does have that feature. One simple way to work around this limitation is to create a separate entry (row) in the register for the items you want to uniquely categorize. As long as those entries total up to the exact amount that your bank recorded, your balances should match. For example, if you went to Target and the entire transaction was $200 but $80 was for groceries while the remainder was household goods, you could put the $80 as a separate row (categorized as groceries) and the $120 as household goods.
Q: Can this spreadsheet be used to manage or track investment properties, portfolios and/or for business or rental properties?
A: While this spreadsheet is intended for personal use for managing basic checking accounts and savings accounts, if your needs are fairly basic, the 2024 version might be an option for you. Be sure to check out the video on creating your own transaction summaries using pivot tables.
Q: Is there a free version of Excel or a free spreadsheet program that can be used with this checkbook spreadsheet?
A: Microsoft does offer Excel Online through a browser (Google Chrome, Microsoft Edge) for free, and it is possible to use my basic checkbook register or the 2023 version that doesn’t have a macro. Excel Online doesn’t support macros so my 2024 version of the checkbook won’t work but you can upload Excel files without macros to Excel Online.
Q: Is there a way to produce something like a Profit and Loss (P&L) statement or report?
A: Yes, using Excel’s pivot table feature, you can generate a table that summarizes expenses and deposits by category. See this video or follow this basic summary.
(1) Go to the register worksheet that you want to summarize. Click on the Insert menu and choose PivotTable.
(2) The default settings for “New Worksheet” are best so just click OK.
(3) In the pivot table fields box, drag the Years (Date) field down into the Rows box. Drag the Sub-Category field into the Rows box but place it under the Years (Date) field.
(4) Drag the Withdrawal into the Values box. Do the same for the Deposit field.
You’ll now have a summary of expenses and deposits by year.
Q: When I go to update the data source setting so that the Dashboard will work, I get a message that says: Microsoft.Mashup.Container wants to use your confidential information stored in “Microsoft.Office.Excel.ProtectedDataServices” in your keychain. What should I do?
A: To resolve this, when the spreadsheet is first opened, the status bar in Excel warns you about external data connections, and it’s important to click on “Enable Content” (that button appears on the far-right side of the Excel screen). Next, when you visit the “Data” menu and pick “Data Source Settings…” you might get the Microsoft.Mashup.Container.app pop-up message. You’ll need to put in your Mac password and click on “Always Allow” to make it go away and get things working. Here are a few articles from trusted sources about this keychain pop-up, if there are concerns.