## Tuesday, December 27, 2011

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?

Update: get the 2012-2013 NBA schedule here!

## Monday, December 26, 2011

### 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.

## Monday, December 19, 2011

### 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.

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.

## Tuesday, December 13, 2011

### 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!

## Monday, December 5, 2011

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.

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

## Sunday, December 4, 2011

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!

Update 12/6/11:

Update 12/27/2011

## Monday, November 28, 2011

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
.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
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

## Monday, November 14, 2011

### 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.

## Monday, November 7, 2011

### 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

## Monday, October 31, 2011

### 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

## Sunday, October 23, 2011

### 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)

## Wednesday, October 19, 2011

### 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

Set workbooks = Excel.Application.workbooks

'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!

## Monday, October 10, 2011

### 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.

## Tuesday, September 6, 2011

### 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?

## Monday, August 22, 2011

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.

Check out our 2012 NFL fantasy mock draft creator spreadsheet.

2012-2013 NFL Excel Helmet Schedule Spreadsheet.xls

## Sunday, August 7, 2011

### 2011 NFL Fantasy Football Spreadsheet

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.

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!****

## Tuesday, August 2, 2011

### 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!

## Tuesday, July 19, 2011

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

## Tuesday, July 12, 2011

### 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).

# 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.

## Thursday, May 26, 2011

### 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.

## Wednesday, May 25, 2011

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.

## Tuesday, May 17, 2011

### 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:

• 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!

## Tuesday, May 10, 2011

### 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"'

## Monday, May 2, 2011

### 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.

## Wednesday, April 13, 2011

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:

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

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

## Tuesday, April 5, 2011

### 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.

## Monday, March 14, 2011

David Tyler

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?

## Thursday, March 10, 2011

### 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

## Saturday, February 26, 2011

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:

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

## Thursday, February 24, 2011

### 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.

## Monday, February 14, 2011

### 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?

# ------------------------------------------------------

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:

`=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!

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

## Sunday, February 6, 2011

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.

## Sunday, January 30, 2011

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.

## Thursday, January 20, 2011

### 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!

## Wednesday, January 12, 2011

### Excel Tips: Skill Assessment Exam Answers

I recently took a Microsoft Excel skills assessment exam. There were a few questions which surprised me. Luckily, I was able to figure them out rather easily. I have decided to share a few of the answers from the skills assessment exam (though not all of them) with you. If you're never going to take an Excel exam then please few these as a few friendly spreadsheet tips.

Q: How do you go to the dialog box in Excel (what is the shortcut to go to the dialog box)? Answer: F5

Q: If you enter a time into Excel, does it default to AM or PM? Answer: AM

Q: How do you display the current time in Excel? Answer: crtl+shift+:

Q: How do you recalculate all formulas in a worksheet (what is the shortcut to recalculate all formulas)? Answer: crtl+alt+F9

## Thursday, January 6, 2011

### Advanced Custom VLOOKUP Formula Help

You may encounter a problem with the VLOOKUP function the VLOOKUP formula will only return the first solution it finds. How can the VLOOKUP function return all correct entries? The easiest method to accomplish this will require VBA. We must create a custom function in Excel. Begin by pressing ALT+F11 to open VBA window. Next, click Insert- Module. Try the following code:

Public Function VlookupNth(MyVal As Variant, MyRange As Range, Optional ColRef As Long, _Optional Nth As Long = 1)
'Similar to Vlookup, but returns the Nth value found from the top of myrange.
'Not necessarily the First.
'The TRUE/FALSE argument usually found in Vlookup is assumed FALSE in this function. 'Data does NOT need to be sorted, and it searches for EXACT match.
'if ColRef is omitted, uses the number of columns in myrange
'if Nth is omitted, returns the first value found

Dim Count, i As Long
Dim MySheet As Worksheet

Count = 0
Set MySheet = Sheets(MyRange.Parent.Name)
If ColRef = 0 Then ColRef = MyRange.Columns.Count
For i = MyRange.Row To MyRange.Row + MyRange.Rows.Count - 1
If MySheet.Cells(i, MyRange.Column).Value = MyVal Then
Count = Count + 1
If Count = Nth Then
VlookupNth = MySheet.Cells(i, MyRange.Column + ColRef - 1).Value
Exit Function
End If
End If
Next i
End Function

Now, once the VBA is complete, close the window and save your file. Now you can enter this custom formula in the worksheet to pick any row number that you want:

=VLOOKUPNTH(LOOKUP_VALUE, LOOKUP_ARRAY, COLUMN_NUMBER, Nth_Number_Row)

So, if you want to choose the value in the 4th column of the second row where the value matches exactly, then the Excel formula would be something like this:

=VLOOKUPNTH(Lookup_Value,LOOKUP_ARRAY,4,2)

What's the difference between this advanced VLOOKUP formula and the standard VLOOKUP? If you have a normal VLOOKUP formula like this =VLOOKUP(A1,B:C,2,FALSE) whereas the VLOOKUPNTH formula would look like this =VLOOKUPNTH(A1,B:C,2,2). The last digit corresponds to the nth part which will return the nth occurrence of the lookup value.

Now you're probably wondering what happens if there are more than two occurrences? What if there are 200 occurrences of the data? Well, then you would need 200 formulas with one returning the first value, one returning the second and so on. You don't want to have to manually edit all 200 formulas so insert the ROW function in order to increment the nth as you fill the formula down. =VLOOKUPNTH(\$A\$1,B:C,2,ROW(A1))

Check out our follow-up post: advanced custom HLOOKUP formula.