Now you can Subscribe using RSS

Submit your Email


Do More, With Windows

The Teacher

Office, For Work and Home

Microsoft Word
Microsoft Excel
Microsoft PowerPoint


Microsoft Excel 2016 Tutorial | How To Freeze Rows and Columns | Using F...

The Teacher
Hello Friends, and welcome again
to our channel, “The Teacher”. My name is Rajiv, and today, in this Excel
video, we will learn about using Freeze Panes in our Worksheets. While working
with worksheets with a large amount of data, you might fall into a very common
problem, and that is, when you scroll down to see other records in worksheet,
the top heading row is no longer visible then, and you might actually get lost
while searching for a particular set of record. What I need here is to, see the
top heading row all the time, doesn’t matter how far I scroll down through my
To Freeze a single or multiple
rows in this worksheet, we will use the Freeze Panes command from the View tab,
under Window section. When you open the Freeze Panes drop down menu, here you
have 3 different freeze option to choose from. As in this worksheet, I want to
Freeze the top heading row alone, then I will go for the 2nd option
i.e. “Freeze Top Row”, and as I click on this option, you can notice a dark
border is created underneath the first row, which indicates that the first row is
freeze now, and if I scroll down to see other records in my worksheet, the top
heading row will always be visible, which ultimately makes it easy, to find a
particular set of information.
When you need to unfreeze the
first row, just go to Freeze Panes section, and click Unfreeze Panes.
As with rows, you can also use
Freeze Panes for columns as well. For instance, in the second worksheet of this
workbook, which is Full Orders Summary, I have a large amount of records in
rows, and columns as well. What I need here is, I want to freeze the first
column, so I can match the Order ID with Product Category. But when I scroll
horizontally to see other columns in this worksheet, the first column is no
more visible. So, to freeze the first column here, I will use 3rd
option from Freeze Panes menu, which is, Freeze First Column. You can
immediately notice that, the border between column A and Column B get darker,
which indicates that the first column is freeze now, and if now I scroll
horizontally in this worksheet, I may still be able to see the first column.
You can Unfreeze the column
anytime, by clicking on Unfreeze Panes from the Freeze Panes drop down.
But it is not necessary that you
may always want to freeze the top row or first column. For instance, in the 3rd
worksheet of this workbook, you can see that here we have an additional heading
above the column headings, and if I just go and use Freeze First Row command,
then only the first row get freeze and the actual headings in the second row
that I want to freeze, will not be visible as I will scroll through this
worksheet. In this case, I want to freeze the first two rows of this worksheet.
So, to freeze more than one rows, first you need to consider the active cell
position, means what is your current position in the worksheet, because the
command that we are going to use now will be freezing all the rows above from
your current positions. As I want to freeze the first two rows of this
worksheet, so I will move and will set my active cell position on the first
column and third row, which is cell A3. Now to freeze the first two rows, I
will use the first command Freeze Panes, which actually freezes the columns and
rows according to the active cell position. So, as our active cell is A3, then
the Excel will only freeze the two rows above A3 alone.
Sometimes, you might want to
freeze more than one column and rows at the same time. Let’s unfreeze
everything first, and then look into this scenario. For instance, in this same
worksheet, I want to freeze the top two rows, and first three columns from the
left. This time again I will use the same command, and before I just go and use
the Freeze Panes, I will first change my active cell position. So, in this
case, it will be the 3rd row, and 4th column, and the
active cell position will be D3. Now, if I apply the Freeze Panes command, and
then according to the current active cell position, if I scroll vertically
then, the top two rows will always be visible, and if I scroll horizontally
through this worksheet, then the first 3 columns will always remain visible.
Anytime, when you no longer need
to freeze any column or row, just go and click Unfreeze Panes.
So, this is how you can use
Freeze Panes in worksheets with a large amount of data.

I hope you will find this video
useful. Do leave your suggestions and comments in the box below. Don’t forget
to hit the like button, and do subscribe to our channel for more upcoming
tutorial videos. Thanks for watching, and have a nice day!

Microsoft Excel 2016 Tutorial | How To Use Scroll Lock in Spreasheets

The Teacher
Hello everyone, and welcome to
our channel, “The Teacher”. My name is “Rajiv” and today we are going to learn
about the Scroll Lock Key. If you are not already familiar that what the Scroll
Key does, then here are some explanations about this Toggling Lock Key, just
like Caps or Num Lock key on your keyboard.
Scroll Lock is typically used in
Excel or other Spreadsheet programs, and honestly I have never seen or used it
in any other application. Before we go and start tapping Scroll Lock key, let
me explain you something.
Have you ever compared the
difference between scrolling your worksheet from the arrow keys of your
keyboard, and scrolling through vertical or horizontal scroll bars, or from the
Mouse Wheel, and if you have not, then let me clear you the difference first
before we start talking about the Scroll Key.
Notice, when I move or change my
Active Cell position by using the up, down or left, right arrow keys from the
keyboard, even though I am scrolling through the worksheet horizontally and
vertically, but the Active Cell is also getting changed. So, when you scroll
through Arrow Keys, your Active Cell, or you can say that the Cursor Position
changes accordingly. But when you scroll through Scroll Bars or through the
Mouse Wheel, your Cursor position or Active Cell remain the same, doesn’t
matter how far you scroll through your worksheet. You might be thinking that how
this is related to Scroll Lock? I will explain you this in brief.
First, you can find the Scroll
Lock key at the top right corner of your keyboard, usually between the Print
Screen and Pause Break keys, and if you are using a Notebook or a Tablet, then
they might don’t have a Scroll Lock key, then you can open On Screen Keyboard
from Ease of Access Center of Control Panel, or can directly type and search it
right from your Start Menu. From the On Screen Keyboard, you can find Scroll
Lock at the lower right corner with a short name SCRLK. Click it once will turn
ON the Scroll Lock, and then you can minimize the On Screen Keyboard, if you
don’t want to use it further.
You might not get confused that
either the Scroll Lock is ON or OFF, so you can right-click on the Status Bar
and then make a check mark on Scroll Lock. You will see an indication about the
Scroll Lock in your status bar.
So finally, when the Scroll Lock
is ON, and you move through your worksheet, using the Arrow Keys from your
keyboard, the Arrow Keys start behaving liking the Scroll Bars or Mouse Wheel,
and the current Active Cells remain the same as you scroll through your
When you need to go back to the
Active Cell or the same position where you were before in your worksheet, then
you don’t need to scroll back again, just hold down the Control Key and then
press Backspace, and in an instant you will be right back at the Active Cell.
This shortcut really saves a lot of time when you scroll through larger
worksheets, and this shortcut will also work if you are even scrolling through
Scroll Bars or Mouse Wheel. When you need to go back, press Control + Backspace
and that’s it.
When you no longer require to use
the Scroll Lock, turn it off my pressing it once again, and you can immediately
see the notification from the Status Bar has removed and now your cursor or
active cell will change as you will scroll through your worksheet using the
Arrow Keys.

So this was all about using
Scroll Lock Key, and if you found this video useful, then don’t forget to hit
the like button, and share this video with your friends. You can subscribe to
our channel to stay in touch. Thanks for watching, and we will meet in our next
video. Bye and take care!

Microsoft Excel 2016 Tutorial | Page Break Preview & Scale To Fit

The Teacher
Hello everyone, and welcome again
to our channel, “The Teacher”. In our previous video, which was about Page
Setup and Print Previewing in Excel, we had learned about, that how you can
manually configure Page Settings to adjust contents between the pages. As I had
said in the last video that, our next video will be about, how to use Page
Break Preview and Scale to Fit options. So, in this video, which is basically
covering the same concept as the previous video, we will first talk about using
Page Break Preview option.
When working with larger sheets,
you might consider seeing the Print Preview first, before you actually print
your worksheet, and that you can do from File Menu’s Print option, or you can
also press Control+F2 key to instantly see a preview.
But what happened is, when the
contents are not getting fit, especially when you want all the columns heading
to be printed on a single sheet, you have to manually adjust various page and
print settings, and during this you need to see the Print Preview again and
again, to check if the contents have been fitted or not.
So if you are thinking of a quick
possible solution to this, then you have the Page Break Preview option at the
lower right hand corner of your Status Bar. This option gives you an instant
preview right into your worksheet, and will give you a quick glimpse that what
will happen, if you just go and print this worksheet. As I scroll through this
worksheet, you can see watermarked page numbers over each page, explaining
which contents will be printed on what number of page. So it will not be a good
idea to print this worksheet right away, as certain columns are not getting fit
according to Page Layout that I want for my worksheet, and unnecessarily this worksheet
is expanding over to 8 pages.
In Page Break view, you can see
that your worksheet has been divided into two section, one with the dotted blue
line, and the other with a solid blue line. In Excel Terms, the dotted blue
line refers to the Scaling, and the solid blue line refers to Print Area, and
if you don’t have any idea about those terms, then I will explain them to you
in a minute.
Let’s first see, what these lines
does and how they may help you? Let’s say in a particular scenario, you might
not want to print a few columns in this worksheet. One approach for this is to,
Hide those columns that you don’t wish to print, simply by right-clicking on
column heading, and then click Hide from the context menu. The second approach
is to adjust the solid blue line to set the Print area. For instance, if I
don’t want to print the last Ship Date column, I can just drag and drop the
solid blue line before this column. That’s it, and this column will no longer
be available in printing.
As I said earlier, what the solid
blue line will does, it will set the Print Area of your worksheet, and if you
are interested in knowing that how Excel does this, then you can open the Page
Setup dialog box, and then click the Sheet tab. Here under the Print Area, you
can see a range of columns that will be printed. Before moving that solid blue
line, there was no value in this section. Let’s undo it for a second, and see
it again. So you can notice that when you move the solid blue line to remove
the last columns from the worksheet for printing purposes, Excel actually
adjust the Print Area in the background. But this option is best only for the
last columns while using Page Break Preview, but if you do not want to print a
column from the center of the worksheet, let’s say the Ship Mode column, then
you can go and hide that particular column. So, adjusting the solid blue line
is an easiest method to adjust the last columns of the worksheet for printing
Now, let’s talk about the dotted
blue line. As you can see that, still two columns are not getting fit over the
first page, which I want, and the Margins, Orientation, and Paper Size all are
fine. So what I can do here is, I can move this dotted blue and can extended it
over these columns. If I scroll over the worksheet now, then you can see that,
the total number pages now have reduced to 4 from 8, because all the headings
are properly fitted over a single page.
It is extremely important for you
to know that how Excel actually does this? Let’s undo this for a second, and
move the Page Layout tab again. Here do note down the current Scaling percent
under Scale to Fit. So as I move the dotted blue line over these two columns,
you can immediately notice that the scale percent is reduced to 84 percent. So
what the Scale actually does is, it reduces the font size and adjust the column
widths automatically to fit the contents to the worksheet. Do note that, it is
not the same, like if you do this for yourself. Like, if you select the entire
worksheet and then reduce the font size, then you have to manually adjust the
column widths, or you will require to use the Auto Fit Column Width command
from the Cells section of Home tab, and you have to repeat this until
everything fit nicely. Whereas, the Scaling does this for you automatically,
without requiring you run any additional command. Let’s undo all this, and move
back where we were before. So, if you want to quickly adjust the contents over
the page, then you can either adjust the dotted blue line, or you can also go
and can adjust the Scale manually for yourself.
As I said earlier, when the Scale
is reduced, it ultimately reduces the content size, so you must be aware that
how much scale size will be fine, so that after the print out the contents may
not look distorted, and you can have a little idea about this through the Print
You can also ask Excel to adjust
the Scale automatically, by telling Excel to fit the contents over one page
according to Width, by setting 1 Page from the Width section. If there are a
fewer columns to adjust, then you can go with this option. You can also do the
same for the page height as well. Like, a fewer rows are extending over to 4th
page. So either can move the dotted blue line manually, or can tell Excel to
adjust the contents over 3 pages from the Height section.

So this is how you can fit
contents over the pages, either manually or using Print Area and Scaling
options. I hope you may found this video useful. Let us know by leaving your
suggestions and comments in the box below. Don’t forget to share this video
with your friends and do hit the like button. Subscribe to our channel for more
upcoming videos. Thanks for watching, and we will meet in our next video. Bye
and take care!

Coprights @ 2016, Blogger Templates Designed By Templateism | Distributed By Gooyaabi Templates