My goodness, you are the best Excel Tutor! You provide the reasons before proceeding to do it and then showing it patiently step-by-step. Please continue to do more of this for all your Subscribers.
Wow, I didn't even expect you'll have time to respond. If I may ask a demonstration please of how you'll handle nominal and ordinal data from 4 different worksheets, then summarize into one Master Sheet, then create a Pivot table and a graph. For example, 77 rows are colored either Green (labeled 'ready'), yellow (almost ready), and red (not ready). Each worksheet contains exactly the same data except for the color choices that vary for each worksheet. Thank you Nabil!
Hi, although I do not provide consulting on TH-cam but I will give you some helpful tips: 1- What you want to do can be easily done in Power Query. However you can also consolidate multiple lists from multiple worksheets but in this case you need to use the OLD PIVOT TABLE wizard. This wizard can be only brought by using a shortcut: ALT > D > P (ALT D P consecutively). This 3 steps wizard has an option for consolidating multiple sheets. 2- You cannot summarize Data and Analyze it by creating Pivot Tables & Charts based on Fill Color. However you can add a helper column say "Status" which shows the different options: Ready, Almost Ready, Not Ready... then use this column for drag and drop in the Filter area (or Slicer) 3- You could then use the same color in your Pivot Table by applying Conditional Formatting Hope that helps you I invite you to watch my new Tutorial on creating a Dynamic Calendar for Any Month in Any Year, just click here: th-cam.com/video/uSe1obBSgAg/w-d-xo.html&lc=z225ivcbevnbifp2iacdp433mk0cifrr1qg0xlk1zl5w03c010c
Finally, been searching for a while, not the chart i wanted to make, but information was so perfectly explained that i could put together how to make the chart i wanted.
Thank you. Usually, when using a workbook's scope name-range (DifferentInput), there is no need to prefix the name with the Worksheet's name (ChangeSource!DifferentInput). This is quite unusual.
Hey. Thanks for giving such nice techniques. In addition to this formula I did one more exercise which works great. Im this example height "12" is not dynamic, by COUNTA and OFFSET function i did it dyanamic. Now this entire data for me is dynamic ..if I am adding month and subsequent amount then its updating automatically.. Great idea
I am using excel version Home Office and Student 2016 and cannot make the Named range work in the series of the chart. Can you please tell me if my version does not work with named ranges in charts?
Can you take this a step ahead using 2 years data together- tell me how to arrange the source data and the offset formula for managing YR1,YR2 filter along with exisiting F1 filter.
Hey - Can you show us the line chart for the same data after switching column into rows.vis-a-versa , all the sale channel but only latest 6 months data ? Thanks in Advance
it didnt work for me (excel 2010).followed your instruction . defined name equation also okay my data has months at the top(header)and years as column (left) when i select data the dialog box is not similar to one in the video Pls help unable to attach screen grab pls help
Hello, I am desperate need of assistance. I have been looking for weeks now for the exact solution. I am working in google sheets- I need to know how to create a chart that will give me rolling totals over the most current 3 month period. I do not know what the best formula for this would be. I have my data source set up and i know how to create pivot tables. I just need to know how to have my reports roll as I enter new data for new dates. Can anyone help me please
when I try to insert the name range "differentinput" by editing chart source data its giving me an error "Excel found a problem with one or more formula references in the worksheet. Check that the cell references, range names, defined names, and links to other workbooks in your formulas are all correct." Please help.
My goodness, you are the best Excel Tutor! You provide the reasons before proceeding to do it and then showing it patiently step-by-step. Please continue to do more of this for all your Subscribers.
Thank you for your motivating comment.
Even if you are my only subscriber, I will continue doing it for this very nice feedback
Wow, I didn't even expect you'll have time to respond. If I may ask a demonstration please of how you'll handle nominal and ordinal data from 4 different worksheets, then summarize into one Master Sheet, then create a Pivot table and a graph. For example, 77 rows are colored either Green (labeled 'ready'), yellow (almost ready), and red (not ready). Each worksheet contains exactly the same data except for the color choices that vary for each worksheet. Thank you Nabil!
Hi, although I do not provide consulting on TH-cam but I will give you some helpful tips:
1- What you want to do can be easily done in Power Query. However you can also consolidate multiple lists from multiple worksheets but in this case you need to use the OLD PIVOT TABLE wizard. This wizard can be only brought by using a shortcut: ALT > D > P (ALT D P consecutively). This 3 steps wizard has an option for consolidating multiple sheets.
2- You cannot summarize Data and Analyze it by creating Pivot Tables & Charts based on Fill Color. However you can add a helper column say "Status" which shows the different options: Ready, Almost Ready, Not Ready... then use this column for drag and drop in the Filter area (or Slicer)
3- You could then use the same color in your Pivot Table by applying Conditional Formatting
Hope that helps you
I invite you to watch my new Tutorial on creating a Dynamic Calendar for Any Month in Any Year, just click here:
th-cam.com/video/uSe1obBSgAg/w-d-xo.html&lc=z225ivcbevnbifp2iacdp433mk0cifrr1qg0xlk1zl5w03c010c
Hi Nabil, I think I can try that. Thank you so much for getting back to me. Keep up the good work!
Not everyone can be a good teacher like you. Hats off for the wonderful tutorial.
Not everyone can be such a Nice and Motivating follower like you
Finally, been searching for a while, not the chart i wanted to make, but information was so perfectly explained that i could put together how to make the chart i wanted.
Great Job Man!!! The best on youtube so far!! Please keep up the great job!!
Excellent video. And your teaching is awesome
Very nice, Please keep adding your excellent videos on TH-cam
Thank you Sameh... Please subscribe and share
Regards Sir, you have explained this in a best way that a student of low caliber like me has got it.......Thanks
Fantastic tutorial.. Very well explained.. Thanks..
You are welcome!
Very good indeed! You are really good at making complex things look simple. Keep up the good work!
Thank you
An *excellent* tutorial. Thanks Nabil. Best regards. Salim
Nice tutorial friend
Thank you! Cheers!
Excellent video. Thank you Mr. Nabil
You are welcome
Great video, ur explanation nice sir
Thanks and welcome
Thank you. Usually, when using a workbook's scope name-range (DifferentInput), there is no need to prefix the name with the Worksheet's name (ChangeSource!DifferentInput). This is quite unusual.
Very good video to understand offset function and graph modification.
Glad you liked it
great sir amazing sir 👍🏻👍🏻👍🏻👍🏻
thankyou very much for your very informative video, very helpful;
Glad it was helpful!
I love this piece of work man!!! Great job sir
Hey. Thanks for giving such nice techniques. In addition to this formula I did one more exercise which works great. Im this example height "12" is not dynamic, by COUNTA and OFFSET function i did it dyanamic. Now this entire data for me is dynamic ..if I am adding month and subsequent amount then its updating automatically.. Great idea
Fantastic!
Great explanation easy to understand.
Glad it was helpful!
Awesome sir. While working dynamically the data labels are not displayed for the columns other than online data. Is it possible sir.
You are right... some of the formatting is not retained
I am using excel version Home Office and Student 2016 and cannot make the Named range work in the series of the chart.
Can you please tell me if my version does not work with named ranges in charts?
Great tutorial. Thanks for sharing
You’re welcome 😊
Can you take this a step ahead using 2 years data together- tell me how to arrange the source data and the offset formula for managing YR1,YR2 filter along with exisiting F1 filter.
Yes it can be done with a different layout ... it needs another tutorial not a comment
Hey - Can you show us the line chart for the same data after switching column into rows.vis-a-versa , all the sale channel but only latest 6 months data ?
Thanks in Advance
Awesome 🤩 👏
I love your solution, however, is it possible to have the range dynamic and only show the last 6 months in the chart rather than all 12 months?
will you explain how formatting applies then change the category?
my online chart data isnt showing after making all data dynamic! others show
it didnt work for me (excel 2010).followed your instruction . defined name equation also okay
my data has months at the top(header)and years as column (left)
when i select data the dialog box is not similar to one in the video Pls help
unable to attach screen grab
pls help
Hello, I am desperate need of assistance. I have been looking for weeks now for the exact solution. I am working in google sheets- I need to know how to create a chart that will give me rolling totals over the most current 3 month period. I do not know what the best formula for this would be. I have my data source set up and i know how to create pivot tables. I just need to know how to have my reports roll as I enter new data for new dates. Can anyone help me please
Cracked it, but the chart formatting disappears every time dunno why.
I would recommend basic formatting with this volatile function
Great tuts!
Thank you
Beautiful trick, thank you!
Glad you liked it
Fantastic job
Assalamu olaikom
Nabil Bro, from 🇧🇩 thanks
Very useful!
Sir
I tried, I have two error sir
Only work two dropdown only other two is not work what is the problem I fully checked step by step
I do not know Venkata which step you missed but the common mistake is if you miss the Sheet name before the cell reference
Officeinstructor
thanks your reply
Occurred error was My careless mistake. Now it’s working fine
when I try to insert the name range "differentinput" by editing chart source data its giving me an error "Excel found a problem with one or more formula references in the worksheet. Check that the cell references, range names, defined names, and links to other workbooks in your formulas are all correct."
Please help.
make sure your Offset function includes the sheet name before the cell reference
@@Officeinstructor I included the sheet name. Followed your instructions step by step. Still showing the error.
@@Officeinstructor Well I tried again from scratch and guess what it worked...thanks
Wonderful
Sir very good videos
Thank you!
Superb....