PC - Handy Hints

[The Statman]

If you have an Excel spreadsheet and you want to sort by half of a cell, you can cheat this way:

Suppose I have a list of snooker players. Three columns, Player, Highest ranking, Number of ranking events. There are 160 players in the list so the table is 160×3.

The first column contains the names as "John Higgins", "Stephen Hendry" etc., and I want them to appear like that, but want them sorted by surname. Here is how I can do it:

Highlight the column containing the names.

In Word, paste the data (Paste Special, select 'Unformatted text').

Do Ctrl+H to Find and Replace. In the Find box, put a space. Click 'More'. With cursor in the Replace box, click Special and select Tab character. Click Replace All.

Now check that no names contained two spaces and correct where necessary.

Now copy the whole data and, in a spare space at either end of your Excel data, paste it. The first name and surname will now be in separate columns, so you can sort by surname (and then first name, so that Alex Higgins comes before John Higgins, for example).

The new columns that you have added can be made outside the printed area, so it won't affect the appearance of the chart.

[The Statman]

This actually approaches the big secret of my tables in the Stats and the Prediction results. I get Excel and Word to do all the work!

I have my table of Prediction results. So I have six or seven columns – Score, Predictor, First, Second, Quarters, Semis, Final.

I paste them into a black spreadsheet and insert a blank column between each column.

I put '<tr><td>' in every row in a blank column to the left, '</td><td>' in all the blank columns between each data column, and '</td></tr>' to the far right.

Then I select all the columns/rows and paste-special them as unformatted text into Word. Then use Find/Replace to find all Tab characters and replace them with nothing, and find all Return characters and replace them with nothing.

This text can then be pasted into my message, and it is properly coded for the table, without any spaces, and I just need the <table border=1> at the top and </table> at the bottom, and it's ready to go!

[RaNeN]

The Statman's secrets revealed!

[Semih_Sayginer]

ffs Statsy, i should just have sent a PM to you instead.












seriously though, thanks for all the hints so far. appreciated

[The Statman]

My pleasure!

In Excel, if you ever have a list which is not in a sortable order but needs to be kept in that order specifically – such as the 90's world championship qualifying results charts in my Stats section there is a simple but effective way of ensuring they can always be returned to the right position after meddling.

The reason I might want to 'sort' the results chart might be to get an alphabetical list of players in a certain round, or to count up quickly the number of 5-0s or whatever.

This is one example, but there must be other occasions where you might want something that is not in a 'sortable' arrangement.

What I do is, in an extreme right-hand column (outside the print area), have in each cell the numbers 1 - 2 - 3 etc. down to the bottom. (You can do this easily by putting the 1 in the top cell, highlighting that cell down to the last one you want, hitting Alt+E,I,S, then Return.)

Then, whatever muddle I get in, where I might sort it, undo, redo, or whatever and possibly accidentally save it, I can then go back in, select the whole sheet, and sort it by the 1-2-3 column and hey presto! The sheet will be back in its original order, no probs.

================

This is a serioulsy underutilised thing which saves a lot of heartache if you do accidentally save when you don't want to. I found out the hard way that once you hit Save, you cannot Undo to before that point!

[ronnieisthebest]

Knew you would be an excel wizard statman!

[cap]

Hit <PrtScn> key to screen-grab the desktop. Alt + <PrnScn> to capture the active window only. Then you can paste the capture image on any graphics programs or Word, etc.

[cap]

When you are typing a url on IE, you could type SomeWebSite then hit <Ctrl><Enter>. IE autofills the address with http://www.SomeWebSite.com and takes you there.

<Ctrl><Esc> to bring up the windows menu as if you had clicked on the 'Start' button.

[elvaago]

I have two very helpful hints:

1) SAVE OFTEN

2) MAKE BACKUPS

If my users were to do these things a little better, I'd have a lot less headaches!

[The Statman]

When you are typing a url on IE, you could type XXXXXX then hit <Ctrl><Enter>. IE autofills the address with http://www.thesnookerforum.com and takes you there.
(Don't click on the link!!! unless you really want to)

<Ctrl><Esc> to bring up the windows menu as if you had clicked on the 'Start' button.

Why? Is it an adult site?

[cap]

Why? Is it an adult site?

Don't know, never been there. I thought it might be.

[Sarmu]

I don't think this has been mentioned yet, but Alt + Left Arrow and Alt + Right Arrow will move page back and forward when browsing

[The Statman]

Another Excel tip:

If you have a column/row, which contains data that is calculated from another cell(s), and you want to paste it elsewhere, it will not paste properly because it will then shift the cell(s) that it bases its calculation on.

But if you go Paste Special, and select Values, it will alter it from a calculation to ordinary data.

Equally, you can Paste Special it into the place you are copying it from.

Paste Special is also a quick way of pasting the cell formatting, or the column widths, or whatever, from one place to another. If you have complicated cell border settings (such as I do in the Excel version of my World results tables), I can copy the formatting from one place to another without overwriting the data.

[Semih_Sayginer]

I don't think this has been mentioned yet, but Alt + Left Arrow and Alt + Right Arrow will move page back and forward when browsing

cheers Sarmu

[RaNeN]

The Windows key + D minimize's all the open windows. Shows your desktop!