1

I'm trying to build a chart within google sheets to compare my portfolio contributions vs yearly allowed maximum limits. My data looks like the below. I'm not sure which chart to make use of to visualize this information. I would like to generate a chart that lets me compare these two values each year and each account. I'm okay to filter on year on the chart. Can you please help get me started building the right type of chart?

  • The first column is account type
  • Then each year will be a column which will be split into two Max vs Current.
  • Each Row will be an account type followed by actual values
2022 2023
Max Current Max Current
Roth 6000 6000 6000 0
401k 20000 4000 21000 0
HSA 7300 6000 7800 0

Edit

I would like to be able to filter on the year and display data only for that particular year. I don't need comparison between years. I only want to compare max vs current values for each account type under one year.

Edit 2

I've further simplified my data table a bit and am able to accomplish my requirements using a bar chart. I now want to understand how to use filters/slicers to populate chart based on the year selected.

This is how my updated table looks like

Year HSA Roth 401k
2022 Current 6000 6000 6000
2022 Max 7350 6000 20000
2023 Current 1000 1000 1000
2023 Max 7850 6000 21000

chart

The chart works fine when I filter for 2022. However, when I filter for 2023 data, there is nothing. How do I make my chart dynamic depending on the values I select on slicer?

2
  • 1
    Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer.
    – Community Bot
    Oct 29, 2022 at 2:34
  • @BlindSpots, thank you for sharing these links. They look like a great source of information. Oct 29, 2022 at 4:24

2 Answers 2

0

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.

1
  • Thanks this looks like a possible solution to my problem. however, I'm not sure how to filter the data for only one year? Oct 29, 2022 at 4:17
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

4
  • Thanks this helps a bit. I've edited my question to limit my requirements. I would like to filter data by year and compare max vs current by account type. How can I proceed with this? Oct 29, 2022 at 4:15
  • Hello! I've added an edit where you can create a column with data validation in the first row of the year and then HLOOKUP to bring the corresponding values. You can create the chart with that column and it will vary accordingly with what year you choose! I couldn't make the graphic because I'm not at a computer now, but in my link you can see that column created with the formulas 😉
    – Martín
    Oct 29, 2022 at 4:47
  • Thanks. I've simplified my data table further now to keep it easy. Can you please look at Edit 2 and help me with applying slicers/filters? I want to just filter out 2022 current and 2022 Max and generate chart dynamically. Oct 29, 2022 at 5:18
  • Mmm, I don't know exactly how you've made your chart and filters. The data ranges of the chart look strange but I can't say it that is because of the filters or it was your specific selection. Nor what 2023 slicer shows. Can you share an editable copy?
    – Martín
    Oct 29, 2022 at 10:17

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.