Sunday, March 23, 2008

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.

2 comments:

Suchitra said...

hey! can't help but comment that you don't need to select a column before setting the column width...the cursor can just be in any cell in the column to change the column width :)

Wally said...

@sushi... yup. error on my part.