Advanced Charting (Vertical to Horizontal Series?)

Hawk11ns

Board Regular
Joined
Jul 21, 2015
Messages
52
Office Version
  1. 365
Platform
  1. Windows
I would like to create a line chart that visualizes the three product types in B across each month in A for a given year in C:L which is defined by a user from a drop down list (will be located in Cell A40). Is there a way to do this without modifying the data table?

1602091007305.png
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,943
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Do you want to show just one product type at a time, or all three as different series? Is the user selecting the product or the year?
 

Hawk11ns

Board Regular
Joined
Jul 21, 2015
Messages
52
Office Version
  1. 365
Platform
  1. Windows
Do you want to show just one product type at a time, or all three as different series? Is the user selecting the product or the year?

The user will be choosing the year, and the goal is to show all three products across the 12 month period for that year
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,943
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Well, I know you don't want to hear this, but your data is in the wrong arrangement. Unfortunately, we receive data with little control over its arrangement, or someone is too invested in the pretty table they painstakingly formatted.

The best arrangement would be a four-column array, with one column each for Year, Month, Product, and whatever the dollar value represents (presumably Sales). Then you could build a pivot table, filter on year, and plot the three lines. (You could also make a second pivot table with the arrangement shown in your first post.)

Fortunately, your data isn't too complicated and is regular enough, that a few simple formulas can give you a chart data range that you can use. It looks like the following. The numbers are not the same, because I wasn't about to type all those numbers from a screenshot.

I put the months in the left column of the staging area (N3:N14) and the products in the first row (O2:Q2). The year goes in the top left corner (N2). To make it easy to choose a year (rather than having to type it in manually) I set up a Data Validation dropdown in cell N2, allowing a list whose source is $C$2:$L$2, the years from your original table.

The magic formula in cell O3 is:
Excel Formula:
=INDEX($C$3:$L$38,MATCH($N3,$A$3:$A$38,0)+MATCH(O$2,$B$3:$B$5,0)-1,MATCH($N$2,$C$2:$L$2,0))

I made a dynamic chart title in cell N16 using this formula:
Excel Formula:
=N2&" Product Sales by Month"

Then I made a chart using the range N2:Q14. To get that title into the chart, I selected the chart title, typed = into the formula bar, then clicked on cell N16 and pressed Enter.

Chart Data Staging Area.png
 

Watch MrExcel Video

Forum statistics

Threads
1,123,442
Messages
5,601,695
Members
414,469
Latest member
Murrell78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top