Plotting multiple data sets by year in Excel

R

reporting_95

New Member
Joined
May 7, 2019
Messages
30
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hello,
I have about have about 3 years worth of sales data and I'm comparing in-store to online sales. I summarized my data in a pivot table and included two slicers to easily click on a year and month to view the total sales for that period. I'd like to use a graph to show a visual of this data, but the suggested graphs don't exactly capture what I'm trying to display. Can anyone please suggest a better way to graph and visualize this data? I'll include a screenshot of my pivot table and slicers. Thanks!
 

Attachments

  • summary_2023.JPG
    summary_2023.JPG
    53 KB · Views: 13

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
is there anyway you can send a sample of your data for each year and some of the months?
It really hard for the forum to recreate your scenario.

The Mr. Excel Forum has a tool called xl2bb add in (link below) that allows you to do this. It won't copy the pivot table coding or any charts, but it give the data .
 
Upvote 0
awoohaw said:
is there anyway you can send a sample of your data for each year and some of the months?
It really hard for the forum to recreate your scenario.

The Mr. Excel Forum has a tool called xl2bb add in (link below) that allows you to do this. It won't copy the pivot table coding or any charts, but it give the data .
Click to expand...
 
Upvote 0
awoohaw said:
is there anyway you can send a sample of your data for each year and some of the months?
It really hard for the forum to recreate your scenario.

The Mr. Excel Forum has a tool called xl2bb add in (link below) that allows you to do this. It won't copy the pivot table coding or any charts, but it give the data .
Click to expand...
Hello awoohaw,
Thanks for your reply. I'm trying to use the xl2bb tool but it only allows a max of 3000 cells. I posted some of the data but not sure if that's enough. Any way I can send the file?






Sample_2023.xlsx
ABCDEFGH
1TICKET_ID1CREATEDMODIFIED1MonthDayYearItemSales Method
27656801/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
37656811/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
47656831/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
57656841/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
67656861/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
77656951/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
87656991/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
97657001/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
107657011/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
117657031/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
127657051/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
137657071/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
147657121/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
157657131/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
167657151/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
177657181/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
187657191/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
197657291/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
207657301/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
217657311/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
227657331/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
237657371/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
247657391/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
257657401/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
267657411/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
277657421/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
287657471/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
297657501/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
307657511/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
317657521/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
327657531/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
337657571/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
347657721/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
357657751/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
367657761/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
377657851/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
387657921/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
397657961/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
407658101/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
417658251/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
427658271/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
437658341/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
447658501/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
457658511/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
467658631/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
477658661/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
487658731/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
497658931/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
507658951/4/20211/4/2021JanuaryMonday2021iPhone ChargersOnline Sales
Sheet2
 
Upvote 0
awoohaw said:
is there anyway you can send a sample of your data for each year and some of the months?
It really hard for the forum to recreate your scenario.

The Mr. Excel Forum has a tool called xl2bb add in (link below) that allows you to do this. It won't copy the pivot table coding or any charts, but it give the data .
Click to expand...
Sample_2023.xlsx
ABCDEFGH
1TICKET_ID1CREATEDMODIFIED1MonthDayYearItemSales Method
27924212/1/20212/1/2021FebruaryMonday2021iPhone ChargersOnline Sales
37924252/1/20212/1/2021FebruaryMonday2021iPhone ChargersOnline Sales
47924272/1/20212/1/2021FebruaryMonday2021iPhone ChargersOnline Sales
57924442/1/20212/1/2021FebruaryMonday2021iPhone ChargersOnline Sales
67924452/1/20212/1/2021FebruaryMonday2021iPhone ChargersOnline Sales
77924462/1/20212/1/2021FebruaryMonday2021iPhone ChargersOnline Sales
87924592/1/20212/1/2021FebruaryMonday2021iPhone ChargersOnline Sales
97924672/1/20212/1/2021FebruaryMonday2021iPhone ChargersOnline Sales
107924692/1/20212/1/2021FebruaryMonday2021iPhone ChargersOnline Sales
117924702/1/20212/1/2021FebruaryMonday2021iPhone ChargersOnline Sales
127924782/1/20212/1/2021FebruaryMonday2021iPhone ChargersOnline Sales
137924792/1/20212/1/2021FebruaryMonday2021iPhone ChargersOnline Sales
147924832/1/20212/1/2021FebruaryMonday2021iPhone ChargersOnline Sales
157924902/1/20212/1/2021FebruaryMonday2021iPhone ChargersOnline Sales
167924962/1/20212/1/2021FebruaryMonday2021iPhone ChargersOnline Sales
177925002/1/20212/1/2021FebruaryMonday2021iPhone ChargersOnline Sales
187925072/1/20212/1/2021FebruaryMonday2021iPhone ChargersOnline Sales
197925152/1/20212/1/2021FebruaryMonday2021iPhone ChargersOnline Sales
207925172/1/20212/1/2021FebruaryMonday2021iPhone ChargersOnline Sales
217925192/1/20212/1/2021FebruaryMonday2021iPhone ChargersOnline Sales
227925252/1/20212/1/2021FebruaryMonday2021iPhone ChargersOnline Sales
237925272/1/20212/1/2021FebruaryMonday2021iPhone ChargersOnline Sales
247925552/1/20212/1/2021FebruaryMonday2021iPhone ChargersOnline Sales
257925622/1/20212/1/2021FebruaryMonday2021iPhone ChargersOnline Sales
267925962/1/20212/1/2021FebruaryMonday2021iPhone ChargersOnline Sales
277925972/1/20212/1/2021FebruaryMonday2021iPhone ChargersOnline Sales
287926002/1/20212/1/2021FebruaryMonday2021iPhone ChargersOnline Sales
297926022/1/20212/1/2021FebruaryMonday2021iPhone ChargersOnline Sales
307926052/1/20212/1/2021FebruaryMonday2021iPhone ChargersOnline Sales
317926162/1/20212/1/2021FebruaryMonday2021iPhone ChargersOnline Sales
327926272/1/20212/1/2021FebruaryMonday2021iPhone ChargersOnline Sales
337926412/1/20212/1/2021FebruaryMonday2021iPhone ChargersOnline Sales
347926482/1/20212/1/2021FebruaryMonday2021iPhone ChargersOnline Sales
357926622/1/20212/1/2021FebruaryMonday2021iPhone ChargersOnline Sales
367926832/1/20212/1/2021FebruaryMonday2021iPhone ChargersOnline Sales
Sheet3
 
Upvote 0
reporting_95 said:
Hello awoohaw,
Thanks for your reply. I'm trying to use the xl2bb tool but it only allows a max of 3000 cells. I posted some of the data but not sure if that's enough. Any way I can send the file?
Click to expand...

you can post it to a file sharing site like dropbox, then post a link. Sanitize the data for privacy and security first.
 
Upvote 0
Okay. I am not a fan of pie charts, but I do like tree maps. But tree maps are strange.
I cannot: 1) make a dynamic title 2) use pivot tables as sources and 3) (probably) use slicers.
Here is what i came up with. First is an image, and then I'll try to post a mini sheet. but it has hidden rows and not sure if those mess up the xl2bb:


1683909376691.png



Mr excel questions 35.xlsm
ABCDEFGHIJ
1TICKET_ID1CREATEDItemSales MethodTreemap Instore Sales for July, 2020
27924212021-02-01 09:16:48iPhone ChargersOnline SalesYear2020
37924252021-02-01 09:18:51iPhone CasesOnline SalesMonth7
47924272021-02-01 09:19:42iPhone CasesOnline SalesInstore SalesOnline Sales
57924442021-02-01 09:38:56iPhone ProtectorsOnline SalesiPhone Cases315274
67924452021-02-01 09:39:34iPhone ChargersOnline SalesiPhone Chargers360342
77924462021-02-01 09:39:52iPhone ChargersOnline SalesiPhone Protectors4936
87924592021-02-01 09:56:37iPhone ChargersOnline SalesiPhone Stickers1212
97924672021-02-01 10:02:27iPhone CasesOnline Sales
107924692021-02-01 10:02:49iPhone CasesOnline Sales
117924702021-02-01 10:03:57iPhone ChargersOnline Sales
127924782021-02-01 10:11:33iPhone ChargersOnline Sales
137924792021-02-01 10:13:06iPhone CasesOnline Sales
147924832021-02-01 10:15:42iPhone CasesOnline Sales
157924902021-02-01 10:24:30iPhone CasesOnline Sales
167924962021-02-01 10:29:41iPhone ChargersOnline Sales
177925002021-02-01 10:31:56iPhone ChargersOnline Sales
187925072021-02-01 10:37:58iPhone ChargersOnline Sales
197925152021-02-01 10:47:58iPhone ChargersOnline Sales
207925172021-02-01 10:49:33iPhone CasesOnline Sales
217925192021-02-01 10:50:25iPhone ChargersOnline Sales
227925252021-02-01 10:55:41iPhone ChargersOnline Sales
237925272021-02-01 10:57:29iPhone ChargersOnline Sales
247925552021-02-01 11:14:50iPhone ChargersOnline Sales
257925622021-02-01 11:19:06iPhone ChargersOnline Sales
267925962021-02-01 11:35:58iPhone CasesOnline Sales
277925972021-02-01 11:36:21iPhone ChargersOnline Sales
287926002021-02-01 11:38:43iPhone ChargersOnline Sales
297926022021-02-01 11:42:06iPhone CasesOnline Sales
307926052021-02-01 11:43:17iPhone ChargersOnline Sales
317926162021-02-01 11:50:08iPhone ProtectorsOnline Sales
327926272021-02-01 11:59:26iPhone ChargersOnline Sales
reporting_95
Cell Formulas
RangeFormula
F1F1="Treemap Instore Sales for "&IF(OR($G$2="",$G$3=""),"All Time",PROPER(TEXT(DATE($G$2,$G$3,1),"mmmm, yyyy")))
G5:H8G5= IF(AND(ISNUMBER($G$2),ISNUMBER($G$3)), SUM((--(SalesData[[Item]:[Item]]=$F5))* (--(SalesData[[Sales Method]:[Sales Method]]=G$4))* (--(INT(SalesData[[CREATED]:[CREATED]])=DATE($G$2,$G$3,1)))), COUNTIFS(SalesData[[Item]:[Item]],$F5,SalesData[[Sales Method]:[Sales Method]],G$4))
 
Upvote 0
Solution
awoohaw said:
you can post it to a file sharing site like dropbox, then post a link. Sanitize the data for privacy and security first.
Click to expand...

Thanks for your replies! Here is the Dropbox link with the Excel data

www.dropbox.com

Sample_2023.xlsx

Shared with Dropbox
www.dropbox.com www.dropbox.com

One sheet has the original data and the other has a pivot table. I'm wondering if I can use a line graph to visualize the data and use the slicers to filter the data.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Formatting data series for chart created via Slicer and Pivot Table
Replies
6
Views
982
awoohaw
awoohaw
oriwitt
  • Question Question
Graph from multiple data series
Replies
1
Views
542
Jon Peltier
J
E
  • Question Question
Pivot chart joining multiple series
Replies
0
Views
999
Exceloway
E
B
  • Question Question
Graph/chart options for 5 years of monthly data points for 40 departments
Replies
2
Views
551
broken keyboard
B
D
  • Question Question
Use YEAR slicer to control pivots from multiple data source
Replies
0
Views
453
deLockloire
D

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

Share this page

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