Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

How To Lock Cells From Excel Sort Function

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More


I have a table in which the first row of cells are headings for each column and can sort data from all the columns. On the second row are the totals of all the columns with formulas.

Every time i sort the table the totals row gets sorted with all the other data messing up the table.

Is there anyway to lock or protect a row from the sort function in excel?

Thank you for your time

View Answers     

Similar Excel Tutorials

Automatically Lock Certain Cells in Excel using a Macro
This macro allows you to have a cell automatically locked after a user enters something into it or into another spe ...
Freeze or Lock Specific Rows and Columns when Scrolling in Excel
Prevent specific rows or columns from moving when you scroll through a spreadsheet in Excel. This allows you to kee ...
Case Sensitive Sorting in Excel
How to perform case sensitive sorting in Excel. Select a cell within the data that you want to sort and then go to ...
Sort Data Alphabetically or Numerically in Excel 2007 and Later
This Excel tip shows you how to Sort Data Alphabetically and Numerically in Excel 2007. This is a great feature to ...
Sort Data Left to Right in Excel
How to sort columns of data in Excel.  This is the same as sorting left to right. This will change the position of ...
Sort on Multiple Columns at Once in Excel
How to sort a data set by multiple columns in Excel.  This allows you to better organize a data set and is really e ...

Helpful Excel Macros

Sort Data that Doesn't Have Headers in Ascending Order in Excel
- Sort data that doesn't have headers in ascending order in Excel with this macro. This is a simple sort macro that assum
Bubble Sort
- This macro will perform a bubble sort in excel. You use it simply by selecting one column to sort and then running the
Sort Data With Headers in Ascending Order in Excel
- Macro that sorts data that has headers in ascending order in Excel. This macro assumes that you data has headers on it.
Sort Data With Headers in Descending Order in Excel
- This Excel macro sorts data that has headers in descending order. This means that data is sorted Z to A and 10 to 1 - o
Sort Data that Doesn't Have Headers in Descending Order in Excel
- Sort data in Excel that doesn't have headers using a macro. This Excel macro assumes that your data set does not have a

Similar Topics

I am a newbie for excel, so please excuse if this is a easy question.

I am trying to lock the entire row so that when I sort by a column all data to left and right of the column sorted stays in the same rows as originally was with sorted data.


a 9 8 7 6
c 0 5 5 1
b 8 3 0 5

If sorted on column A new table would look like this

a 9 8 7 6
b 8 3 0 5
c 0 5 5 1

Thanks in advance for any help.

The attached example is a copy from a workbook i am working on and what i want to with the table is be able to sort the table in ascending order by any of the given columns through option buttons.

My problems are, 1) the cells are merged and of different sizes so i get an error message when trying to sort these merged cells because they are of different sizes and 2) the number of rows in the table will not always be used and if i unmerge the cells and sort the table then empty rows go to the top of the table and i want them to stay at the bottom.

Any help will be much appreciated .


Forum search found nothing but I can't imagine it's not covered in some way somewhere here.

Excel 2007 - I have a table of data - I formatted a rectangualr area and used a table style but the small sort buttons in the Column1 column2 don't sort the tabel correctly (I think it's because I have two rows underneath the column1, column2, etc row - one has a few cells filled in with dates, the next row has some column headings in it - I just want to get rid of the sort row containing the column1 column2 labels with the small sort buttons but excel won't allow me to delete this row - all I want to do is go back to highlighting the cells I want sorting and use the main menu bar sort command unless I can find an easy way to sort out this sorting functionality that I find confusing. I'll off to dig around in the help files again.

I have a workbook kept on a shared server. In one worksheet, there are multiple formulas that need to be protected. However, if I protect the worksheet - then users are unable to sort the data.

I've looked up multiple topics on this - but nothing seems to be working. Can I protect certain columns from being modified for some users without protecting the worksheet? If I protect it and select "Allow all users to select cells and sort" it still will not allow the sort.

Please help. I don't know how to write macros - otherwise I would make a "sort by" button that I've read about.

Thanks in advance.

I need VBA code for Excel 2007 to sort column A (includes header row) in a formated table after I enter data into any cell in column A.

This will be used in a few tables within this workbook, the tables being products, vendors, and payment types. I pull the data from these tables into a purchases table. I have to remember to sort the colums A to Z each time I enter new data, otherwise when I Alt+DownArrow in the purchases table to bring up the corresponding list, it's only alphabetized since the list time I remembered to sort A to Z. Would really like to automate this A to Z sorting procedure.

Is there any way to "lock" the rows of a worksheet so a Sort will keep all cells on each sorted row if I forget to select a column or two?

I want to sort multiple rows, at the same time but seem to only be able to do one at a time. I know that to sort multiple columns, I can highlight as many columns as I want and then select A to Z. But this doesn't seem to be the case for sorting rows. In terms of sorting rows, the only thing I can seem to do is to go into Data-->Sort-->Options-->Select Left to Right. I can then sort a single row, and from this window it looks like a may (although haven't figured out how to get it to work) three rows, but I cannot tell how I might be able to sort more than three rows. For instance, suppose I have the following data

3, 2, 1
6, 5, 4
98, 97, 96
33, 32, 21

I want to be able to select all of these rows and quickly sort them as follows:

1, 2, 3
4, 5, 6
96, 97, 98
21, 32, 33

i forget to mention at the same time! Not one by one!
let's say i have 200> rows so i would like to sort them form min to max but all together!

I have a pivot table that does not seem to follow my instructions to sort alphabetically. I have tried going into field settings and advanced to set the sort ascending by Div. It still sees MON before CAL as seen below.

Div Jan MON 1033 CAL 19 CHI 532 COL 2068 GFC 682 KAN 543 NEB 4231 NWE 238 PWR 8727 SPR 2555 SWE 428 TEX 734 TWI 1176

I even deleted the pivot table and rebuilt it resulting in same sort as before.

I also sorted the data source to independently from the pivot table. The data source sorts the Div in the correct order.

Is there some other way to set the pivot table sort order besides field settings and advanced? Thanks for any helpful tips.

I have a work sheet using rows A - L.

I am currently using a macro that auto sorts the info in Column A based on a numerical account number, after information has been entered into column L. I now need to to lock all the cells in columns A - L after the auto sort has happened and place a date and time stamp in Column M.

I am extremely green with Macros. Thank you.

I am creating a simple check register in Excel. I have set it up to look like a check register with all the data for a single check being entered into a row. I want to be able to sort the data by using the columns to sort ascending/descending and keep the data in the row intact, then sort by date, check number, amount, etc. When I currently do this only the data in the selected column sorts and it is no longer associated with other data in the row. I know I can double click in the upper most left cell to select all of the data and then sort but this limits my sorting options to one type of check attribute. So I guess my question is, Can I lock the data in a row so that it can be sorted by column and never scrambled or disassociated with the other data it is associated with in the row?

Thanks in advance


Is it possible to select multiple sheets and then apply the Sort tool to all the sheets selected? I just tried it myself but it didn't work. I selected several sheets and then highlighted a table I want sorted, but when I went to the pull down menus, "Sort" was grayed out, as was most of the tools under the "Data" menu.

I have the same table on every sheet (same location, same format/formulas, etc. The only thing that is different is the contents of each table. I want to be able to sort all the tables on every sheet simultaneously rather than have to go from sheet to sheet and applying the sort tool once on each sheet.


How to sort multiple columns in a pivot table. I can able to sort one column at a time in Pivot table. If i want to sort two or more columns at a time, how to achieve that. Please help. Thanks in advance.


I am needing help with a sort function. My columns are parts that are populated from another sheet and some orders use 2 - 3 rows and others may use 100. I have manually sorted these for quite awhile.

Now, I want to automate the sort. If I select enough rows to be more than any order will need, I won't allow me to choose largest to smallest. Choosing Z to A works, but it leaves all the blank rows as entries.

Is there a way to make the sort function ignore any blank cells(rows) and collate the data from largest to smallest (Column C) regardless of the quantity?

Thanks for any help.


Hello, I have a spreadsheet that has a list of names that I need to be able to create a table for. The problem is that there are multiple cells for say the address column and so on. How can I make it to where I can sort all the cells in multiple columns to be seen as one? So If I sort by name all the other columns with multiple cells sort to that persons name.


I have an Excel Workbook having a protected worksheet. I want to allow users to Sort the Worksheet and at the same time ensure that they are not able to edit (chnage) data excpet sorting which is allowed. I tried using the Tools>Protect Sheet by checking the checkbox for "Sort". I protected the sheet after that.

After that I thought I would be able to sort the data However i get the error pop up saying The worksheet is Read only and protected, the user needs to unprotect it for making any changes to it.

My worksheet data has 250 columns and 1500 rows. I want to keep an option open to sort it with reference to any one of it.

Can someone help?


I see no way to specify a particular area (which columns, by which rows -- which specific cells) to include in a sort?

I have data lying in rows beneath the data which I will sort [many times], but don't want that lower data to get messed up or reordered by the sort.

Is there a way to do this? In Filter & Sort - Custom Sort, there are no rows by columns options that you can set, so I'm wondering if you can specify the cells to sort any other way?

Thanks so much!!

Hi all,
I have a pivot table where I group by week number and year to show weekly totals of cycling ride data for each week of each year, with a chart to show comparison of the data. I have just noticed that for the past 3 weeks this year I have entered, the week number sort order is out of sequence, and this is reflected in the chart. Weeks 1-38 are in correct sort order, then it goes 44-46 then 39-43. The week number is taken from cells that use the "WEEKNUM" function, and this is showing the correct value for week number in the appropriate cell for each row of entered ride data.
How can I get the sort order to display correctly?
I have a screenshot with highlight showing the problem weeks in the table and chart, but can't work out how to attach it to this post (I tried the "Insert Image" button but it requires a URL to the pic - I have no website to post it to). I can provide the screenshot if you can advise me how to attach it).
PS. I also have other tables and charts that group by month, and these show correct sort order in the table but the chart sorts with December at the top and January at the bottom - is there a way to fix this? I have not been able to work out a way to do this.

Thanks in advance,
Mick McKean

Attached I have a workbook that explains exactly what I'm trying to accomplish. I need to sort columns A and B in the pivot table, but I also want to sort column C which is outside of the pivot table but is a serious of averages tied to the pivot table data. Is that possible? Thanks in advance!


I'm still pretty new to charts and I was jsut wondering if it is possible to sort a chart somehow so that the data shows largest result in the left hand column down to smallest in the right hand without having to re-sort the source data

The reason I ask is that I have two sets of charts running off one table of data, one chart has to show sorted in cost and the other in numbers of reports

I have a rotten feeling it can't be done and that I will have to double up the table and sort each on for each set of charts, but I thought I'd ask you clever people before I gave in!

Many thanks in advance


thanks to every one in advance...

i have a pivot table and if i select my headers i have the availability to sort a-z and z-a plus manual ...manual says to drag the item where i would like it...i have tried various ways to do this but am stumped. i have tried the excel help database..they reference manual sort but really give no explanation.

i want to sort a-z..
then manual sort a few items to the top of the list..any suggestions?

I am using a table to select a value. Either 1,2,3,4,5,6,7,8,9,10.
Then based upon what value is how I would sort 7000 lines (rows) of data. There are 5-6 columns. I have a macro that I can use to sort depending on which value but I was hoping that It would do it automatically.

Like if I had selected 3 then it would sort first on column C.

Right now I can manually select a macro that I have but to do this automatically would be great.

I have a huge list that It would be easier to find an item if I first sorted based upon a criteria.

Something like: If C=1 then run macro 1, If C=2 then run macro 2, etc...

Thanks - I have came along way so far. Thought to automate would be nice.

How do I sort but have the other data move with the column I'm sorting? Whenever I sort, for example, by name, the address and phone numbers columns stay the same and are therefore incorrect with the newly sorted columns. I've looked everywhere.

I'd like to sort multiple columns in VBA (Excel 2003). That's not a
problem you'd think, but the columns are not placed next to each other.
There are columns in between which may NOT be sorted!

Since a simple example says more than words... Take following table:

[A] B [C] D [E]
1 | B | =IF(A1... | 12| =C1*... | 75|
2 | D | =IF(A2... | 11| =C2*... | 70|
3 | A | =IF(A3... | 19| =C3*... | 80|
4 | C | =IF(A4... | 20| =C4*... | 40|

We want to sort columns A, C and E alphabetically according to the data
in column A, but leave B and D alone. This should lead to:

[A] B [C] D [E]
1 | A | =IF(A1... | 19| =C1*... | 80|
2 | B | =IF(A2... | 12| =C2*... | 75|
3 | C | =IF(A3... | 20| =C3*... | 40|
4 | D | =IF(A4... | 11| =C4*... | 70|

Selecting columns A, C and E without selecting B and D isn't a problem:


But when I try a sorting construction like shown below, I get an error:

Range("A1:A4,C1:C4,E1:C4").Sort Key1:Range("A1"), Order1:xlAscending

I'm not sure why though... Anyone knows a way to accomplish what I want?


I have updated my source data, and ordered in the way that I need it to appear (my settings are to sort with source data order), but since I have added new data, the pivot table will not sort the "new" data in alphabetical order with the "old" data even though my source data is not sorted in any type of chronological order (i.e., alphabetical only).

Does anyone know how to fix this? I am using excel 2010. Also, the alphabetical piece, is the secondary "sort". The first is sorted by a custom analysis group, in ascending order, which works fine, but my entries must be listed in alphabetical order after that as well.

I just bought a new HP computer with the Windows 7 O/S and the 2010 Microsoft Office package. (Huge learning curve for me from the ten year old system I had.)

I do the record-keeping for a fun NASCAR pool for my friends. There are 30 columns with the players name at the top and then weekly I update the drivers picked list because you can't have duplicates. In the 2003 version I would just highlight the drivers and choose sort AtoZ and it would happen. Now when I do that I get a "Sort Warning" that says " Microsoft Excel found data next to your selection. Since you have not selected this data, it will not be sorted. What do you want to do?" The default is set at "Expand the selection" with the option of choosing "Continue with the current selection." So each time I have to select the "Continue with current selection" and then sort. It is irritating and time consuming. Is there a way I can disable this sort warning function? (No fancy formulas or anything involved.)

Thank you in advance for your help.