All Excel Templates Normally an 80% saving at $299.00. Super Special $175.00!
ADVERTISEMENTS
If you require training in Excel, Word, Access, PowerPoint, or pretty much anything to do with computers, you cannot beat$5.99 Training .
EXCEL TEMPLATES SPECIALS
Check out this HUGE range of File Conversion Software . Some at ridiculously low prices!
MAIN INDEXWhat's New at OzGrid | Excel Tips and Tricks | Excel VBA Tips and Tricks What's New at OzGrid
Winter is approaching here in Australia, which is lucky for us as it's not so bad working when it is raining outside!!! Our book, Excel Hacks - 100 Industrial Strength Tips & Tricks has now hit the technical bookshops and is also for sale via Amazon, O'Rielly Publishing (the publishers) and many other websites. We have set up some web-pages on our site for our book and so far the feedback has been excellent, we are more than thrilled. It is our intent to publish all our reviews, both good and bad (not had a bad one yet). So far we have had a couple of very positive reviews and the sales are "so far, so strong" according to our editor. Although we have no statistics available until the end of June (stats are available quarterly) we are hoping the book sells well.You can purchase a copy here.
We have also updated our Question & Answer Forum to the latest and greatest version. This should greatly speed up the loading of pages for those on dial-up internet connections, and the ability to add small attachments is now back. As they say, there is no gain without pain, an unfortunate but very true saying, so we hope that any downtime experienced by the upgrade was not too painful.
That's all for this month, we hope you enjoy May's newsletter
I thought this month I would lighten up somewhat, give the grey matter a rest and show you some handy hints, tips and tricks with Excel. I won't bore you with shortcut keys, other than to say you can get a full list of all shortcut keys in Excel (Word, Access etc for that matter) by simply typing into the help......wait for it.........."Keyboard shortcuts"!
One of my favourite features of Excel is its Fill Handle . The Fill Handle is the small black square you see at the bottom right of any cell/range selection. Try these;
Enter the number 1 into any cell. Now select this cell. Hold down the Ctrl key and left click on the Fill Handle and drag down. The numbers will increment.
Enter the text Depart1 into any cell. Now select this cell. Left click on the Fill Handle (no Ctrl key) and drag down. The text will increment.
Enter the number 1 into any cell. Now select this cell. Right click on the Fill Handle and drag down at least 101+ rows then release the mouse button. Select Series. Type a "Step value" of 0.5, or any value. Enter a "Stop value" of 100 and click OK.
Enter any valid date into any cell. Select this cell again. Right click on the Fill Handle and drag down about 20+ rows then release the mouse button. Either select "Fill Days", "Fill Weekdays", "Fill Months", "Fill Years" or "Series" for even more control.
Not sure what I mean byvalid date ?
The Fill Handle will automatically recognise some text as that which would need incrementing. For example "Mon", or "Monday", "Jan" or "January". Sometimes however you may have your own list which requires frequent use. If this is you, try this.
Enter your list into cell A1:A100, or whatever. Select the list*, no blanks though! Sort if required. Now go to Tools>Options-Custom Lists. Ensure the correct range is in the "Import list from cells:" box. Now simply click "Import". Next time you need this list, simply enter the first entry of the list, select this cell, now drag down with the Fill Handle.
*TIP: to quickly select all entries in a list. Select the first cell, hold down Ctrl+Shift and push the Down arrow.
Need to quickly copy cells down and automatically stop at the first blank cell in a list to the immediate right? Ensure you have a continuous list in cells in Column "B". Enter a formula, or value into cell A1. Select A1 again and double click the Fill Handle.
The same principle can be used when/if you need to replace an existing list with new values, formulas etc. If your old list is A1:A100, select cell A1, enter the new formula, or the first entry of the new list. Select A1 again and double click the Fill Handle.
Have a list of formulas that you wish to convert to values only? You can of course Copy then go to Edit>Paste special and paste as "Values" over the top, or try this. Select the list of formulas. Right click on the selection border (not the Fill Handle). Drag over one column, or row if horizontal. Then drag back and release, select "Copy here as values only".
Move or Copy cells quickly. Select the cell(s). To copy hold down the Ctrl key. Drag to destination via the selection border (not the Fill Handle) and release.
To Move, do the same as above without holding down the Ctrl key.
Move or Copy the cell content and *insert the cell* somewhere else. Use either of the two methods above but also hold down the Shift Key.
With all three of the above methods use the Alt key to change Worksheets.
ADVERTISEMENTS
Like to getExcel training from those that develop it?
REAL ESTATE INVESTMENT ANALYSIS SOFTWARE
Last month we uploaded to our site a free open source Excel Add-in containing lot's of custom functions.DOWNLOAD FROM HERE
This month I will show how to use a few of these and explain the code that drives them.
Count_By_Color and Sum_By_Color can be read onlinehere andhere
Days_In_Month is used like;
=Days_In_Month("1/1/2004","Mon")
or
=Days_In_Month(A1,"Mon")Where A1 houses a valid date. You can of course use any combination of cell references and static values.The code for this Function is;
Function Days_In_Month (FullDate As String, sDay As String) As Integer
Dim i As Integer
Dim iDay As Integer, iMatchDay As Integer
Dim iDaysInMonth As Integer
Dim FullDateNew As Date
iMatchDay = Weekday(FullDate)
Select Case UCase(sDay)
Case "SUN"
iDay = 1
Case "MON"
iDay = 2
Case "TUE"
iDay = 3
Case "WED"
iDay = 4
Case "THU"
iDay = 5
Case "FRI"
iDay = 6
Case "SAT"
iDay = 7
End Select
iDaysInMonth = Day(DateAdd("d", -1, DateSerial _
(Year(FullDate), Month(FullDate) + 1, 1)))
FullDateNew = DateSerial _
(Year(FullDate), Month(FullDate), iDaysInMonth)
For i = iDaysInMonth - 1 To 0 Step -1
If Weekday(FullDateNew - i) = iDay Then
Days_In_Month = Days_In_Month + 1
End If
Next i
End FunctionHere is how it works. The variable "FullDate" stores the date. The variable "sDay" stores the day.A number between 1 and 7 is passed to the variable "iMatchDay" via the use of the Weekday Function. That is;
iMatchDay = Weekday(FullDate)
A number between 1 and 7 is passed to the variable "iDay" via the use of theSelect Case Statement . See also Next we find out how many days are in the month that has been specified. This is then passed to the variable "iDaysInMonth" , via
iDaysInMonth = Day(DateAdd("d", -1, DateSerial _
(Year(FullDate), Month(FullDate) + 1, 1)))
This is done by using the DateAdd Function to take 1 day off the DateSerial value of DateSerial(Year(FullDate), Month(FullDate) + 1, 1). if the date use was 12/Jan/04 the DateSerial value returned would return a DateSerial value equal to the date 1/Feb/04. As we then take one day off this date, we must get the date of the last day of the month for Jan/04, which is 31/Jan/04. The Day function then passes the number 31 to the variable "iDaysInMonth".
The DateSerial Function returns a Variant (Date) for a specified year, month, and day. it is universal and accounts nicely for any date format.
This date is then passed to the variable "FullDateNew" again via the use of the DateSerial, Year and Month Function;
FullDateNew = DateSerial _
(Year(FullDate), Month(FullDate), iDaysInMonth)
Now we have this needed info stored in variables, we use a For Loop to work backwards from the last day of the month (31,30,29 or 28)to 0 (zero);
For i = iDaysInMonth - 1 To 0 Step -1
If Weekday(FullDateNew - i) = iDay Then
Days_In_Month = Days_In_Month + 1
End If
Next i
Each time we loop we take off the number of the loop from the date stored in the variable "FullDateNew" and check it's number that corresponds withWeekday(FullDateNew - i)against the number representing the wanted Weekday stored within the variable "iDay". Is there is a match, we need to count this so we add 1 to our Function "Days_In_Month" via;
Days_In_Month = Days_In_Month + 1
So, in the case of;
=Days_In_Month("1/1/2004","Mon")
The end result is a number that tells us how many Mondays there are in Jan-2004.
Until next month, keep Excelling!
ADVERTISEMENTS
Artificial neural network software for stock markets!
You are more than welcome to pass on this newsletter to as many people as you wish. Or even upload it (as is) to your Web site!
To no longer receive our newsletter, send a NEW email with the exact words "action: Unsubscribe" in the body of the email, or click here .
Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation
Read this issue and past issues online here :