I completely understand the explanations. However the second one (where you added the sum functions below the data) can easily be remedied by moving the sum functions in by one column. I do this all the time in my excels using whole column referencing and it is never an issue.
I use whole column references ALL the time. And I don’t have these issues. Mainly because I avoid spill and array formulas when doing so. And I think the bigger error is putting outputs - like totals - at the bottom of a data set. Data sets should be data sets. Outputs should be outputs. I don’t use tables very often because they have their own limitations. The most recent I’ve found is corruption in a network based file. So use whole column references where it makes sense, and don’t use them where it doesn’t. But that is the same guideline for any tool or function you use.
Thanks for this Jon... question.. at the 00:23 mark, when you show your desktop.. at the QAT / Quick Access Toolbar... I see a mouse pointer icon over there.. what is that please? I use my QAT a lot... all the way to "0C" Thanks for all the great work you do! Best wishes! Cherian from hot and sunny Abu Dhabi
Hi Cherian, Great question! That is called the Select Objects tool and it allows you to draw a rectangle to select multiple shapes on the sheet. Similar to how PowerPoint allows you to select multiple objects. I explain more about it in this video on 7 dashboard tips. ruclips.net/video/Yx8o1a4Sw8o/видео.html I hope that helps. Thanks again and have a nice weekend! 🙂
So I have an excel spreadsheet to monitor our funds. On sheet 1 I have a master sheet with each fund and one the sheets after that I have each fund individually. Basically all the same info but placed on separate sheets to make it easier for my manager to view as she isn't too familiar with excel and the filter function. So sometimes info doesn't get copied over from the master sheet to its individual fund sheet because I have to manually do it and sometimes it slips my mind. Is there any formula that can help me automatically update the sheets from the master sheet?
Hi Azza, Great question! I used a macro to set the calculation to Manual mode, then run the Calculate method on the range that contains the formulas I'm testing. There is similar code in the example file for this other video on Vlookup performance. ruclips.net/video/Tkn1VlFc-QQ/видео.html I hope that helps. Thanks again and have a nice weekend! 🙂
Hi Chris Thank you , question i have pms file i want to know is there any way to excel calculate by own and find 3rd date between start and finish date with formula and gives best match date between 2 start and finish date , it's example that can expand for 1000 tasks Imagine i have 3 tasks with 5 days duration start is 1/1/22 and finish is 1/12/22 so first task start 1 day and 3rd task start 1/7/22 now I want excel calculate and find best date between in 2 tasks that must be 1/4/22 For 3 task i used sumerized 3 task days then or 2nd task i did finished 3rd task mines sum of 3 task divide by 1.3 and get closet date but it hard for 1000 task to start calculate from last task came up to 2nd task Now is there anyway excel calculate and find this date, this is can use for 1000 tasks more or less that I need to find it 1000000 tnx if you help me 🌹🌹🌹🌹 Thank you 🌹🌹
![Gucci Mane & Sexyy Red - You Don't Love Me [Official Music Video]](http://i.ytimg.com/vi/j7IBqyFKT4c/mqdefault.jpg)
![Megan Thee Stallion - Mamushi (Remix) [feat. TWICE] [Official Audio]](http://i.ytimg.com/vi/5c0_u9PsrsQ/mqdefault.jpg)
The first thing I learned from Excel Campus was to use tables. I put all my data in tables and it has saved me so much time. Thanks Jon!
Thanks Angela! I'm happy to hear you are using tables AND that they are saving you time! 👍
Agree!!
Excellent explanation Jon. Personally, whenever I can I try to avoid using whole column references. Thank you!
Thanks Ivan! 🙂
Great explanations, thank you.
I completely understand the explanations. However the second one (where you added the sum functions below the data) can easily be remedied by moving the sum functions in by one column. I do this all the time in my excels using whole column referencing and it is never an issue.
Great Tutorial With Super Helpful Tips....Thank You Jon :)
Thanks Darry! 🙂
I use whole column references ALL the time.
And I don’t have these issues.
Mainly because I avoid spill and array formulas when doing so.
And I think the bigger error is putting outputs - like totals - at the bottom of a data set.
Data sets should be data sets.
Outputs should be outputs.
I don’t use tables very often because they have their own limitations.
The most recent I’ve found is corruption in a network based file.
So use whole column references where it makes sense, and don’t use them where it doesn’t.
But that is the same guideline for any tool or function you use.
Thanks for sharing!
Thanks for this Jon... question.. at the 00:23 mark, when you show your desktop.. at the QAT / Quick Access Toolbar... I see a mouse pointer icon over there.. what is that please? I use my QAT a lot... all the way to "0C"
Thanks for all the great work you do!
Best wishes!
Cherian from hot and sunny Abu Dhabi
Hi Cherian,
Great question! That is called the Select Objects tool and it allows you to draw a rectangle to select multiple shapes on the sheet. Similar to how PowerPoint allows you to select multiple objects. I explain more about it in this video on 7 dashboard tips. ruclips.net/video/Yx8o1a4Sw8o/видео.html
I hope that helps. Thanks again and have a nice weekend! 🙂
@@ExcelCampus very many thanks for the quick reply and the link Jon. Appreciate it
🤝
So I have an excel spreadsheet to monitor our funds. On sheet 1 I have a master sheet with each fund and one the sheets after that I have each fund individually. Basically all the same info but placed on separate sheets to make it easier for my manager to view as she isn't too familiar with excel and the filter function. So sometimes info doesn't get copied over from the master sheet to its individual fund sheet because I have to manually do it and sometimes it slips my mind. Is there any formula that can help me automatically update the sheets from the master sheet?
question, is power bi service is app like bi desktop? what the main apps in power bi?
Hi Jon, how do you calculate those Calc Time in your presentation?
Hi Azza,
Great question! I used a macro to set the calculation to Manual mode, then run the Calculate method on the range that contains the formulas I'm testing. There is similar code in the example file for this other video on Vlookup performance. ruclips.net/video/Tkn1VlFc-QQ/видео.html
I hope that helps. Thanks again and have a nice weekend! 🙂
Hi Chris Thank you , question i have pms file i want to know is there any way to excel calculate by own and find 3rd date between start and finish date with formula and gives best match date between 2 start and finish date , it's example that can expand for 1000 tasks
Imagine i have 3 tasks with 5 days duration start is 1/1/22 and finish is 1/12/22 so first task start 1 day and 3rd task start 1/7/22 now I want excel calculate and find best date between in 2 tasks that must be 1/4/22
For 3 task i used sumerized 3 task days then or 2nd task i did finished 3rd task mines sum of 3 task divide by 1.3 and get closet date but it hard for 1000 task to start calculate from last task came up to 2nd task
Now is there anyway excel calculate and find this date, this is can use for 1000 tasks more or less that I need to find it
1000000 tnx if you help me 🌹🌹🌹🌹
Thank you 🌹🌹