I am a self taught Excel user. I knew that I wasn’t taking advantage of Excel like I should, but after finding your channel I realize that I have a lot to learn. I find your teaching technique and examples are very good and I plan to go back in your lessons and relearn using the tips you provide..great job thanks!
problem is the merged cells with the year dates in them should never have been unmerged and she never have to select across years ... she should be using cell ranges for the data ... as it is display page she was on NOT a calculation page ... she messed up big time ... excel first and foremost is a database ... thats right folks a database ... not a spreedsheet ... thats the maths you do on the database of information you have entered into the spreedsheet ... most of what she calls wrong is actually just NOT the way she was taught ... she never learned the old fashioned hand made spreedsheets and accounting books ... and so has no background in WHY things are done differently than the way she understands them ...
@@felipesignorellireis7839 at 8:42 you see a "1" in cell C21, which changes as she changes the selection in the dashboard. She probably manually added a radio button with the options and linked it to cell C21, with a dynamic array formula (FILTER) just below it. See this other video of hers: ua-cam.com/video/oSTgFUiRDGI/v-deo.html
The external links issue has plagued me for years. Thank you for clarifying how this happens, how to prevent it and how to correct it. This is why I subscribed to your channel.
I had a similar problem but when I go to break the external link I get errors as it cannot find the original document! I have a feeling I need to rebuild sheet from stratch!
This is a big headache, MS is acting like terrorist when we do NOT want external links to be created in the first place. So If anyone needs external links to be created , then make it from paste drop down menu ... but hey, why would MS do that,right? they cant even make it simple to implement CTRL+SHIFT+V for PASTE AS TEXT feature .... google has it for years. crap
I also had similar issues which I tended to resolve by global search/replace. This is a much more elegant solution; thank you Leila, especially as it also catches the remote links in conditional formatting which is notoriously difficult to find without resorting to some sort of VB routine.
Hi Leila, I'd like to thank you for all the material you provide us! I've started watching your videos in 2017 and took the Unlock Excel VBA and Macros course when I was still an Electrical Engineering undergraduate. The skills I've learned with you helped me a lot during my internship, and today I work as a planning engineer in a huge utility company in my country (Brazil). Your courses surely have been a great part of my success. Congratulations for your excel!ent work and thank you very much!
Although an extensive Excel user, I've learned a lot from your videos. Thanks Finally have something to contribute. Hidden numbers. Your comments are great. One additional - Name the cell that you will use in multiple cells. In your example, name 8% "InterestRate". When you include it in cells, it already is fixed with '$'. Also, you mention using 7 or 24 is okay. Those are still "magic numbers." Why 7? Instead, use Name Manager, define "DaysOfTheWeek" as "=7" Now your formula is divided by "DaysOfTheWeek" making it very clear what the 7 meant.
Somehow I prefer to also put the labels in a sheet 🤷♀️ Now I’m curious what happens with =7 (hard coded value), but not enough as to open the computer. It will have to wait till tomorrow Yup, it doesn’t appear in the Name Box like the labels that are cell references. I can see the benefit of having universal constants “protected” in Name Manager rather than at large in a sheet. I guess it has advantages and disadvantages like most things in life :))
Yes, after the Mars Climate Observer became the Mars Crustal Observer due to a units conversion error, I replaced hardcoded values with named conversions. I have a sheet named Conversions with entries like this: Basic conversions: Factor Value Atm2pa 101325.0 atm2psi 14.6960 btu2j 1055.1 cal2J 4.1868 F2R 459.6699607 ft2m 0.3048 lb2kg 0.4536 psi2pa 6894.757 The values become named variables that can be used in formulas and I can then do complex conversions like: Enthalpy 5.000 Btu/lb 11.63 kJ/kg where the 11.63 value is =B33*btu2j/lb2kg/1000 Thus the conversions are documented in the formulas.
It's not much, but I wanted to send a small token of my appreciation. Thank you so much for what you do. Your videos are very helpful. I hope you find even more success in the future!
Epic as always! I do have one thing to add on the edit links issue: If you "move or copy" a protected worksheet over to a new workbook, you won't be able to change the source to the destination workbook until you unprotect the worksheet. So if anyone runs into this and panics when the "Change Source" button is grayed out, just unlock the worksheet and carry on... *IF, the worksheet you want to copy is protected and you don't know the password, just select the entire worksheet in the source file, then copy/paste all of it into a blank worksheet in the destination file, you can use Paste Special to paste formats, formulas, column widths, etc. to get an unlocked identical version of the locked source worksheet. Then Edit Links/Change Source, and you're good to go in that case as well. Also, if you get plagued by external links that you just can't seem to find, check into your Data Validation Drop Down lists and look for references to external sources. I've run into that one often too.
Center across selection?!! I never knew that was there! I'm definitely going to be using that now, thanks Leila! :) Good tip about not using hard-coded numbers. In programming those are called "'magic numbers" and it's considered bad practice.
I absolutely despise merged cells. When Excel added that feature, I had to add a macro button for the center across cells function, since I use it so frequently.
Check out these videos on my channel 👇👇 Thermometer Graph ua-cam.com/video/oFw_EQuJMww/v-deo.html Database Functions (DCOUNT, DSUM, DAVERAGE) ua-cam.com/video/ahETzqXk8QU/v-deo.html Excel Shortcut Keys (Urdu / Hindi) ua-cam.com/video/EcZdUZMHjY8/v-deo.html Spell Number - Convert Numbers To Text In Excel ua-cam.com/video/p95xplEadq0/v-deo.html Settling Invoices against payment using solver ua-cam.com/video/a7ZvXYOCDUc/v-deo.html Quarterly Sum using Offset Function ua-cam.com/video/fAMXOxhxDsw/v-deo.html One Minute Learning Series Column Charts ua-cam.com/users/shortsxqnt_gFJGl8?feature=share Remove Excel Blanks ua-cam.com/video/kWApMQ64i8Y/v-deo.html Autofilter Hack ua-cam.com/video/s0zFJniAygg/v-deo.html Sumif ua-cam.com/video/dSZ5LdcU-sA/v-deo.html
As a Financial Professional I spend a lot of time in Excel, and while I use many excel features, learning tips or things I just never knew existed but find useful is so helpful. The simple text fill and justify tab will save me so much work, and it will make Notations on the bottom of reports so much easier. Thank you so much.
I am the only person in my company with any prior IT experience and everything you mention here, as well as many other basic problems, I come across daily. It's seems to be a never ending task adjusting and correcting all these sheets. Other sheets I refuse to touch as they have become so complex over the years it is easier to just start again with a blank page. I wish I could get everyone in the company to spend a couple of hours a week watching your channel. I watch a few now and then and manage to pick up a gem or two I didn't know. Keep up the good work there are plenty of people who benefit from your knowledge and generosity.
Although I retired after 40 year career in Marine Engineering, I love to learn as well as I love Excel! I wish I had this channel when I was working but I’m glad I found it now. Keeping up with the Kual kids now!
One 'new' item for me, i.e. checking for external links, and 6 helpful 'reminders' ... for a total of 7 gems ... 👍👍 Thank you ... thank you ... thank you ....
I've been using Excel for decades,and usually think "there can't be anything new here to me!". And yet, you always manage to highlight features I was completely unaware of. Keep up the amazing work.
Have learned it by trial and error and feel that people who are watching this video are lucky as they get readymade intelligence. Thanks, Leila for making me think in Excel.
When I look at your video thumbnails and the titles, I click without any expectations thinking that "I probably know it all" attitude. But you smash my attitude with your content every time. I pick up at least one thing from your videos even if it is a simple topic or the tasks that I do on a day to day basis. Thank you @Leila Gharani. Truly appreciate it.
I watched the video expecting to get great tips, which was correct. Some I did know, some not. Yet the most valuable thing to be was quickly referenced in the third tip when you double clicked format painter to apply formatting across multiple locations. I had no clue that existed. So I got 8 tips instead of 7. Thanks Leila.
Thank you for the link feature. I actually manually amended all the formulas when copying from one workbook into another! Thank you so much for teaching this one.
Hi. I'm considered an expert by my colleagues but I know I have so much to learn. Excel is such an awesome program. Everytime I have a challenge I say I have to ask Leila! Thanks so much for your excellent videos!
RE hardcoding values, another tip is to use consistent cell styles (home>styles). I'm "forcing" myself, colleagues, and clients to always format cells according to whether it's data input, calculation, or comments to help distinguish between hard-coded input and soft / calculated values. Makes the sheet also visually easier to understand.
The external links issued has bothered me for five years, I liked the other selections and use many of them but being able to update the external links is a great help.
Also, for variable, instead of referencing using an abolute cell reference, there is the option to Name the Cell whcich can also help clarify what variables are in a formula.
It can also be useful to name constants, so people looking at the formula can follow your thinking. And it will reduce the possibility of a constant being mistyped if used in multiple places.
Fabulous video as always. Kudos Dear Leila! You are definitely the Angel Queen of Spreadsheets. Two alternative, albeit less elegant, but perhaps slightly faster approaches for the first two things done wrong in Excel. *(1) External Links* :: Press *CTRL+H* to open the Find & Replace window, and replace all instances of _[Start.xlsx]_ with _just nothing._ Don't forget to choose "Look in Formulas" in the last dropdown list of the Find and Replace window. *(2) Manually Adjusting Text in Cells* :: While editing the long text spanning multiple columns so as to break it into multiple lines, press *ALT + ENTER* as many times to insert as many line breaks within the text as needed.
The fixing of a group of cell within a formula is life changing for me. I knew I needed to do that, but didn't realize there was a shortcut key for it.
Leila, I was taught not to merge cells years ago, 2006 maybe. Ever since, I've despised those that would do it. I have to figure out how to get all those that do that to come to your site. Then you go one step further and teach me how to find those pesky things. This is outstanding!!!
I wish that the guys from Sage Cloud would watch these videos. Their exports to Excel are a pain. Quickbooks is a bit better but their Excel exports still take a lot of cleaning up.
Referring to tip#6: “hardcoded” values An even better idea is to put such a value (usually a parameter, such as: VAT, discount factor, local tax etc.) into an outstanding cell at the top of the worksheet [with its description] so that it can be easily seen and easily modified. This, of course, is not applicable in cases where you have many parameters on the same sheet. Unfortunately, there's no method to make sure that this parameter is being used in all the formulae in which it was meant to be used.
Good tips. I love to use table, but sometimes it seemed to slow down the process when you have a more complicated formulas. Also, I never understand the concept of grouping. Can you do a video of grouping? What’s the purpose it? Thanks.
If you've ever used the Subtotals feature, the end result of that one is to create something like groups and subgroups for the data, allowing you to toggle between viewing the detailed entries within the data and viewing just the category totals (which an SQL user might call "rollups"). Groups work similarly, except they don't have the extra row, they just allows you to toggle visibility of data. (while leaving a visible marker on the side, as the video shows.)
@3:45 WAIT WHAT!!!! I've merged cells, I've ALT-ENTERed, but I've never used FILL&Justify. TY TY TY. Signed up for your courses and can't wait to dig in!!! LOL, the Center Across Selection is another great tip. That "can't perform on merged cells" always IRKED me.
It's always a delight to watch your creations! The content depth and the speed of how you go about things seem to always match my expectations and information intake capabilities 😊
Some great advice there, Leila. Regarding external links - they are annoying to work with! The biggest challenge is breaking them from the Edit Links window. Sometimes it’s impossible to find every last trace of them in a workbook. It’s a shame that clicking Break Link doesn’t do that for you!
At times, some external references exist in Name manager as well. Delete references from Name Manager (Ctrl F3 is shortcut) and your links would break easily after that.
When it comes to hardcoding - ofcourse never within a formula, and it is also easy to spot which cells are a hardcoded number and which are the outcome of a formula by making a hardcoded input number blue compared to the standard black print.
I'm retired now and still find your videos amazingly useful. Where were you when I was starting my career?! I shutter to think of how much time & frustration I would have saved by knowing your tips. Ugh. Thanks.
That channel is one of my favourites and certainly one of the best in excel learning and improvement. Your presentation is awesome. Thank you very much and may God bless you
Leila .. You are doing a wonderful job of educating us all. Thank you. 10 years ago I was teaching Excel at night school. it's amazing how much they have sneaked into the product since then (that I hadn't noticed). I thought I knew a lot - wrong! I went through your 7 missing things. Numbers 2,3,5,7. were news to me. When tables were added I did not bother much as I had found ways to get round some of the difficulties. I now appreciate them more. I think there was one tiny inaccuracy in video - if you don't use tables, charts will update automatically as long as you take care to look out for the bounds of the range. That said, your videos are wonderful. Thankyou ! Thankyou ! Thankyou !
Best tip from this video: F4 for stepping through the options for locking a reference. Brilliant. Used Excel since forever, never seen that one before.
Thank you for sharing these tips-I am almost entirely self-taught and I am NO power user. All my best stuff I got from my wife. Now I know that I can refer to your channel and courses to get, like, really good at excel.
I really like the tip about searching for formatting using Find/Find Next/Find All... we normally think about searching for the content so this is a creative alternate way to think about the workbook content.
5:00 I saw an old advert for Excel from the 1980s where the guy was doing his last minute presentation inside an elevator and he did a "center across selection" action with a click of a single button, rather than having to find the option that is buried deep inside a pop-up. Go figures MS decided the feature is too obscure to warrant a button these days, thereby encouring users to do it the wrong way by merging cells just to get texts to center.
Every time I watch one of these videos, I have *at least* one moment where I gasp and start thinking about how I can use what I’ve just learned. Thank you!
Having to manually adjust the references to the former file I thought was quick, but Edit Links is so much easier and no risk of missing a reference. Tyvm
I took an Excel course in the mid 90's. I thought I was pretty good at excel until I started watching your videos. So much new that I did not know. Could you make a video about the most important changes in Excel in the last 20 years
People take this that I know Excel very well, But they won’t listen when I tell them, “Don’t ask me. Ask Leila, by watching her video on the subject.” 🤷🏼
Center across selection is one I'd never noticed, and it's going to make a few of my employer's spreadsheets so much cleaner once I implement it. Thanks!
I clicked this and was fully captivated & amazed by #2 Merge Cells-Center Across Cells is a game-changer. Who knew there was so much room for improvement with excel basics?! 🤷🏽♂️ Leila knew 😊
This made me so happy because you just confirmed that I do best practices! On my personal ribbon bar called "go to work" which I run anytime someone sends me a new workbook. It basically eliminates all the bad practices you pointed out. I wish they would just eliminate center&merge from the tool bar. As a keyboard guy it is pure evil.
Thank you very much. Suggestion for next tutorial: Automatically update control chart when there is input data, and when the input is specifically on time and date.
Get your own copy of our Excel mistakes Cheat Sheet here 👉 pages.xelplus.com/excel-mistakes-cheat-sheet
I am a self taught Excel user. I knew that I wasn’t taking advantage of Excel like I should, but after finding your channel I realize that I have a lot to learn. I find your teaching technique and examples are very good and I plan to go back in your lessons and relearn using the tips you provide..great job thanks!
Same here:)
Me too. 😅
Even if I already know 90% of the material, the 10% new things I learn are always fantastic. Thanks Leila
Same! The center across colunms, and justify fill for text are going to be two nifty tricks that are going to help me take my sheets to another level
problem is the merged cells with the year dates in them should never have been unmerged and she never have to select across years ... she should be using cell ranges for the data ... as it is display page she was on NOT a calculation page ... she messed up big time ... excel first and foremost is a database ... thats right folks a database ... not a spreedsheet ... thats the maths you do on the database of information you have entered into the spreedsheet ... most of what she calls wrong is actually just NOT the way she was taught ... she never learned the old fashioned hand made spreedsheets and accounting books ... and so has no background in WHY things are done differently than the way she understands them ...
I agree.
i can bet that there is more really advanced excel users watching Leila's videos than those who just think they are advanced ;)
You absolutely make my excel experience WAY BETTER on each video you upload every week!
I'm very greatful dear Leila.
Hi Leila, how do you create the Selection in the dashboard that is showed in you explanation about Groupy lines? 8:00
Mohammad stop speaking to women it is haram
@@felipesignorellireis7839 at 8:42 you see a "1" in cell C21, which changes as she changes the selection in the dashboard. She probably manually added a radio button with the options and linked it to cell C21, with a dynamic array formula (FILTER) just below it. See this other video of hers: ua-cam.com/video/oSTgFUiRDGI/v-deo.html
@@fstorino ah ok. Thanks my friend. I will see it.
The external links issue has plagued me for years. Thank you for clarifying how this happens, how to prevent it and how to correct it. This is why I subscribed to your channel.
Thanks William!
I had a similar problem but when I go to break the external link I get errors as it cannot find the original document! I have a feeling I need to rebuild sheet from stratch!
@@markward8483 worksheet where the link is found is probably hidden
This is a big headache, MS is acting like terrorist when we do NOT want external links to be created in the first place. So If anyone needs external links to be created , then make it from paste drop down menu ... but hey, why would MS do that,right? they cant even make it simple to implement CTRL+SHIFT+V for PASTE AS TEXT feature .... google has it for years. crap
I also had similar issues which I tended to resolve by global search/replace. This is a much more elegant solution; thank you Leila, especially as it also catches the remote links in conditional formatting which is notoriously difficult to find without resorting to some sort of VB routine.
Hi Leila, I'd like to thank you for all the material you provide us! I've started watching your videos in 2017 and took the Unlock Excel VBA and Macros course when I was still an Electrical Engineering undergraduate. The skills I've learned with you helped me a lot during my internship, and today I work as a planning engineer in a huge utility company in my country (Brazil). Your courses surely have been a great part of my success. Congratulations for your excel!ent work and thank you very much!
I have never used the justify to spill text over cells - great tip. Many thanks Leila 👍
My pleasure!
@@LeilaGharani I feel like the job market for this is over saturated when I look at how many followers you have now 🤣🤣🤣🤣
Agreed...the other tips are great but are known by people that have used Excel for many years. I've always done that manually....not any more!
Thanks Leila, great selection of common mistakes. I am waiting for part 2 of this video.
This was a great tip indeed. Solved a huge problem for me.
Simply put in two words... ABSOLUTELY BRILLIANT. Many thanks from Johannesburg, South Africa
You are just wonderful Leila. Your calm and affectionate voice makes it more easier to understand. Thank you so much.
Although an extensive Excel user, I've learned a lot from your videos. Thanks Finally have something to contribute. Hidden numbers. Your comments are great. One additional - Name the cell that you will use in multiple cells. In your example, name 8% "InterestRate". When you include it in cells, it already is fixed with '$'. Also, you mention using 7 or 24 is okay. Those are still "magic numbers." Why 7? Instead, use Name Manager, define "DaysOfTheWeek" as "=7" Now your formula is divided by "DaysOfTheWeek" making it very clear what the 7 meant.
Thanks for sharing!
Great comment! Thank you
Somehow I prefer to also put the labels in a sheet 🤷♀️
Now I’m curious what happens with =7 (hard coded value), but not enough as to open the computer. It will have to wait till tomorrow
Yup, it doesn’t appear in the Name Box like the labels that are cell references.
I can see the benefit of having universal constants “protected” in Name Manager rather than at large in a sheet.
I guess it has advantages and disadvantages like most things in life :))
Yes, after the Mars Climate Observer became the Mars Crustal Observer due to a units conversion error, I replaced hardcoded values with named conversions.
I have a sheet named Conversions with entries like this:
Basic conversions:
Factor Value
Atm2pa 101325.0
atm2psi 14.6960
btu2j 1055.1
cal2J 4.1868
F2R 459.6699607
ft2m 0.3048
lb2kg 0.4536
psi2pa 6894.757
The values become named variables that can be used in formulas and I can then do complex conversions like:
Enthalpy 5.000 Btu/lb 11.63 kJ/kg
where the 11.63 value is =B33*btu2j/lb2kg/1000
Thus the conversions are documented in the formulas.
It's not much, but I wanted to send a small token of my appreciation. Thank you so much for what you do. Your videos are very helpful. I hope you find even more success in the future!
Wow, thank you!
Epic as always!
I do have one thing to add on the edit links issue: If you "move or copy" a protected worksheet over to a new workbook, you won't be able to change the source to the destination workbook until you unprotect the worksheet. So if anyone runs into this and panics when the "Change Source" button is grayed out, just unlock the worksheet and carry on...
*IF, the worksheet you want to copy is protected and you don't know the password, just select the entire worksheet in the source file, then copy/paste all of it into a blank worksheet in the destination file, you can use Paste Special to paste formats, formulas, column widths, etc. to get an unlocked identical version of the locked source worksheet. Then Edit Links/Change Source, and you're good to go in that case as well.
Also, if you get plagued by external links that you just can't seem to find, check into your Data Validation Drop Down lists and look for references to external sources. I've run into that one often too.
This is brilliant! These Excel tips are in the top, not many creators of content put them. Thank you Leila!
Center across selection?!! I never knew that was there! I'm definitely going to be using that now, thanks Leila! :)
Good tip about not using hard-coded numbers. In programming those are called "'magic numbers" and it's considered bad practice.
I absolutely despise merged cells. When Excel added that feature, I had to add a macro button for the center across cells function, since I use it so frequently.
Wish this video was longer. Hope to see more of what we doing wrong in part two.
Check out these videos on my channel 👇👇
Thermometer Graph
ua-cam.com/video/oFw_EQuJMww/v-deo.html
Database Functions (DCOUNT, DSUM, DAVERAGE)
ua-cam.com/video/ahETzqXk8QU/v-deo.html
Excel Shortcut Keys (Urdu / Hindi)
ua-cam.com/video/EcZdUZMHjY8/v-deo.html
Spell Number - Convert Numbers To Text In Excel
ua-cam.com/video/p95xplEadq0/v-deo.html
Settling Invoices against payment using solver
ua-cam.com/video/a7ZvXYOCDUc/v-deo.html
Quarterly Sum using Offset Function
ua-cam.com/video/fAMXOxhxDsw/v-deo.html
One Minute Learning Series
Column Charts
ua-cam.com/users/shortsxqnt_gFJGl8?feature=share
Remove Excel Blanks
ua-cam.com/video/kWApMQ64i8Y/v-deo.html
Autofilter Hack
ua-cam.com/video/s0zFJniAygg/v-deo.html
Sumif
ua-cam.com/video/dSZ5LdcU-sA/v-deo.html
I'm just flattered you think I'm only doing 7 things wrong
😂😂😂😂She thinks highly of us.
As a Financial Professional I spend a lot of time in Excel, and while I use many excel features, learning tips or things I just never knew existed but find useful is so helpful. The simple text fill and justify tab will save me so much work, and it will make Notations on the bottom of reports so much easier. Thank you so much.
I am the only person in my company with any prior IT experience and everything you mention here, as well as many other basic problems, I come across daily. It's seems to be a never ending task adjusting and correcting all these sheets. Other sheets I refuse to touch as they have become so complex over the years it is easier to just start again with a blank page. I wish I could get everyone in the company to spend a couple of hours a week watching your channel. I watch a few now and then and manage to pick up a gem or two I didn't know. Keep up the good work there are plenty of people who benefit from your knowledge and generosity.
Although I retired after 40 year career in Marine Engineering, I love to learn as well as I love Excel! I wish I had this channel when I was working but I’m glad I found it now. Keeping up with the Kual kids now!
One 'new' item for me, i.e. checking for external links, and 6 helpful 'reminders' ... for a total of 7 gems ... 👍👍
Thank you ... thank you ... thank you ....
My pleasure, Chris. Hope they'll come in handy at some point.
I've been using Excel for decades,and usually think "there can't be anything new here to me!". And yet, you always manage to highlight features I was completely unaware of. Keep up the amazing work.
Have learned it by trial and error and feel that people who are watching this video are lucky as they get readymade intelligence. Thanks, Leila for making me think in Excel.
When I look at your video thumbnails and the titles, I click without any expectations thinking that "I probably know it all" attitude. But you smash my attitude with your content every time. I pick up at least one thing from your videos even if it is a simple topic or the tasks that I do on a day to day basis. Thank you @Leila Gharani. Truly appreciate it.
I watched the video expecting to get great tips, which was correct. Some I did know, some not.
Yet the most valuable thing to be was quickly referenced in the third tip when you double clicked format painter to apply formatting across multiple locations. I had no clue that existed. So I got 8 tips instead of 7. Thanks Leila.
Great to hear, Bob!
Thank you for the link feature. I actually manually amended all the formulas when copying from one workbook into another! Thank you so much for teaching this one.
Great hint about deleting table rows. I've been using Excel since 1993 (Ver.5) but still learning new things every day.
What you said, except back to Lotus.
Hi. I'm considered an expert by my colleagues but I know I have so much to learn. Excel is such an awesome program. Everytime I have a challenge I say I have to ask Leila! Thanks so much for your excellent videos!
RE hardcoding values, another tip is to use consistent cell styles (home>styles). I'm "forcing" myself, colleagues, and clients to always format cells according to whether it's data input, calculation, or comments to help distinguish between hard-coded input and soft / calculated values. Makes the sheet also visually easier to understand.
The external links issued has bothered me for five years, I liked the other selections and use many of them but being able to update the external links is a great help.
Also, for variable, instead of referencing using an abolute cell reference, there is the option to Name the Cell whcich can also help clarify what variables are in a formula.
It can also be useful to name constants, so people looking at the formula can follow your thinking. And it will reduce the possibility of a constant being mistyped if used in multiple places.
Thanks for sharing!
Fabulous video as always. Kudos Dear Leila! You are definitely the Angel Queen of Spreadsheets.
Two alternative, albeit less elegant, but perhaps slightly faster approaches for the first two things done wrong in Excel.
*(1) External Links* :: Press *CTRL+H* to open the Find & Replace window, and replace all instances of _[Start.xlsx]_ with _just nothing._ Don't forget to choose "Look in Formulas" in the last dropdown list of the Find and Replace window.
*(2) Manually Adjusting Text in Cells* :: While editing the long text spanning multiple columns so as to break it into multiple lines, press *ALT + ENTER* as many times to insert as many line breaks within the text as needed.
Thanks for sharing!
The fixing of a group of cell within a formula is life changing for me. I knew I needed to do that, but didn't realize there was a shortcut key for it.
That last one is one I try to tell people all the time. Tables in Excel are an absolute godsend. Great video!
Hi Leila. Could you do a tutorial about using Connections and specifically "to Access"?
Leila, I was taught not to merge cells years ago, 2006 maybe. Ever since, I've despised those that would do it. I have to figure out how to get all those that do that to come to your site. Then you go one step further and teach me how to find those pesky things. This is outstanding!!!
I wish that the guys from Sage Cloud would watch these videos. Their exports to Excel are a pain. Quickbooks is a bit better but their Excel exports still take a lot of cleaning up.
I had no idea the first three things existed, but I've been wanting them for years. Of course, now I have to go back and changed all my sheets.
😊
the first two examples is a life saver indeed....thanks leila!
Thank you Leila! You never fail to impress with every video by revealing something about Excel, that surprises us! Keep up the great work! 😊👍
Thank you! 😃
Same as Puerta de la Estrella: even thinking I knew a lot about excel, what you explained has improved my knowledge! Thank you so much!
Wow ! Working for years on Excel and this 7 tips blowned my mind 🤩 *THANK YOU*
You're very welcome!
Referring to tip#6: “hardcoded” values
An even better idea is to put such a value (usually a parameter, such as: VAT, discount factor, local tax etc.) into an outstanding cell at the top of the worksheet [with its description] so that it can be easily seen and easily modified.
This, of course, is not applicable in cases where you have many parameters on the same sheet.
Unfortunately, there's no method to make sure that this parameter is being used in all the formulae in which it was meant to be used.
Hey Leila, I'm a professional excel user! At least I thought as much, but now I'm enjoying your videos!
Good tips. I love to use table, but sometimes it seemed to slow down the process when you have a more complicated formulas. Also, I never understand the concept of grouping. Can you do a video of grouping? What’s the purpose it? Thanks.
If you've ever used the Subtotals feature, the end result of that one is to create something like groups and subgroups for the data, allowing you to toggle between viewing the detailed entries within the data and viewing just the category totals (which an SQL user might call "rollups"). Groups work similarly, except they don't have the extra row, they just allows you to toggle visibility of data. (while leaving a visible marker on the side, as the video shows.)
@3:45 WAIT WHAT!!!! I've merged cells, I've ALT-ENTERed, but I've never used FILL&Justify. TY TY TY. Signed up for your courses and can't wait to dig in!!! LOL, the Center Across Selection is another great tip. That "can't perform on merged cells" always IRKED me.
It's always a delight to watch your creations! The content depth and the speed of how you go about things seem to always match my expectations and information intake capabilities 😊
You are the best! Merged cells have annoyed me for years. And the benefits of using tables makes more sense. Thank you.
Some great advice there, Leila.
Regarding external links - they are annoying to work with! The biggest challenge is breaking them from the Edit Links window. Sometimes it’s impossible to find every last trace of them in a workbook. It’s a shame that clicking Break Link doesn’t do that for you!
True. Sometimes you get a lot of them hidden too in name manager. These can also end up making the workbook slower....
At times, some external references exist in Name manager as well. Delete references from Name Manager (Ctrl F3 is shortcut) and your links would break easily after that.
This is one of the best Excel resources. Excel is immensely powerful and there still so many things to improve productivity.
So true. There's always more to learn.
Very useful video Leila, now I can´t see any reason to use "merge" function anymore...can you any? Thanks!
Agreed I wish they would replace the merge button.
Really love the "formatting as table"! Awesome!
When it comes to hardcoding - ofcourse never within a formula, and it is also easy to spot which cells are a hardcoded number and which are the outcome of a formula by making a hardcoded input number blue compared to the standard black print.
I'm retired now and still find your videos amazingly useful. Where were you when I was starting my career?! I shutter to think of how much time & frustration I would have saved by knowing your tips. Ugh. Thanks.
Leila is the best as always! I did not know about the Fill/Justify and the Center Across Selection trick.
That channel is one of my favourites and certainly one of the best in excel learning and improvement. Your presentation is awesome. Thank you very much and may God bless you
Great things. First time, I have started understanding tables. I still do a lot of things manually
We need to protect this woman at all costs. She has such eloquently delivered knowledge! Thank you for the content!
I usually do not get much new info in Excel tutorials since I know stuff already, in yours I did. Well done. Useful tips!
The use of tables with charts - pure GOLD! Yes, I WAS doing it wrong before but not now.
Leila .. You are doing a wonderful job of educating us all. Thank you. 10 years ago I was teaching Excel at night school. it's amazing how much they have sneaked into the product since then (that I hadn't noticed). I thought I knew a lot - wrong! I went through your 7 missing things. Numbers 2,3,5,7. were news to me. When tables were added I did not bother much as I had found ways to get round some of the difficulties. I now appreciate them more. I think there was one tiny inaccuracy in video - if you don't use tables, charts will update automatically as long as you take care to look out for the bounds of the range. That said, your videos are wonderful. Thankyou ! Thankyou ! Thankyou !
Best tip from this video: F4 for stepping through the options for locking a reference. Brilliant. Used Excel since forever, never seen that one before.
Thank you for sharing these tips-I am almost entirely self-taught and I am NO power user. All my best stuff I got from my wife. Now I know that I can refer to your channel and courses to get, like, really good at excel.
I really like the tip about searching for formatting using Find/Find Next/Find All... we normally think about searching for the content so this is a creative alternate way to think about the workbook content.
5:00 I saw an old advert for Excel from the 1980s where the guy was doing his last minute presentation inside an elevator and he did a "center across selection" action with a click of a single button, rather than having to find the option that is buried deep inside a pop-up. Go figures MS decided the feature is too obscure to warrant a button these days, thereby encouring users to do it the wrong way by merging cells just to get texts to center.
Great tips especially merging, justify and grouping. Thanks a lot Leila
Thanks for sharing this! And the spill over technique was gold! That is exactly what I needed to save me some time!
Happy to help!
Ctrl+Shift+Arrow is a new thing for me. Thanks Leila!
You’re maximising peoples skills and consequently, our earnings ! I’m so so grateful!!
Every video that you put up has taught me something useful that I did not know or appreciate fully before. So thanks Leila!
Every time I watch one of these videos, I have *at least* one moment where I gasp and start thinking about how I can use what I’ve just learned. Thank you!
I knew a number of those practices, but the ones I learned are invaluable. As always, a joy to learn from Leila.
Having to manually adjust the references to the former file I thought was quick, but Edit Links is so much easier and no risk of missing a reference. Tyvm
Good advice and good reminders. Avoiding cell merging and justify text were new tricks...great for an old dog
You always impress me. I wish I knew even a teaspoon worth of what you know in Excell. You are simply so knowledgeable.
Great, great video and tips, as always! Thank you Leila!!
Man, i love working with Excel, especially learning from leila. Well explained, easy to understand.
Knew many of these but the center across selection is new. Thanks for the tips.
I took an Excel course in the mid 90's. I thought I was pretty good at excel until I started watching your videos. So much new that I did not know. Could you make a video about the most important changes in Excel in the last 20 years
People take this that I know Excel very well, But they won’t listen when I tell them, “Don’t ask me. Ask Leila, by watching her video on the subject.” 🤷🏼
You are the best Leila. I've been looking for trace dependence for a long time. you just saved my life.👍
Happy to help!
Thanks for our kind help Leila. I 've learned a lot of new things about excel.
Center across selection is one I'd never noticed, and it's going to make a few of my employer's spreadsheets so much cleaner once I implement it. Thanks!
Glad it was helpful!
Thanks a lot! Every single thing in this video has been extremely helpful. Greetings from Argentina!
wonderful. thanks for sharing. liked your "cell justify" tips.
Thanks!
Thank you Patrick!
Leila - All I want to say is love you to the moon and back - Your videos are like meant perfectly for me - always give me solution to a probelm
Whoosh. Completely over my head!
You are very very good. You have my admiration.
Great tip about generating chart from table so the chart range updates automatically, thanks!
Good and useful tips here. A couple of things I didn't know about. Thanks.
Great tips. I absolutely found the external links useful among others you've shown.
I clicked this and was fully captivated & amazed by #2 Merge Cells-Center Across Cells is a game-changer. Who knew there was so much room for improvement with excel basics?! 🤷🏽♂️ Leila knew 😊
Glad you found something helpful!
This made me so happy because you just confirmed that I do best practices! On my personal ribbon bar called "go to work" which I run anytime someone sends me a new workbook. It basically eliminates all the bad practices you pointed out. I wish they would just eliminate center&merge from the tool bar. As a keyboard guy it is pure evil.
"I wish they would just eliminate center&merge from the tool bar." I totally agree !!!! :)
Leila, I never fail to pick up tips from your presentations. They are excellent.
Thank you for taking the time to make them.
Use tables every day. The justify and center across cells is a new one. That will help me eliminate additional 'pretty' sheets. Thanks!
Great stuff. Thank you Leila. Next video ideas: 1) naming ranges, 2) iferror
Thanks for the suggestions!
Thanks Leila, every time you make a video I learn something!!! 👍
Thanks for another excellent video
Particularly about tables - I never knew how these worked and I'll find them so useful now.
Thanks very much for these tips Leila! Glad to find a community of Excel enthusiast like I am.
We are lucky to have you!! Thank for making our work easier😊
Simple and dirty tips I was not aware of. It should be obvious sometimes you’d think, until you put it into prospective. Thanks @Leila!
My pleasure :)
Thank you very much.
Suggestion for next tutorial: Automatically update control chart when there is input data, and when the input is specifically on time and date.
My all time best teacher. Seeking to learn Dax now from you