Two Hoots Banner



Excel Tips

  • Excel's AutoSave Add-in automatically saves your worksheet at preset intervals. If you don't have AutoSave installed, choose Tools, Add-Ins. When the Add-Ins dialog box opens, click the check box to the left of AutoSave and click OK. AutoSave is on the Tools menu. Choose Tools, AutoSave. When the AutoSave dialog box opens, make your selections and click OK.

  • There's no built-in feature that alphabetically sorts Excel worksheets. But the following macro will: 
Sub AlphaSort()
Dim iCount As Integer
Application.ScreenUpdating = False
iCount = Sheets.Count
For i = 1 To iCount - 1
  For j = i + 1 To iCount
    If Sheets(j).Name < Sheets(i).Name Then
      Sheets(j).Move Before:=Sheets(i)
    End If
  Next j
Next i
End Sub
To create this macro, open the VB Editor by pressing Alt-F11. Choose Insert, Module and enter the above macro. Return to your worksheet and run the macro to sort the worksheets in the current workbook. To run the macro, press Alt-F8, select AlphaSort, and click Run.

  • To remove tge #DIV/0! error when dividing cell values by zero insert either of the following formula -
=IF(denominator=0,"",numerator/denominator, where denominator is the cell that's doing the dividing and numerator is the cell that's being divided.

Alternatively use this formula =IF(ISERROR(numerator/denominator),"",numerator/denominator)

  • Use the following underused keyboard shortcuts to boost your productivity.
ActionKeystroke
Type and function and then show the formula paletteCtrl+A
Type and function and then show the brackets and names of the argumantsCtrl+Shift+A
Insert a HyperlinkCtrl+K
Type and function and then show the formula paletteCtrl+A
Edit the active cellF2
Paste the name of a range into a formulaF3
Paste a function into a formulaShift-F3
Calculate all sheets in all open workbooksF9
Calculate the active worksheetShift-F9
AutosumAlt-=
Enter the date as textAlt-;
Enter the time as textCtrl-Shift-;
Copy the value from the above cellCtrl-Shift-"
Copy the formula from the above cellCtrl-'
Display the AutoComplete listAlt-down arrow
Create or edit a cell commentShift-F2
Create Named ranges from row and column labels (select area first)Ctrl-Shift-F3
Display the Style dialog boxAlt-'
Display the Format Cells dialog boxCtrl-1
General number formatCtrl-Shift-~
Currency format (two decimal places)Ctrl-Shift-$
Percentage format (no decimal places)Ctrl-Shift-%
Scientific format (two decimal places)Ctrl-Shift-^
Date formatCtrl-Shift-#
Time formatCtrl-Shift-@
Number format with two decimal places, thousands separator andminus sign for negative numbersCtrl-Shift-!
Outline borderCtrl-Shift-&
Remove outline bordersCtrl-Shift-_
Hide rowsCtrl-9
Unhide rowsCtrl-Shift-(
Hide columnsCtrl-0
Unhide columnsCtrl-Shift-)
Delete the selected cells, rows or columnsCtrl--
Insert cells, rows or columnsCtrl-Shift-+
Move clockwise round the corners of the current selectionCtrl-.
Select the current region around the active cellCtrl-Shift-*
Select the current arrayCtrl-/
Select all cells with commentsCtrl-Shift-O
Select cells in a row that don't match the value in the active cell in that rowCtrl-\
Select cells in a column that don't match the value in the active cell in that columnCtrl-Shift-|
Select only cells that are directly referred to by formulas in the selectionCtrl-[
Select all cells that are directly or indirectly referred to by formulas in the selectionCtrl-Shift-{
Select only cells with formulas that refer directly to the active cellCtrl-]
Select all cells with formulas that refer directly or indirectly to the active cellCtrl-Shift-}
Select only visible cells in the current selectionAlt-;
Moving between Worksheets in a WorkbookCtrl-PgUp or Ctrl_PgDn
View Excels VBA (Visual Basic for Applications) Development EnvironmentAlt_F11


  • To stop Excel starting up with a blank document already open run Excel with the command line 'excel.exe /n'.

  • To use a named cell select a cell and select Insert->Name->Define and enter a name for the cell. Now go to another worksheet and in the requried cell enter =[defined name].

  • You can easily change the font and font size without ever lifting your hands from the keyboard. To change the font, press Ctrl + Shift + F. This will select the Font box. Next you can press the Up and Down arrows to select the font you want to use. After you make a selection, press Enter. To change the font size, press Ctrl + Shift + P and then use the Up and Down to select a font size. Press Enter after you make a selection.

  • When you write an Excel macro, the macro is attached to your current workbook and is not available to other workbooks. However, you can create global macros in Excel. All you have to do is put them into a special workbook named Personal.xls. To do this, open a blank worksheet. Choose File->Save As and name the worksheet Personal.xls. Go to the folder 'c:\Program Files\Microsoft Office\Office\XLStart' and click Save to save the file and close the Save As dialog. Now, run Excel. It will open with Personal.xls. Choose Window->Hide and then choose File->Save and exit Excel. Each time you open Excel, Personal.xls will open hidden. To enter a macro into Personal.xls, you must choose Window->Unhide to make the sheet available.

  • When you want an Excel worksheet to scroll without scrolling the headings out of the picture, you can freeze the cells that contain the headings. Let's say that you have headings in row A. Click cell A2 and choose Window->Freeze Panes. If you'd like to prevent columns A and B (as well as row A) from scrolling, click C2 and choose Window->Freeze Panes. To unfreeze panes, choose Window->Unfreeze Panes.

  • Use conditional formatting to display a different colour when the cell's date is the current date. To set up an example current date indication, click cell A1 and choose Format->Cells. When the dialog box opens, click Date and then select a date format. Now, make sure cell A1 is selected and choose Format->Conditional Formatting. When the Conditional Formatting dialog opens, under "Condition 1" select Formula Is and enter =a1-today() and then click Format. Select black text and click OK. Now, click Add and for "Condition 2" use the following entries: Cell Value Is Not Equal To 0 (zero). Click Format and select red text. Click OK to close the dialog and continue. Enter today's date in cell A1 and the date should appear in red. If you enter any other date, it will appear in black.

  • When you're working with a small worksheet in Excel, there's no need to squint at the screen to see those few dozen cells. Instead, you can instruct Excel to display only the area you need to use. To do this, select the area and then choose View->Zoom. When the Zoom dialog box opens, select the "Fit selection" radio button and click OK.

  • To print the spreadsheet columns on every page choose File->Page Setup. When the dialog opens, click the Sheet. Now, click the icon at the right side of the "Rows to repeat at top" entry box. When the Rows to Repeat at Top dialog box opens, click the row where your titles appear (usually the top row) and press Enter. Back in Page Setup, click OK.

  • To use Find and Replace on all the worksheets in a workbook, select the first sheet and whilst holding down the Ctrl key click on the tab of the other sheets. You can now select Ctrl + H to open the replace dialog box.

  • When opening a file with File->Open you can use Alt and n where is between 1 and 9 to access the toolbar buttons in the file open dialog box.

  • Use the 'Application.ScreenUpdating=False' statement to turn off screen updating and speed up macros.

  • To go to the bottom of a column use 'Selection.End(XLDown).Select'. To get to the far right of a row us 'Selection.End(XLToRight)'.

  • Use either of the following two macros to loop through all the Worksheets in a Workbook. 
Sub WorksheetLoop()
Dim iwsCount As Integer
Dim I AS Integer

' Set 'wsCount equal to the number of worksheets in the active workbook
iwsCount = ActiveWorkbook.Worksheets.Count

' Begin the Loop
For I = 1 To iwsCount
  ' Add your own code here e.g.
  MsgBox ActiveWorkbook.Worksheets(I).Name
Next I

alternatively

Sub WorksheetLoop2()

' Declare Current as a Worksheet object variable
Dim Current as Worksheet	

' Begin the Loop
For Each Current in Worksheets
  ' Add your own code here e.g.
  MsgBox Current.Name
Next
  • To paste a picture of a worksheet into Microsoft Word select the range you want in the worksheet and then hold down Shift while you choose Edit|Copy Picture. When the Copy Picture dialog opens, accept the defaults and click OK. Move to your Word document now and click where you want the picture to appear. Press Ctrl + V to paste in the picture.