Excell question

I’m entering a bunch of data into an Excell spreadsheet, and I have two questions:

  1. I want the data within each row of the spreadsheet to remain constant, even if, by accident, I select a single column and then hit the “sort” button. I want to be able to move and sort whole rows, and I may want to move or insert whole columns. But I want to prevent any rearrangement of values within a single column or a single row. How do I do this?

  2. I want to replace the letters at the top of each row with column titles. I’ve found out how to name columns, but those names appear only in the “names” box, and they only appear if I select the whole column. How do I replace the heading “A”, for example, of the first row, with a descriptive title, like “Date of sample collection,” or some such thing? I want to be able to see this title every time I look at the spreadsheet, so I can minimize my risk of entering the wrong data in the wrong cell.

I’ve already consulted the Excell Office Assistant help thingie, and it’s been very little help at all. I’d appreciate any tips or info you could give me. Thanks.

I’m not sure what you’re asking with question one. It seems you want to sort some data but don’t want to move it. I guess I’d recommend copying a whole worksheet and doing your sorts on the copy. This would then preserve a worksheet with your data in it’s original state.

Question two. What you want to do is put your column names in the cells in row one. And then use the “freeze panes” function to prevent them from disappearing off the top of the screen when you scroll down. Consult Excel’s help file for instructions on using this feature; it’s pretty simple.

Both of these things happen automatically with Access & I can’t think of any way to make it happen within Excel- why are you specifically using Excel for this data entry?

Grim

Are you saying that you want to sort your data, while retaining all the values in every row and every column, **in their original order? **When you sort by rows you are, by definition, changing the order in your columns, and vice-versa. That’s the whole point of sorting.

Sorry if my first question was confusing. Maybe this’ll clear it up a bit.

Let’s say you have a spreadsheet that looks like this:

Item sold | number sold | date of sale

Widget | 3 | Oct. 2, 2004
Thingamabob | 1 | Oct. 10, 2004
Thingummy | 4 | Oct. 7, 2004
Excell has a function that lets you sort data within columns, such the order of data entires within one column changes, but the order of data entries within any other column stays the same. Say, for example, I hit the “date of sale” column and then the “sort” button. I’d get the following data table:

Item sold | number sold | date of sale

Widget | 3 | Oct. 2, 2004
Thingamabob | 1 | Oct. 7, 2004
Thingummy | 4 | Oct. 10, 2004

The order of data points in the “date of sale” column has changed, but the order of data points within the other two columns hasn’t. So I might read a row of data (if, for example, I wanted to find out the number sold and the date of sale for thingummies) and get information that’s different from what I first entered.

In the original spreadsheet, I have 4 thingummies sold on Oct. 7. In the second, I have 4 thingummies sold on the 10th. I want to prevent rearrangements like that.

I might want to rearrange the order of whole rows by date of sale. I might want to make my original spreadsheet look like this:

Item sold | number sold | date of sale

Widget | 3 | Oct. 2, 2004
Thingummy | 4 | Oct. 7, 2004
Thingamabob | 1 | Oct. 10, 2004

This way, the spreadsheet keeps telling me that I sold 4 thingummies on Oct. 7, but I can see easily how sales of my products have changed in the first 10 days of October.

I might want to change the order in which I have my columns. Maybe I’ll want to put the “date of sale” column to the left of the “item sold” column. But I always want to see the same data within each row, no matter what I do. How do I do that?

Not sure if I understand, but to move a whole row (or column), go into the gray area which has the numbers (or letters) and click on the rowm (or column to be moved). This will highlight the whole row (or column) as high as it goes. Then “Cut”, put your cursor at the far left (or top) of the destination row (or column) and hit the insert row (or column) icon. Do not paste, but insert and that will create space for the row (column) being moved and will delete the space where it used to be.

To sort a whole chart of values, highlight the whole thing and hit “Date->Sort”. Depending how you want things sorted, pick the row or column to sort on. Everything else will remain in line.

Use “freeze panes” as suggested. Keep you cursor all the way to the left, and in the cell just below the line which contains your headings. Hit “Window->Freeze Panes”. Then you can scroll down as usual and the headings will stay put.

Yep, again, highlight the whole area that contains data and then sort by whichever variable(s) you want. The other data will move with whichever is chosen.

I hope the instructions on rearranging row and column order made sense.

The circumstance you outline in post 5 only happens if you highlight some, but not all columns of data, and then choose to not expand the selection. If you just click in one cell and choose sort it does just what you want.

And headings are just typed in to Row 1.

When you want to sort a big spreadsheet, select the entire sheet (click the empty gray square above “1” and left of “A”. Then choose Data>Sort. Make sure the “Header Row” radio button is selected as appropriate. Then choose up to three sort levels from the drop down boxes.

As for your second question, just to clarify, it is not possible to change the column headings to descriptive titles. They will always be “A,B,C…”. As suggested, do the following:

  1. Enter column headings in row 1 (insert a row if necessary).
  2. Select the entire row just below your headings.
  3. Select Window>Freeze Panes

Hi Scribble!

I know what yer talking about. There is no way to prevent this from happening by accident. Why excell should allow you to reorder the values of a single collum is beyond me. What possible purpose can this serve? Its a data-corruption accident waiting to happen. Just be careful never to touch that re-order button before doing ctrl+A. And save a back-up version of your file each time you do any work on it just in case.

Excell is for spreadsheets, not real serious data storage. But sometimes you have to use excel for this, say when you want to synch your db to your hand-held and your hand-held supports only excel, or if you don’t have access on your pc.

I think your other questions got answered.

One tip I would add is to use auto-filter feature in the data menu. It’s a really handy feature in excel. It lets you query you db really easily. Each field header becomes a drop down menu enabling you to view all the rows where the value in that particular collum is what you selected.

Hope this helps.

Hi, akohl!

Exactly. That’s exactly what my concern is.

Thanks to everyone for answering my questions and giving me tips.

In my job there are many many reasons you may want to do this. The reason you can do this is in EXCEL is because it is not a database and allows you far more flexibility in manipulating your data. BTW, Excel XP will ask you when you only select one column if you would like to include adjacent columns in your sorting.

I don’t understand the problem with the sort. I made a dummy spreadsheet of this, clicked on a random cell in the date column, clicked on the sort icon, and it sorted all three columns as it should. As far as I know, Excel has always sorted this way. You shouldn’t have to highlight the columns at all. The only thing I can think of that would make it sort one column & not the others is if you have empty columns between the populated columns.

AllShookDown, read the way she was selecting the data:

She was selecting a column and then sorting, which only sorts the column. As a few others have pointed out, Scribble, selecting the entire database or 1 single cell will sort everything but keep the rows intact.

To replace the column headings, you can’t. The ABC labels are needed for reference. There is a workaround, though. In row 1 place all of your column labels. Then, select Tools -> Options from the top bar. On the View tab in the Options box, there is a value that says “Row & column headers”. Deselecting this will hide the ABC across the top and the 123 down the side. If the cell outlines are bugging you at all, they can be turned off as well by deselecting “gridlines”.

Another solution for problem number 1, create a new worksheet, and make a Pivot Table of the data in the original spreadysheet. This will allow you to manipulate the data list in any number of ways.

Can you give an example? I’m just curious, and not imaginative enough, I guess.