Context:
Hi everyone, I have recently been attempting to rework my expense tracker and personal budget in Excel. I am attempting to make my main page contain an actual budget function and feel like more of a "dashboard" than just a bunch of charts. Prior to revising, this was what I was working with:
View attachment 65266
As you can see, it is quite literally just a bunch of pie charts and their respective quantitative data. While this was effective, it certainly wasn't the prettiest way to view my data and spending information. The worksheet functions by manually inputting expense/revenue data into a table as they occur. Then I use formulas to sort and filter the data into organized arrays which are used to display data and create tables (such as: revenues by supplier, expenses by vendor, revenue by category, etc).
Fast forward to now and I have finished drafting out what I would like the dashboard to look like. After spending hours merging and organizing all the cells in the format I want, this is what I came up with:
In this "dashboard" all the light red colored lists are arrays, light blue lists are manually inputted values, and yellow lists are formulas. The ""buttons" would be used to switch charts and change relevant data in the corresponding cells to the right side. From what I've seen, using pivot charts would be able to achieve this "button" functionality (although admittedly I've never used pivot charts before and ran into problems before I could even attempt making them). I've attached an image containing a mock image of worksheet with actual data at the end of this post.
The problems started when I began inputting my arrays into the cells. I had absolutely no idea that excel strictly requires arrays to have a spill zone and thus does not allow them to work in merged cells. Having found this out, practically everything in the "dashboard" is useless.
Question:
Is there any workaround to place dynamic arrays in merged cells? Even better, is it at all possible to disable array spill range requirement? While we're on the topic would anyone be able to confirm that the "button" function works in the way I am thinking it does?
Thank you so much for reading my post. My apologies if I have left any important information out. It's been years since I've posted anything in a forum, and I am notoriously horrible at explaining problems involving excel. If I can provide any clarifying details or information, please let me know! I look forward to hearing any solutions, tips, or tricks anyone can come up with.
Hi everyone, I have recently been attempting to rework my expense tracker and personal budget in Excel. I am attempting to make my main page contain an actual budget function and feel like more of a "dashboard" than just a bunch of charts. Prior to revising, this was what I was working with:
View attachment 65266
As you can see, it is quite literally just a bunch of pie charts and their respective quantitative data. While this was effective, it certainly wasn't the prettiest way to view my data and spending information. The worksheet functions by manually inputting expense/revenue data into a table as they occur. Then I use formulas to sort and filter the data into organized arrays which are used to display data and create tables (such as: revenues by supplier, expenses by vendor, revenue by category, etc).
Fast forward to now and I have finished drafting out what I would like the dashboard to look like. After spending hours merging and organizing all the cells in the format I want, this is what I came up with:
In this "dashboard" all the light red colored lists are arrays, light blue lists are manually inputted values, and yellow lists are formulas. The ""buttons" would be used to switch charts and change relevant data in the corresponding cells to the right side. From what I've seen, using pivot charts would be able to achieve this "button" functionality (although admittedly I've never used pivot charts before and ran into problems before I could even attempt making them). I've attached an image containing a mock image of worksheet with actual data at the end of this post.
The problems started when I began inputting my arrays into the cells. I had absolutely no idea that excel strictly requires arrays to have a spill zone and thus does not allow them to work in merged cells. Having found this out, practically everything in the "dashboard" is useless.
Question:
Is there any workaround to place dynamic arrays in merged cells? Even better, is it at all possible to disable array spill range requirement? While we're on the topic would anyone be able to confirm that the "button" function works in the way I am thinking it does?
Thank you so much for reading my post. My apologies if I have left any important information out. It's been years since I've posted anything in a forum, and I am notoriously horrible at explaining problems involving excel. If I can provide any clarifying details or information, please let me know! I look forward to hearing any solutions, tips, or tricks anyone can come up with.
