Thursday, March 24, 2016

2016 Presidential Candidate Comparison Spreadsheet

I'm not really too big into politics but a few readers have asked me to create a 2016 Presidential Candidate Comparison spreadsheet and here it is! This new spreadsheet serves two purposes - it allows you to see where each candidate stands on forty-eight different issues and also allows you to see how your own views align with what candidate.

1. Candidate Comparison

The first sheet in the spreadsheet is the candidate comparison that shows all five candidate's responded to forty-eight different questions on topics such as immigration, gun control, abortion, and more.

2. Which candidate should you support?

If you're still unsure of which candidate you might vote for in this election, you can use the scoring system in the candidate comparison spreadsheet to help you figure it out. Use the drop down menus to give your opinion on each of the issues - either yes, no, or undecided. Additionally, you need to decide which issues are very important to you and which ones you could care less about. Use the drop down menu to select very important, a little important, you're indifferent, or you don't care. The spreadsheet automatically uses a weighting system to score each candidate's response in relation to your opinion.

. Go to the scores sheet to see which candidate has the highest score and is therefore the most similar to you. The spreadsheet uses a "rank without ties" formula to list the candidates in order of highest to lowest score based on the weighting system.

Only 229 more days until the 2016 election! I'd love to hear what you think of this spreadsheet, if it helps you figure out your politics or not. Any suggestions to improve it?

Wednesday, March 16, 2016

March Madness 2016 Excel Brackets

It’s the most wonderful time of the year! March Madness 2016 edition is here and the first of the four “play-in” games have already begun (which we’re not counting off any points for in my office pool). Once again, I’ll be using David Tyler’s NCAA Excel brackets, the same ones I’ve been using since at least 2010, as they're still the best as far as I’m concerned. David doesn't lock or hide anything behind password protection so you can examine all the formulas to see how they work and make any modifications you desire. There are two files: the bracket manager and the individual bracket file, that can be downloaded by following the link below.

A great way to increase your Excel knowledge is to reverse engineer templates, like these office pool March Madness brackets. Last year, I shared 11 things you can learn from the best Excel brackets and they all still apply for this year’s version.

I'd like to give a special thanks to David Tyler for continuing to update and post his excellent brackets each and every March and giving us something to look forward to as we crawl out of the final few weeks of winter.

I also created a short screen capture video to show you just how easy it is to use David's brackets. Simply click on each team to advance them (don't forget to enable macros).

Pictured below is my bracket. You should probably just go ahead and treat it like the Bible, Koran, and Torah all rolled into one. It’s as good as truth.

Good luck in your office pools!


*just a special note to my email subscribers: I will hopefully have an option soon where you can opt out of notifications about sports templates, that is if you only want to read about my general Excel tips and tricks.

Tuesday, March 1, 2016

Isn't it time you learned Excel macros?

Excel is a great tool for working with all types of data. You can slice and dice that data in lots of preset ways using the tools and functions that the program makes available. You can develop your own formulas that mix Excel's rich set of functions to get just the results you need.

Note that I said that Excel allows you to work in lots of "preset" ways. For some people, using those preset ways is enough, but for others, Excel may be a bit restrictive in allowing them to work with their data in exactly the way they need.

That's where the power of macros comes into play. With macros you can extend what Excel can do, making it process your data, worksheets, and workbooks just the way you want. If you haven’t noticed already, I’m a huge fan of using macros and automating those repetitive processes.

If you know how. (That's always the kicker, right?)

You see, the whole idea of macros can be a bit intimidating to some people. It needn't be, particularly if you have the right instructor showing you how to create and use your own macros.

Fortunately, I've found the "right instructor." My friend, Allen Wyatt, has been publishing his ExcelTips newsletter since 1998. (Yes, for 18 years! I wish I had his kind of consistency) He's also been writing books about Excel and teaching others how to use it for even longer than that.

The cool thing is that he has put together what he's called the "Excel Macros for Beginners" course, and it's awesome! (and I would know because I’ve just gone through it myself) He's prepared a great overview to the course that you should definitely check out if you are new to the whole idea of creating your own macros.

Allen opens the doors to his course only a few times each year, and they are open right now. The great news is that, because he is my friend, you can sign up for the course at 20% off the regular price! (This early bird pricing is available for only a limited time.) When you register for the course, you get 24/7 access to all materials--and there's a boat load of them--for a full year. Plus, Allen personally provides support for the first 6 weeks of the course.

Macros are a great tool that can enhance what you do with Excel. Those who learn how to create and use them aren't limited to the built-in tools provided by the program. Allen's course can provide you with the valuable skills you need to finally master macros.

If you register for Allen's class by Thursday, March 10, it is available at 20% off. BUT THAT’S NOT ALL!!! (always wanted to say that). If you sign up I'll personally throw in my Ten Macros I Use All the Time as a bonus just for you, absolutely free. Just e-mail me your receipt, once you've signed up for the course, and I’ll send you your exclusive bonus material.

Do yourself a favor and check out the course. You've got nothing to lose and an entirely new skill set to gain. 

Since he started accepting registrations for the course, Allen has received a few questions, which he shared with me. I figured that I would share those questions (and Allen's answers) with you, just in case some of the same questions crossed your mind.

Question: Do you think I can learn how to create macros?

Allen's Answer: Yes, I do. In fact, I haven't run into a single Excel user who couldn't learn how to create macros with just a little bit of effort. It isn't magic, and it isn't a "black art." With the right instruction, you can be creating macros in no time! (And, I'd be remiss if I didn't mention that I think the Excel Macros for Beginners course is "the right instruction.")

Question: Is it hard to learn to create macros?

Allen's Answer: That's a tough one to answer. Everyone is different, and everyone comes to the table with different skills and aptitudes. As I said, I think that everyone can learn how to create and use macros, but it really all depends on your determination and drive. This is no different than acquiring any new skill; if you make the effort, put in the time, and do the work, you'll reap the benefits available through the course.

Question: When and how will I receive each lesson in the course?

Allen's Answer: Once you register for the course, all lessons and materials are immediately available. That way you can learn at your own speed, day or night, weekdays or weekends—it is up to you. Plus, registration entitles you to access all lesson materials for an entire year.

Question: What type of time commitment does the course require?

Allen's Answer: If you take a look at the web page I've prepared, you'll find that the video modules that make up the course are, in aggregate, about 11-1/2 hours long. Each module is anywhere from a few minutes to about 30 minutes in length, and I provide complete information (once registered) as to the length of each module. That way you can plan out what is best for your schedule as you discover how to create macros.

If you want to see a sneak peek inside of the course, you can watch me dig around some of the amazing content here: 

Oh, and in the spirit of full disclosure, you need to know that I do make a small commission if you decide to join Allen's course. I don't just recommend anything and everything; Allen's got the necessary professional "chops," and his course has the quality and value that make me happy to recommend it without hesitation. As I said before, I have taken the course myself and I would never recommend something to you that I don’t see value in.

Give it a try. Remember that you've only got until March 10 to take advantage of the early bird pricing, though. Don't miss out on this opportunity to expand your skillset. Do you want to learn how to create powerful macros you can use in Excel? Do you want to take your Excel skills to a new level? Do you want to finally figure out how to use all those cool Excel macros you see all over the place? Well, there is no better time than now.

OK, thanks for reading. Now back to our semi-regular Excel tips.

Sunday, February 14, 2016

2016 NASCAR Fantasy League Manager Spreadsheet

2016 is the third year in a row that I’ve made the NASCAR Fantasy League spreadsheet available for download. This NASCAR fantasy game is based on Total Driver Points according to NASCAR’S scoring system. In order for your drivers to score they must be running for Sprint Cup Points. Drivers may run in more than one NASCAR division but can only score points in one division.

How to Play NASCAR Fantasy in Excel

At the beginning of the year (first race is Daytona on February 21st) participants choose ten race car drivers. These will be their 10 drivers for the entire year - no changing after the entry deadline. All players also submit the three drivers that they think will place 1st, 2nd, and 3rd in the NASCAR standings at the end of the year. They don’t need to have these drivers in their picks, just who they think is going to finish on top. This is used as a tie-breaker.

The fantasy league manager manually enters each player’s picks into the spreadsheet at the beginning of the year (or uses the new entry form to copy and paste entries). Players keep the same drivers all year long. After each race, the manager then goes to and enters the amount of points that each driver earned during the race into that driver’s column. The spreadsheet then automatically adds and ranks each player according to their driver’s scores. I used many of the same formulas found in the leaderboard of my NCAA Bowl Prediction Pool sheet. This Nascar fantasy manager template can currently handle up to one hundred drivers and one hundred participants without needing to modify a single formula!

This year, there are two options to download the 2016 NASCAR Fantasy League manager:
Download Option 1 (no info require):

Simply click the link below which will take you to the, where the file is hosted. Next, click on the “Download” link in the upper right hand corner. That’s it! No email address is required.

Single Player Entry Form.xls

Download option 2 (email address required):

File is hosted on Gumroad. Click "I Want this" then submit your email address to download the spreadsheet. Your email address will ONLY be used to alert you if I upload a new version of the file, either an update for this year or you will also be notified when next year's sheet is ready to go.

Why two links? Sometimes users find small bugs or offer suggestions for improvement and so I update the spreadsheet. I recommend downloading through download link 2 so you will be automatically notified when an update is made, whether for this year’s spreadsheet or next. But if you don’t feel comfortable submitting your email address I understand, which is why download option 1 is available.

I’ve received comments from many readers informing me they like to use these types of sport manager templates to raise money for their favorite charities, which is so awesome! In the future. I will improve the spreadsheet by automating the entry process (I know, been saying that for two years now) and come up with a way to update the driver scores easier. This is just the third iteration of the template and I don’t follow NASCAR myself like I do football, so I’m sure it can be improved in some areas. Please let me know if you have any suggestions!

Tuesday, February 9, 2016

Excel Help Milestone: 1.5 Million Page Views!

In my 7th year of blogging, I’ve reached another monumental milestone. In April of 2014, I surpassed one million page views. Today, I am excited to announce Excel Spreadsheets Help has surpassed the 1.5 MILLION page view mark. Yes, I realize there are larger websites out there that may have 1.5 million views in a single day, but those are larger brands with an entire team or company of people. This Excel help blog is run by one person in his free time using Google’s free Blogspot blogging platform, so to me it’s quite the accomplishment. And it’s hard to wrap my mind around 1.5 million of anything. That’s like 15 Ohio Stadiums full of scarlet clad football fans. Wow!

Now, let’s take a look at some fun numbers and statistics from seven years of sharing Excel tips and templates:
  • Excel Spreadsheets Help Age: 7 years
  • Total number of posts published: 248 (249 after this one goes live)
  • Time to get to 1 million page views: 53 months (2009 - 2014)
  • Time to go from 1 million to 1.5 million views: 21.5 months (2014-2016)
  • Total time to hit 1.5 million: 74.5 months
  • Estimated timeframe to hit 2 million page views: November 2017
  • Average number of posts per month: 3
  • 1.5 million views / 74.5 months = 20,134 average pageviews per month
  • 1.5 million views / 248 posts = 6,048 average pageviews per post

The top five most popular posts since December of 2009 are listed below and interestingly, all top five posts were written in 2010:

Sunday, January 31, 2016

5 Lessons Learned from Super Bowl Squares 2016 to Improve Your Excel Skills

Last week I released the latest and greatest version of my Super Bowl squares game template with more ways to play than before. Even if you don’t care for football or sports, numerous lessons can be learned by dissecting this spreadsheet, including:
  • How to assign a macro to a command button
  • How to clear cells with a macro
  • How to generate random numbers in Excel with a macro
  • How to use a macro to create a list based on cell values
  • How to create a dynamic drop down list
I thought I would offer a few more details and insights into how each of these features work.

1. How to assign a macro to a command button

To insert a command button into your Excel spreadsheet you’ll want the Developer tab to be available. Go to File > Options> Customize Ribbon and under Main Tabs check Developer. Now at the top of Excel you should see the Developer tab. Click on the Insert button drop drown, then under form controls click Command Button. Place the button wherever you want along with the desired size. To have a macro run on the click of the button, right click the command button and select Assign macro. Pick your macro and you’re good to go!

2. How to Clear a Range with an Excel Macro

To clear a range with an Excel macro, use the code below. When you click the “Clear Numbers” button, it is linked to this macro and will clear the range.

Sub Clear_Random_Numbers()
'clear the random numbers
Dim DelRange As Range
Set DelRange = Range("B5:B14")
End Sub

Unlike a formula, if you insert a row or column and your target range moves, the macro will not update automatically like a formula will. Keep this in mind when making changes to a spreadsheet containing a macro. It’s a good idea to have the layout and format fixed as much as possible before writing your macro code.

3. How to generate random numbers in Excel with a macro

To generate random numbers in a range with a macro, modify the code below. This code generates random numbers between 0 and 9. To start from 1, take out the “-1”. To get more random numbers, change 10 to 100 or whatever value you need.

Sub Generate_Random_Numbers()
'define range of cells for NFC random numbers
Dim NFCRange As Range
Set NFCRange = Range("B5:B14")
For Each c In NFCRange
c.Value = (Int((10 * Rnd + 1))) - 1
Loop Until WorksheetFunction.CountIf(NFCRange, c.Value) < 2

4. Macro to Create a List Based on Cell Values

The next macro for the Super Bowl squares sheet is for the Assign Names Randomly sheet. This style of play allows users to enter their name then input how many squares they want. So we need to generate the complete list of names. The key to this program is getting the row numbers to add correctly.

Nick 3
Sarah 2
Henry 1

List would look like this:


How do we get the result? Use loops to repeat the code that adds the name to the list.

Sub Assign_Random_Number_For_Names()
'stop screen updating so macro runs faster
Application.ScreenUpdating = False
'clear the range
Dim DelRange As Range
Set DelRange = Range("AA2:AA101")

'generate a list of all the names in order to populate the field
Dim row As Integer
Dim NumberSquares As Integer
Dim Squares As Integer
Dim RowNum As Integer
Dim PlayerName As String

RowNum = 2

For row = 2 To 101

NumberSquares = Excel.Cells(row, 24)

For Squares = 1 To NumberSquares
PlayerName = Excel.Cells(row, 23)

Excel.Cells(RowNum, 27) = PlayerName

RowNum = RowNum + 1



5. How to create a dynamic drop down list

In this spreadsheet you may have also noticed I used a dynamic list – a drop down list where the values change based on other cell’s value. There are only one hundred squares in Super Bowl squares, so when each player picks how many squares they want, the total value can only add to one hundred. When making your selection it should only show how many squares remain, basically it needs to subtract from 100 the total value of squares already entered. This is done in column P using an IF and ROW formula. If the total value of squares taken is less than one hundred, then show the row number, if not, show nothing.

I hope by examining this template you’ve learned a thing or two about Excel.

Sunday, January 24, 2016

Super Bowl Squares 2016 Excel Template for Office Pools

Match-ups for the 50th Super Bowl are set which means it's time to download your Super Bowl Squares 2016 Excel template for office pools. Peyton Manning and the Denver Broncos will play the Cam Newton and the Carolina Panthers in Super Bowl 50 on Sunday, February 7th at 6:30pm. To make the big game even more fun start a football office pool using our printable Super Bowl spreadsheet. Fill in the squares, watch the game, laugh at the commercials, and see who wins and maybe make a little money at the same time, or help. If you’re a fan of a team that like mine (the Factory of Sadness) that will never make it to the championship (or playoffs for that matter), then playing Super Bowl Squares will make watching the game more exciting and gives you something to actually root for. Others may be torn between cheering for their favorite team and cheering for their squares.

This year’s Super Bowl Squares spreadsheet includes three different versions and you can choose the way you want to play:

1. Printable version - This print ready version contains a blank grid, simply print it off and write all the player’s names down on the piece of paper. It’s quick and easy!

2. Pick your own squares - Manage the office Super Bowl pool directly in Excel. In this version, each player gets to pick what squares they want. Once all are taken, press the randomize button to generate the random score numbers.  No modifications are necessary, though you can if you so desire.

3. Assign squares randomly - In this new version, type each player's name in the manager sheet and use the drop down menu to assign how many squares to give each player. Then click the Assign Names Randomly button and it will automatically populate the grid with all of the names.

super bowl spreadsheet

Super Bowl Squares Rules & How to Play

Listed below are the basic instructions on how to play Super Bowl Squares (which are also included within the spreadsheet for the three different versions, along with an example):

1. Participants “purchase” individuals squares by writing their name in their desired squares, until all one hundred are filled.
2. After all squares are taken, the numbers 0 through 9 are written in the empty horizontal and vertical rows in random order (draw the numbers out of a hat).
3. After the end of each quarter of the game, match the last digit of each team's score to the corresponding square to find the winner.

Tip: 0, 3, and 7 are the best numbers to get. 8, not so much.

Below is a short video tutorial I put together of how to use my Super Bowl Squares template in Excel

Download the Super Bowl spreadsheet for free, simply click the link below then click on the “Download” link in the upper right hand corner.That’s it!

Super Bowl Squares 2016 Excel Template for Office Pools.xlsm download

Or download here to subscribe and receive instant notification whenever the file is updated.

Please let me know in the comments or by email which version you'll use to play - printable, pick your squares, or randomly assign names.