Hello everyone and welcome to the engineering tool box channel where I give you the tools you need to solve real-world engineering problems now 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