Excel Page 5
Welcome to Blaisdell's Little Corner of the Web
MsOffice Index 1  MsOffice Index 1I
 Microsoft Mac Office 8.0 
Microsoft PowerPoint 97  Microsoft Office XP  Microsoft Bug of the Month
 Microsoft
Macro Virus from MacOffice  Updated 12/20/05
THREE WAYS TO DO THE SAME THING IN EXCEL 
PART 3
+Writing a VBA script
+Visual Basic (Applications Edition) VBA
+Trhe Script Part 1
+The Script Part 2
BULLETS IN EXCEL
You can set up a style to make it easy to apply bullets in Excel spreadsheets.
One note: when you type the character number for the bullet (Alt+0149), you need to use
the numeric pad, on the right side of your keyboard. If you use the numbers directly above
the letters, Excel will just beep at you. You can find an exhaustive list of common
character numbers at http://www.bbsinc.com/symbol.html
Note that if you add more spaces after the bullet, Excel "indents" by as
many spaces as you type.
If you want to make the bullet style available in all of your worksheets.
Then click File  Save As, in the File Name box type book, in the Save as type box, pick
Template (*.xlt), and click Save.
Two additional ways to apply bullets, once you've set them up in a cell: "Copy the cell that has the bullet style in it. Select the cell or cells in which you want to apply the style. Choose Edit  Paste Special, then select Formats. Voila. That's really not much faster but will give you whatever other formats you have in the cell as well as the bullets. Or, you can select a cell that has the bullet. Click the Format Painter toolbutton on the Standard toolbar. Select another cell or range of cells where you want to apply the bullet style, and there it will be. To use the format painter more than once, double click on the button and then wherever you click, the format you're "painting" will be applied. Doing the "more than once" thing requires that you turn the Format Painter off when you're done by simply clicking the button again."
"The click on 'Text' option step is unnecessary. The '@' symbol is defined as a custom number format, so you can just type it over the word 'General' and it will work."
ColorCode Your Excel Data
Excel 97/2000
To make your data easier to read, colorcode it with the Conditional Formatting feature. Select the desired cells, then choose Conditional Formatting from the Format menu. In the first field of the dialog box, choose which value you want to base the condition on (for example, Cell or Formula). In the second field, select the terms (for example, Between or Greater Than Or Equal To). In the third and fourth fields, choose the desired numerical range. To specify the cell's appearance, select the Format menu, then experiment with font, border and pattern (which includes color). Click on OK to complete the first condition, then repeat the process to add more conditions.
Q. I have an Excel Worksheet with data in cells A1:B10 plotted on a chart. Each day I add another row of data. How can I make the chart range grow automatically, so that tomorrow it is charting A1:B11, the following day A1:B12, and so on?
A. Lets assume you are working in sheet one with column headings in ROW 1 and days in column A. Data relating to each date is in column B. Using Insert  Name  Define, type each name in the Names in workbook area, and then type the corresponding formula in the refers to area. Click Add and type in:
Days=OFFSET (Sheet1! $A$2,0,0,
COUNTA (Sheet1!$A:$A)1)
PlotData=OFFSET (Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)1)
Click on the series in your chart so that the =SERIES formula appears in the formula bar. Edit the second and third arguments in the formula, replacing the reference to the Column A range with the range name Days and the reference to the column B range with the range name PlottData. The result should be something like this:
=SERIES9Sheet1!$B$1,book1!Days,Book1!PlottData,1)
Now the chart will automatically grow as you add data
Excel 97/2000
Simulate Greenbar Paper
in a Sheet
Data printed on greenbar computer paper can be much easier to read than rows and rows of data printed on plain white paper. If you don't have access to special paper, you can reproduce the effect in your worksheets. The first step is to color the first row or rows. Then, select those rows plus an equal number of blank rows and click Format Painter (the button with a paintbrush on it). Next, select the remaining rows in your sheet area. Excel will copy the colored and blank rows to fill the rest of the sheet area. Generally, it's best if you apply this format to a sheet before adding data. Now, let's look at a quick example. In a blank sheet, select row 1, then select any color from the Fill Color palette on the Formatting toolbar. (Generally speaking, a lighter color is better.) Now, select rows 1 and 2 and click Format Painter on the Standard toolbar. Select rows 3 through 20, and Excel will alternate the color and blank rows accordingly.
EXCEL ENERGIZER  "OR" CONDITIONAL
CALCULATIONS
By Helen Bradley
In the previous issue of Excel Energizer I looked at some of your options for creating sums based on two conditions where the conditions were of the type "Sum Wages where Staff = Yes and Department = Accounts". This week I'll show you how you can make calculations which use OR conditions to make calculations of the type, for example, "Sum Wages where Staff = Yes or Department = Accounts".
A breif review: example, here's a sample worksheet fragment to enter so you can test
the formulas:
Or see: MANAGING CONDITIONS IN EXCEL
Consider this worksheet fragment:
A B
C
1 Staff Wage Department
2 Yes 350 Accounts
3 No 100 Sales
4 Yes 200 Sales
5 No 300 Accounts
One of the formulas from last week, which we used to add the Wages for employees who were both staff (Yes) and who worked in Accounts was this:
=SUM(IF((A2:A5="Yes")*(C2:C5="Accounts"),B2:B5))
This formula works on the basis that each test will evaluate to TRUE or FASLE (1 or 0) and the results of both tests are multiplied together. Only if both tests evaluate to TRUE (1) will the result of the multiplication be 1, and the Wages amount for that row will be included in the total.
You can use this same process to make an OR calculation, but this time you should add the results, rather than multiplying them. Now, when the results of the tests are evaluated and added, the Wages amount in any row in which any one of the tests evaluates to true will be included in the result. If both tests evaluate to true, the amount will still be included. So, this formula will sum all the Wages for all employees who either work in Accounts or who are staff:
=SUM(IF((A2:A5="Yes")+(C2:C5="Accounts"),B2:B5))
Remember that this is an array function, so you must press Control + Shift + Enter after you've typed it for it to work. If you don't, press F2 to edit the cell and then press Control + Shift + Enter. You'll know you've got it right when you check the cell formula and it's surrounded by curly brackets {}.
Similarly, you can use array formulas to count the number of employees who either work in Accounts or who are staff:
=COUNT(IF((A2:A5="Yes")+(C2:C5="Accounts"),B2:B5))
But the action doesn't stop here. You aren't limited to COUNT and SUM and you can use other functions like AVERAGE for example. Try these functions which average the Wages for both our previously stated AND and OR test criteria:
=AVERAGE(IF((A2:A5="Yes")*(C2:C5="Accounts"),B2:B5))
=AVERAGE(IF((A2:A5="Yes")+(C2:C5="Accounts"),B2:B5))
You can also use MAX and MIN as shown here:
=MAX(IF((A2:A5="Yes")*(C2:C5="Accounts"),B2:B5))
=MAX(IF((A2:A5="Yes")+(C2:C5="Accounts"),B2:B5))
Once you see how easy it is to create array formulas you can extend them to calculate multiple criteria, for example, this will calculate the Wages for all employees who work in Accounts and are Staff, or those who are not Staff and who work in Sales:
=SUM(IF(((A2:A5="Yes")*(C2:C5="accounts"))+((A2:A5="no")*(C2:C5="sales")),B2:B5))
When you need to do tedious calculations like this, array formulas allow you to reduce the workings to a single cell which is both neat and very easy to troubleshoot when things go awry.
MANAGING CONDITIONS IN EXCEL
by Helen Bradley
You already probably know that you can use the SUMIF and COUNTIF functions to either sum a range of values or count the number of matching entries based on a single criteria. If you're unsure how these work, the formula let's look quickly at one.
Consider this worksheet fragment:
The formula =SUMIF(A2:A5,"Yes",B2:B5) will add up all the values in B2:B5 for which the corresponding entry in A2:A5 is Yes and it will return 550 (350+200).
A  B  C  
1  Staff  Wage  Department 
2  YES  350  Accounts 
3  NO  100  Sales 
4  YES  200  Sales 
5  NO  300  Accounts 
COUNTIF works similarly and counts how many matching entries there are. It would read =COUNTIF(A2:A5,"Yes") and would return 2. But how do you handle the situation where you want to test for more than one condition. For example, if you want to know how much you've paid to Staff in the Accounts department? SUMIF doesn't handle multiple criteria so you can't use it.
To do this you can use an array formula. An Array formula is a more complex formula to create but it works very well in situations like this one. The array formula which will solve our problem is:
=SUM(IF((A2:A5="Yes")*(C2:C5="Accounts"),B2:B5))
or even shorter,
=SUM((A2:A5="Yes")*(C2:C5="Accounts")*B2:B5)
These formulas only add the corresponding entry in column B if the corresponding tests on the data in columns A and C return true values. Excel calculates this by treating the values in columns A, B & C as one dimensional arrays and it performs the calculations using these arrays. It evaluates the tests on the data in row 2, then row 3, then row 4 and finally row 5 and then sums the results.
To count the matching entries (rather than sum them) use:
=COUNT(IF((A2:A5="Yes")*(C2:C5="Accounts"),B2:B5))
However, you can't just enter either of these formulas into a cell and expect it to work  it won't unless you press Control + Shift + Enter which indicates to Excel that you're using an Array formula. If you forget, you'll see #VALUE! appear in the cell and you should select the cell, press F2 as if to edit it and press Control + Shift + Enter to enter the formula in the correct manner.
Array formulas like these make multiple calculations and return a single result from them. While many calculations can only be made using array formulas, this is one calculation you could have made another way  using Excel's Conditional Sum Wizard addin. Test this by loading the Addin  choose Tools, Addins, enable the Conditional Sum Wizard and click Ok. You'll find it is now installed on the Tools menu  select Wizard then Conditional Sum. To create the formula, first select the entire data area as the list to work with, then specify the two tests which must be met (the Staff value must be equal to Yes and the Department value equal to Accounts, and finally indicate the cell to contain the answer. The resulting formula will be different to the two we've shown but works just as well.
PASTING UNFORMATTED TEXT IN EXCEL AND POWERPOINT
The Macro
TAMPERING WITH A RECORDED MACRO
Let me take advantage of this opportunity to show you a bit more about Office macros in
general. To do that, I'd like you to crank up the Macro Recorder  the little gizmo that
looks over your shoulder, and creates macros based on what you do. In theory, you can
record your actions, have them stored away in a macro, and "play them back" by
running the macro.
I almost never recommend that people use the Macro Recorder. Through no fault of its own, the Recorder isn't capable of recording most macros the way you want them recorded. Most of the time, if you record a macro with the Recorder, it won't "play back" the way you expect it to. Sometimes the problems are so obscure you really need to understand Visual Basic for Applications in order to make heads from tails out of the recorded macro. (A lot of it has to do with the cursor location, and how the Recorder has to guess what you are doing when you click or move the cursor. Details and examples are in the books listed in the preceding section.) Adding injury to insult, PowerPoint has a weird way of storing macros, so you even if you could get the macro to work, you could only make the unformatted paste work for individual presentations.
The Macro
In this case, though, recording a macro can kind of get you kickstarted, taking care of
several behindthescenes details that are a pain in the neck to do by hand. It'll also
show you how PowerPoint's macro Recorder doesn't work worth squat. So I'll dispense with
my usual Recording caveats and cut to the chase.
Here's how to create a PasteUnformatted macro in Word or Excel  and how you should be able to create one in PowerPoint:
> Start the application  Word, Excel or PowerPoint (the other Office apps don't have macro recorders).
> Copy some text. It doesn't matter where you get the text, or what it is. You just need to put something in the Windows clipboard.
> Click Tools  Macro  Record New Macro.
> In the Macro Name box type something like PasteUnformatted
> If you're using Word, make sure the line "Store Macro In" says "All documents (Normal.dot)" and click OK to start the Recorder.
> If you're using Excel, "Store Macro In" should say "Personal Macro Workbook" (and you'll save yourself a bit of time if you tell Excel to assign this macro to the Shortcut key Ctrl+V). Click OK to start the Recorder.
> If you're using PowerPoint, you are pretty much stuck with putting the macro in a specific presentation, which is a real pain. (Yes, I know there are alternatives, but none of them are much good.) Choose the presentation and click OK to start the Recorder.
> With the Recorder running (you can see it sitting in its own window), click Edit  Paste Special  Unformatted Text (in Word or PowerPoint) or Text (in Excel).
> While you're here, savor the consistency among Office apps. Word is quite different from Excel, and both are quite different from PowerPoint. You can't even record a macro in Access. In Outlook, you can only record a macro if you're using Word as your email editor  and then you're actually using the Word Recorder. But I digress.
> When Word, Excel, or PowerPoint is done with the Paste, click the Stop button on the Recorder.
In the next section I'll show you how to take a look at the macro that was recorded.
TAMPERING WITH A RECORDED MACRO
If you were following along in the preceding section, you now have a recorded macro called
PasteUnformatted. What you do next depends on whether you're trying to get
PasteUnformatted to work in Word, Excel or PowerPoint.
In Word, click Tools  Macro  Macros and pick PasteUnformatted. Click Edit. You'll see
something like the macro I gave you in PASTE UNFORMATTED TEXT IN WORD
More on the Word
Macro here
In Excel, you have to "Unhide" your Personal workbook before you can look at the macro. (Ah, consistency among the Office apps. Ain't it wunnerful.) To do so, click Window  Unhide, pick Personal.xls and click OK. At that point, you can see the macro you recorded by choosing Tools  Macro  Macros, clicking PasteUnformatted, then Edit. Here's the macro I recorded:
Sub PasteUnformatted()
'
' PasteUnformatted Macro
' Macro recorded 10/24/2001 by Woody Leonhard
'
' Keyboard Shortcut: Ctrl+v
'
ActiveSheet.PasteSpecial Format:="Text",
Link:=False, DisplayAsIcon:= _
False
End Sub
The Recorder sticks in a lot of unnecessary junk. If you go
into the recorded macro and edit it so it says, simply:
Sub PasteUnformatted()
ActiveSheet.PasteSpecial Format:="Text"
End Sub
It'll work.
PowerPoint is a basket case. In PowerPoint, click Tools  Macro  Macros, pick
PasteUnformatted, and click Edit. Visual Basic for Application appears, and shows you that
PowerPoint's Recorder has created a macro that looks something like this:
Sub PasteUnformatted()
'
' Macro recorded 10/24/2001 by Woody Leonhard
'
End Sub
If you've ever seen a macro before you might notice something's not quite right
with this recorded macro. The PowerPoint Recorder neglected to record anything! That macro
doesn't do a thing.
I played with it a bit, and discovered that you can paste the contents of the
clipboard into the current slide by using the command ActiveWindow.View.Paste
But I'll be hanged if I can figure out how to paste text only, or do a Paste
Special of any type. If one of you PowerPoint gurus can feed me a clue, please do so!  Please
click here
I'm a firm believer in the philosophy of not doing anything I can con, cajole or just plain bribe someone else to do for me. Well, Ok, I'm exaggerating a bit, but I really hate doing unnecessary work. That's why I love all the quick fill and list tools in Excel and why I'm always looking for a way to save a keystroke or three.
This week I have a range of quick solutions for entering data into Excel. They won't do all the work for you, but they can relieve you of some of the burden of repeated data entry.
The first offering is Excel's list options which will fill a range of cells with related data such as days of the week and months of the year. If you haven't used it before, go ahead and type January into a cell. Now select the cell and position your pointer over the bottom right hand corner of the cell so it changes to a small plus symbol, grab it with your mouse and start dragging. It doesn't much matter which direction you go but down or to the right is the most usual. If you hold your mouse button steady for a second or two, the later versions of Excel will show a tooltip at the mouse pointer telling you the month that will go into the last cell you have selected if you let go the mouse right now. Adjust your mouse position until you get December in the tooltip and let go. Voila! the months of the year are entered for you.
You can start this series at any month, so you could have begun at June, for example. You can also type three character abbreviations for the months and Excel will follow suit, e.g. Jun, Jul, Aug etc.. If you're using Excel 2000 or later and you have the first cell you enter already formatted, the formatting will be copied to the cells along with the month names.
This also works with the Days of the week using either their full names, e.g. Monday, Tuesday or their three letter abbreviation.
DIY LISTS
While showing others how to use these built in lists is a great party trick 
you can go one step further and create your own. So, for example, if you routinely enter
the names of a list of people, or offices or states and provinces you can easily create
these as a list so they'll perform just like the days of the week and the months do.
Laura Stewart covered the creation of lists in some detail in WOWMM volume 1, Issue 4 so I suggest you check that out. The shortened version is this; choose Tools, Options, Custom Lists and type the list items, in order, in the dialog with each item separated from the next by a comma. Click Add to create these entries as a list. Now to try it out, type one entry from your list and drag away to fill adjacent cells with your data.
QUICK FILL
If you're still not totally impressed, let's look at the Data Fill options which
can make short work of tasks like entering days of the week, for example. In a cell in a
workbook type the date for a Monday, select the cell and use your mouse to locate the fill
pointer in the bottom right of the cell and this time drag it down or across the workbook
using your right mouse button. When you let go you'll see a range of options, including
ones to fill using Fill Days, Fill Weekdays, Fill Months, Fill Years etc.. Using days will
fill each cell with the successive day of the month, using Weekdays will omit Saturdays
and Sundays from the dates and Months and Years will increment the date you've entered by
one month or one year at a time. It also accommodates shortened months like February and
the 30 day months too.
Until now, you may have noticed that, when you type a number, for example, the number 1 or even the year 1999 and drag the fill handle you'll fill the selected cells with the same date. However if you use the right mouse button to drag with, you'll be able to choose Fill Series from the menu to enter the numbers 1, 2, 3 etc., or the years 1999, 2000, 2001 etc..
You can also enter data in a series you choose, for example, you may want to enter the dates for successive Mondays. Enter the starting Monday's date and use your right mouse button to drag the fill handle down or across a number of cells. From the shortcut menu, choose Series (not Fill Series) and set the Step value to 7 (the number of days between one Monday and the next). Set the Type to Day and the Date Unit to Day and click Ok and the dates will be entered for you. If you don't want the dates to go beyond a certain point, you can enter that 'stop' date in the Stop value box  just ensure you enter the dates in the format Excel expects according to your country and date format. If you're unsure, using the format 11Nov2001 should work just fine.
QUICKLY CREATE DUPLICATE ENTRIES
When you have data entered on one worksheet that you want to copy to other worksheets, you
can do it using a single step (and one which doesn't use the Copy button!). Begin by
selecting both the sheet that is complete and the sheets you want to copy the entries to.
To do this, click the sheet tabs to select them while holding down the Control key or
click the first tab and hold the Shift key as you click the last in the series. Now select
the actual cells which contain what you want to copy, and choose Edit, Fill, Across
Worksheets. You can then choose to fill All, Contents or Formats as you require. To return
to work on just one worksheet, click on a worksheet tab to select it or right click a
worksheet tab and choose Ungroup Sheets.
If you know ahead of time that you'll be wanting to enter the same data on a number of worksheets you can select the worksheets before you begin and then enter the data into one of them. It will then be automatically entered in all the others you have selected.
Another quick and easy way to enter data is useful when you want to enter the same value into a number of cells. To do this, select the cells, type your data into one of them and press Control + Enter to enter it into them all. You can do this for cells which are adjacent and those which aren't (hold the Control key to select non adjacent cells).
This selection is merely the 'tip of the iceberg' of possible ways you can shortcut your day's work in Excel. Keep your eyes open and you're sure to find plenty more.
Autofill
All recent versions of Excel are able to fill in a sequence of cells based on
the few you've typed.
For example, enter 1 into a cell then 2 into the one next to it. Select both
then drag that selection (using the little box in the bottom right corner of the
selection) across. Excel will fill the new cells you select with the
numbers 3, 4 ,5 and so on.
It gets even cleverer  type in 2 in one cell and 4 in the next, extend the
selection as you did before and the numbers are 6, 8, 10 etc. You can
enter any two numbers and Excel will try to guess the series:
10, 20 will continue with 30, 40, 50 etc
5, 10 continues 15, 20 etc
It also works in reverse:
100, 95 continues 90, 85 and so on into negative numbers if you wish.
F
Back to Autofil Index
Autofill For Text
This same idea extends to other nonnumber lists like Days of the week and
months.
Type in Jan in one cell, Feb in the next and extend those cells out to see Mar,
Apr, May appear. This also works for the full name of months, starting
with January.
Custom Autofill
Going to Tools  Options  Custom Lists lets you see where Excel has the days of
the week and months listed in long and short form.
You could alter those lists if you wanted to though I can't imagine why.
Perhaps you have a really autocratic boss who has decreed that Wednesday is to
be named after him instead of Woden ? <g>
This is also where you can add new lists to suit your particular needs.
Click on New List type in your list entries on the right side, finish with Add
to include it in the list of Autofill lists.
To edit an existing list, click on that list summary on the left side then the
whole list will appear on the right for editing.
The common example used of custom lists is a list of staff or team members but
that's probably not a good practical example because a list of staff can alter.
Custom lists are probably better for lists that are unlikely to change but you
use regularly. Some possibilities are:
List of family members  Mum, Dad, Belle, Claudia etc.
Floors in a building: Basement, Car Park, Ground, Mezzanine, 1, 2 etc.
Decks on a ship (where the decks have names): Mozart, Puccini etc
In the spirit of the season you could have a list of apostles, books in the old
or new testament or reindeer in the famous poem.
F
Back to Autofil Index
Importing A Custom List
On the custom list dialog there's an option to import a list from a series
of cells. This gives you an easier way to create and manage a list.
Type in your list into a column of cells, then go to Tools  Options  Custom
Lists and then the import button. Select the range of cells then Import to
bring the list into a new custom list.
F
Back to Autofil Index
Reordering A List
There is no way to directly reorder a list, all you can do is cut and paste the
list into the order you want.
Or you can autofill the entire list into a worksheet, edit or reorder the list
using normal Excel features then import it into Custom Lists to replace the
original list.
F
Back to Autofil Index
MultiLingual Lists
You can also add lists in different languages. You may need to insert days
of the week in French, German or some other language. Since Excel inserts
only the lists applicable to your default language, you can add other 'parallel'
lists in other languages.
F
Back to Autofil Index
Limitations Of Autofill
Autofill is a nifty idea but, like too many other features it really doesn't go
far enough.
Most obviously, the lists are not dynamic. They cannot be linked to a
series of cells or other data source. Therefore you have to remember to
change the custom list as circumstances change.
In addition once you have autofilled a list into a worksheet, that list doesn't
change if the source custom list changes. The commonly used 'staff
members' example is a good instance of this limitation. If a staff member
is replaced, your existing custom lists in worksheets won't change automatically
(which may, or may not, be what you want).
If you forget to change a custom list, inattentive staff might accidently use an
outdated list which could have annoying or more dire consequences.
Tip: If
you have a lot of custom lists, create a worksheet that has all the lists in a
series of cells. This makes for easier reference, changing and updating
the custom lists as necessary.
Using AutoFill in Excel
The Same thing, only different
Reader Bob asks:
Question: Request for assistance in taking autocorrect autotext from MS Word 97 to a spread sheet in excel for a data base. Can you help? Thank you in advance for your consideration.
Aanswer: Maybe Bob.
Not to sure what you are asking here. Word and Excel are a tad different in the way they handle similar or like data.
That being said, and I am taking a shot in the dark here, I am assuming that you want to lessen the hassle of constantly entering like data into cells? This is accomplished with the use of Fill Handles in Excel.
What is a fill handle?
The small black square in the corner of the selection (See figure 1 below). When you point to the fill handle, the pointer changes to a black cross ( + ). To copy contents to adjacent cells or to fill in a series such as dates, drag the fill handle. To display a shortcut menu that contains fill options, hold down the right mouse button as you drag the fill handle. Types of series that Microsoft Excel can fill in for you
You can automatically fill in several types of series by selecting cells and dragging the fill handle or by using the Series command (point to Fill on the Edit menu, and then click Series). To select the type of series from a shortcut menu, select the starting values for the series; then hold down the right mouse button as you drag the fill handle.
What is auto fill, part due? Well, pictures are worth a thousand words. Here is an example of a string of dates in cell A, by grabbing the + on the lower right hand side of the auto fill and dragging it accross you can auto fill the rest of the days of the week. Please see figure 1 below. This also works if you drag vertically or to fill a series by holding the right mouse button. Experiment a little, you'll get it.
Time
A time series can include increments of days, weeks, or months that you specify, or it can include repeating sequences such as weekdays, month names, or quarters. For example, the initial time selections in the following table result in the series shown.
Initial selection Extended series
9:00 10:00, 11:00, 12:00
Mon Tue, Wed, Thu
Monday Tuesday, Wednesday, Thursday
Jan Feb, Mar, Apr
Jan, Apr Jul, Oct, Jan
Jan96, Apr96 Jul96, Oct96, Jan97
15Jan, 15Apr 15Jul, 15Oct
1994, 1995 1996, 1997, 1998
AutoFill
The AutoFill feature extends several types of series as shown in the following table. The fourth example shows how Microsoft Excel can extend part of a selection (Product 1) and copy another part (On backorder). The last example is a bestfit trend.
Note Items separated by commas are in adjacent cells.
Initial selection Extended series
Mon Tue, Wed, Thu,...
1Jan, 1Mar 1May, 1Jul, 1Sep,...
Qtr3 (or Q3 or Quarter3) Qtr4, Qtr1, Qtr2,...
Product 1, On backorder Product 2, On backorder, Product 3, On backorder,...
text1, textA text2, textA, text3, textA,...
1st Period 2nd Period, 3rd Period,...
Product 1 Product 2, Product 3,...
1, 2 3, 4, 5, 6,...
1, 3, 4 5.66, 7.16, 8.66,...
Linear and Growth series
When you create a linear series by dragging the fill handle, Microsoft Excel increases or decreases values by a constant value that is based on the selected starting values. When you create a growth series by selecting the Growth Trend command from the shortcut menu, Microsoft Excel multiplies values by a constant factor. For information about linear and growth series that you can create with the Series command, click .
Initial selection Extended linear series
1, 2 3, 4, 5
1, 3 5, 7, 9
100, 95 90, 85
Initial selection Extended growth series
1,2 4, 8, 16
1,3 9, 27, 81
2,3 4.5, 6.75, 10.125
Want to add words that Excel doesn't know how to auto fill?
Do the following and see figure 3 below:
A  B  C
 D
John  Paul  Peter  Michal
If you have a series of cells all layed out in a workbook,
you can import that data into the custom list.
Here is how: See figure below
The differences in the way each program, MsWord and MsExcel do things is the reason that they aren't compatable.
I hope this is something like what you wanted to know.
Ensure the data in the table is accurate, then open the Data menu and choose Filter. Click AutoFilter and a down arrow will appear on the top cell of the range. Click on the arrow and choose the minimum value desired. To display the entire table again, click the down arrow and select All. To completely disable AutoFilter, select AutoFilter from the Data menu again.
Adjust Page Breaks
Excel 97/2000
Using two Page Setup options can reduce and enlarge your worksheet data for printing purposes. If you use this method, you should be sure to check your page breaks when resetting these options. Chances are if you change print options, you'll need to adjust any manual page breaks you've set. To quickly reset all page breaks, switch to Page Break Preview by clicking Print Preview, then Page Break Preview. Rightclick any cell on the sheet and choose Reset All Page Breaks from the context menu. This action removes manual page breaks and restores all automatic page breaks.
THREE WAYS TO DO THE SAME THING IN EXCEL  PART 1
+Cell References
THREE WAYS TO DO THE SAME THING IN EXCEL  PART II
+VLOOKUP
THREE WAYS TO DO THE SAME THING IN EXCEL  PART 3
+Writing a VBA script
+Visual Basic (Applications Edition) VBA
+Trhe Script Part 1
+The Script Part 2
By: Wayne A. Newton, Bytewise Systems
In this article, we will describe a simple problem and solve it using a conditional
formula in an Excel cell (also known as a "nested IF").
These first three articles will show three different approaches to solving a single
problem. There are always many ways to solve any problem and you will, no doubt, consider
others when reading these articles. The important thing to remember is that you should
consider alternate ways of solving any problem and then you can determine which the best
is for you.
Let's start by defining the problem. We have been asked to calculate the number of
FrequentFlyer points we collect
when we fly ABC Airlines. FrequentFlyer points are calculated according to the following
table:
E  F  G  
1  Points  ClassDiscription  per mile 
2  0  Discounted Economy  0.70 
3  1  Economy Class  1.00 
4  2  Business Class  1.25 
5  3  First Class  1.5 
According to the table, we will collect 1,000 points if we travel 1,000 miles in
Economy Class and 1,500 points in First Class, etc.
A quick look at the problem shows us that there are two inputs: miles flown and class
flown and depending on the class flown, we use a predetermined number to multiply by
the number of miles flown and generate the result.
To begin, open a new Workbook in Excel and enter the data as shown below in Table 2. We
will use this as test data to compare all three methods:
For the moment the class (Business, First etc) are represented by numbers. We've seen
that done is real world worksheets by well meaning people and so use it here as a
cautionary tale <g>. We'll fix that later in this series.
To test the nested IF, enter one of the two following formulae into Cell C2:
=IF(B2=0,A2*0.7,IF(B2=1,A2*1,IF(B2=2,A2*1.25,IF(B2=3,A2*1.5,0))))
or
=A2*IF(B2=0,0.7,IF(B2=1,1,IF(B2=2,1.25,IF(B2=3,1.5,0))))
Both of these formulae are functionally identical. You choose which one you prefer, you
say tomato and I say tomato.
Now, copy this formula down column C (Cells C3:C5). Use a calculator (or the calculator
program supplied with Windows, or even the one on your desk!) to check that you have the
correct results.
A  B  C  
1  Miles Flwon  Class Flown  Points (By Formula) 
2  200  0  
3  25,460  1  
4  15,950  2  
5  33,650  3 
The results should be correct, but the formula is fairly clumsy, difficult to debug and
difficult to maintain. Additionally, a novice Excel user may not understand what it is
supposed to do, even an expert will have to glare at it for a second or two.
Cell References
Instead of explicitly giving the values you could reference the list on the
worksheet so the formula looks like this:
=IF(B2=0,A2*G2,IF(B2=1,A2*G3,IF(B2=2,A2*G4,IF(B2=3,A2*G5,0)))) or =A2*IF(B2=0,G2,IF(B2=1,G3,IF(B2=2,G4,IF(B2=3,G5,0))))
This gives you the chance to change the multipliers in one place (the G column) to affect the whole worksheet. For example, if the First Class rate changes to double then just change cell G5 to 2.0 and all the relevant points will be recalculated.
Named References Of course, G2 as a cell reference doesn't tell you much.
You could improve the readability a bit by assigning Range names to the class multipliers eg instead of 0.7 or G2 you could give it a name like DiscEconomyMultiplier .
Do that by clicking on cell G2, from the menu choose Insert  Name  Define, type in the name 'DiscEconomyMultiplier' then OK. Repeat for cells G3, G4 and G5 using different names.
That done the formula can be changed to look like this:
=IF(B2=0,A2* DiscEconomyMultiplier,IF(B2=1,A2* EconomyMultiplier,IF(B2=2,A2* BusinessMultiplier,IF(B2=3,A2* FirstMultiplier,0))))
or
=A2*IF(B2=0, DiscEconomyMultiplier ,IF(B2=1, DiscEconomyMultiplier,IF(B2=2, BusinessMultiplier,IF(B2=3, FirstMultiplier,0))))
at least now you can see what the G2 reference is supposed to be just by reading it's name.
Whether you use numbers, cell references or named ranges, the nested IF statement is often seen in Excel worksheets despite the fact that it is messy to read, easy to get wrong and almost impossible to understand. A three part nested IF is one thing, but we've seen people doing it for 10 options or more!
There has to be a better way, and in fact there's several better ways.
For those of you that think all the above is simple  great!
We've set the groundwork we'll look at the better options in detail in a little while. Check back often, more to come.
THREE WAYS TO DO THE SAME THING IN EXCEL  PART II
By: Wayne A. Newton, Bytewise Systems
Welcome to the second in a series of articles where we will show how Excel can be used to solve simple daytoday problems in different ways.
In the previous article (THREE WAYS TO DO THE SAME THING IN EXCEL  PART 1), we described a simple problem and solve it using a conditional formula in an Excel cell (also known as a "nested IF"). It's works but it's messy.
In this article, we will take the same problem and solve it using Excel's powerful VLOOKUP() Function. You may have seen this function and even tried to make it work! We'll take you step by step through it.
Let's start by redefining the problem. We have been asked to calculate the number of FrequentFlyer points we collect when we fly ABC Airlines. FrequentFlyer points are calculated according to the following table:
A  E  F  G 
1  Class  Description  Points (per mile) 
2  0  Discounted Economy  0.70 
3  1  Economy Class  1.00 
4  2  Business Class  1.25 
5  3  First Class  1.50 
+++++
  E + F
+ G

+++++
  
 Points 
 1 Class Description (per mile)
 ++++
 2  0 Discounted Economy 0.70
 3  1 Economy Class 
1.00
 4  2 Business Class 
1.25
 5  3 First Class 
1.50
+++++
According to the table, we will collect 1,000 points if we travel 1,000 miles in Economy Class and 3,000 points if we travel 2,000 miles in First Class, etc.
A quick look at the problem shows us that there are two inputs: miles flown and class flown and depending on the class flown, we use a predetermined number to multiply by the number of miles flown and generate the result.
To begin, open a new Workbook (or you can use the Workbook from the last instruction and insert a new Worksheet) in Excel and enter the data as shown below in Table 2. We will use this as test data to compare all three methods:
+++++
A  B  C  

1  Miles Flown  Class Flown  Points (by Lookup) 
2  200  0  
3  25,460  1  
4  15,950  2  
5  33,650  3 
  A  B 
C 
+++++
  Miles  Class  Points

 1  Flown  Flown  (by Lookup) 
 ++++
 2  200  0 

 3  25,460  1 

 4  15,950  2 

 5  33,650  3 

+++++
To test the Lookup table, start by entering Table 1 exactly as shown beside your test data in the cells shown. Once the table has been set up, using your mouse, select all cells in the range E2 to G5. Now, locate the Name Box. You'll find it to the left of the Formula Bar (it should have "E2" in it). Using your mouse (cells E2 to G5 are still selected aren't they?), click inside the Name Box and type the word "LookupTable" (without the quotes). You could choose any name here; it really doesn't matter, but for the sake of this exercise, just stick with "LookupTable". You have now created a Named Range.
To verify that you have named the range correctly, choose Insert from Excel's menu, then Name and Define. You should click "LookupTable" in the ListBox on the Define Name Dialog Box, and then look at Refers to box at the bottom. With any luck, you should see "$E$2:$G$5" as part of the reference. Assuming all is well, click the Close Button.
Now, enter one of the two following formulae into Cell C2:
=A2*VLOOKUP(INT(B2),$E$2:$G$5,3) or =A2*VLOOKUP(INT(B2),LookupTable,3)
Both of these formulae are functionally identical. You choose which one you prefer.
If entering the first formula, be sure to include the dollar signs because this is an absolute reference. To get around the difficult syntax, you could use the Named Range in the second formula.
Copy this formula down column C (Cells C3:C5). Use a calculator (or the calculator program supplied with Windows, or Excel, or even the spreadsheet you created from last week's article) to check that you have the correct results.
These Formulae should have tests to ensure that the flown class is 0, 1, 2 or 3 and I will leave it to you to add these later. The formulae are, arguably a little easier to understand, but novice Excel users may have difficulty understanding the VLOOKUP() Function.
So what does the Formula do?
Let's work from the inside out.
1. The value contained in Cell B2 is converted to an Integer, using the INT() Function.
This ensures that we are checking for whole numbers only.
2. LookupTable refers to cells $E$2 to $G$5; a three column block of cells, which
represents the lookup table.
3. VLOOKUP() looks down the first column of the lookup table (column E in our case) for a
matching value (INT(B2))
4. VLOOKUP() then takes the value contained in the third column (notice the
",3)" at the end of the VLOOKUP() Function call) and that is the value returned
by this call to VLOOKUP().
5. The returned value from VLOOKUP() is then multiplied by the value contained in cell A2,
giving us the result we require.
In this article we show the second of the three solutions we have chosen to demonstrate the power and flexibility of Microsoft Excel for solving daytoday problems. Next week, we take the same problem and solve it by extending Excel in a way that's very neat and will solve more than just this simple problem.
Until next time, happy problem solving with Excel!
THREE WAYS TO DO THE SAME THING IN EXCEL  PART 3
By: Wayne A. Newton, Bytewise Systems wayne@.com
Welcome to the third in a series of articles where we will show how Excel can be used to solve simple daytoday problems.
In the last article, we described a simple problem and solved it using Excel's VLOOKUP() Function.
Writing a VBA script
In this article, we take the same problem and solve it by extending Excel. We will write a
Function in Excel's powerful programming language, VBA. This may sound scary, but its
often the best and easiest way to make a function to suit your particular needs. A VBA
function is also easier to understand than a long and deeply nested cell formula.
As we did last week, let's start by redefining the problem. We have been asked to calculate the number of FrequentFlyer points we collect when we fly ABC Airlines. FrequentFlyer points are calculated according to the following table:
+++++
E  F 
G  
1  Class  Discription  Points (Per Mile) 

2  0  Discounted Economy  0.70 
3  1  Economy Class  1.00 
4  2  Business Class  1.25 
5  3  First Class  1.50 
  E + F
+ G

+++++
  
 Points 
 1 Class Description (per mile)
 ++++
 2  0 Discounted Economy 0.70
 3  1 Economy Class 
1.00
 4  2 Business Class 
1.25
 5  3 First Class 
1.50
+++++
According to the table, we will collect 1,000 points if we
travel 1,000 miles in Economy Class and 3,000 points if we
travel 2,000 miles in First Class, etc.
A quick look at the problem shows us that there are two inputs: miles flown and class
flown and depending on the class flown, we use a predetermined number to multiply by the
number of miles flown and generate the result.
To begin, open a new Workbook (or you can use the Workbook from last week and insert a new Worksheet) in Excel and enter the data as shown below in Table 2. We will use this as test data to compare all three methods:
+++++
A  B  C  
1  Miles Flown  Class Flown  Points (By Fundction) 
2  200  0  
3  25,460  1  
4  15,950  2  
5  33,650  3 
  A  B 
C 
+++++
  Miles  Class  Points

 1  Flown  Flown  (by Function) 
 ++++
 2  200  0 

 3  25,460  1 

 4  15,950  2 

 5  33,650  3 

+++++
Visual Basic (Applications Edition) or VBA is the macro programming language supplied with the Microsoft Office Suite. VBA does much more that automating keystrokes; it is a fullyblown, objectlike, complete programming language that allows you full control over your Office application.
From Office '97, VBA programming is performed using the Integrated Development Environment or IDE. To get to the IDE, press AltF11 when in an Office Application. Pressing AltF11 again returns you to your application.
To test the VBA Function solution to our problem, from within Excel, press AltF11 to view Office's IDE.
You should be able to see three windows: The Code window (the largest one), the Project Explorer and the Properties window. If you cannot see all three windows, go to the View menu and select them. The standard layout for the IDE is to have the Code window occupying most of your screen real estate, with the Project Explorer and Properties windows docked at the topleft and bottomleft of the screen respectively.
For this exercise, we will add a new Code Module to the Project. Rightclick your mouse inside the Project Explorer window and choose Insert, then choose Code Module from the contextsensitive menu. A new Code Module will appear in the Code window and the Project Explorer. In the Properties window, doubleclick "(Name)" and type "FlyerCode" (without the quotes) and press Enter. You have now named your new Code Module.
Trhe Script Part 1
In the Code window, carefully type the following code (or copy it )
Public Function FlyerPoints(FlightMiles As Range, _
FlightClass As Range) As Double
Dim dblMultiplier As Double
Select Case CInt(FlightClass.Value)
Case 0
dblMultiplier = 0.7
Case 1
dblMultiplier = 1
Case 2
dblMultiplier = 1.25
Case 3
dblMultiplier = 1.5
Case Else
dblMultiplier = 0
End Select
FlyerPoints = FlightMiles.Value * dblMultiplier
End Function
This Function is declared "Public" meaning that it can be seen anywhere within
the Excel Application whenever this Workbook is loaded. It is called
"FlyerPoints" and accepts two "arguments"  "FlightMiles"
and "FlightClass". The two arguments are of type "Range", which means
that they are references to ranges of cells within the spreadsheet. Finally, the Function
will return a "Double" (doubleprecision, floatingpoint) number. Within the
Function, the "Select" statement determines the multiplier, depending on the
value contained within the cell called "FlightClass". The last executable
statement in the Function does the multiplication and assigns the result to the Function's
name. This is VBA's way of returning a value from a Function.
The next step is to call the new Function from the spreadsheet. To do this, press AltF11 to return to Excel.
To test the new function, enter the following formula into Cell C2:
=FlyerPoints(A2,B2)
Copy this formula down column C (Cells C3:C5). Use a calculator (or the calculator program
supplied with Windows, or Excel, or even the spreadsheet you created from last week's
article) to check that you have the correct results.
An advantage with the VBA method is that the formula is simple and easy to understand. It's also much easier to trap errors or out of range situations (for example the CASE ELSE trap in the above code).
A disadvantage is that the model loses a little of its "transparency". The code shown above is the minimum requirement to be "functional" (excuse the pun), but if your spreadsheet has to be audited or maintained, you should explain what the VBA code does.
Most auditors do not understand program logic and an explanation may be necessary to get your spreadsheet approved.
There's also the general fear of VBA due to virus worries. This stems from years of poor decisions and frankly 'hiding head in the sand' attitude from Microsoft. Your functions may not work if the user has security set to High (which disables all VBA functions with no warning).
The Script Part 2
In the next example, I have included comments. If any of your functions are implementing
part of your business logic, you should add the following comments, so that auditors and
others can understand what it does:
* What the function is called;
* What it does;
* What information it needs (arguments);
* What it returns;
* Version number and author initials are a good idea too.
Option Explicit
Private Const gcDiscEconomyClass As Double = 0.7
Private Const gcEconomyClass As Double = 1
Private Const gcBusinessClass As Double = 1.25
Private Const gcFirstClass As Double = 1.5
Private Const gcErrorClass As Double = 0

Public Function FlyerPointsA(ByRef FlightMiles As Range, _
ByRef FlightClass As Range) As Double
'
' Excel Function: FlyerPointsA (Version: 1.00.01  WAN)
'
' Description: Calculates the number of
Frequent Flyer points,
'
given the number of miles flown and the class
flown.
'
' FlightMiles: Reference to a single Excel Cell
containing
'
the number of miles flown on this flight.
'
' FlightClass: Reference to a single Excel Cell
containing
'
the class flown on this flight.
'
' Returns: A
doubleprecision, floatingpoint number,
'
representing the calculated number of Frequent
'
Flyer Points collected when flying FlightMiles,
'
miles in FlightClass, class.
'
Dim dblMultiplier As Double
Const cDiscEconomy As Long = 0
Const cEconomy As Long = 1
Const cBusiness As Long = 2
Const cFirst As Long = 3
Select Case CLng(FlightClass.Value)
Case cDiscEconomy
dblMultiplier =
gcDiscEconomyClass
Case cEconomy
dblMultiplier =
gcEconomyClass
Case cBusiness
dblMultiplier =
gcBusinessClass
Case cFirst
dblMultiplier = gcFirstClass
Case Else
dblMultiplier = gcErrorClass
End Select
FlyerPointsA = FlightMiles.Value * dblMultiplier
End Function

As you look through this code, you'll notice the use of Constants that improve readability as well as the mandatory comments previously discussed. A little extra work using Constants and comments when writing your function helps everyone to understand your masterpiece, even you, when you revisit it in six months time <g>
Userwritten Functions can add enormous power to an already powerful application. When seeking the solution to a problem, add VBA Functions to your toolkit  the possibilities are endless!
In this article we show the last of the three solutions we have chosen to demonstrate the power and flexibility of Microsoft Excel for solving daytoday problems.
Happy problem solving with Excel!
The count, counta, and countblank functions are very straightforward, so I'll skip these and get straight to the more useful countif. This function takes two arguments: the range that holds the data to be counted, and the criteria used to determine if a cell is included in the count.
To count the number of cells that contain the value 12: =countif(data,12)
To count the number of cells that contain 1 or 12: =countif(data,1)+countif(data,12)
To count the number of cells that contain a negative value: =countif(data,"<0")
To count the number of nonzero values: =countif(data,"<>0")
To count the number of cells that contain a value between 1 and 10: =countif(data,">=1")countif(data,">10")
To count the number of cells that contain the word "yes" (not case sensitive): =countif(data,"yes")
To count the number of cells that contain any text: =countif(data,"*")
To count the number of cells containing threeletter words: =countif(data,"???")
Okay, I think you get the idea. Modify the the =countif value to suit your needs.
Enumerating With Data
Countif is useful when you have a single criterion. For more complex comparisons, you may
want to use dcount. This function requires that your data be set up as an Excel database
(with field names in the first row), and that a separate criteria range specify the
conditions.
FIGURE 1 shows an example of a simple database in A1:C13 and a criteria range in E1:G2. The formula in cell E4, which returns the number of sprockets sold in January, is =dcount(A1:C18,,E1:G2).
The formula will yield a different value whenever you change the values in the criteria range. Also, the criteria range can handle logical "or" operations when you use additional rows.
FIGURE 1: Complex counting requires complex formulas, such as dcount, which tallies
records in a database.
If none of the standard techniques fit the bill, construct an array formula, which lets you work with multiple cells in a range. To create an array formula, press CtrlShiftEnter after typing the formula.
To tally the number of values in a range, enter =sum(if(isnumber(data),1,0)).
To count the number of cells that contain an error, try =sum(if(iserr(data),1,0)).
Pivot Tables
The ultimate in counting tools, a pivot table lets you summarize data in just about any
way. Select Data, PivotTable to start the PivotTable Wizard.
Here is a simple chart to help you with counting:
FUNCTION  What it does 
Count  Counts the number of cells in a range that contain numeric values. 
Counta  Counts the number of nonempty cells in a range. 
Countblank  Counts the number of empty cells in a range. 
Countif  Counts the number of nonempty cells in a range that meet a specified criterion. 
Subtotal  Performs various calculations (including counts) only on the visible cells in a range. 
Dcount  Within a list or database, counts the number of cells that contain numeric values that meet specified criteria. 
Other techniques  
Array formula  Performs multiple calculations (useful when the other techniques won't work). 
Pivot table  Summarizes large amounts of data in various ways. 
Excel lacks a function for calculating the number of days in a particular month, so you'll need to construct your own formula. If cell A1 contains a date, this formula will return the number of days in the month: =day(date(year(A1),month(A1)+1,1)1).