Free Microsoft Excel 2013 Quick Reference

How to keep rows together when sorting columns?


I'm using MS Excel 97. How do I sort columns in alphabetical order, and keep
the rows together? Right now, the way I'm doing this is to move the column I
want to sort on as the first column, and then highlighting all the columns,
and then sorting.

Is there a way to sort the data by a column that is say, the third or the
fourth column, and keep all the rows together?

e.g. I have the following columns (numbering reflects their order):
1. First Name
2. Last Name
3. Social Security Num
4. Address
5. City
6. State
7. Phone Number

So, if I want to sort by City (which is 5th column), if I highlight that
column and sort, only that column is sorted (which is fine), but the
corresponding rows do not move with the sorted data. If I move the City
column to be the first one, and then highlight all the columns, and then
sort, in that case, the data is sorted by City, and the rows are kept

I checked the Help section, but that wasn't very helpful. Will appreciate
any help.



Hi I am sure there is a way of keeping rows together when sorting but
can not remember how to do it.

ie 2 rows are totalled in a 3rd row, and the 2 rows are hidden, when a
sort is done they all get sorted together, thanks

Hi everyone,

My question is:

How to keep a line or a column in a static position even if I roll over the rest of the document (pretty handy for labels or parameters in large tables).

Thanx in advance

I don't how this guy did, he managed to keep row 1 2 and 3 from moving.

How can I do this?


I need to lock consecutive rows together before sorting. e.g. A1 contains a name and A2 is blank: B1 contains data related to name in A1 - so does B2, and so on. Therefore need to lock rows 1 and 2 together, then 3 and 4 together and so on, but sorting on the data in the first cell of the group e.g. A1

I want to sort by a column where each entry is associated with multiple rows and keep all of those rows together when I sort by that column. Its a very big spreadsheet.

I am trying to figure out how to keep the 1st column visable when scrolling
across the spreadsheet.


Anyone know how to update pivot table when new rows/columns are added to its original data without redoing the whole thing again?


I was wondering how to keep the row height of a cell associated with that
line even after a column is sorted? Any help would be appreciated. Thanks.

When using an excel worksheet I put my own headings for columns in row #1.
When I scroll down too far I lose those headings. Is there a way to keep them
at the top of the screen?

I have a workbook with ten (10) columns. The first nine (9) columns (A through I) are auto-updated from another source using a macro. These nine (9) columns display project/task related information. This data is assigned a number that can be seen in the "Task Unique ID" column (B) and displayed in a row. The last column (J) is "date completed" which is manually input by project managers.

Each time the worksheet is auto-updated, the project/task information may change row number depending if data has been added or deleted. So, my problem is how to keep the manual input column (J) "date completed" associated with the specific task unique ID column (B).

I tried to use the macro to copy the information from one sheet (On Time Delivery) to another (Vlookup) as a place holder and then copy it back based on the "Task Unique ID" number. I am having trouble with identifying what would be the simplilest way to accomplish this task and what programing steps to take. Any help is greatly appriciated.


I'm looking for help on how to add two ROWS (E and R) Together.

In E Rows

In R Rows

How to sum rows and columns in Access.
I have a data base with lots of rows and columns. In some cases I would like to sum either the row or column and use the total in another query to either work out each row/column as a % of the total of use the total elsewhere. How would I go about doing this.

An e.g.:
Product 1 Product 2 Product 3 Total
12 15 17 44

% of product 1= 12/44 = 27%

So what I did is to from this information write a query to say:
Total : [Product 1] + [Product 2] + [Product 3]
Then I wrote another query to say:
% of product 1 : [product 1] / [total]
% of product 2 : [product 2] / [total]
% of product 3 : [product 3] / [total]

But this becomes unfeasible sine I have 150 products.

Hope you can assist me with this.

With columns it is a little easier since I can use the Group By; sum function. I did it like this:

Volume of Product 1
Customer 1 50
Customer 2 40
Customer 3 30
Customer 4 40

The query I wrote just said that volume of product 1, and then I choose the “Group by” function, “sum” and store it as total volume.

So the % I did in another query:
% Customer 1 : [customer 1] / [total volume]
% Customer 2 : [customer 2] / [total volume]
% Customer 3 : [customer 3] / [total volume]
% Customer 4 : [customer 4] / [total volume]

But this becomes unfeasible sine I have 33000 customers.

Kindly assist in this.

Thank you


How to keep running macros so that these two operate
together and can access each another by controls like

1. A window as main interface
2. A form as index


can any one help me as to how to interchange rows & columns in a table

I am not Excel programmer.
I use visual basic, but my software interacts with the spreadsheet.
I have the following problem.
My code uses statements like the one below:

This works fine when user uses one spreadsheet only.
But when the user opens another spreadsheet the first one is not ACTIVE
however my code still needs to access the first sheet.
So here is my problem. How to keep track of the first sheet?
I tried to do the following:
Dim oExcelWBk As Excel.Workbook ' This is the work book
Dim oExcelWS As Excel.Worksheet ' This is the sheet

oExcelWS = oExcel.ActiveWorkbook.ActiveSheet
but there is Run time Error '91':
Object variable or With block variable not set.

How to do that properly?
Your thoughts please,

how to find negative figure in various columns and rows


I am working as software engineer, I will use Excel most widely.
So, I have one question

How to increment a value, when filtered condition is applied for a column.

Please help me out with your suggestions


Hello all,

I have a question that I'm not quite sure how to ask. I have an excel document that certain rows dont add up with what is on a few separate columns. For example:

Let us say Column1 and Column3 are last names.

| Column1|Column2|Column3|Column4|
|__alpha_|________|__alpha_ | _______ |
|__bravo_| _______ |__alpha2| _______ |
|__charlie| _______ |__bravo | _______ |
|__Delta_| _______ |__bravo2| _______ |
|__Ekco_| _______ |__charlie| _______ |
|__fox___| _______ |__Delta__| _______ |

Instead of moving column1 down to match what is on column3 I would like to write an If statement(maybe) that can look if column3 does not = column1 then move the row down 1 until it is aligned with column3 so it can look like this.

|__alpha__| _______ |__alpha__| _______ |
| _______ | _______ |__alpha2_| _______ |
|__bravo__| _______ |__bravo__| _______ |
| _______ | _______ |__bravo2_| _______ |
|_charlie__| _______ |__charlie_| _______ |
|__Delta__| _______ |___Delta_| _______ |

I hope you guys can understand what I'm trying to say and are willing to to take on the challange if not please ask so I can try to explain better.
Please excuse the underscores I was unable to put spaces without them -Edit

How to use cells (located in a column) in formulas in a row automatically? I
cannot copy the formula to other cells in the row because the coping moves
the selection to another column, not to a another row in the same column.

How to keep sheets displayed(arranged) when moving between them?

I have data in column B(PART NUMBER), C(Info) and additional data in column G(MPNs). I need to filter column B (C respectively) to show rows matching data in column G.
How to filter PART NUMBER column to show only the rows matching the values in MPNs column - see attached example.xlsx.
Actually I am not sure if "Filter" is the right way, because I did not find any option in this function.

I have a column in the excel sheet that is of the in this format-> 00001234,
But when I am saving it in the CSV format it does not show the leading zeroes
for some reason. Can anyone please tell me if there is a way to keep those
leading zeroes.
Thank you

Hello all,
Does anyone know how to exchange rows for columns and vice versa without
creating a pivot table? ex:
Red Green Blue
Jim 1 2 3
Joe 4 5 6

Changed to

Jim Joe
Red 1 4
Green 2 5
Blue 3 6


HI All:
I was wondering if someone can advice me on how to transpose the fields below into columns by IDWeek and Forklength using VBA (eg. IDWeek 3 column A, IDWeek 4 Column B, IDWeek 5 Column C and so on).
This is what i have.
IDWeek ForkLength
3 ------- 69
3 ------- 72
3 ------- 73
3 ------- 74
3 ------- 76
3 ------- 78
3 ------- 79
4 ------- 65
4 ------- 66
4 ------- 67
4 ------- 70
4 ------- 71
4 ------- 71
4 ------- 71
4 ------- 72
4 ------- 75
5 ------- 72
5 ------- 89
6 ------- 76
6 ------- 76
6 ------- 77
6 ------- 81
6 ------- 88
6 ------- 100
6 ------- 122