Excel Tips.  Following are a few useful tips for editing and operating in Excel.  This is by no means comprehensive and the tips are limited for brevity.  If you are new to Excel, or require more in-depth training, I recommend Dr. Al Napier’s tutorial at http://freetutorialcentral.com/. 

  1. Install the analysis toolpak and Solver add-in.  Search Excel help for the procedure, or Windows Button < Excel options (at bottom of pop-up) < Add Ins (left menu ribbon) < Go button (bottom left of box), pull down should have Manage:  Excel Add-ins selected < check the box for Analysis toolpak and the box for Solver.  You can also obtain excel options by pressing “Alt+t o” in 2007 or 2005.
  2. Activate iterations.   Windows Button < Excel options (at bottom of pop-up) < Formulas < check the box for enable iterative calculation (upper right hand area).  Again “Alt+t o” will pull up the options menu box.  Iterations allow circular functions in your spreadsheet.  Circular references are necessary to appropriately figure interest income and expense and in certain equity and valuation situations.
  3. Formula Auditing.  In older versions of Excel add the Auditing Tool Bar.  In newer versions, it is on the ribbon under the “Formulas” tab.  This function is invaluable in creating, editing, and debugging models.  The “Trace Precedents” command creates a series of arrows (double click the arrow body to follow to the end) from the cell you have selected to the cells that that cell refers to.  Each time you click on the command, the arrows “tree” out as references follow references.  Thus you can quickly find where the ultimate driver of an answer is.  If you start with a total result like NPV, you will / should eventually see an arrow point to each cell in the model.  When a reference is off sheet, a dotted arrow with a small table at the end of it appears.  Double click on the arrow body, and a dialog box will appear with a list of off sheet references.  Double click on any of these and you will be transported to the off sheet reference.  “Trace Dependents” is the opposite command.  When you click this command it tells you if any other cells refer to the selected cell.  To find out what depends on an input, or if a cell can be deleted, this is very useful.  It also helps cull out “dead wood” in a model that does not affect the output or operation of the model.
  4. Reference jump.  There is a keyboard shortcut for Trace Precedent and Trace Dependent, but it will only go to the first cell in the formula progression or the first cell that refers to that cell (the others are highlighted)  Simply press Ctrl+[ (Precedent) or Ctrl+] (Dependent).
  5. Reference view and formula editing.  Press F2.  This not only opens the formula for text editing (w/o double clicking) but also highlights the cell references in different colors.
  6. Formula view.  Ever wonder which cells contain formulas, or want to have a permanent F2 view?  Press Ctrl+~ and the spreadsheet will toggle into a view of the inputs of the all of the cells (you will see “=F2+F3”, rather than “100”).  This is a good way to quickly look through another’s spreadsheet to find where hard codes (which you should not do) and formulas are located.
  7. Moving between tabs.  In the format section of this site, I recommend using different tabs in a financial model.  Navigate between tabs using Ctrl+PageUp and Ctrl+PageDown.  You will fly around with ease.
  8. Page Break Preview.  Use it often to arrange your pages for better printing and squeeze information on or off of a page.  On the Ribbon:  View < Workbook Views < Page Break Preview.  Drag around the lines to change the page settings.
  9. Page Layout View.  One thing that the ribbon actually made better is adding headers and footers.  Many spreadsheets are printed, and without page numbers and headers and footers, model review meetings can quickly turn into a literal “getting on the same page” meeting.  Use this view to add headers and footers.  Add the file name (where you will have a version number), tab name, page of pages, and date and time.  That way, whenever you print, you know which version you have and what you are looking at.  The header is a good place for the tab name, or an overall title to the model.
  10. Printing All at Once.  If you Ctrl+click multiple tabs (or more quickly Ctrl+Shift+Pageup) you will enter group mode (you will see [group] next to the file name).  WARNING: In group mode any edits made to a cell will be made to all of that cell in all sheets in the group (all cell A1s on each selected tab for example).  This is very useful in a few narrow situations, but can seriously blow up your model.  Once you have printed, click on a tab or Ctrl+Pageup/down to clear group mode.  After grouping the whole model, print it.  Now, all of your sheets will print together and will have sequential page numbers.
  11. Cycle through absolute formula references.  When you are entering a formula, press F4 to cycle through absolute references (none, row, column, both).  If you are clicking, this is quicker than editing by typing the “$”.
  12. Use F4 to repeat an action.  When taking a repeated action (delete shift cells right for example), press F4 (when you are not editing in the formula bar) and the action will be repeated.
  13. Use a split screen.  Excel allows you to view two or more different windows containing the same workbook at the same time.  This is very helpful when working across tabs or viewing results when changing inputs.  On the view ribbon tab, select “New Window”.  Then, downsize and/or re-arrange the windows.
  14. Freeze panes for your dates and labels.  When scrolling across large sheets, the labels for the rows and columns can be lost.  Use the freeze panes button on the view tab of the ribbon to keep rows and columns in view.
  15. Filling down when you need to go across / transposing while preserving formulas.  You may find situations where you need to refer to data going down (row 1,2,3,4,5…) when you need the resulting formulas to lie horizontally (column A,B,C,D ).  Excel advises to use absolute references.  That requires editing each cell after you fill, which may not work.  This simple trick (which appears complicated, but is in fact very rapid with practice) will save you a lot of time in these situations.  I have included an example download Horizontal Vertical to better illustrate the example.  The download sheet shows the steps through repetition on the sheet - you would obviously not re-copy everything.  First, fill down your formula in some empty space on the sheet, then highlight all of the instances.  Second, open the replace text dialog (Home
    1. Finding named cells (or a particular reference when trace is too difficult).
    2. Changing sheet and file reference names.  If your versions are out of control, or you are copying formulas from somewhere else, changing the text in the formula can be helpful in quickly modifying formulas.
    3. Creating wedges.  Depreciation, tax MACRS, and acquisition layer wedges can be quickly created using similar techniques and using find and replace for text.  Text replacement can be useful to wholesale change references to absolute after filling formulas if syntax is not duplicated.  Use additional characters (such as find and replace “+C7” with “+$C7” rather than “C7” with “$C7”) to avoid unintended replacements.
    4. Cleaning up raw data.  This happens less often with time, but there are still occasions when you import data that has extra spaces or dashes rather than minus signs.  Use find and replace to remove them.  Excel can also change a text number into a real number by multiplying it by 1.
  16. Watch out for Excel.  Sometimes, Excel thinks too much.  When filling across more than one column, the new version of Excel quite often increases any fixed number by 1 for each column.  If you thought you were filling across 500, you may really get 501,502,503, etc.  If you do have a repeating, fixed number it is better practice to have a single defined input and use a formula equal to the prior cell.