Advanced Charting (Vertical to Horizontal Series?)

Hawk11ns

Board Regular
Joined
Jul 21, 2015
Messages
61
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

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
Back
Top