Useful tips

Format Cells - Number - Custom The most frequently asked question seems to be - how to align positive numbers with negatives in brackets. In Custom Type enter the following #,##0.00 ;(#,##0.00) The space before the semicolon matches up the bracket space. Leave off decimals if not required. Apply format to the cell, block of cells or column as required.

F4 - Repeats the last command ie Insert a Row, then press F4 to repeat as many times as necessary

AutoSum - Did you know that if you select a block of figures plus one blank row and one blank column you can cross cast and down cast all in one go.

Ctrl ['] - Press control and apostrophe together to copy the contents of the cell above.

Right Click the sheets scroll bar I< < > >I - To see and select from a list of all sheets in workbook.

Don't Hide join a Group - Select Rows or Columns to hide and then select Data - Group and Outline - Group. You can then click the + or - or the [1] [2] to Hide/Unhide - much easier.

Columns to text - Ever wanted to split information in one column into two or more i.e. First Name Surname. Type John F Smith in one cell. Select cell, select Data - Text to Columns, when the wizard pops up select Delimited then Next then click Space under Delimiters, then Next, then Finish. If this were a list of names you could now sort by surname.

Text to Columns - To merge data from many columns into one i.e. if John F Smith is now in A B C in row 3 - select D3 and type =A3 & B3 & C3 and enter. Answer will look like JohnFSmith to insert spaces type = A3 & ' ' & B3 & ' ' & C3 or use the Function Wizard f(x)
and Concatenate.

Edit Paste Special - After selecting data and clicking the copy icon or pressing Ctrl C there are several different ways of pasting - ie Values pastes result of a formula rather than the formula, Format just pastes format i.e. not data, Transpose pastes from column to rows or vice-versa, Paste Link references the pasted cells back to the source sheet or workbook ie change original entry and linked cells change.

Find and Replace - Select Edit - Find or Ctrl F and type in text, number or formula to find. To replace all occurrences of something - select Edit Replace - Replace all - but be careful that the item to replace cannot be part of something else i.e. 'the' could be part of 'these' or 'therefore' etc.
To refine the Replace - select the area to be searched and use Match case and Find entire cells only options if relevant.

Fill Cells - there are many ways to do this but it is worth remembering that if you want to fill say C3:C30 with text, value or formula and B3:30 has data in it - then put the item to be copied down in C3 position cursor on bottom right of C3 so it appears as a + and double click.

Filter Quickly - There is an AutoFilter icon Select - View - Toolbars - Customise - Commands tab - Data and drag the funnel with an = next to it onto a toolbar. Select a data sheet containing multiple occurrences of something i.e. a word or a value etc - select the item to filter and click the icon - all rows not containing the item will be hidden (ie filtered out). - Select - Data - Filter - AutoFilter to return to unfiltered data.

Total Filtered Data - Using AutoSum beneath a column of filtered data will return =Subtotal(9,C2:C300) or whatever the data range is. Subtotal(9,n:n) will just total the filtered items left visible.

Select Contiguous (Touching) Data - To select all data in a block surrounding cursor cell press Ctrl Shift * together.

File Location - If you don't know why File Open and File Save usually go to My Documents then select Tools - Options - General tab and look in default file location and change as required.