I have an Excel spreadsheet that is unsorted. We use this spreadsheet in review meetings so it is important that I maintain the original, unsorted, order. But I would like to make a few changes to the file before we review it.
What is the easiest way to do the following:[ol]
[li]Sort by Column X[/li][li]Modify the contents of a few cells[/li][li]Restore the spreadsheet to the original, unsorted, order[/li][/ol]
Here is what I have tried so far:
I added a dummy column A and filled it with a dummy index using “=SUM(Ax, 1)”. I thought I could sort by Column B and then later re-sort by Column A. The problem I ran in to is that “=SUM” is a function not a fixed label and so the re-sort does not work. Is there a way to just insert sequential numbers in a column? This used to be really obvious on Lotus 123, but I don’t see how to do it on Excel. If that doesn’t work, is there a way to take a column that consists of cells with functions and convert them into the equivalent cells with text?
Assuming what you want to do is, say, find all of the jobs for XYZ Corp and change the Project Manager name to John Doe, you could do this with Autofilter. Autofilter allows you to see only the rows where the Customer column = XYZ Corp, then you can make your changes, then turn off the filter. The order of the rows hasn’t changed.
You can also get fixed numbers (called values) from your sum function. What you do is select the cells that contain the results of your SUM function, then Paste Special, Values (or Values and Number Formats).
I have never found a way to automatically sequentially number a row or column.
The way that I have done this is kludgy but it works.
Make a dummy column, let’s call it A.
In cell A1, put 1
In cell A2, put “=A1+1” (no quotes)
Highlight column A from A2 down to the last row
Select from menu Edit, Fill Down
Highlight column A (if it’s not already highlighted)
Copy
Select from menu Edit, Paste Special, and select Paste Values.
Now you can use your strategy of sorting however you want, then resort by Column A to return to the original order.
There’s a better way to do it:
Put the number 1 in the first cell.
Select all the cells you want numbered sequentially.
Click on the menu items: Edit – Fill – Series…
Use the defaults you sare given, including Step Value = 1, and click on “OK”.
Click numbers, entering
Select the first cell, enter 1
Select the second cell, enter 3
Select both cells, click on the fill handle (the small dark box in the low right corner of the selection) and drag it down
