A Better Microsoft Excel Checkbook Register – Part 4

I originally shared my sample Microsoft Excel Checkbook Register two years ago. A few readers have asked how can they track a second checking account with this spreadsheet. And some have also wanted to have a separate chart to connect with the second checking account. So with that request in mind, I have a new download available that has two checking accounts with separate pie charts for each.

In the image above, you’ll see two different register sheets, two different category sheets, and two separate chart sheets.

January 2024: To see all of the latest versions of the Excel checkbook register spreadsheet, please visit this site dedicated to that content

So how was this done? How can you recreate the same thing in your existing spreadsheet?

You can watch my 9 minute YouTube video here, or read the step-by-step below.

As an experienced Excel user, it took me less than 10 minutes to create all of the extra sheets. So if you want to have the same thing, you could download the new sample spreadsheet and then copy/paste all of your current transactions into the new spreadsheet, or you can follow these basic steps to replicate the same thing in your existing spreadsheet:

1. Have your current checkbook spreadsheet opened in Excel.
2. Do a right-click on the Register tab and choose “Move or Copy…” (see sample screenshot below).
3. When the ‘Move or Copy’ dialog box appears, choose where to locate this new sheet. So for example, when I copied the existing “Register” sheet, I clicked on “Categories” in the dialog box to indicate I wanted my copied sheet to appear before the existing “Categories” sheet.
4. Click the “Create a copy” checkbox and click OK.

Use the existing categories? Or create a new set?

Your new copy of the register sheet will be connected to the existing categories sheet, which provides the dropdown choices for categorizing your transactions. While I would personally find that ideal, if you want to maintain a separate list of categories, follow the steps above to “copy” the existing category worksheet. After doing so, you’ll need to change the data validation setting on your new register sheet.

The screenshot above will give you a visual. Here are the steps to change your new register sheet to look at a new categories sheet for dropdown choices.

1. Make sure you are in your new register worksheet (e.g., Register (2) ) and also make sure you have already created (copied) the categories worksheet.
2. In your Register (2) sheet, click your mouse into one of the existing category cells.
3. Click on the Data menu, and choose Data Validation from the ribbon bar.
4. In the dialog box that appears, there’s a setting for “Source” that needs changed. There’s an up-arrow you can click on in the source field to specify your new list. Click on it.
5. At the bottom of your screen, click on your new Categories (2) sheet, and then click on the “A” column heading, which should cause the entire column to show as selected. Your data validation box should now look like the sample below.
6. CRITICAL: Make sure to click the checkbox labeled “Apply these changes to all other cells with the same settings“, and then click OK.

After following the steps above, you can test your category dropdown to make sure it’s still working. And if you revise the choices in your new Categories (2) sheet, they should appear in dropdown list as expected.

How Can I Create a Separate Pie Chart?

If you’d also like a separate pie chart to connect with your new Register (2) sheet, here’s how. Start by making a copy of the existing Chart sheet.

1. Do a right-click on the existing Chart tab and choose “Move or Copy…”
2. When the ‘Move or Copy’ dialog box appears, choose where to locate this new sheet. For example, you might click on “(move to end)”.
3. Click the “Create a copy” checkbox and click OK.

After completing the steps above, you’ll have a second chart sheet but it will still be connected to the original Register worksheet. To point it to your new Register (2) sheet, do the following:

1. Go to your new Chart (2) sheet, and click your mouse into one of the row or sum cells in column A, B or C. It doesn’t matter which cell — just as long as it’s an existing cell of the pivot table.
2. After doing so, you’ll see a new menu choice appear for PivotTable Analyze. Click on that, and then choose the Change Data Source button as shown in the sample screenshot above.

3. You’ll see the dialog box above. Ignore it and click once on the bottom tab for your new Register (2) sheet. By doing so, the value in the dialog box for your “Table/Range:” should change from ‘Table1’ to some other value, like ‘Table13’ or something similar.
4. Click OK.

Your new chart should now be connected to the Register (2) sheet. See my earlier post on how the chart works if needed. Keep in mind that you can repeat this process outlined above if you want even more register sheets.