Forum Discussion
Help With Excel Line Graph "Switches"
I apologize if this query has already been raised before. I have been tasked with creating a graph that compares data across multiple years. With how cluttered the graph is becoming, I was curious if it was possible to show/hide certain data ranges through formulas. I am aware I can do so through the graph settings, selecting which data series to hide and such, however it is likely I will be switching comparison between different years on the fly, and was wondering if I could set a simple command that will allow to show/hide each year's data ranges as necessary? Or will I just need to suck it up and interfere with the graph settings every time?
you can look at using slicers and filters and such but otherwise you can also use 'helper' columns that pull the data you want and then graph those columns of data. So if A1:A100 is for 2024 and B1:B100 is for 2025 then in C1 you have =IF(D1=2024, A1:A100, B1:B100) then you plot C1:C100 then just change D1 from 2024 to 2025 accordingly. (this is obviously simplified but gives the idea). If the length of those data set may vary then you consider using a named dynamic range for the graph. Here is a reference for how to do that:
that said, if you search for excel graph dynamic range you will find plenty of sites and videos that may be even easier to understand.
hope that helps.
2 Replies
you can look at using slicers and filters and such but otherwise you can also use 'helper' columns that pull the data you want and then graph those columns of data. So if A1:A100 is for 2024 and B1:B100 is for 2025 then in C1 you have =IF(D1=2024, A1:A100, B1:B100) then you plot C1:C100 then just change D1 from 2024 to 2025 accordingly. (this is obviously simplified but gives the idea). If the length of those data set may vary then you consider using a named dynamic range for the graph. Here is a reference for how to do that:
that said, if you search for excel graph dynamic range you will find plenty of sites and videos that may be even easier to understand.
hope that helps.
Sounds easy enough. Thank you for your assistance, I'll have a crack and see what sticks
