Best Excel Posts from 2012

As the year is about to end it's time to look back and reflect on our most popular posts from 2012, listed below, in order:

1. Printable 2012 NFL Playoff Bracket
2. Printable 2012 Superbowl Squares Spreadsheet
3. 2011-2012 NHL Stanley Cup Playoff Printable Bracket
4. Downloadable 2012 NCAA Tournament Bracket
5. Career Advice: Make a Website

After trying a couple of different options, including ShareCash and Google Docs, I've finally settled on using www.box.com to host my Excel spreadsheets and templates to allow you to easily download them with no extra hassles.  Listed below are the top downloaded spreadsheets from the past year:

1. 2012 NFL Helmet Schedule 8.20.12.xls
2. 2012 NCAA Helmet Schedule.xls
3. Expense Report Template.xls
4. Golf Scorescard Templete.xls
5. Give Yourself a Raise.pdf

As you can see, the sports templates are proving to be very popular and we've got some good ones on deck for 2013! Also, my project management template (that includes a free gantt chart) proved to be very popular but you'll have to signup for our email newsletter to recieve it.

Looking ahead, we plan to bring you even more and better Excel spreadsheets templates in 2013. Do you have any suggestions for the site? What do you like or don’t like? Any features you want to see more or less of?

Thanks for reading Excel spreadsheets help this past year, I really appreciate it. This blog wouldn't exsist without you! Have a happy new year and I'll see you in 2013.

-Nick

NCAA Football Bowl Schedule and Pool 2012

The NCAA college football bowl season is finally here which means it’s time to make your picks and predictions about who you think will win each game. One of the best times of the holiday season (other than giving and receiving gifts) is gathering around the TV and rooting for your favorite football team in a BCS bowl game. Make the occasion even more fun and competitive by trying to predict the NCAA football bowl games.

Use my spreadsheet template to create a college bowl pool with your friends or coworkers. Simply download the Excel file linked to below and follow the instructions included within the spreadsheet. The NCAA bowl pool also doubles as a printable college football bowl schedule. The spreadsheet is all set up and ready to go - simply make your picks and then watch the game to see who the winner is. It automatically keeps track of several different stats like how many games each person gets correct, what percentage of games you’ve picker correctly, and more!

How it Works

Altogether you do not have to enter or change any formulas or complicated functions to use the spreadsheet, I will briefly explain how it works in case you want to create your own from scratch or modify mine:

The college bowl spreadsheet uses drop down lists to allow the user to select a winning team. The number of games picked correctly is added using Excel’s SUMProduct function. Conditional formatting is used to color code correct picks from incorrect picks.

Download the NCAA College Football Bowl Schedule and Pool

To download the spreadsheet simply click the link below which will take you to the box.com, where the file is hosted. Next, click on the “Download” link in the upper right hand corner. That’s it!

Download: NCAA Football Bowl Schedule 2012.xls (hosted on www.box.com)

Update 12/12/12: I uploaded a new version of the spreadsheet with a macro that automatically adds the correct number of columns and stats for the total number of players. To run the macro go to the Master Pool sheet and run the "AddPlayers" macro (by going to View>Macros>View Macros>AddPlayers>Run). After hitting run, enter total number of players into pop-up input box. Click OK then complete the rest of the template as usual.

Also, I’ve created a Google doc to start an Excel Spreadsheets Help NCAA bowl pool. To join, download the spreadsheet, make your predictions, then copy and paste your picks into my Google doc here:

https://docs.google.com/spreadsheet/ccc?key=0Av7RTFdlK3AmdFBwcHBINi1jUi1jdm1ZWTFtbWNuQkE

Final Thoughts

I’m sad to see my Buckeyes sitting on the sidelines this year (should have taken a bowl ban last year) although I’m hoping my Toledo Rockets can cap off the year with a good win. I’m a Big Ten and MAC guy so I’ll be pulling for each conference team but I have to admit I have almost no hope or confidence for any of them winning a single match up. What are your NCAA bowl predictions? What are your bowl picks? Leave a comment below and let me know! Like our Facebook page to receive notification when we post our NFL playoffs bracket and other sport templates.

-Nick

Go Rockets!

Travel Expense Report Template

If you’ve ever traveled for your corporate day job then you’ve probably completed a travel expense report or travel reimbursement form at some point. Of course, before you were even granted permission to go on a trip you probably had to complete a travel authorization request. If you’re only traveling locally and don’t have to spend the night at a hotel you may only have to turn in a mileage reimbursement voucher. (Why are there so many forms?) But if you get stuck staying overnight or have the privilege of traveling to a foreign country chances are your company is going to want you to keep track of every single expense. This is where my travel expense sheet comes in handy!

The form is a great tool to log personal or employee travel expenses. I’ve literally tried to think of every possible expense that may need to be tracked and report, including lodging (room), transportation (rental car, taxi, airline, gasoline), meals (breakfast, lunch, dinner, or per diem), other (entertainment, parking, tolls, etc.). There is even a sheet for currency conversion figures. Sometimes your company may give advance pay to cover your traveling expenses before you even leave. This is included in the spreadsheet in the section which shows how much the company owes the employee (or vice versa if given an advance pay and you don’t use it all). Seriously, I’ve really tried to think of everything!

There is a section at the bottom of the template for your manager or accountant to sign off on or if you’re a solo gig you can simply ignore it. I’ve provided ample room for explanations too, in case your boss wants to know why you are declaring that beer you bought at the hotel as a company entertainment expense. Download the form below (hosted on Box) and please do let me know if you think of anything missing from this spreadsheet.

Travel Expense Report Template.xls download

Nick
Travel Paid For

Travel Authorization Request Form Template

Today I am going to share with you an Excel spreadsheet template I use every time I need to travel for my day job - my travel authorization request form. Many companies today use travel request forms to help log and keep track of employee’s travel. Yes, even I still have to get permission from the bean counters before I have approval to make travel arrangements!

My request for travel authorization form is very simple and easy to use. It’s extremely light - there are no formulas involved. If you’re setting this up for your own business you can also choose different per diem pay rates if the traveler will be paid for food on a daily basis.

Below is a link to a free download of my Travel Form spreadsheet. Please feel free to let me know if you have any questions. Check out our Downloads page for more free Excel spreadsheets or the project management page for other templates.

Travel Authorization Request form.xls

My travel authorization form is also included in my new project management template which you can download for free when you sign up for the Excel Spreadsheets Help email newsletter.

What about you? Do you have your own travel reimbursement form? Does your company require a travel authorization sheet to be filled out?

Nick
Travel Authorized

NBA 2012 Schedule Download

The NBA 2012 schedule has been released and I’ve created a Microsoft Excel spreadsheet of it available for free download. The NBA preseason has already begun (not included in the Excel file) and the regular season begins October 30th. File is hosted on box.com. If you're an NBA fan you can also check out our mock draft creator.  The NBA 2013 schedule is sorted by teams and by date. Remember, when the playoffs roll around (seems like a long, long ways off for now) we'll have a playoff bracket for you to download. Until then, enjoy the regular season!

2012 - 2013 NBA Schedule.xls download

 
Check out the updated Excel Downloads page for more spreadsheet templates or join our newsletter to receive the latest updates automatically in your inbox.

What pro basketball teams are you rooting for? Does anybody in the NBA use the Flex offense?

-Nick
Probably not watching much pro basketball until May

Printable 2012 MLB Playoff Bracket

The Major League Baseball season is over and now the 2012 MLB playoff schedule has been set beginning with the inaugural pair of Wild Card Games on Friday, October 5th. The postseason MLB has been expanded this year by adding additional wild card teams, bringing the total number of participants from 8 to 10. Other changes to the 2012 MLB playoff format include the following:

• Clubs from the same division will be allowed to play one another in either the Wild Card Game or the Division Series of the major league baseball playoffs.
• Tiebreaker games will be played to determine Division Championships, even if the two tied teams are assured of participating in the Postseason. If a Division Championship tiebreaker game is necessary, the head-to-head record between the tied teams will then determine home field advantage. If the head-to-head record is tied, the division record will be the next tiebreaker.
• If two teams are tied for both Wild Card berths, home field will be determined by the head-to-head record between the tied Clubs. If the head-to-head record is tied, then division record will be the next tiebreaker.

In its 17th season as the official network broadcaster of Major League Baseball, FOX Sports will present exclusive live telecast coverage of the World Series. Game One of the 108th World Series is scheduled for Wednesday, October 24th in the ballpark of the National League Champions. I can’t say I’m a big fan of the single Wild Card games. The sample size is too small to me. I’d like to see a best out of three at the least.

I’ve created a printable 2012 MLB Playoff Bracket spreadsheet available for free download. The bracket includes the schedule and playoff dates of each series. The download is hosted on box.com.

Printable 2012 MLB Playoff Bracket.xls download


Visit our downloads page for more sports spreadsheet templates or check out the organized baseball coach spreadsheets.  I’ll be cheering for the Reds to win the World Series. Who are you rooting for?

-Nick
Watching October Baseball (and a fan of Moneyball)

Career Advice: Make a Website

As an engineer and daily Excel user, there are a few key things I’ve learned along the way that have significantly helped advance my career. My top three pieces of career advice for Excel users are:

1. Learn how to use Excel and its functions
2. Learn how to program VBA macros for Excel
3. Build a website to promote your skills and abilities

I’m going to be posting about all of these topics but today I decided to start with building your own personal blog or website. Creating your own professional looking blog or website is a great way to market yourself and your skills when applying for a new job and a great topic of discussion during an interview. You could create an authority site about a subject you are passionate about, like an Excel website, or make a simple biographical site with your resume.

My Top Ten Reasons for Building a Website or Blog

1. Networking – Get your name out there, new career opportunities may arise.
2. Professional image – Provide additional information not on resume.
3. Education – Learning experience of how to do it.
4. Help Others - Teaching is a great way of giving back.
5. It’s easy to do and you don’t need to know HTML coding.
6. Marketing – allows you to showcase your work.
7. Credibility – shows you’ve spent time developing your goals and objectives.
8. Communication – Opens more avenues of communication.
9. Competition – other job seekers may be doing it so you should too just to keep pace.
10. Residual income – Websites can potentially be a source of additional income for you.

How to Create Your Own Website

 

While creating and maintaining your own website may sound like a daunting task, and it’s hard to take that initial plunge, I’m here to show you how and let you know, with a little help, it can actually be a very simple process. Unless you’re planning on creating something completely custom, starting a website really isn’t that hard. I register my domain names with NameCheap because it’s, well, cheap! I use WordPress for my content management system (CMS) automatically installed through Host Gator, my hosting service. I bought the Socrates theme to make my websites look nice and so there’s no html coding. The most confusing part for me was pointing the domain name to my hosting service. Luckily, there are plenty of YouTube videos to walk you through all that.

One of the first things I recommend you do is purchase a domain name. Websites are like online real estate- there isn’t much use in building a house you own on rented land. Blogging platforms like WordPress and Blogger are free to use but you don’t own them, meaning they could decide to turn off the lights at any point, plus you have to add terms like .blogspot to your URL. Namecheap is easy to navigate, and very affordable solution to owning your own domain name.

Next, you need a place to host your website. I use, and highly recommend, HostGator, as do many other bloggers out there. It is as cheap as $4 per month if you pay in advance or $8 per month paying month to month. Because I have many domains I always go for a setup that I can host an unlimited number of domains on rather than just one. HostGator has such a setup called the ‘Baby Plan’. I recommend using the plan which allows you to host multiple websites. If you create only one site this is expensive but the more sites you create the cheaper it gets on a per site basis. HostGator is known for their exceptional customer service. Their online chat representatives are standing by at all hours to assist you with any problems you may have setting up and maintaining your website. Enter “EXCEL_HELP″ in the coupon code field to receive a discount.

The other thing I recommend to use is WordPress CMS. This is not the free blogging version of course, but the free downloadable one that you install on your new web host. There are many videos and articles on how to do this. Just Google ‘How to Install WordPress’. Another reason I picked Hostgator was because it has an automatic “one-click” WordPress installer called Fantastico. WordPress CMS allows you to easily edit the layout of your blog without having to know how to code anything.

If you have already thought of a domain name which you know is available, you can literally go through all of the above steps and setup your website in a matter of minutes. If this is your first time dabbling in website creation it may take a little longer but after you create one then I promise you your subsequent websites will be created faster and faster.

Concluding Thoughts

 

Creating a website is a great way to get your name out there and showcase some of your Excel skills. When you will have a visible web presence you can allure visitors and show confidence on your expertise and work. It may even be a deciding factor in receiving a job offer over another candidate.

~Nick

P.S. Remember, enter “EXCEL_HELP″ in the HostGator coupon field for a discount on website hosting.

Unique Excel Uses: Designing Roller Coasters

We’re always looking for new and unique uses for Excel and I recently stumbled upon a very thrilling example of what Microsoft Excel is used for. Travis Rothbloom is a mechanical engineer and aspiring roller coaster designer. He decided to design a roller coaster for a school project using a  combination of Excel and MatLAB. This massive spreadsheet with roller coaster physics formulas contains 8500 rows by 50 columns of data! Travis explains how he compiled his engineering spreadsheet:

The first thing that I established in my spreadsheet were the constant values that I used, namely gravitational acceleration, friction coefficients, and finite step size along with other parameters that helped define the physical geometry of the track. Excel's functionality of maintaining a reference's cell index with the "$" symbol really came in useful when I needed to change friction values - all I had to do was change the one cell storing the coefficient and the entire spreadsheet (thousands of lines of data) would update automatically.

Formatting, constant values, a table containing statistics, and an embedded equation using Excel's native trig functions.

Then it was time to take the physics equations that I derived and embed them into the spreadsheet. This was pretty easy given that Excel has built-in methods for calculating trigonometric functions, powers/roots, and division remainders while maintaining the proper order of operations. Given that many of the calculated rows' values are dependent on their respective column's previous value, I had to set up a row to store initial conditions as to not cause a null reference. When I did have null references or circular dependencies, however, it was easy to spot the source with Excel's error handling mechanisms. Formulas that described the dictating curves of the track, whether they be in g-forces, roll angle, curvature radii, etc., relied on an incrementing time index whose interval was dictated by the finite step size parameter's cell.

With all this, I relied on Excel's formatting to help visualize what was transpiring in my spreadsheet. I highlighted both rows and individual cells to indicate what was a dictating, inputted value vs. what was being calculated by other values; this was not the same for every row because I would sometimes rearrange the equations for nuanced track elements, and using this color coded system made this a whole lot easier to keep track of. I also used blank columns highlighted with a color to separate columns into groups for easier viewing. Along with that, I frequently would hide multiple columns or rows to help navigate the spreadsheet as thousands of lines and up to 50+ columns of data can become unwieldy at times. Lastly, I created a table at the top of the sheet that maintained the maximum or minimum values of particular values such as speed and different g-forces.

2D plot showing an elevation of the ride

Although Excel doesn't have a built-in 3D plotter, I created 2D plots of the track coordinates so I could view the track geometry within the spreadsheet. I stored these in separate tabs for easy navigation. Also stored in a separate tab was any other miscellaneous information that I would reference.

Finally, I made use of the fact that other programs usually have an easy time reading/parsing Excel documents. I frequently imported my spreadsheet into Matlab for further post-processing, including 3D plotting and some other calculations. It's not necessarily the case that Excel wasn't able to do any of these other things (for example, I have found user-created 3D plotting macros online) but rather I'm more comfortable coding in Matlab rather than VBA. People would often ask me why I didn't just work in Matlab for the entire project, to which my response was that I thought (and still do think) that working with mass data sets in spreadsheet format is best done by Excel and since it is found on so many computers and it's so easily read by other programs, it was easy to work on the project wherever I was.

2D plot showing the ride's plan

Thanks again to Travis for sharing his awesome “Excel uses” example and good luck towards your goal of becoming a coaster creator - and let us know when you do so we can go ride your  breathtaking creation! Read more details about Project Soar at his website.

Subscribe to our newsletter for more updates and drop us a line (or a comment) if you know of someone with a unique or interesting  “Excel Used For” example.

Mileage Reimbursement Form Template

Today I am going to share with you an Excel spreadsheet template I use every week, my mileage reimbursement form. Many companies today use mileage forms to help log and keep track of employee’s mileage when they travel. This template allows you to input your mileage as your total number of miles driven or you can enter your beginning and ending  odometer readings thus giving you your total reimbursable mileage. If you’re setting this up for your own business you can also choose different payback rates depending on if the vehicle driven is personal or company owned.

My mileage sheet is very simple and easy to use. All grey colored cells are formulas. You shouldn’t need to change any of these and can use it as is. The mileage form assumes the report date is on Friday. The formulas then use this date to input the dates for the rest of the week using simple subtraction. I also use the ROUND function in order to round the amount owed to the employee to a nice number (like 9.85 instead of 9.84586).

Below is a link to a free download of my Mileage Reimbursement Form spreadsheet. Please feel free to let me know if you have any questions. Check out our Downloads page for more free Excel spreadsheets or the project management page for other templates and resources.

Mileage Reimbursement Form Template.xls

What about you? Do you have your own mileage log spreadsheet? Does your company track employee's mileage?

Nick
Effective Mileage Tracker

Friday Fun: Spreadsheet Engineer

Gantt Chart Template Pro Giveaway Contest

Gantt Charts are extremely useful tools for planning and  project management. These bar type charts are helpful when laying out schedules and tasks associated with a given project. A spreadsheet version of a Gantt chart is often a cost effective alternative over more expensive project management software (like Microsoft Project).

In my experience, one of the best Excel Gantt charts I’ve used is the Gantt Chart Template Pro from Vertex42. This easy to use tool will make your project planning more efficient. Helpful instructions and an FAQ section are included within the spreadsheet.

You can test drive the free version of the Gantt Chart but you will need the Pro version to unlock all of the features, such as grouping rows. Fortunately, I have a copy of the Pro version to giveaway for free - a $39.95 value! 

Entry into the giveaway contest is simple:

1. Visit Vertex42’s website.
2. Leave a public comment with your name on this blog post about how the Gantt Chart Template Pro will help you manage your project and what you will do with it.

That’s it! But wait. You want even more chances to win? Increase your chances of winning by doing any or all of the following:

1. Subscribe to our free email newsletter to hear about future contests
2. Share this post via Twitter, Facebook, LinkedIn, etc. Be sure to include in your comment how you shared the post so I can credit you for the additional entries.

The contest period starts today and ends Wednesday, September 12th. I will use Excel to randomly selected a winner. Enter now for your chance to win the free Gantt chart Excel template and improve your project planning.  Thanks and good luck!

-Nick
Efficiently Planning Projects

Friday Fun: Thanks for sending the Spreadsheet

I've decided to start a new, lighthearted series called Friday Fun where I will attempt to post a funny Excel related joke, story, or image. Today I've found an all too true eCard from someecards.com. Enjoy!

Excel Create Folder Macro Updated

Today I’m going to revisit how to create a folder in Excel. There’s been some good discussion on my earlier post about to use an Excelmacro to automatically create folders. My original version only created folders in the same file where the Excel spreadsheet was saved. After some reader questions and collaboration we’ve created a new version which allows you to browse to the directory location where you would like the VBA macro to automatically create all the folders you have listed and selected in the Excel workbook.

A reminder of how the Excel macro creates folders. Make your list of folders in any column in a worksheet (which does NOT have to be saved like in previous versions). Select the range of names you want to create. Run the macro.

To open a folder browser with an Excel macro we need to create a shell application object using this code:  

Set ShellApp = CreateObject("Shell.Application").BrowseForFolder(0, "Please Choose The Folder For This Project", 0, OpenAt)

I put together a short video showcasing the end result and how the Excel VBA create folder macro should work. Also, if you’re looking to build your own website watch the video to get a 25% off coupon for Host Gator.

The complete code is listed below. Now you can show your bosses and coworkers how to make a folder with Excel. Please join our newsletter for more Excel tips.

Sub Create_Folders()

‘http://excelspreadsheetshelp.blogspot.com

penAt = "My computer:\"

Set ShellApp = CreateObject("Shell.Application").BrowseForFolder(0, "Please Choose The Folder For This Project", 0, OpenAt)

'Set the folder to that selected. (On error in case cancelled)

On Error Resume Next

BrowseForFolder = ShellApp.Self.Path

'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 (BrowseForFolder & "\" & Rng(r, c))

On Error Resume Next

End If

r = r + 1

Loop

Next c

End Sub

 

Create Folders.xlsm Download

Join our newsletter for more helpful macros.

Weighted Olympic Medal Count 2012

In honor of the 2012 Summer Olympic Games currently being held in London, England, I decided to create a Microsoft Excel spreadsheet template for the medal count. There are two primary methods most websites appear to be ranking the 2012 medal count. Sites like Yahoo rank countries by the total number of Olympic medals won. Other sites, like the International Olympic Committee (or IOC) rank countries by their gold medal count. (And others, like this one from Forbes, rank by other factors like per capita or GDP.)

If you rank by gold medals countries like Great Britain and South Korea look really good. On the other hand, Japan has 27 medals, ranking fifth overall, but only TWO of them are gold. I’ve devised my own ranking system to give each Olympic medal a weight where the silver is worth half a gold medal and a bronze is worth only a quarter of the gold. Based on this new scoring system, the Olympic results suddenly become quite interesting.
 

I looked at the Olympic Game results for the top twenty countries medal counts up through today (Monday, August 5th). The top four countries actually remain in the same order but Japan drops from fifth to eighth. South Korea jumps up from 7th to 5th due to 11 gold medals. The biggest increase is Kazakhstan which shoots up from barely making the list at #20, almost all the way into the top ten at #11.The biggest fall is by Canada from 12th to 16th. Oh, and if he were a country he’d rank 14th overall because Michael Phelps' medal count at these Olympics Games is four gold and two silver.

I’ve shared my Excel spreadsheet on Google docs and listed out the Olympic medals by country (as of the morning of August 5th - I will try to keep this updated but no promises!). How would you weight each medal against the others? Comment below and share any of your more interesting Olympic medal counts!

Check out our Downloads page for more sports templates and join our mailing list to be notified about new posts and spreadsheets.