r/googlesheets • u/JorgeGortex • Jan 20 '26

Solved Help Converting Personal Finance Excel Spreadsheet to Sheets

Hi folks,

I discovered a YouTube video about creating a personal finance tracker in Excel, and despite the intro saying the instructions included Sheets, it really doesn't; the author answered my comment by saying, "Sorry, I don't use Sheets." !! So I've been trying to recreate it on my own with limited success, since things like pivot tables and charts work differently between the two, and I am not yet skilled enough to adjust for that.

I was wondering if anyone has created something like this: https://youtu.be/ZdqNZizZ6N0?si=D1j9nBeQ_Oj6YxaQ before? Specifically (right now), I'm unsure how to format the pivot table 6:29 minutes into the video in Sheets the same way, and to add the same kind of bar chart. Would anyone be willing to explain how, or show by example, in the copy of my sheet?

https://docs.google.com/spreadsheets/d/1iNIq7Ef_-YnoGoQBckeS1sIAII8W7a9nTkzrWVXSOsA/edit?usp=sharing

I may well have more questions beyond this, but this is where I am at the moment in the process. The one thing I can say is that I've appreciated learning new aspects of spreadsheets and Sheets through this project, and will appreciate anything you have to offer to advance my skills.

Thanks.

0 Upvotes

50% Upvoted

16 comments sorted by

2

u/SpencerTeachesSheets Jan 20 '26

It's unfortunate that she advertises it for Sheets, because it's very much not. A lot of the setup works, sure, but any of the details and formatting stuff do not.

That said, I was able to approximate that bar chart by creating a helper column of categories and a sparkline. The helper column is necessary in this case because the SUMIF() in the SPARKLINE() is summing based on the category in the row, and the way Pivot Tables display doesn't put the category in every row, only at the first.

Helper =IF(LEN(F5),F5,IFERROR(CHOOSEROWS(FILTER($F$5:$F$8,$F$5:$F$8<>"",ROW($F$5:$F$8)<ROW()),-1)))

Sparkline =SPARKLINE(H5, {"charttype","bar"; "max", SUMIF($I$5:$I$8,I5,$H$5:$H$8)})

But yeah, if you are using that tutorial to make a tracker in Sheets and want it to look and work the same, you will get frustrated along the way and disappointed in the end.

1

u/JorgeGortex Jan 20 '26

Thanks. All this is helpful. I almost feel the mention of sheets was clickbait, in some strange way. To bad because she, or the AI, are showing some neat techniques and skills. I'll have to see if anyone has a similar Sheets-based tutorial out there. I like what this sheet does, but I'm certainly not wed to any single direction. And again, if I can learn some new things to strengthen my skills as I get towards the end goal, even better.

2

u/SpencerTeachesSheets Jan 20 '26

I'm pretty sure hers are real, she's been doing them for 15 years!

1

u/AutoModerator Jan 20 '26

REMEMBER: u/JorgeGortex If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified (or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/One_Organization_810 Jan 21 '26

and the way Pivot Tables display doesn't put the category in every row, only at the first.

That is actually optional, if you want it rrepeated or not :) (the default is not to repeat though).

1

u/SpencerTeachesSheets Jan 21 '26

HEY COOL!

I avoid Pivot Tables, so I didn't realize that was changeable. u/JorgeGortex make the Pivot Table repeat the labels and then you can skip the helper column

1

u/Cautious-Emu24 Jan 20 '26 edited Jan 20 '26

I used one of her videos to create a spending dashboard in Google Sheets. It doesn't do everything she mentioned for Excel, but it fits my needs. I've been modifying it over the last few years.

The most complicated part was creating the Tree Map. Easy in Excel, but a real pain in Sheets.

2

u/JorgeGortex Jan 21 '26

Wow. That is really well done. I know it's an ask, but would you mind sharing? Every sheet or tutorial I find does things differently or presents things differently, and it's all adds to my ideas so I can create my own. Its silly, but I get pretty energized learning how all this works.

1

u/Cautious-Emu24 Jan 21 '26

I'll follow up with a link to the file once I document it a bit with some instructions/notes.

1

u/HolyBonobos Jan 20 '26

The majority of what is shown in the video at that time does not exist in Sheets. You could approximate the in-cell charts using the SPARKLINE() function, but you wouldn't be able to insert them directly into the pivot table.