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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,931
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,931
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,118,358
Messages
5,571,708
Members
412,413
Latest member
dvprajapati
Top