Freezing Rows and Columns on a Worksheet
When you have a large range of data on a worksheet, could be a table of data, a calculated display area,... the need
generally arises to freeze a header row or column of information so the user can see it while they scroll the worksheet.
To freeze the top row of a worksheet:
- Select the worksheet tab
whose top row you want to freeze.
Next, proceed to the Ribbon and select View/Freeze Panes/Freeze Top Row. The top row of the worksheet
will now stay stationary while you scroll down the sheet.
Freeze Top Row on Worksheet
To freeze the first column of a worksheet:
- Select the worksheet tab
whose first left column you want to freeze.
Next, proceed to the Ribbon and select View/Freeze Panes/Freeze First Column. The first left column of the worksheet
will now stay stationary while you scroll to the right on the sheet.
Freeze First Column on Worksheet
To freeze a worksheet at a specific column and row:
- Select the worksheet tab
whose row and column you want to freeze. Next, click on the cell below the row you want to freeze and to the right of the column you want to freeze.
Just think of it this way, the worksheet window will freeze on the left-hand side of the selected cell and along the top of it. Last, proceed to the Ribbon and select View/Freeze Panes/Freeze Panes. The worksheet
will now stay stationary to the left and top of your selection while you scroll the sheet.
Freeze Row and Column on Worksheet
To unfreeze a worksheet, proceed to the Ribbon and select View/Freeze Panes/Unfreeze Panes.
Closing All Workbooks at Once
Short and simple tip, if you have 20 workbooks open and you want to close all of them at once,
hold down the Shift Key and then click the close button in the upper right-hand side of any Excel window. It will prompt to Save All if
required. Do not let up on the Shift key until everything is closed.
The UFO Pie Chart
In the world of charting in Excel, one of the coolest charts to make is what we call the UFO pie chart.
Think flying saucer and you are on the right track. The idea is, if the data is not the best news, at least the charts look cool!
- To make the chart, first organize your data on the worksheet with the category
labels in the first column of your table and the pie values in the second. Bold your headers to make them
different from the rest of the data
Organize Your Data for the Pie Chart
- Click anywhere inside of your table, proceed to the Ribbon and select Insert / Pie Chart / 3-D Pie.
Select Insert / Pie Chart / 3-D Pie
- Click and hold in the chart's white area, close to the border edge,
and move that chart where you want to put it on the worksheet.
Move the Chart
- Next click on the pie chart itself. Be very careful, you want to
select the entire pie chart, not a slice. If you click twice over the pie chart, once to select
it and once again, you will select an individual slice. If you mess up, click on a worksheet cell, this
clears the chart selection, and then reselect.
Select Entire Pie Chart
- Right mouse click over the pie chart and select Format Data Series.
Format Data Series
- On the Format Data Series dialog box, click the effects tab, then select 3-D Format.
In the bevel boxes, type in 512 in all boxes. You do not have to type pt. These numbers are what give the pie
chart the UFO shape. Altering them will change the effect.
Format Data Series
- Now push it over the top. With the pie chart selected, now click on an individual pie
slice by clicking on the pie chart again. On the dialog box that is still open, it should now say
Format Data Point. Select the Fill
& Line tab then the Fill group. In the Fill group,
select a solid fill color then make it transparent, 30 to 50%
usually suffices. The transparent effect with the bevel usually
makes the crowd go wild. Use vivid colors. Have fun.
Set the Transparency
To learn more about these type of charts, check out our
Microsoft Excel Dashboards Training.
How to Quickly Determine If a Value in One
Worksheet Table Exists in
Another Worksheet Table
One of the most common tasks
you will face in Excel is determining if a value in one
worksheet table exists in another worksheet table. While
there are many ways to do this, a quick way is to use the
COUNTIF worksheet function in a formula. Simply put, the
COUNTIF worksheet function counts how many instances of what
you are looking for, called the criteria (i.e. logic),
exists in a range of data that you specify. The function is
easy to use in a formula when looking up values and is very
versatile. Remember when checking for the existence of
something using logic, the easiest test to do is to count.
- To use the COUNTIF function,
first open the workbooks containing the tables that you
will need to check. The data does not have to be in the
Open the Workbooks the Containing
- Next in the workbook that
contains the values you want to find in the other table,
designate a column in the table to place the COUNTIF
formula in. In our example, we called it Used.
Create an Extra Column
- In the column just created,
type a formula using COUNTIF in the first cell starting
with =COUNTIF( . The
first argument for the function will be the column range of the
other table that you want to look in using COUNTIF, in
this example A1:A14. The extra notation in front of A1:A14 is
used to reference an external range, a range in another
workbook. This was automatically picked up when we
selected the range with our mouse. The second argument will be a cell reference
to the value to lookup in the table, in this case cell A2.
When selecting the two
arguments with the mouse, be sure to type a comma after
the first argument selection so you can use the mouse to
select the second argument. Type a ) and press
Enter to enter the formula. Be sure to use an absolute
reference to the range of data you are looking in, in
our case A1:A14. This
means putting dollar signs in the range reference like
$A$1:$A$14. That will keep the range reference from moving in the
formula when it is dragged and filled down the
worksheet. That is what an absolute reference means, it
does not change when copied. Sometimes the $ signs are inserted
automatically like in an external reference when
Type the COUNTIF Formula
- Last, select the cell you
just created the formula in, click and hold on the
fill handle of the cell (black or green dot, lower right-hand corner of
cell), then drag it down the
required number of rows and release the left mouse
button. You can also double click on the fill handle dot as
well to send it down the sheet. Be careful, it may stop
early if there is a gap in the data.
Drag and Fill Formula Down
- Where you see a number
greater than 0, the value exists in the other table. One
note here, this type of problem is highly susceptible to
rogue data with extra spacing at the beginning and ends
and also non-printable ASCIIs on the ends. To counter this
problem in the formula, you can use * 's along
with the lookup value. If you use *Text, that means ends
with the value, if you use Text*, that means begins with
the value and if you use *Text*, that means contains the
value. You can use the & ampersand operator to
concatenate the *'s with the cell value in the formula,
for example =COUNTIF( [Book2]Sheet1!$A$1:$A$14, "*" & A2
& "*" ). You will have to type these. Be careful with the contains, *Text*, as that
might grab unintentional data. Think this way, if
looking for xyz, that might be
part of xyzz and so forth, not what you are looking for. Start with the other two
cases first and see if they solve the problem as they
are more precise. You will know
you have extra spaces or ASCII's in a problem like this because you will run
COUNTIF and return 0 even though you know the value
exists in the other table. Instead of using the *, you
could also just clean the lookup data using the TRIM and
CLEAN functions first before analyzing with COUNTIF.
Bottom line, know your data and never assume it is
To learn more about looking up data, check out our
Intermediate Microsoft Excel Training.
Need Help? Please call us at 1.866.924.6244
Copyright © 2002-2017
EMAGENIT All Rights Reserved