Sandy Writtenhouse on September 18, 2018. When you use Microsoft Excel on your Mac for creating spreadsheets, you have plenty of layout options. But, sometimes it’s not until after you start entering your data that you wish you had structured the spreadsheet differently. Go to and click the cell where you want to paste your data. A cell will not be locked until you protect the sheet. In your case make sure you have a formula, and then select in the Formula Bar the cell reference you want to anchor and hit F4 the number of times until you have set the correct anchor preference. To freeze both rows and columns, select the upper left cell in the data you don't want to freeze. Then choose Freeze Panes from the menu. Now the rows above this cell, and the columns to the left of this cell will be frozen, and remain visible as you scroll. Excel 2007 Posts 854. How to freeze first two rows only. Select cell A3 View, Freeze Panes, Freeze Panes Now the first two rows will be frozen and rows 3 and below will scroll Jeff STOP PRESS: Forum Rules Updated September 2018! Please read them here.
I normally don’t think absolute and relative cell references are difficult, until I try and mix them in one formula with two cell references. Come along and see what I mean.
Relative Cell Reference
The first example is a worksheet with data for Quantity, Price, and Total. The formula in cell C2 is Price times Quantity, or =B2*A2.
There are two cell references in this formula, B2 and A2. Both have a column reference (B and A) and a row reference (2).
You’ll notice that B2 changes to B3, B4, B5, and A2 changes to A3, A4, A5 when copied down.
As we copy the formula in cell C2 all the way down to cell C5, both of these cell references change automatically. They are relative references. Copying down changes the row reference.
We can look at this same data, transposed to columns. Now the formula in cell B3, Quantity times Price, is =B1*B2.
When I copy this formula across to C3, D3,and E3 you’ll notice the row stays the same, but the column reference changes.
Still a relative reference, but copying across changes the column reference.
Absolute Cell Reference
I’ve changed my spreadsheet example to calculate the Tax for Quantity times Price. The Tax Rate is located in cell B7.
The formula for Tax in cell C2 is Quantity times Price times Tax Rate, or =A2*B2*$B$7.
The cell reference for B7 is an absolute reference, which is needed because the Tax Rate is fixed in one place.
Freeze Cell Excel For Mac 2018 Freeze
The reference to cell B7 is modified by using the dollar sign ($) before the column and row reference.
By doing so, B7 will remain constant as I copy the formula down to C3, C4, C5.
The other two cell references are still relative references and change as the formula is copied down. Although, if you’ll notice, neither of the column references change, they’re still A and B.
What this means is that the reference to cell B7 needs only an absolute row reference for this formula to work. As you see below, B$7 is now the cell reference and row 7 will not change when you copy the formula down.
If we transpose this data to a column setup and use the formula =B1*B2*$B$5 the first two cell references are relative, and $B$5 is absolute.
Copying the formula across changes the first two cell references, but not the cell reference for Tax Rate.
Notice the row reference doesn’t change, but the column reference does, so we could have used $B5, freezing the column reference with the dollar sign ($) and the formula would’ve worked perfectly.
Changing Absolute and Relative Cell References
Instead of manually typing in the dollar sign ($) there’s a shortcut to changing the cell reference. You have to be in Edit mode for this to work. Select a cell to modify, then enter Edit mode by pressing the F2 button or use the mouse to click inside the formula bar.
Select the cell reference you want change and press the F4 button to toggle through the different states. If your formula is =A1 hitting the F4 button repeatedly will give you =$A$1, then =A$1, then =$A1, then back to =A1.
Note: Don’t try this in Excel 2008 for Mac.
Absolute verses Relative Cell Reference — The True Test
The spreadsheet below is a square footage reference table for a particular room size. The formula in cell C3
=B3*C2
can be modified with mixture of absolute and relative references so that it can be copied down and across to fill the entire table. I usually funk this test, hence the reason I wrote this post.
Side 1 data is fixed in row 2 and not going to change. As we learned above, copying down will change the row reference so I’ll put a dollar sign ($) in front of the reference to row 2 to make it absolute.
Side 2 data is fixed in column B and since copying across will change the column reference, I’ll put a dollar sign ($) in front of the column B reference to make it absolute.
Now my formula is
=$B3*C$2
which will work for all the cells in this table. Here’s a look at how some of these formulas appear when copied.
To keep the formatting from being copied and utilize a new feature in Excel 2010, Copy the formula in cell C3 and Paste as Formulas by doing the following:
- Select cell C3
- Copy, then select cells C3:J12
- Click the Paste drop-down from the Home menu and hold the mouse over the Paste as Formulas icon to see a preview of what the paste operation will look like (new in Excel 2010) then click to complete.
I’ve dealt strictly with cell references here, but ranges can also be relative, absolute, or mixed references.
Related posts:
If you are working on a large spreadsheet, it can be useful to “freeze” certain rows or columns so that they stay on screen while you scroll through the rest of the sheet.
As you’re scrolling through large sheets in Excel, you might want to keep some rows or columns—like headers, for example—in view. Excel lets you freeze things in one of three ways:
- You can freeze the top row.
- You can freeze the leftmost column.
- You can freeze a pane that contains multiple rows or multiple columns—or even freeze a group of columns and a group of rows at the same time.
So, let’s take a look at how to perform these actions.
Freeze the Top Row
Here’s the first spreadsheet we’ll be messing with. It’s the Inventory List template that comes with Excel, in case you want to play along.
The top row in our example sheet is a header that might be nice to keep in view as you scroll down. Switch to the “View” tab, click the “Freeze Panes” dropdown menu, and then click “Freeze Top Row.”
Now, when you scroll down the sheet, that top row stays in view.
![Mac Mac](/uploads/1/2/6/1/126190375/638074975.jpg)
To reverse that, you just have to unfreeze the panes. On the “View” tab, hit the “Freeze Panes” dropdown again, and this time select “Unfreeze Panes.”
Freeze the Left Row
Sometimes, the leftmost column contains the information you’ll want to keep on screen as you scroll to the right on your sheet. To do that, switch to the “View” tab, click the “Freeze Panes” dropdown menu, and then click “Freeze First Column.”
Now, as you scroll to the right, that first column stays on screen. In our example, it lets us keep the inventory ID column visible while we scroll through the other columns of data.
And again, to unfreeze the column, just head to View > Freeze Panes > Unfreeze Panes.
Freeze Your Own Group of Rows or Columns
Sometimes, the information you need to freeze on screen isn’t in the top row or first column. In this case, you’ll need to freeze a group of rows or columns. As an example, take a look at the spreadsheet below. This one is the Employee Attendance template included with Excel, if you want to load it up.
Notice that there are a bunch of rows at the top before the actual header we might want to freeze—the row with the days of the week listed. Obviously, freezing just the top row won’t work this time, so we’ll need to freeze a group of rows at the top.
First, select the entire row below the bottom most row that you want to stay on screen. In our example, we want row five to stay on screen, so we’re selecting row six. To select the row, just click the number to the left of the row.
Next, switch to the “View” tab, click the “Freeze Panes” dropdown menu, and then click “Freeze Panes.”
Now, as you scroll down the sheet, rows one through five are frozen. Note that a thick gray line will always show you where the freeze point is.
To freeze a pane of columns instead, just select the whole row to the right of the right most row you want to freeze. Here, we’re selecting Row C because we want Row B to stay on screen.
And then head to View > Freeze Panes > Freeze Panes. Now, our column showing the months stays on screen as we scroll right.
And remember, when you have frozen rows or columns and need to return to a normal view, just go to View > Freeze Panes > Unfreeze Panes.
Freeze Columns and Rows at the Same Time
We have one more trick to show you. You’ve seen how to freeze a group of rows or a group of columns. You can also freeze rows and columns at the same time. Looking at Employee Attendance spreadsheet again, let’s say we wanted to keep both the header with the weekdays (row five) and the column with the months (column B) on screen at the same time.
To do this, select the uppermost and leftmost cell that you don’t want to freeze. Here, we want to freeze row five and column B, so we’ll select cell C6 by clicking it.
Next, switch to the “View” tab, click the “Freeze Panes” dropdown menu, and then click “Freeze Panes.”
And now, we can scroll down or right while keeping those header rows and columns on screen.
Freezing rows or columns in Excel isn’t difficult, once you know the option is there. And it can really help when navigating large, complicated spreadsheets.
READ NEXT- › How to Make Your Family Love Your Smarthome
- › How to Enable Google Chrome’s New Extensions Menu
- › How to Stop Spammers From Attacking Your Google Calendar
- › How to Power Off Your Samsung Galaxy Note 10 or 10 Plus
- › How to Switch from a Windows PC to a Mac