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.
|