In this video I’m going to show you my top 17 tips and tricks for Microsoft Excel These are tips that I promise will help you be more productive and get the most out of this great software. Let’s check them out Before we get started let’s take a look at our list so first We’re gonna be going through tables then using the control arrow key quick fill quick select copy enter advanced pasting techniques like transposed Formatting numbers as text in order to show leading zeros for example multi-role insert fit columns to page print range freeze pane grouping remove duplicates status bar opening multiple sessions or screens of excel at the same time macro recording and Custom toolbar and ribbon settings now there’s a lot of good stuff in here, so I think this is going to be fun Let’s dive in Tables this one’s one of my favorite so to give us more control over a data range especially long lists where we’re gonna be entering Data into a lot we can go to the insert tab insert a table and we’ll say yes My table has headers already and it’ll create a table for us and what that does Right away off the bat is we have banded columns so we can separate rows easily it also gives us these filters where we can sort and filter data And then it also gives us some cool control over the columns so we can actually it’s move columns around do that kind of thing And then we can easily add columns here, so Let’s say this would be 8 and Let’s say we want to add a formula to this column If we just enter in our formula And hit enter it automatically fills that formula down and then check this out if I am going along And I’m entering data, and then I get to the last column in my table, and I hit tab it’ll automatically jump down and create a new row here and Let’s just finish this out.
Just to show you how this works And then watch this now sixteen And it automatically calculates because that formula will always Populate as we add rows to this table so tables are one of my favorites. There’s a lot of cool functionality there Say we have a long list and we want to jump right down to the bottom While we can just hold ctrl and then press down on the arrow pad and it’ll jump down to the bottom of that list or The last cell with a value in it we can hit ctrl up then to go to the top of the list Control right to jump to the end, and if we really wanted to we can hit control right again And it’ll take us to the very last column in Excel and then if I go down into the left.
It’ll jump back to The next cell that it finds with a value in it so it’s a really quick way to jump around Most people when they want to fill a series down they’ll take this bar and click and drag And there’s nothing wrong with that But let’s say we have a really long list and we just want to populate this and Fill it all the way down to the bottom of the list well if we just find this little corner in the bottom right of The selected range and then we double click that it’ll actually fill that all the way down Hit ctrl down and it’ll fill that all the way down to the last Value that it finds to the column to the left So let’s say we want to select all the data within this data region Maybe we want to take it and copy and paste it somewhere else we can just hold ctrl and press a and It’ll select all the data within this data cluster kind of region right here But just notice that it doesn’t grab this out here now another way we could have done this similar to the control arrow key method before How we can jump around like that we can hold ctrl + shift and it’ll select the range between the two cells that have moved from so if we do Ctrl shift arrow right key and then ctrl shift Arrow down key now we have that whole range selected as well One cool little trick is copy and pasting in Excel if we hit ctrl C to copy that then we go over to another cell and Actually, just hit enter it’ll actually paste that there for us We don’t have to hit ctrl V or right-click paste, so just another way some people prefer that method If we wanted to copy this let’s say we hit ctrl C We want to Look at what other options Excel has for pasting if we go to this paste special and look at all the different options they have we can paste the formulas we can place formulas and number formatting Keep the source formatting all of these different things paste the values only Paste as links all those different things But there’s a one particular one in this case that I want to do and that is going to be a paste Transpose and check that out so we can take vertically orientated data And then transpose it to horizontally so that can be very useful at times So excel it does a really good job for the most part of formatting the types of data that we’re entering into cells Correctly, but sometimes we run into issues where it actually tries to format the data for us But we don’t want it to format in that way one really common place that I see this is when we’re entering Numbers that we want to behave like text so a lot of times it’ll be with leading zeros So if we hit enter there it takes those leading zeros off So what we can do is if we double click in the cell and put just an apostrophe in front of the numbers that we’re trying to type and hit enter it won’t show that Apostrophe and it’ll actually give us a warning here saying This is a number stored as text And that’s exactly what we want it to do we want that to be a text value We don’t need it to be a number so sometimes on these longer part numbers.
What happens is Excel will actually format it as a scientific Notation like that so again same thing we can just come up here add an apostrophe and then it will actually Format that as text for us then Most people know that you can click on a row and right-click it ends I don’t think everybody knows that you can actually select the number of rows that you want to insert Then right click, and it’ll actually insert that number of rows So I have a problem I want all of these columns to print on one page and you can see that the page line runs right there And if we look in the print preview we can see that there’s actually two pages It’s cutting that last column off and now a lot of times I see people trying to scrunch down columns to get things a fit and doing that whole number But we don’t have to do that we can actually just go in to our print tab here and then down in the scaling settings we can just Fit all columns on one page, and that’s just a quick way It’ll shrink it down so that all of the columns will show on one page Say we only want to print a certain region, maybe we will just want to print these first five columns Well, we can tell Excel to only print those columns by selecting the print area so if we set the print area to that range And then go to our print preview We can see that only those columns or that range that we selected shows up in the Print Preview When we have large lists sometimes it can be hard to keep track of the column headers and To know what data we’re looking at when? We’re down farther in the list so what we can do is add a freeze pane so that the top column is always showing and There’s two ways to do this so if we go to the View tab and then we hit freeze pane We can just select freeze top row and that’ll freeze the top row right away the other way to do it is To let’s say we wanted to freeze the top two rows we can select the row below The row that we want to freeze, and then we hit freeze panes there, and then we can see that those rows are then locked Let’s say we want to make it really easy to show and hide data in our spreadsheets Maybe for some kind of report like this we want to be able to hide the week values and only show the total monthly Sales numbers so if we go to the data tab and then find this group button, and we hit group.
It’ll add this little Collapsible tool on the side where if we hit the minus button it’ll actually hide those rows for us And then we can expand them later And then the one button up here is collapsed all and the two is expand all so if we had multiple groups We could collapse or expand all of those buttons so a pretty cool way to hide and show data there So notice I have duplicated data in each of my columns here So in my first column that one two three four one two three four and let’s say I don’t want any Rows to repeat with that first number in this one column Repeating so I can actually select my data range here, and then go to data remove duplicates But I only want to remove Or I only want it to look in the first column this column one for duplicates And then delete the rows if there is a duplicate if I were to select another Column header here.
It wouldn’t Delete anything because it’s looking to both those columns for duplicates So there can’t be a duplicate of this column and this column together Hard to explain. I hope that makes sense but basically this is how it works I select the column with the duplicates that I want to look at I hit OK and Then it says we found four duplicates, and we deleted the rest So if you’re somebody that likes to use the status bar to see quick Information about certain data ranges we can select arrange And it’ll actually give us the average to count the sum down here, but we can also right-click and add Things to that so we can see the max the min So it’s almost like we get all of our descriptive statistics right down here, which is pretty cool So it’s not uncommon that we want to be able to view two Spreadsheets at the same time and the problem is if we goal and we right-click here And maybe I want to pick one of my spreadsheets and a lot of the older versions of X I’m sixteen right now but on the older versions a lot of them will actually open up that file within the same session of Excel and It won’t allow you to view them at the same time so what you can do instead is Open up another instance of Excel and then it allows you to then dock both Spreadsheets at the same time, or if you have two monitors, you can also dock them on the separate monitors then If you find yourself doing a lot of repetitive tasks in Excel then definitely check out macros or VBA coding and Just as a quick example if we go down on this bottom left hand corner We can see this macro button and if we click that it’ll prompt us to enter a new macro name, and then if we just give it a generic name and hit OK and Is now recording our actions so if we go and type in 1 2 3 and? Then we hit the stop button, and then we go to the View tab and let’s just delete this and then Go to view macros test select our test and hit run We can see that it repeats the exact steps that we just did and that’s just a super super simplistic example There’s tons of stuff you can do with this so if you want to see more on that I definitely plan on having a lot Of videos on that topic on my channel so check them all If you find yourself using the same commands all the time There’s some customizations we can do to the quick access toolbar so this up here or any of the tabs here we can edit as well, so if we go up here and We go to more commands We can actually edit what buttons show on that quick access toolbar and since we just made our first macro Let’s go ahead and add that macro button up to our quick access toolbar so if we select this drop down hit macros Select our test macro and add that hit ok now We have a button available for us, and it’ll actually run that mackerel anytime we select that button Now there’s also a ton of other tools in there so make sure to check them out You can go in here and kind of filter by different topics you can go all commands and see whoops, sorry it’s got a low dollar, and you can see all the different things that you could go and add to the quick access toolbar you can also go to this customize ribbon where you can look at all the tabs that are available to you and hide them show them Edit what’s in them add things to different tabs create your own tab even Lots of different stuff to play around with there so check that out Alright, I hope you all enjoyed that one to get more videos as they are released make sure to hit that subscribe button if you Like this video make sure to like and share And if you didn’t like it well then leave me a comment and let me know how I can improve Last but not least this channel is for you So if you have any suggestions for topics you want me to cover make sure to let me know.
Thanks for watching We’ll see you next time .
As found on Youtube