How to create charts for individual rows of data

[glenng765]

Is there an easy way to create charts for individual rows in a spreadsheet and/or table?

I'm trying to compile and graph sales data for multiple locations over the latest 10 year period. The problem is that I need to save and present each individual graph separately, so the typical pivot chart won't help.

I need to graph individual sales numbers, as well as regional averages and averages based on size classifications (A,B,C,or D). I also need to update these numbers on an annual basis and have the charts update as well.

I have all of the data in a table format with the locations in rows and the yearly sales data in columns. I'm not very proficient with macros or VBAs.

Any suggestions?

[protonLeah]

(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!

[glenng765]

Attached is the file you requested with the data.

[JeteMc]

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.

[glenng765]

Thanks for the help, but is there a way to combine those 3 different graphs into 1 for each store location based upon their individual values?

I know I can go in and select the 3 rows individually to create a chart, but it is very time consuming if I have to repeat that procedure for every location. Is there a way to automate this process?

[AliGW]

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

[glenng765]

How would I do that?

[Roger Govier]

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

Please Login or Register  to view this content.

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

[JeteMc]

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.

[glenng765]

Thank you. However, is there a way to include more than one slicer on a single graph? For example, store #820 with "South" average and "B" average so the graph shows 3 distinct lines.

[Roger Govier]

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.

[glenng765]

Great, thank you. Now the only question is can I save each graph individually in a separate tab or something, or will it overwrite every time I make a new selection?

Somehow I need to be able to save each graph separately and update them on a yearly basis.

[Roger Govier]

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.