2011-2012 NBA Schedule Spreadsheet Download

I know this is a little bit late, I had wanted to get it out before the season started but things have been pretty crazy. Anyways, it's finally here, the full 2011 - 2012 NBA schedule. The spreadsheet includes the a complete list of all 66 games for each team. When the NBA playoffs roll around we'll have a new spreadsheet schedule for that too.

This spreadsheet is very useful for 2011 NBA fantasy basketball players. I also may update the Excel file with a list of the TV schedule and what stations each game will be broadcast on. Anyone know where I can find that information?

Click here to download the NBA 2011-2012 Spreadsheet Schedule.xls

You can find all or our other sports schedules and other spreadsheet templates to download here.

Update: get the 2012-2013 NBA schedule here!

Mechanical Engineer: Evolution of Computer Skills

A friend sent me this graphic and I thought it was pretty funny, mainly because it's so true! Ironically, I never learned how to use Excel in school, or didn't even how to write a simple code. Excel is a great and powerful tool. I think you could group PowerPoint into that category too. Learn more about Matlab, CATIA, AutoCAD, Pro-E, and more over at my CAD Systems Help blog. I also find this engineering flow chart to be hilarious.

Basic Excel VBA Macro: Loop through all worksheets

What is an Excel macro? If you perform a task repeatedly, you can take advantage of a macro to automate the task. A macro (or makro to some people) is a series of functions, written in a scripting language, that you group in a single command to perform the requested task automatically. You can record a macro but it is highly recommended that you learn VBA in order to write and use macros of your own creation. I will help you learn VBA through a series of Excel macro tutorials, starting with how to scroll through all the tabs in a workbook.

In this Excel macro example, I want to loop through all worksheets in order to copy a specified range of cells and then delete the contents of a couple of other cells. To loop through all worksheets I will utilize a function call a For Loop. Here's a programming syntax tip for you: Add comments to your statements using an apostrophe ('), either at the beginning of a separate line, or at the end of a statement. It is recommended that you add comments wherever possible, to make your scripts easier to understand and maintain, especially if another user has to make changes to it later on down the road. I use comments in my example code below to help explain each step of the program. I have indented all the code which takes place inside the for loop.

Sub WorksheetLoop()

   Dim WS_Count As Integer
   Dim I As Integer
   Application.ScreenUpdating = False

   ' Set WS_Count equal to the number of worksheets in the active workbook.
   WS_Count = ActiveWorkbook.Worksheets.Count

           ' Begin the loop through all worksheets.
           For I = 1 To WS_Count

              'message box displaying the name of the worksheet
              MsgBox ActiveWorkbook.Worksheets(I).Name
     
            'active sheet i    
            ActiveWorkbook.Worksheets(I).Activate

        'select the old data and insert new rows
            Rows("5:39").Select
            Selection.Copy
            Rows("5:5").Select
            Selection.Insert Shift:=xlDown
            Range("C39").Select
       
        'delete old data
        Range("H17:AA22").Select
            Range("W17").Activate
            Application.CutCopyMode = False
            Selection.ClearContents
            Range("AB11:AB39").Select
            Range("AB39").Activate
            Selection.ClearContents


   Next I
   
 Application.ScreenUpdating = True

End Sub

Now you can answer the question what is a macro! Please comment below if you have any questions.

Learn how to export to Excel from CATIA.

Updated 2011 NCAA College Football bowl sheet

With the help of David Tyler (creator of one of the best NCAA college basketball Excel brackets) I have made some modifications to my 2011 college football bowl sheet.

·         By using the sumproduct formula and conditional formatting we have replaced the VBA COLORFUNCTION, making the spreadsheet much more user friendly. No need to press crtl+alt+f9 to update the formulas!

·         I moved the scoreboard to its own individual sheet and included a few new stats, such as the bowl game picked correctly by the most number of players.

·         I expanded the default number of players to twenty but there’s still the issue where new players beyond the first twenty added to the pool are not automatically accounted for in the leader boards.

Please keep in mind; this is just the beginning of the evolution of this spreadsheet. The end goal is to automate inserting the exact number of players along with all of their picks. There will also be some sort of tie-breaking function, like guessing the score for the BCS championship game or using a confidence interval to rank each pick.

And hopefully by the time this new spreadsheet is completed we can throw the whole thing out because we’ll finally have a college football playoff!

Download the previous version here.
*******UPDATE Dec 27 - latest version has been uploaded

Download the latest Excel file here

2011-12 NCAA College Football Bowl Pool Spreadsheet Dowload

Now that the2011-2012 NCAA college football bowl game match-ups have been announced, I was able to quickly put together a schedule in Excel spreadsheet form. I have made it available to download. Pictured below is the schedule of each and every bowl game as well as the time of the game and what TV channel it will be shown own.

I have also created a simple college football bowl predictions pool. When I get the emails of folks picks I put them in the spreadsheet and then when games are played I color the winners the green as in box A2 and the wrong ones in red. Then out in the far column it counts all the picks that are colored green using the COLORFUNCTION formula: 

=ColorFunction($A$40,C3:C38,FALSE)

You may have to double click on the sum cells in order to get them to compute. Please let me know if you have any questions.

A few interesting bowl game notes.

• The Humanitarian Bowl has been replaced by the Famous Idaho Potato Bowl. 
• The Texas Bowl is now called the Meineke Car Care of Texas Bowl.
• The old Meineke Car Care Bowl has been replaced by the Belk Bowl.

Update: The download link should now be working! Please comment below if there are any issues. Thanks!

Download the 2011 College Football Bowl Prediction Pool Spreadsheet.xls

Check out our other sports spreadsheets, including a college football helmet schedule.

Latest download is here 12-27-11 

Update: Download the 2012 - 2013 version here.

2011 NCAA College Football Bowl Schedule Announced - Spreadsheet Download Coming Soon!

The 2011-2012 NCAA college football bowl game match-ups have been announced. I am currently working on a bowl prediction spreadsheet and schedule which you will be able to use for your betting pools at work or with friends and families. Hopefully, the football spreadsheet will be available for download within a day or two. Updates coming soon!

Download the 2011 College Football Bowl Game Prediction Pool Spreadsheet.xls

Follow me on Twitter for the latest information.

Update 12/6/11:
The spreadsheet download link for the 2011 NCAA college football bowl game predictions pool has been posted here. 

Update 12/27/2011
Latest spreadsheet

Advanced Excel: Macro Examples

Creating Excel spreadsheet macros is a great way to save time and reduce the possibility of human error by automating repetitive processes. Other reasons for learning how to write VBA macros include: standardization, improve efficiency, expand capabilities, and streamline tasks. Macros in Excel are created through two primary methods: the macro recorder or by writing custom code with the macro editor (or even with notepad). Recording is a great way to figure out syntax or a specific, simple task if you are stuck but writing custom code is far superior, especially if you are dealing with multiple Excel files. Here are a few basic, easy lines of code which are helpful to learn when beginning to create macros. Most of these deal with layout or formatting of your spreadsheet. I typically would add these types of commands to the end of my macro after all the data is where I would like it.


To insert a row above an active cell use this code:

ActiveCell.EntireRow.Insert

Conversely, to insert a  row below the active cell:

ActiveCell.Offset(1).EntireRow.Insert

To insert a new, blank column to the left of the active, selected cell, use this:

ActiveCell.EntireColumn.Insert

Or, to insert a column to the right of the active cell, change to this:

ActiveCell.EntireColumn.Offset(0, 1).Insert


To delete a column, specify the column number (A being 1, B = 2, and so on):


ActiveWorkbook.ActiveSheet.Columns(16).Delete


To add a diagonal line through a range of cells with a macro use the following code:


Range(insert desired range , ie A2:B2).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    With Selection.Borders(xlDiagonalUp)
        .LineStyle = xlContinuous
        .ColorIndex = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With

To change the background color to white, select a range of cells then type this:


With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With


To create a thick border on the right of a cell:


Selection.Borders(xlEdgeRight).Weight = xlThick


Or, make the bottom border thick:


Selection.Borders(xlEdgeBottom).Weight = xlThick


To Autofit a columns width, specify a range:


Range(AA:CC).EntireColumn.Autofit
Read more about creating Excel spreadsheetmacros.

How to use the SUMIF formula in Excel

SUMIF is a useful formula to know how to use when creating Microsoft Excel spreadsheet templates. An example I can give is from the world of engineering. Say you have to create a part list or a bill of material (BOM for an assembly, such as a snow blower. The snow blower has multiple parts with have standard parts, such as washers, bolts, nuts, etc,, attached to it. At the bottom of my BOM I want to sum all of the parts for my top level assembly but I don’t want to include the number of standard nuts and bolts. In this case I use the SUMIF formula along with some nifty formatting.

 

 

To count the number of parts in a specified column use the =SUMIF() formula. In my case, =SUMIF(K5:K17,">0").

If a number exists in a column which is greater than zero then sum the given range in the column.

One thing that I did was to change the standard part counts, which used to be entered as (1), (2), (3), etc. (entered '(1)). I have since changed this to a new format: -1, -2, -3... yet the entries still look like (1), (2), (3).  This way the SUMIF formula can count the numbers since they are not being recorded as text.  With the numbers being negative it can also make it so the formulas can selectively count them in the different parts of the spreadsheet.  So, when entering parts like weld nuts, nut plates, pin, collars, etc. enter the count as a negative (-) number.  This can either be done as -1 or (1), both will mean the same. I also put in a custom number format so you  no longer have  to type "-1", "-2", etc.  Just enter 1, 2, 3, etc. and the "-" will be placed in front automatically. Little improvements like this go a long way in improving the time it takes to compile one of these massive spreadsheets.

Let me know if you have any questions or concerns and if this format is more or less helpful and an improvement in function and readability.

Why doesn't center alignment macro work?

Troubleshooting Excel macros
I was recently working on an Excel spreadsheet macro to automatically center the text with a cell on the cells within a sheet. This is a VBScript macro executed from a CAD system and exports desired data to an Excel file. I used the following code:

With Excel.Range("A"&"1", "F"&RwNum)

.Font.Name = "Arial"
.Font.Size = 9
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.ColumnWidth = 25
.RowHeight = 20
.Borders.LineStyle = xlContinuous
.Borders.Weight = xlThick
.Borders.ColorIndex = 1
.WrapText = True
.EntireColumn.Autofit

End With

Well, the Horizontal and Vertical alignment was not working at all. I tried numerous different methods, spellings, etc. Nothing seemed to work. Then I discovered I was missing a line of code. Simply add this statement before the With loop:

Const xlCenter = -4108

Because we're accessing a spreadsheet from outside of Excel the xlCenter constant is not declared which makes VBA treat it as 0. So in our code we need to set a value for xlCenter, hence the code line of: Const xlCenter = -4108. Here are some other values you may need to set if you are formatting or sorting your Excel sheet.

Const xlCenter = -4108
'Const xlAscending = 1
'Const xlYes = 1
'Const xlSortOnValues = 0
'Const xlSortNormal = 0
'Const xlTopToBottom = 1
'Const xlPinYin = 1

To figure out the value number go into excel VBA editor and press crtl+g then type ?xlAscending and it will list the current value.

To sort a list using VBScript as opposed to VBA here is an example code you might need:

'sort parts in numerical order then sort fasteners
Excel.Range("A:G").Select
Excel.Selection.Sort Excel.Range("G1"),1,Excel.Range("A1"),,1,Excel.Range("B1"),1,1,1,False 

How to delete a row based on condition?

I created a macro to delete an entire row in an Excel spreadsheet based upon a specific given condition. I've seen a few others code for this same problem and I thought they overthought it and wrote a unnecessarily long code. Here is my quick and easy solution. Let's say I have a few cells in column A named "FINAL_BODY" where I want to delete the entire row.

Sub Delete_FINALBODY_Rows()

Dim r As Long

For r = Range("A" & Rows.Count).End(xlUp).Row to 1 Step -1

If UCase(Left(Cells(r, "A").Text, 10)) = "FINAL_BODY" Then Rows(r).Delete

Next r

End Sub

Dilbert Comic Strip: How to do Formulas in Excel

I saw these Dilbert strip today in the Sunday paper and thought I would share it as it mentions Microsoft Excel. (Click on the image to see the full strip)

VBA to Export Properties to Excel

 Excel is a powerful program and I often find myself using it in conjunction with other software programs. As a CAD engineer, I am often making parts lists in Micosoft Excel based upon 3D data files in CAD software, such as CATIA V5. Lucky for me CATIA has its own catscript VBA editor which allows you to write macros which can automatically export data from Catia to Excel, a huge timesaver! 

I recently created an example where I take several key properties from a 3D CAD model part including the mass, thickness, material, and part number and export them into a nice little spreadsheet. I can then format the spreadsheet from within my Catia macro. Every little thing I can do to cut back on those extra mouse clicks (call me lazy I guess). 

This code can also help you to learn how to program in visual basic which is very helpful in automating repetitive tasks. Here is a small sample:

'to excel
Dim Excel As Object
Dim workbooks As workbooks
Dim workbook As workbook
Dim Sheets As Object
Dim Sheet As Object
Dim worksheet As Excel.worksheet
Dim myworkbook As Excel.workbook
Dim myworksheet As Excel.worksheet

On Error Resume Next
Set Excel = GetObject(, "EXCEL.Application")
If Err.Number <> 0 Then
Err.Clear
Set Excel = CreateObject("EXCEL.Application")
Else
Err.Clear
MsgBox "Please note you have to close Excel", vbCritical
Exit Sub
End If
Excel.Visible = True

'load a sheet
Set workbooks = Excel.Application.workbooks
Set myworkbook = Excel.workbooks.Add
Set myworksheet = Excel.ActiveWorkbook.Add
Set myworksheet = Excel.Sheets.Add

'download the properties
'row one
Excel.Cells(1,1)="Part Number"
Excel.Cells(1,2)="Thickness"
Excel.Cells(1,3)="Material"
Excel.Cells(1,4)="Mass"  

Download the full code here. You can also import Excel files into programs such as CATIA but that is a post for another day!

 CATIA V5 to Excel Properties Exporter Macro.catscript download

How to view mpp files without Mircosoft Project

This may not be an excel tip but it is in the Microsoft Office family of products. If you're searching for how to open a .mpp file without Microsoft Project you may have found yourself in a similar situation which I was in not too long ago. I needed to open a Microsoft Project file but I didn't have the software on my pc. I did what most people would probably do and browsed the internet for a free mpp viewer. The problem is all the viewers I found had to be downloaded and installed. Well, I had no administrative privileges on my machine and could not install any new programs. Luckily I found a great solution. Go to www.amiproject,com. You can open and view your mpp files in an internet browser without having to download and install any software. I think it even lets you edit and save your project file as well.

Check out other project management resources here.

How to Insert a Diagonal Line in Excel Spreadsheet Cell

How do you make a diagonal strike through line in a Microsoft Excel spreadsheet cell? If you're using Excel 2007 go to the home tab where you'll see the Format Numbers options area. There is a drop-down box which may be currently displaying "date". Click the arrow and select "More number formats." You'll see a bunch of tabs on the top of the pop up display. Go to the "border" tab. Select the diagonal line. This will insert a diagonal line from the lower left hand to the upper right hand corner of a cell, creating a "strike-through" effect if you will. Easy eh?

2011-2012 NFL Spreadsheet Schedule

I've found two great 2011 NFL schedule spreadsheets which I've made available to download. The first is similar to the NCAA 2011 helmet schedule and features the helmets of all the NFL teams. The schedule is broken up into two work sheets for the AFC and NFC respectively. If you are interested you can download the NFL 2011-2012 Schedule in an Excel spreadsheet by clicking on the download link.

The other spreadsheet doesn't have the cool football helmets but it is more highly customizable. You can filter the below Excel spreadsheet as you like an you can  actually sort the workbook by week, date, away team, home, team, even game time! Download here.

UPDATE 7/13/12: Download the 2012-2013 NFL Schedule Excel Spreadsheet

Check out our 2012 NFL fantasy mock draft creator spreadsheet.

2011 NFL Excel  Helmet Schedule.xls download
2011-2012 NFL Spreadsheet Schedule.xlsx download
2012-2013 NFL Excel Helmet Schedule Spreadsheet.xls

2011 NFL Fantasy Football Spreadsheet

 Downloadable 2011 Fantasy Football Spreadsheets for Excel 2007

Are you looking for 2011 fantasy football spreadsheets that you can download, print out or make adjustments to? Well, I am too and luckily I found a few good ones which I have made available to download here.

This 2011 Fantasy Football Rankings spreadsheet can be updated throughout training camp and the preseason with the latest player rankings by position.  Note that the different positions can be found by clicking on the different sheet numbers at the bottom of the workbook. Download the 2011 NFL fantasy football spreadsheet.

Check out my other sports related downloadable spreadsheet templates. What other fantasy or sports related spreadsheet would you find useful?

I would appreciate it if you could vote for me in this CATIA 2011 visualization contest. http://apps.facebook.com/dassault-catia/profile/150.html

***Also, an update on the Excel Spreadsheets Help $100 giveaway contest is coming soon!****

2011 NFL Fantasy Football players list by position Excel spreadsheet.xls download

2011 NCAA Excel Helmet Schedules

Are you ready for some football? I sure am. I found an excellent 2011 NCAA football schedule spreadsheet online which also includes the helmet design for every team. You can download individual conference schedules or the master schedule for all conferences in Excel. If you're as big a fan as I am you might keep it on your desktop for easy reference.

Download the full schedule which includes all conferences: ACC, Big 10 Ten, Big 12, SEC, PAC 10, and Big East Helmet Schedules! NFL schedule and fantasy league templates coming soon!

2011 College Football Excel Spreadsheet Helmet Schedul.xls download

Employee Timesheet Spreadsheet Template

Are you an employer looking for an employee time-sheet template? I've created a very easy to use employee work log spreadsheet which is available to download, along with some of our other templates. The instructions are included within the spreadsheet but they are not really needed as it is pretty straightforward. I have conveniently colored all cells yellow where your data needs to be entered. Another thing to note, the spreadsheet automatically assumes a half hour lunch. However, you can enter an "X" if no lunch was taken. Please let me know if you would like to see any revisions or additions. I am always looking for ways to improve my spreadsheets. Download the Excel time keeping template here.

Employee Timesheet Spreadsheet Template.xls download

Mileage Reimbursement Template Download

Are you looking for a mileage reimbursement form? Instead of making your own from scratch you can download my free template. This is a good place to start for individuals managing their own business. You can adjust the mileage reimbursement amount for yourself. Currently I have it set at forty-five cents per mile. This request form takes into consideration the fact that you may have to subtract your normal commuting miles from your total miles traveled before getting paid out for your mileage. Feel free to make any changes or suggestions. I am always looking for ways to improve my Excel spreadsheet template. Download the mileage reimbursement request form template here.

Also, don't forget to participate in our contest for a chance to win $100! Contest ends August 1st.

Mileage Reimbursement Expense Form.xls download

WIN $100! Follow my blog for a chance to win

 Welcome to Excel Spreadsheets Help's very first contest! It's easy to enter and you could win $100 (via PayPal). To enter the contest you MUST do two things:

(1) Become a follower of my blog by pressing the "Follow" button on the right hand side
(2) Leave me a comment on this post letting me know you are now a follower


But wait, there's more! You can earn more chances to win by sharing my blog to others, including but not limited to:

1. Tweeting the post and following me on Twitter
   
2. Signing up for RSS feed
3. Sharing on Facebook, StumbleUpon, Digg, etc.
4. Mention this post on your blog
5. Subscribe to my YouTube page
   

That's it! Simple, isn't it? To win you MUST have a PayPal account. I will then post a list of who joined and how many entries each person gets into the contest. The winner will be announced Friday, 7/22/2011.

IMPORTANT!! - Make sure you leave a comment here so I can add you to the drawing. Each action you take will give you one entry into the drawing which will take place on July 31, 2011.  (I do take bribes for extra entries, but they must be over $100 You don’t have to do all of this stuff to get an entry but I’d REALLY like it if you do and if you don’t and you win you will feel extremely guilty!!  When you leave your comment just let me know what you did so I know how many entries your name gets.

Thanks and good luck!

(Due to an apparent lack of interest I have increased the prize to $100 and extended the contest until the end of July. Must have a minimum of ten participants in order for a prize to be awarded. I will give each current follower an entry into the contest for each friend you refer).

How do you enter the current date in an Excel Spreadsheet?

One question I often get from Microsoft Excel users is if there is way to enter the current date into a cell and have it keep that date without changing if you open the same spreadsheet a few days from now. If you use the NOW function it will always display the current date.

The easiest way to enter the current date into a cell is to use the Ctrl-; key combination (press the Ctrl key and type a semicolon). Similarly, you can enter the current time by pressing Ctrl-Shift-; (press the Ctrl and Shift keys and type a semicolon). Both of these key combinations enter the information as a volatile value, not as a formula.

How do you create folders with an Excel Spreadsheet macro?

At work I often find myself having to create multiple folders before beginning a Project. Many others may take the time consuming method of doing this by hand but you can actually save yourself a lot of time by using a simple VBA macro in an Excel Spreadsheet. One method of doing this is to start a new spreadsheet and save it as a macro-enabled workbook in the location where you want to create the multiple folders (such as C:\Work Directory\Parts List)

Next, in column A list all the names of the folders you want to create. Now, hold the "Alt" key down and press "F8" to open the Macros window. Enter "CreateFolders" and click the Create button which will open the VBA editor. You can copy and paste the following code:


Sub CreateFolders()

'create the folders where-ever the workbook is saved

Dim Rng As Range

Dim maxRows, maxCols, r, c As Integer

Set Rng = Selection

maxRows = Rng.Rows.Count

maxCols = Rng.Columns.Count

For c = 1 To maxCols

r = 1

Do While r <= maxRows

If Len(Dir(ActiveWorkbook.Path & "\" & Rng(r, c), vbDirectory)) = 0 Then

MkDir (ActiveWorkbook.Path & "\" & Rng(r, c))

On Error Resume Next

End If

r = r + 1

Loop

Next c

End Sub


 
Now all you have to do is highlight the cells and run the macro. Your folders are automatically created just like that! Save the macro and you can use it over and over again, saving you lots of time and impressing your fellow employees.

Apartment Search Spreadsheet and Shared Expenses Template Download

Update 6/11/12: Check out our new ULTIMATE Apartment Search Spreadsheet and Guide.

I'd like to apologize as I was having a little dispute with the site which hosts all of my Excel spreadsheet template downloads. The issue has been peacefully resolved but in the process it looks like all of the files I uploaded have been deleted. This means all the download links are probably broken. I have started the long process of re-uploading all of the files today but please be patient as it is going to take some time to get them all back up.

I decided to upload my two most requested files first, the Apartment Search Template and the Shared Living Expenses Spreadsheet. Let me know what other files you would like right away.

Apartment Finding Spreadsheet.xls download


Shared Apartment Expenses for 5 Roommates Template.xls download

How do you fix large file sizes in Microsoft Excel?

Are you wondering why your Excel file size is so huge? Is there a way to make large file sizes into smaller, more manageable sizes? As a matter of fact, there is! One method is to examine each separate object in your spreadsheet files so that you can see how it contributes to the objects' overall size. Here's how to do it:

• Save your file
  
• SaveAs a completely new name so that you can mess about with it
  
• SaveAs Web page (choose 'Other Formats' in Excel 2007)
  
• Open Explorer
  
• Navigate to where you saved the file
  
• Open the folder with the same name as your file
  
• Sort descending by the file size
  
  • The largest objects will be named and at the top
    

This works best when you are dealing with multiple sheets. If you only have one sheet, the most likely cause is file formatting. My advice is to recreate the sheet with no formatting, then add the formatting to entire sections such as columns or large ranges.

• Do not format a range of cells, and copy that formatting to other cells.
  
• You can find out exactly how small your file could be by copying only the data to a new sheet and saving that.
  

Why does the file size get so huge? It could be due to a corrupt spreadsheet, which I will tell you how to fix in an upcoming post!

How do you open two Microsoft Excel sessions independently of each other?

Being able to open two session of Microsoft Excel indecently of each other is a very useful feature. What do I mean by this? Let's say I have "Book1" file currently open. Now I open another file namely "Book2". Although, the "Book2" opens up but I don't see it until I go into excel window menu----go at the bottom --- and there I see options for both files i.e. "Book1" &"Book2". So, every time I need to switch back and forth between these two files, I have to go window menu and from there choose which file I need to work with. In addition, if I do ALT+TAB to switch between Book1 & Book2, it does not work.

So basically, I need to find out how can I open both files i.e. Book1 & Book2 such that Both files are visible on the Window STATUS Bar ( the bar right next to "START" button on windows). This way I can easily click on any of the file I need to open. Or else I can use ALT+TAB to switch between any of the files. Currently, I don't see files visible on my status bar and I have to go to window menu to select the file I want.
There are a few methods to accomplish this.
Open Excel then open one workbook. Open Excel again then open other workbook. It works for me in 2003 to get two separate instances of Excel going that are independent of one another.

 
Open Excel twice and then open Book 1 in one Excel and Book 2 in the other. Then go to the desktop toolbar and right click. Select tile windows vertically and the two versions should be side by side.
I was able to get it done by going into Tools--Options--View --click on Windows Task bar.
In Excel 2007, Click the Office button -> Excel Options -> Advanced.
Under General, check 'Ignore other applications that use Dynamic Data Exchange'.
Excel 2010: Go to...
Excel Options | Advanced and under "Display" checkmark "Show all windows in the Taskbar"'


From Mr. Excel Forum

How do you resize Mircosoft Excel’s sheet tabs?

If your eyesight isn't what it used to be, you may want to increase the size of the sheet tabs displayed in your Excel workbooks.

This parameter is determined by a Windows system-wide setting. To change it, select Start, Settings, Control Panel and double-click Display. In the Display Properties dialog box, click the Appearance tab, and choose Scrollbar from the Item list. Adjust the sheet tabs until they're the size you want. Be aware that this setting affects the scroll bars in ALL OF YOUR APPLICATIONS.

2011 NFL Draftees and Mock Draft Template - Excel Spreadsheet Download

I don't know if this is useful for anybody here but many individuals participate in mock  NFL drafts where each of the 32 teams is represented by a member. I imported all 2011 draftees from a website into Excel. I might have missed a few. Like I said I don't know if this is useful to anybody here but thought I'd share anyways. List of players:

http://www.megaupload.com/?d=3ATTL5XG

Use this blank NFL Mock Draft to predict the players being drafted. Fill in your predictions or print it out and manually write in the predictions. Download this blank NFL Mock Draft by clicking the image or this link: NFL Mock Draft

NHL Downloadable Excel Spreadsheet Bracket

Click here to download the newly updated 2012 NHL Playoffs bracket!

Track your favorite teams progress in this 2011 Printable NHL Stanley Cup Playoffs Bracket.  Fill in your team’s progress in this 2011 Printable NHL Playoffs Bracket. Download this 2011 Printable NHL Stanley Cup Playoffs Bracket by clicking the image or this link: 2011 Printable NHL Stanley Cup Playoffs Bracket

Click here to download the newly updated 2012 NHL Playoffs bracket!

Excel as a 3D Graphics Game Engine

I've longed believed that Microsoft Excel really is the Swiss Army Knife of software tools. It never ceases to amaze me just *how* versatile a program it is and how user friendly it is as it doesn't matter what your skill level is - anyone can use it. Some people take Excel programming to the extreme. One such example is using Microsoft Excel as a 3D graphics engine.

 
Peter's work doesn't exploit any hidden code whatsoever – the feat was done using completely documented features installed as standard on a default Excel build. Worksheet=Screen of the engine. Cells=Pixels.

"Obviously whimsical but slightly mind-blowing — an Eastern European coder has published video and the Excel tables to get full 3D wireframe running in Microsoft Excel. He even has solid polygonal graphics running. This isn't an Easter Egg by the Excel creators. Rather, he's using formulas to output the graphics, using two different methods, and showing all the variables on-screen in real time as the 3D is created."

 
Now we have arrived at the next step of this integration, as Excel's cutting-edge 3D functionality is not hidden in Easter Eggs anymore but can be accessible publicly and easily. Excel has grown up and started its conquest as a revolutionary 3D game engine. Excel is very useful program indeed. 


Download the Microsoft Excel 3D Graphics Engine Here


 

March Madness 2011 Best Downloadable Excel Bracket

David Tyler (with Tom Szarek) is the creator of what I consider to be the best March Madness Mircosoft Excel bracket on the internet, which you can download for free. He recently put a new version of the Excel basketball bracket and pool manager file on his website. (http://whenthewhistleblows.wordpress.com/brackets/). Be sure to download the spreadsheets from David's site as they are constantly being updated. 
 
David says, "I overhauled both this year - the bracket is pretty good, but the pool manager file isn't as strong as it's been in years past. I couldn't put the time in the pool manager file that I wanted. I'm excited to make updates for future years, however.  This version is more scalable than in the past, so it will be fun to build on it going forward."

So what are the differences between this year's bracket versus last year's? 

"The biggest thing was building it to handle the expanded field of teams.  The NCAA tournament is now 68 teams, but I built this to handle up to 128.  The bracket also does a better job displaying whether the user has made all the picks in a round (see the conditionally formatted chart on the bottom) and not yelling at the user for temporarily leaving cells blank as he/she works through the bracket.  We also added TV channels and times, which can be shown/hidden via a non-VBA-based checkbox.

Expanding to 128 teams required a complete overhaul, hence why I didn't have time to do much else in the way of enhancements.  But I took the opportunity to redesign how Picks are stored in the pool manager file. Previously they were storied in a flat-file format, but now it's more like a database model.  This will provide the required flexibility in order to put in custom scoring options next year." 

How are the brackets being constantly updated and why?

"I'm updating those files frequently as I catch bugs (the problem of having your end users as your beta testers). Just fixed a bunch of issues with the pool manager file and uploaded a new version.  People might not even notice them (a stray border getting deleted, a certain combination of steps resulting in a list not being populated, etc), but I wanted to get them fixed."

Sounds very exciting! I can't wait to see future versions as this year's spreadsheets are even better than last year's. I thought it was very smart to build the bracket with the future in mind in case of further expansion (though I hope it doesn't happen). I look forward to completing the bracket! Anybody have a good pool going?

NCAA2011PoolManager.xls download
NCAA2011Bracket.xls download 

How to make a Yes or No macro in Excel

There may be times when your Excel spreadsheet will want users to click a "Yes" or "No" button. To do this simply insert this line of VBA code. Tthe Select Case statement is used in this example.

YesNo = MsgBox("This macro will ... Do you want to continue?", vbYesNo + vbCritical, "Caution")
Select Case YesNo
Case vbYes
'Insert your code here if Yes is clicked
Case vbNo
'Insert your code here if No is clicked
End Select

Excel Spreadsheets Project Management Templates Download

Throughout my engineering career I've compiled a nice collection of project management style spreadsheets as well as other document to help with the development and process flow of a project. I have decided to make this documents available for download. These management help documents include: a work plan template, a risk assessment log, a log for keeping track of potential issues which may slip up a planned project, a project change log, an Excel Gantt chart, and a master project management template (great to use if you don't have access to Microsoft project). Enjoy the download links:

Work Plan Template.xls download
Risk Log Template.xls download
Issue Log Template.xls download
Change Log Template.xls download
Excel Gantt Chart.xls download
Project Management Template.xls download

As always, suggestions for improvement are welcome and very appreciated!

Split the Rent Complement Spreadsheets

I stumbled across a really great website yesterday - Split The Rent. Splitting rent with your friends shouldn't be stressful. If your rooms are different sizes or different levels of niceness, my calculator will give you a neutral and objective opinion on how to share the rent. I really like the SplitTheRent calculator. I wish I would've found it a few years ago because it really would've been very useful when I was in college.

I think the Split the Rent calculator is a great complement to my apartment related Excel spreadsheets. Before you begin splitting the rent you have to find a place to live. You can use my apartment search spreadsheet while you and your friends are apartment hunting and looking for a place to live. Next, use the Split the Rent calculator to figure out exactly how you are going to split the rent between roommates. After that decision is made you need a way to keep track of which friend you owe money to. My shared apartment expenses spreadsheet (now modified for 5 roommates) does just that- keeps track of what each roommate owes the other roommates. I think these three items are great complements to each other and hopefully make life easier for some of you!

Update 6/11/12: Check out new and improved apartment search spreadsheet.

Excel Spreadsheet Help Android App Suggestions

I'm currently learning how to write apps for Android phones. However, I need a goal to work towards. I was wondering if any readers had any ideas or suggestions for an Excel Spreadsheets Help phone app (or any other related apps). I'm trying to think of something somewhat simple to start off with but could be added onto later or inspire new ideas. I think the ideal model is the have a basic version which is free to download but more advanced users could then download a "pro" version for a small fee.

Maybe I could do an app for my Apartment Search Spreadsheet where you could enter the info into your phone while you are touring an apartment complex and that info could then be used to easily complete the spreadsheet later. What do you think? Any suggestions?

Also, how many Android user vs. iPhone users are out that which use this site?

How to Stop Cell References from Changing When Inserting Columns or Rows

We have a special treat for you today. Our second guest blogger is Yoav Ezer, who is going to share with us a technique of how to stop a cell reference from changing when inserting a column or row.  I really appreciate Yoav taking the time and sharing his insight, experience, and wealth of information with us today. Now, on to the article!

------------------------------------------------------

One of the great things about Microsoft Excel is you can copy and paste formulas and they still work relative to where the formula now lives.
You can get around this behavior if you want to also. When you create a formula and reference a specific cell, and you want that reference to remain correct even when the formula is moved, or you add or delete columns or rows, then you should use an absolute cell reference.
But there is one situation where even absolute cell references won't help. In most cases when you add a row or column you would want the formula to reference the original (now moved) cell, but there are some cases you don't want the cell reference to change even when inserting a row or a column. This article shows how to work around this Excel behavior so you can force it to reference the exact cell you choose.

A Working Example

Imagine you are a swimming coach wanting to keep a track of your swimmer's recent average swim times.

We could have columns to record the last nine swim times then calculate the average timing of the last three swims and the average time of all the swims.
Every time we monitor a swimmer their latest swim time is inserted as the first column of the spreadsheet, and the ninth time column is deleted.
Obviously want the formulas in the last two columns where we calculate the averages to always remain the same, even after all the deletions and the insertions.

An INDIRECT Solution

Our solution is to use a little-known Excel function called INDIRECT.

Indirect allows you to specify an exact cell and Excel will go and fetch the contents of that precise location.
What would you normally use?

=AVERAGE(B5:J5) 

As you can see in the screenshot below, once you have added and deleted columns, this no longer works as a solution:

We would instead use:

=AVERAGE(INDIRECT("B" & ROW() & ":J" & ROW()))

Summary

Sometimes Excel seems to work so hard to be helpful that it works against what you want, but there is almost always a way to get the result you need. Lucky for us in this case the INDIRECT() function is an easy solution!

About the author

Yoav Ezer co-authors the technology and productivity blog Codswallop. He is also the CEO of a company that produces PDF to Excel conversion software.
For more Excel tips from Yoav, join him on Facebook or Twitter

Trip Cost Estimator Spreadsheet Template Download

I recently had to estimate the cost of going on a trip to attend a conference for my day job. This lead directly to the creation of a total trip cost estimator spreadsheet, which I have transformed into an easy to use template. It's not totally complete yet. I am looking for suggestions for more categories to add but I will still upload the template as is and make it available for download. If the link doesn't work please let me know and I can email the spreadsheet to you.

If you're looking to estimate the cost of traveling on vacation or need a honeymoon planner then this spreadsheet template is a good place to start. How do you compare airline tickets to determine the best price of flights? What's an easy way to compare hotel or car rental services? Need a brief idea of what the price range of a major trip is going to cost? Use the Trip Cost Estimator Spreadsheet template. Create your own or download mine today.

Trip Cost Estimator Spreadsheet Template.xls Download Here

Download the Apartment Search Spreadsheet

I have uploaded the latest version of my apartment search spreadsheet and made it available to download. Are you looking for a new place to live? This apartment search spreadsheet is the perfect guide and tool to use for your search. Trust me, it will make your life much easier and you won't have to put much thought into which apartment is the best one for you. The spreadsheet does all of the work for you. Download the apartment hunting guide today.

This Excel document could also work for scenarios where you need a rating formula system to decide which option is best. You may also want to download this template and use it as an example to create your own unique rating comparison spreadsheet. Or I can do it for you. Simply comment below what your needs are and I will gladly tailor the spreadsheet for your specific situation.

That's not all! Are you moving into a new apartment with friends? It's a pain in the butt to keep track of who owes who what and which bills have been paid. I was in that same situation many times which is why, out of necessity, I created the Shared Living Expenses Spreadsheet! Download it here. Know exactly which roommate owes the other roommates at any moment simply by using my apartment roommates expenses spreadsheet. Again, I appreciate any suggestions.

Update: 6/11/12 Check out the latest apartment comparison spreadsheet.

How do you create pivot tables in Excel?

Today's post is all about pivot tables. I am going to answer the following questions: How do you use and create a pivot table in Microsoft Excel? What are the advantages of using a pivot table? What is a pivot table? How do you make a pivot chart? Let's begin with the definition of a pivot table.

What is a pivot table?

Pivot tables provide an interactive summary of data. You can quickly rearrange and sort the information in the pivot table. There's no need for any complicated formulas. A pivot table classifies numeric data in a list based on other fields in the list. Before creating your pivot table, you should organize your data in a nice and neat table. Do this by creating headers in the first row. Try not to have any blank cells, rows, or columns in your table.

What are the advantages of using a pivot table?

Pivot tables can easily be rearranged by moving, adding, or deleting unneeded fields. Pivot tables are also dynamic, which means that the results of the table are automatically recalculated and updated whenever something changes, like when fields are added or removed, or if categories are hidden or displayed. Pivot tables are also simple to update. It's also very easy to generate charts from your pivot table.

How do you create a pivot table in Excel?

First, under the "Data" menu, select "Pivot Table and Pivot Chart Report."  This will bring up the Pivot Table Wizard, which consists of the following three steps.

Step 1: If your data resides in a worksheet, simply click "Next."

Step 2: Excel will automatically select the data we are currently working with.  Click "Next" again.

Step 3: I usually open the pivot table in a new worksheet (again, this is the default).  Note that you can save a few seconds simply by clicking "Finish" during step 1 if you don't plan to do anything unusual (which, in my experience, is almost every time).

How do you create a pivot chart?

To create a chart from your pivot table right click on a cell in the Pivot Table and select "Pivot Chart."  A chart is automatically generated.  Note that you can view the data by using the drop down menu at the top of the chart, and you can modify the chart by either of the other variables by using the filter menus on the bottom and side.
If you don't like the type of chart Excel chooses, you can modify it by right clicking anywhere on the chart and selecting "Chart Type."
Note that if you modify the pivot table in any way, the chart is automatically updated.

Now you know all about pivot tables and pivot charts! More examples and pivot table templates are coming soon. Stay tuned!