1. Print or copy the change history of a shared workbook
When sharing spreadsheet between colleagues, it can be handy to review what changes have been made.
Directions:
1. On the Review tab, click ‘Track Changes’, and then click ‘Highlight Changes’
2. select the ‘When’ checkbox in the dialog box, then select ‘All’ from the list box
3. Clear the ‘Who’ and ‘Where’ check boxes
4. Select the List changes on a new sheet check box, and then click OK
5. To print the History worksheet, click ‘Print’
To copy the history to another workbook, select the cells you want to copy, click ‘Copy’, switch to another workbook, click where you want the copy to go, and click ‘Paste’
2. Keyboard shortcut: Toggle between formulas and results
Switch between Excel’s normal display – showing results of formulas in the spreadsheet – and a formula display mode to see what formulas are in place on your spreadsheet.
Directions:
Click ‘CTRL’ + ‘ ‘ ‘ (grave accent)
3. Keyboard shortcut: Add the current date
This is a quick trick to save you having to write out today’s date.
Directions:
Click ‘CTRL’ + ‘;’ (semi-colon)
4. TODAY function
If you need a worksheet to continually update itself, to the current date every time it is read, the TODAY function will do this by reading the computer’s serial date automatically.
Directions:
1. Select the cell in which results will be displayed
2. Click on the Formulas tab and select ‘Date and Time’ to open a drop down list
3. Click on TODAY in the dialog box and click OK.
Alternatively:
Type ‘=TODAY( )’ into the formula bar
5. Split text over lines within cells
If you are typing lots of information into one cell and want to split it over several lines to make it easier to read, do not click the entre key as this will move to the next cell down.
Directions:
Click ALT + ENTRE
6. Changing the ENTRE key function
If clicking the entre key to drop down to the cell below is not the default function you want, you can change it in the Excel Options menu.
Directions:
1. Go to the file tab and select ‘Excel Options’
2. Select ‘Advanced’ option
3. Select the direction of movement from dropdown box
7. Freeze or lock panes
Sometimes you want to scroll down and see your rows of data, but when you get to the bottom of the screen, your column names in the top row have disappeared. To fix this, you freeze the top row so that it’s always visible.
Directions:
1. Go to the View tab and select ‘Freeze Panes’
2. Click Freeze top row
8. Hide data in printouts
You may wish to hide some data from spreadsheets on printouts if it is confidential information. Specific rows and columns can be hidden before printing use the ‘Hide’ function.
Directions:
1. Select desired rows or columns
2. Got to the Home tab and under ‘Cells’ click ‘Format’
3. From the ‘Format’ menu, go to the ‘Visibility’ section
4. Select ‘Hide and Unhide’ and click ‘Hide Columns’
9. Add comments to a formula
It can be handy to label a formula with a comment so you know what all those letters and numbers actually mean at a glance.
Directions:
1. In the formula bar, at the end of the formula, type ‘+’ and ‘N’
2. Open parentheses and type your comment in quotation marks, then close the parentheses.
The comment will then be displayed in the formula bar when you select the cell.
10. Add a function to reverse text in a series
If you have created a list and want to reverse the order it appears in, you can use the Visual Basic Editor (VBE) to add it as a function and make things easy for yourself.
Directions:
1. Click ‘Alt’ + ‘F11’ to open Visual Basic Editor
2. From the Insert menu, select ‘Module’
3. Enter the following lines of code into the module:
Function ReverseText(text) As String
Dim TextLen As Integer
Dim i As Integer
TextLen = Len(text)
For i = TextLen To 1 Step -1
ReverseText = ReverseText & Mid(text, i, 1)
Next i
End Function
To test the function:
1. Open Paste Function by clicking ‘Shift’ + ‘F3’
2. From the User Defined category, select ‘ReverseText’
3. In the function box, select any cell that contains text and click OK
