Filename: Techniques For Large Models1Page # 1

Basic Techniques for Large Spreadsheets

Note for BUSA3700 students –we will come back to these ideas again later in the Term when we do the Filters and Pivot Tables

Objective

When we begin learning how to use EXCEL the spreadsheets, be they “what if models”or EXCEL lists are usually quite “small”. One can often see the whole model on display at a Zoom of 75%. In small spreadsheets manually techniques for selecting ranges, widening columns, and/or filling material work fine.By contrast, in large spreadsheets,manual methods are a pain. Perhaps the mostcommon problem encounters in larger models is the rampant mouse when one tries to manually fill down three thousand rows. New techniques are in order. These techniques are the subject of this document and its companion file Techniques For Large Models2.doc.

Before going on to new techniques note that we have already used two that apply to larger spreadsheets:

  • “auto” (ie/ double click) techniques for filling and widening – I believe you should continually use these even in small models to train yourself for their invaluable use in larger models;
  • The autosum button.

This file coversadditional techniques:

  • Using special keys, in particular Shift, Ctrl, End, and the arrow keys
  • Using mixed references to lock onto large ranges
  • An Appendix on using {Ctrl}+A and fx to input functions

Comments:

  • There a vast number of ways to use Key board shortcuts (the methods here seem to be easiest for people to remember). To see all of these: input key in EXCEL help then select Keyboard shortcuts.
  • The file Techniques For Large Models2.doc shows the use of the mouse to achieve similar results to the special keys. The mouse is fine for “selecting” but the special keys very handy for “function input”.

Obtain the file Test Data.xlsfrom the BUSA3700 Share outand open it …

EXERCISE SET1: Messing around to get a feel for the size of the model

Objective:Exploring how to Move around in large spreadsheets

When dealing with larger models or Lists it is useful to get an idea of the “size” of the spreadsheet so I will usually play around a bit before going to work;

CRUCIAL: How to interpret the computer input and jargon:

{} means input the special key indicated (ex{End} means tap the end key once)

Depress – means hold down key indicated (ex/ Depress {Crtl} means hold down the control key)

+ used in combination with Control or Shift means –Depress the Shift or Ctrl key then input the next key (ex/ {Shift}+{End} means Depress Control then tap the Home key)

Question: Where does it end?

Solution: {End}{Home} takes us to:

Question:How do we get back to cell A1?

Solution 1 (an old timer): Depress {Ctrl}+{Home}

Solution 2 (a modern method):

So again {End}{Home} to go to the last cell used

Use the Name Box:

Point at then click on the label D1010 in the Name Box:

Input A1:

{Enter}

Practise:

Use the Name Box to take you to my favourite cell: AB256;

Most of you will want to use the Name Box (which is fine) but to see another way use the Function Key {F5} to take you to cell A11[1]

Comment:

When you use “names” you can to go to the named cell or range quickly using the “drop box” feature of the Name box.

Before going on to the next set of questions make sure you are currently in A11:

Question: How can we use {End} and Arrow keys to move around?

Solution:From A11 try: {End}{↓} you will be in A1010

Practise:Goto A10 via a circuitous route as follows:

{End}{→}

{End}{↑}

{End}{←}

Interesting: The key are working on the fact that the material is contiguous (ie/ blocked together with no blank cells).

Question (think about the answer before you test it):

From A10 if you do {End}{→} followed by another{End}{→} where are you?

Try it

Question (think about the answer before you test it):

Where will{End}{←} take you if you are currently in IV10?

Try it

Question (think of the answer before you test it):

Make sure you are in cell D10;

What would happen if you did {End}{↓} then another {End}{↓}?

Try it

Question (think of the answer before you test it):

Next go to cell A10 any way you want;

What will happen if you do the sequence {End}{↑} twice?

Test your answer

Do you see how the computer works and the importance of a “contiguous block of input” or a “contiguous block of blank cells”?

EXERCISE SET2: Selecting Large Ranges of Cells[2]

In these exercise we select large blocks of cells so that we might –copy them, format them etc.

Comment:

We are neither selecting whole columns or rows nor the whole sheet. These can be done easily with the mouse or the mouse in combination with the {Ctrl} key.

Question: How can I select “a few cells?

Solution:

Depress {Shift} then do two {→} followed by three {↓}

Yielding:

Question (think of the answer before you test it):

Click on A11 (eliminates your selection)

What happens if you Depress {Shift}+{End}+{↓}?

Try it:

You should have selected all of Column A as illustrated (if it did not work click on A11 and try again):

Question (think of the answer before you test it):

What will happen if you Depress{Shift}+{End}+{→} then select the Bold and Italics format buttons?

Try it

You should have selected everything and made it bold italics (if it did not work try the exercise again:

Practise:

Get rid of the Bold Italics on columns B,C & D leaving only the names in A as Bold Italics as indicated:

Question (think of the answer before you test it):

What will be selected if you sequence as follows:

Name Box to go to cell B123;

Depress{Shift}+{End}{→}+{End}{→}

Solution:

The range B123 through D1010 as illustrated using the Window Menu to Split the screen:

Comment:

Notice we did not use Depress {Shift}+{End}{Home} to try to select the contiguous cells. The reason is that {End}{Home} is not looking for the end of the contiguous range but rather the last cell used. It is therefore unreliable so I do not use it for this purpose.

EXERCISE SET3: Using the Techniques to input formulas

Consider now the first few rows in the worksheet:

The next task is to input functions that calculate things like “average age”, “maximum years of service” and “minimum rate of pay” on our worksheet.

The thinking is as follows:

First see that the layout calls for a “mixed reference” in EXCEL jargon because:

  • we always want to use data in rows 11 through 1010;
  • we want to copy the same functions along columns B to C to D

Second we are dealing with large ranges of cells meaning:

  • we can profitably employ a {Shift}+{End}{↓}technique;
  • we do not want to be continually trying to select the ranges as this can lead to input errors

Third I love {Ctrl}+A to help input functions so I will call it up

Step 1: This is the “most difficult step

Input=COUNT(

use{Shift}+{End}{↓}to select B11:B1010

{F4} twice to select the mixed reference B$11:B$1010

) to complete the function

Notice that the reference for the function says B$11:B$1010 – do you understand the implication of this mixed reference if we fill down? If we fill right?

Step 2: Auto fill the function down (remember to auto-fill double click on fill handle)

Step 3: Activate C6 and use EXCEL like a word processor in the Formula Bar to replace COUNT with average:

Step 4 Repeat the Edits for the max and min functions (practise editing in the formula bar).

Step 5 Select all four functions and fill right…

Step 6: It’s done so make it look pretty and Save….

Appendix: Using Ctrl+A or fx

Background:

In the jargon of EXCEL a function’s syntax is made up of two bits: the name and the arguments.

Example: One commonly used built in function is count. So =COUNT(B1:B12) has two components the nameCOUNT and the arguments inside the () here B1:B12.

Many people use the =COUNT() for years not realizing that it has required arguments and optional arguments. To illustrate I have called up the count function:

Notice three things about the Dialogue box:

  1. The box says the name is COUNT in the upper left
  2. The first argument is Number1 in BOLD so it is a required argumentfor which I have inputted B1:B12
  3. The function has a second argument Number2. Since Number2 is not bold it is an optional argument which extends the power of the sum function beyond its usual (default) use.

So by bringing up the function I can readily see how it can be extended (or think about how to fine tune the function).

Now consider another. Slightly more complex function

COUNTIF has more than one required argument. The problem for more complex functions is that I often forget which arguments are required and in what order. So by bringing it up I get assistance with this problem.

With this background in mind two techniques that I find handy:

Technique #1 – Used whenI can remember the name of the function:

  1. Input the name as indicated:
  1. Depress {Ctrl}+A to bring up the complete syntax:
  1. Then use point and click to input the function. While inputting I get help on what bits are required and learn about other extensions of the function (or REMINDERS when I need to go beyond the basic use and fine tune the function).

Technique #2 – I need to find the name of the function:

(Sometimes I need the full Help but usually fxsuffices)

Problem: I know EXCEL will do payments of loans but I cannot recall the name of the function forall the bits:

  1. Select the cell to input the function.
  2. Then select fx and input Search for: payment as illustrated:
  1. Select Go then peruse to find the right function:
  1. If I need more information I seek Help on this function
  2. However 9 out of 10 times I select OK.
  1. Which takes me to the {Ctrl}+A position:

Appendix on the Absolute Key {F4}

{F4} is a cycle key allowing you to choose various types of reference structure.

Looking at our example:

The default is relative reference B11:B1010 which means that should you filling the function down the range would become B12:B1011while filling to the right results in C11:C1010.

To adjust the function you use the {F4} key. Inputting {F4} once yields:

An absolute reference that always refers to B11:B1010 no matter how you fill.

A second {F4} creates a mixed reference allowing columns to adjust as you fill right but locking the rows:

Similarly a third {F4} creates another mixed reference that locks columns but not rows:

When inputting ranges its easy to mess up the setting – so always double check before you fill. If necessary rest the reference

[1]F5 was the major go to tool in older spreadsheets. It will not be very important for many of you but if you have larger models that require that you “track” your go to moves {F5} can be handy.

[2]In this Section I will use the {End} key as it is easy for most people to remember and see what is going on. For the curious you might want to see what happens if you simultaneously depress {Shift} and {Ctrl} then input various keys like {End} or the arrows. It can speed up selection slightly if you are comfortable remembering this. I myself seem to gravitate to the {Shift}+{End} ideas illustrated. Similarly we do not introduce the extend key {F8}.