Ctrl and Enter in Excel - YOU NEED TO KNOW THIS! - Hit the ground running in Excel Series - Part 1

[Music] welcome to part one of hit the ground running in excel series if someone came up to me and said i don't know anything about excel i would like to learn excel this is exactly what i would tell him as the first piece of information about excel that i think everyone should know and the reason i showed you this who's that handsome fellow is because it's my chapter for this book i chose to write about control enter and for those who know me you know for me not to write about power query power pivot the new dynamic arrays the new data types that has to you know carry a lot of weight i truly believe that control enter is something if you're not using you should start using today and it will bring your excel game to a much higher level with the minimum of effort so without further ado let's jump into it most of the time in excel is spend entering data into cells into ranges into sheets and having said that usually when you try to enter something into a cell you do that by either pressing enter and what would happen is you would get that 100 in cell c4 right now and you would move one cell down right that's what enter does if you press shift enter you would move one cell up if you press the tab you would go one cell to the right and if you press shift tab you would go one cell to the left that's how usually people enter data sometimes they just click somewhere you should never do that but usually they stick to those four keyboard methods but there's another method that nobody's talking about and it is important because usually the scenario is like this you're trying to enter something into excel so you write 100 then you press enter and then you click on the same cell why well because now i want to format it and that is so redundant so i press enter to go away from that cell just to then click on that cell again because now i want to format it right and particularly if that is the next action that i do and there's got to be a better way and there is and that is where control enter number one comes in so the first thing i want you to know about control enter it is the way in excel to enter something into a cell and still keep that cell selected so if i wanted to input 100 into cell c4 and then format it as a currency what i would do is i would go 100 control enter and then control in dollar sign and i would get exactly what i need and that was all because of control enter because when i press ctrl enter the cell was still selected and i could just plow on so let's plow on now we're gonna go through the three examples that i wrote about in this book so the first example is i want to get today's date in the range of cells from a3 to a1000 so that's a lot of cells and this process is made possible by control enter and let's see how that is so this will be a three step process the first part is selecting this range and i'm going to do that by simply going to the name box and saying a3 column a 1000 press enter and that is exactly the selection that i get so a3 to a1 thousand step two simple one control and semicolon control and semicolon to get today's date into excel it's the easiest way of course you could write it by hand but control semicolon is a shortcut for doing that and now comes the critical part right i have the range selected and i have the today's date in the first cell now how will i enter this well if i press enter i would be very disappointed because this would happen and that's not what i want now how am i going to enter this well you've guessed it control enter and control enter has entered this into the whole selection so that's the first thing you should know about control enter so control enter enters data into all the selected cells and that is brilliant right and and extremely extremely powerful in excel okay moving on example two now example two is combining that fact that excel inputs data into all the selected cells with one of the best features in excel for selecting and that is the go to special command now the scenario we have is i have some numbers for this seven months over here and over here i'm calculating averages now averages are aware that there are empty cells here and not aware in a sense that they would pick up those cells but they just skip them but what if i wanted to actually put a zero value into these cells so all the blank cells instead of them being blank i would want to put a zero value into those cells let's see how we do that well first thing i would do is i would select the entire range because the go to special command as brilliant as it is it's always just reducing your selection to only the cells that fit a certain criteria now which criteria will that be well let's check it out f5 or ctrl g so the go to command and then you say special so the go to special command and what you say is i want you to reduce this whole selection to only cells that are blanks okay and there it is now how am i gonna input a zero value into all those cells well i press zero and i say control enter and there it is it's as simple as that so control enter truly a powerhouse in excel okay so let's go to the third and final example of control enter in excel and this one should blow your minds what i want to do is this i have a range of cells that i want calculated so here are prices in euro for 10 items i have the exchange rates for free currencies up here and now i want to calculate prices adjusted for those currencies so what i would do is i would select those cells and before i even write the formula let me just show you something let me show you that these sales are already pre-formatted do you see how the us dollar the swiss franc and the canadian dollar they all have their formats now the canadian dollar is the french one that has the dollar at the end just so you can differentiate between the us and the canadian in a sense that they are different formats and when it comes to pre-formatted cells in excel copying can be hard because if i just import a formula here and let me just do this quickly so let me say i'm gonna take this price in euros and i'm gonna multiply it by this price in dollars now the way i used absolute references in this formula means that i can copy it wherever i want but if i copy it to the right i'm gonna get the right result in a sense of the value of the cell but do you see how they're all us dollars now in a sense of format why well because when i copy in excel i copy not only the content of the cell but also the format of the cell and now let's see how we can make this 10 times better by simply using ctrl enter so what i would do is i would select those cells and then i would enter exactly the same formula so i would enter this formula but then go for the control enter and i want you to really really look at my result and notice that control enter has filled the entire range with my formula so it copied the formula to the entire range and yet all of my formats are untouched now isn't that one of the most brilliant things you ever saw and this concludes this video thank you for watching oh and one more thing if you like this video subscribe to this channel and you should totally buy this book it's full of useful tips and tricks like this one it was written by 24 authors so if you're not into one of those there's still 23 others you can read from and uh i'll leave the links down in the description because it's really worth it and i'll see you on the next video

Channel: Excel Olympics

Views: 12,103

Keywords: excel tutorial, microsoft excel tutorial, excel tips and tricks, microsoft excel tutorial for beginners, ctrl enter excel, ctrl enter not working in excel, ctrl enter shortcut excel, ctrl enter vs enter in excel, ctrl enter keycode, Hit the Ground Running in Excel, Excel Olympics, Gašper Kamenšek, Excel the Right Way, Enter Data in Excel, Basics in Excel, excel shortcuts and tricks, Excel shortcuts, Tips and Tricks in Excel, tips and tricks, Excel Olympics Channel

Id: Gm-dOxuYk64

Length: 10min 0sec (600 seconds)

Published: Tue May 04 2021