How to create charts for individual rows of data

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced next to Quick Post button at the bottom right of the editor box.
• Scroll down until you see "Manage Attachments",
• Click the "Choose" button at the upper left (upload from your computer).
• Select your file, click "open", click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!

Attached is the file you requested with the data.

Hello glenng765 and Welcome to Excel Forum.
If I understand correctly the following will make a graph based on a row of data.
For Store #402:
1) Select cells A2:M2,
2) Press the following keys in order: ALT, N, N, and Enter,
3) Select the horizontal axis, right click and choose 'Select Data',
4) Edit the horizontal axis labels so that the range is Sheet1!$D$1:$M$1,
5) Select the graph and choose 'Cut',
6) Paste the graph to it's designated sheet.
Graphs for 'Lakes' and 'A' were produced in the same manner.
Let us know if you have any questions.

You could pull the data you need to a hidden table and base the chart on that.

Hi

In the attached file I took your Data and renamed the sheet to Data.
I then selected Get & Transform Data (XL2016) or select Power Query (earlier versions) > From Table > selected the first 3 columns > Transform > Unpivot Other columns.
I renamed the two newly created columns as Year and Value and Closed and Saved to new sheet as Table.
I renamed that sheet as Normalised Data
The "M" code that was generated is as follows

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"STORE #", type any}, {"SIZE", type text}, {"AREA", type text}, {"2008", type number}, {"2009", type number}, {"2010", type number}, {"2011", type number}, {"2012", type number}, {"2013", type number}, {"2014", type number}, {"2015", type number}, {"2016", type number}, {"2017", type number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"STORE #", "SIZE", "AREA"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Year"}})
in
    #"Renamed Columns"

From this Normalized data I then created a Pivot Table on sheet Charts, with Year as a Row field, Area as a column field and Value as the Values field.
I created Slicers for Area and Store, and then created a Chart alongside the Pivot Table, and positioned the slicers to the right of the Chart.

Using the Slicers, you can select any area and see all of the data for that Area.
The Store slicer, then shows the Stores within that Area and you can select an individual store or the area average.

Hope this helps

Here's an example.
Sheet 2 shows a table with data pulled from sheet 1. The values in column A are selectable via data validation and the values in the subsequent columns populated via Index/Match formulas.
The graph on the 'Composite' sheet utilizes the table on sheet 2.
Let us know if you have any questions.

Sure.
It's just a question of what you choose to have in your Pivot Table.
Your original post talked about seeing a single line, so I thought that was all you wanted.

Take a look at this version where I have added an extra sheet called Store Charts.
Here, when you select just an area you see a line for each store within that area, which can look very busy.
But you can just select one store, then hold down Control as you select another store. As soon as you release the Control button, that store appears as well. Continue for whatever stores you want, and the average - as in the sample as posted.
Clicking the "x on Slicers clears that selection.

I have shown the PT below the graph, as I often like to see the numeric data as well, but you don't have to have the Pt on the sheet at all.

Hi
Sorry, I had to go out yesterday evening so couldn't get back to you.

You could create a separate tab for each area, each with it's own PT and graph and slicers., and name each tab as the area.
I have set up one as South and one as Calif - don't have the time to do them all, but you could easily do that.

DO NOT attempt to create the extra tabs by copying and renaming.
If you do, all the reports will be based upon the same Pivot Cache, and therefore every slicer would be acting on that one cache, so changing the selection on one tab would affect all.

Start from the Normalised Data each time > Inset > Pivot Table > new sheet
Select Store s the column Field, Year s the Row field and Value as the Values field.
Move the PT down the sheet to make space for the graph and from the Analyze tab > Insert Slicer for Store and Area
Select your Area and then from the Analyze Tab . Insert Chart > choose Line
Name the tab as the Area.
Repeat for each area

It takes just a few minutes to do each one.
Then each has an individual pivot cache and the Slicer will only affect that Tab.
As you enter more data each year to the Data tab, if you go to the Normalised data tab and from the Get & Transform section > Show Queries > double click the Query and it will Refresh to include the latest data added and all of your reports when you Refresh the PT on each tab will then include the new years data as well.