Excel keyboard shortcuts

Finally, completing the stuff i read on chandoo's favorite shortcuts, I use a whole lot of them

1) Ctrl + 1 : format
2) Ctrl + shift + 1: Number with 2 decimals
3) Ctrl+shift+5: convert to %
4) Alt + o, followed by c,w (or alt+o, followed by r,h), column width (row height)
5) Ctrl + spacebar (shift + spacebar) select column (row)
6) Once selected, ctrl+shift+ "plus" (ctrl+ "minus") add (delete) row
7) Alt+e+a+a clear all
8) Ctrl+r, copy right, ctrl+d, copy down
9) Ctrl+down arrow (right arrow), end of series (row).
10) Ctrl+ [ - this is awesome takes you to cells on which your reference cell is dependent. use ctrl+5 to get back.

Xl for dummies - II, Xl Modeling Rules

Now that you are ready, simple modeling rules:

• All assumptions go to a single sheet, to change this is a cardinal sin. There should be no hanging numbers (yes not even tax rates or some other arbitrary number) in any sheet. If I gave the assumption sheet to a someone who knows what he is doing, he will be able to recreate the entire model from the assumption sheet.
• All inputs (hard coded numbers) are colored blue (typical conventions) and only those generally need to be changed. To avoid complexity you can just use one color code, but lot of modeling people i know use a master sheet in which they define what each color coding (assigning a color to a particular text or no).
• The units of each input need to be clearly defined and so should the dates.
• Use links and formulas, there should be no repetition of the assumption numbers anywhere (this is damn irritating, you are trying to see the change in the model based on one assumption and you find that you have to change the inputs at a zillion places)
• Coloring cells: This is a bit of an open area, I personally dislike a lot of colors in my model, but lot of modeling gurus prefer using them (as i said, I am a dummy)
• Do not hide rows, columns (or sheets for that matter). If you don't want the focus there, just group the rows/columns. The viewer will decide whether he wants to see them or not.

Please save continuously, Jesus saves.

Xl for dummies - I

Let's for a second assume that you also have the misfortune of running into (yes weird usage of the term) which involves a lot of xl files and you for lots of reasons did not listen to that class in college.

To begin with, xl is not that truant woman who does not respond your overtures, quite the contrary its one msoft product that you can score if you ask the right queries ( nope i am not a nerd).

Most people use a multi tab format or a single tab format (data in different sheets or data in single sheet) . I suggest you use a multi tab and the cut and paste the data into the same tab to make it easier to model.

Getting started (I am assuming you know a little bit of how xl looks like):

1. Right click on the empty space and choose both standard & formatting options for your menu.
2. Go to tools options, calculations choose iterations with 100 as minimum and maximum change as 0.001 (most models are built in a circular mode, the model will break if you dont have this enabled)
3. Go to tools, add-ins and enable the solver add-in and the data analysis

The basics:

1. Begin with a basis of 5 sheets (each tab being a sheet and the file being called a workbook), right click on the sheet and you will get the option of insert sheets.
2. Right click on one of sheets (say sheet 1) and choose select all sheets (what you are doing is grouping sheets, you will see in the window itself a could suffix). Now you are ready to create a basic good looking workbook. (please note that changes in any one sheet will be reflected in all the sheets)
• In any sheet place the cursor on cell a1 and hit (ctrl+space), the column A is selected. Now hit Alt+o followed by c and then w, you get to column width, set it at 2 or 2.5
• Next, select column b (move cursor to column b, do ctrl+space) and set it to sum huge number say 16 (alt + o, c, w then 16). More often then not the second column is where your primary data should go in a model (if its financial of course)
• Set column c at 0.5
• In the sheet that you are working use Ctrl+A (select all), then choose your favorite font from the menu options. I use trebuchet MS or Book Antiqua
  
• In cell B2, write down your project name (Say Project Madrid)
  
3. Bang you are ready to roll, all the sheets look the same. You could also insert another sheet as a rough, which would be deleted at end of model. Now again click on sheet 1 and select ungroup (the group suffix on the window must be gone).

Warning: Save the sheet first, Jesus always wins over Satan because Jesus Saves.