1

I'm trying to build a chart in Google Sheets to compare my portfolio contributions by year with the allowed maximums.

I would like to be able to filter by year and for each account display the total contributions for that year alongside the maximum allowed. I don't want to do any comparison between years.

Can you please advise the appropriate chart type to use to visualize this information?

Sample Data

  1. Each row contains a different account type with its name specified in the first column.
  2. Subsequent column-pairs each represent a single year, where the 1st column of the pair is labeled Max and the 2nd Current.
2022 2023
Max Current Max Current
Roth 6000 6000 6000 0
401k 20000 4000 21000 0
HSA 7300 6000 7800 0
0

2 Answers 2

1

Possible suggestion would be to use a stacked bar chart. Each bar will represent the account type. It would be a bit easier to see the contribution "progress" with the bars stacked, than to have 2 separate bars for YTD contributions and annual limits. From here you could change the color for contributions to green, and perhaps red to represent the remainder to be filled.

example of stacked bar chart

0
0

What do you think of something like these two options? I've reorganized a little bit your data, and here is a link to that book: https://docs.google.com/spreadsheets/d/1jJ3v5ERrLlAAFWKi_Hy_OOrU7NDiYLmdFHGkXwQSQlY/edit?usp=sharing

enter image description here

Edit: since you want to choose the year, you can create a menu with data validation with the corresponding years (c1 in my example), and then use the next formula in the following rows to search the values of that year

=HLOOKUP($C$1,$E$1:$Z$7,ROW(C2),0)

Then you should be able to pick a chart as suggested by me or the other users and make it based on that dependent column which will vary upon the year you choose in that cell C1

enter image description here PS: here you can see how data validation works to choose the year. Or you just can type the year you want

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.