2018 Holiday Gift Guide for Microsoft Excel Users

The 2018 holiday season is officially upon us here in the United States which means it’s time for my annual gift giving guide. I used to panic every year whenever my spouse, parents, and siblings asked me what I wanted for Christmas. I needed to give them an idea otherwise I’d end up with an ugly sweater or some random gadget I would never use.

So to help alleviate some of my stress I started compiling my own holiday gift guide. It’s kind of like the big toy catalog you used to get as a kid, only this is for adults. I’ve made a list of items I think would be very useful or exciting for your fellow Excel users, sorted by different categories. Some of these items I already use on a daily basis and others are things that are on my own personal wish list. It's my biggest and best gift guide yet! Enjoy!

MY GO TO EXCEL BOOKS

Excel 2016 Bible - The complete guide to Excel 2016, from Mr. Spreadsheet himself! Whether you are just starting out or an Excel novice, the Excel 2016 Bible is your comprehensive, go-to guide for all your Excel 2016 needs. Whether you use Excel at work or at home, you will be guided through the powerful new features and capabilities by expert author and Excel Guru John Walkenbach to take full advantage of what the updated version offers. Learn to incorporate templates, implement formulas, create pivot tables, analyze data, and much more.

Excel 2016 Power Programming with VBA is fully updated to cover all the latest tools and tricks of Excel 2016. Encompassing an analysis of Excel application development and a complete introduction to Visual Basic for Applications (VBA), this comprehensive book presents all of the techniques you need to develop both large and small Excel applications. Over 800 pages of tips, tricks, and best practices shed light on key topics, such as the Excel interface, file formats, enhanced interactivity with other Office applications, and improved collaboration features.

If you’ve ever thought to yourself “there has to be a better way to do this,” while using Microsoft Excel, then know you're probably right. There probably is a better way to complete your tasks you just don't know what it is and you don't have time to read a boring, expensive, thousand page manual on how to use Excel. 76 Excel Tips to Increase Your Productivity and Efficiency is for you. No fluff, just Excel tips and tricks you can put to use right away.

OTHER BOOKS WORTH READING

Will It Fly? How to Test Your Next Business Idea So You Don’t Waste Your Time and Money by Pat Flynn. I’ve been following Pat’s blog and podcast for a number of years, and you might have seen some of his tips at work on my site. If you’re new to the online business world, this book is fantastic. Lots of practical steps to take to prove whether your idea has validity or not. Over 700 reviews and a five star rating, that's impressive!

The Martian by Andy Weir. If you only read one (fiction) book this year, The Martian has to be the one. I absolutely love this book (and it’s even better than the movie). As soon as I finished it the first time, I immediately re-read it – something I’ve never done before. It’s about an astronaut (with a great sense of humor) who gets left behind on a mission to Mars and has to figure out how to survive. If you’re interested in space exploration, problem solving, engineering, chemistry, botany, or disco + 70s TV shows, I highly recommend you read The Martian. Maybe the best book I’ve read in the past five years.

Another quick, shameless self-plug. Where are the most terrifying roller coasters found? Who designs them? Which park builds the craziest rides? Find out by reading my book 50 Groundbreaking Roller Coasters. Another reason for including this book on this list is to show you a real life usage of Excel. How's that? Because this is one of the books I wrote using an Excel spreadsheet!

Nowhere in the world is there a more bizarre theme park than Happy Fun Land. Nike Farmington’s twelve years of thrill-seeking and roller coaster riding has brought him to exotic locales like Perth, Australia, Kaatsheuvel, Netherlands, and Santa Claus, Indiana. He's marathoned a roller coaster for ten consecutive hours and conquered the world’s tallest and fastest. Yet nothing has prepared him for the insanity of Happy Fun Land and it’s mind blowing attractions: a drop ride with no brakes and a death simulator, just to name a few. Will Nike survive his hilarious adventure through the world's craziest theme park? I thought this book was hilarious and I think you will too!

Tools for the Job

Excel Quick Reference Sheets - Laminated quick reference showing step-by-step instructions and shortcuts for how to use Microsoft Office Excel 2016 (Windows Version). Written with Beezix's trademark focus on clarity, accuracy, and the user's perspective, this guide will be a valuable resource to improve your proficiency in using Microsoft Excel 2016. This guide is suitable as a training handout, or simply an easy to use reference guide, for any type of user.

TechSmith Snagit takes the hassle out of creating images and videos. Capture your screen, edit images, and deliver results. Snagit is also the only screen capture tool with built-in advanced image editing and screen recording. So you can easily create high-quality images and videos all in one program. Quickly explain a process, build visual-based documentation and be more engaging by adding images and videos to your communications. It's the tool I use to create all the images for Excel Spreadsheets Help and well worth the price.

Microsoft Surface Tablet. Need to use Excel on the go put don't want to lug around a larger laptop? A Surface tablet is great way to go.

Dimmable Eye-care LED Desk Lamp. A great lamp and exactly what I was looking for in a new clip on lamp for my drafting desk. It has six different light settings so I can find the right lighting for all of my needs. It is well built, works great besides being stylish.Besides using at work can use at home as a reading lamp too.

Keyboard Case for Tablets. How do I get so much done, especially when I’m traveling on the road a lot? I use a combination of a Samsung Galaxy Tablet and my new Keyboard case. They’re small so I can take it almost anywhere and the keyboard allows me to do things like type out this blog post, reply to your email questions, and write Excel macro code.

USB Heated Mouse / Hand Warmer. I'm not sure about you but the office at my day job can get really cold during the winter, especially after weekends or holidays. One solution I've found that helps is a heated mouse to keep you hand warm while not impacting my ability to get things done.

Laptop Privacy Screen Protector. Whenever I visit a customer I always take my privacy screen protector for my laptop. It keeps your personal or confidential information safe from prying eyes as you’ll see the information on your display while people on either side only see a darkened screen. If you’re ever on an airplane or in a coffee shop and feel like your neighbor is constantly looking over your shoulder at your screen then you need to get one of these today!

A good quality laser point. This laser pointer always comes in handy when it’s time for a meeting or presentation. Plus, it doubles as a toy to keep your cats entertained.

Rocketbook Smart Erasable, Reusable Wirebound Notebook with Penstation – the last notebook you’ll ever need!

Krieger Plug Adapters (Most of Europe (type C)) - If you're going to do some international travel I highly recommend taking this adapters with you. They come in a pack of four so you can share when your travel buddy forgets his.

Toys, Tech, Gadgets, and Others

Handheld Gimbal Stabilizer for Smartphone – Outside of this website, in my spare time I like to make and edit videos and montages (mostly of my family). It’s easiest just to use a cellphone but the video is often shaky due to my unsteady hands. So I decided to get a gimbal and I’ve had fun playing around with it.

Anker PowerCore Fusion - This is a portable power charger that plugs directly into the wall so it works as your regular charger, but is also a battery so you always have power on the go.

In my little free time from working and being a dad, I like to play around with my Samsung Gear VR headset. Virtual Reality is really taking off and the technology is getting much better. Though be warned it may cause motion sickness if the app you’re looking at doesn’t perfectly track your head movement.

Solar Powered Christmas Lights. I love putting up Christmas lights and trying to out-do my neighbors, but I have to admit I feel a little guilty about using the additional electricity. Luckily I found these solar powered Christmas lights and they actually work very well! They’re environmentally friendly and they automatically turn on and off each night. They also don’t have to be attached to a power source so I can put them in areas I normally couldn’t string lights.

Dash and Dot – programmable robots. If you’re into programming things like Excel macros and you want to teach your kids the joys of programming then I’ve found the perfect gift for you. These cute robots are designed to help introduce children to the wonderful world of programming. Oh, and they’re fun for adults too! Can’t wait to use this with my son in a few years. For now he just likes watching me drive it around with my phone.

Fitbit Alta. Let’s face it – us engineers nowadays sit in front of a computer a lot. I didn’t realize how much I wasn’t moving until I started wearing a Fitbit. It now helps me to stay motivated by tracking all-day activity like steps, distance, calories burned and active minutes so I can stay healthy for my family. When I’m sick I can’t answer your Excel questions and help you out, so I use Alta to help stay in shape and on top of my game!

Amazon Prime Membership. If you haven’t joined Amazon Prime yet, why not? I do almost all my shopping online and I get free two-day shipping on nearly everything. You can also borrow books, watch movies, and stream music. Get your Prime Discounted Monthly Offering here.

YOUR SUGGESTIONS?

Are you putting any of these items on your holiday wish list? If so, let me know which ones in the comments below. Do have anything you’d like to recommend to me?

20 Unique Uses of Microsoft Excel Spreadsheets

Microsoft Excel is one of the most versatile and user friendly programs around. It’s the Swiss Army Knife of computer software. Even though Excel was originally designed for use in the business world, helping people easily manage data of all forms, some Excel jocks also use it for a variety of other tasks, many you might think are quite unusual. From designing roller coaster, to playing video games, to organization, Excel can do it all. Listed below are some of the most unique uses of Excel that you might not have thought of.

1. Role Playing Video Game

I've seen some very unique Excel uses in the past but this just might be the best (or most fun) application: Excel as a video game! Cary Walkin, a Canadian accountant, has transformed a Microsoft Excel spreadsheet into a playable video game. Arena.xlsm is a turn-based fantasy role playing game where the goal is to collect loot to become more powerful all the while warding off increasingly difficult monsters. In fact, there are currently over 2000 possible enemies with different AI abilities. Sounds complex! Remarkably, it only took Cary four months to completely program this game using VBA macros.

Source: http://excelspreadsheetshelp.blogspot.com/2013/04/unique-excel-uses-video-game.html

2. 3D Graphics Engine

Some people take Excel programming to the extreme. One such example is using Microsoft Excel as a 3D graphics engine. The program doesn't exploit any hidden code whatsoever – the feat was done using completely documented features installed as standard on a default Excel build. It’s really quite incredible! 

Source: http://excelspreadsheetshelp.blogspot.com/2011/04/excel-as-3d-graphics-game-engine.html

3. Weight Tracker

Want to manage your weight loss goals easily? Excel is a great way to begin doing that. Not only can you keep a log of your daily weight fluctuations, but you can easily build a chart that captures all this data and gives you an idea of the direction that you’re headed in. If you want to get even more specific, you can easily add a goal weight (say 20lbs below your current weight in six months) and create a trend line between the two dates. This way you know that if you are below the line, then you are on track to reach your goal. 

Source: http://office.microsoft.com/en-us/templates/measurements-weight-tracker-TC001168403.aspx 

4. Simulate Life 

Conway’s game of life is one of the first programming projects that most people try, as it’s a great way to learn how to design a simulation from the ground-up. To start, you set up a few blocks, and then run a simulation to determine whether those blocks, live, die, or reproduce, according to the following rules:

1. Any live cell with fewer than two neighbors will die of loneliness 
2. Any live cell with two or three neighbors will survive 
3. Any live cell with more than three neighbors dies, because it’s too crowded 
4. Any empty cell with exactly three live neighbors will become a live cell  (Source: Wikipedia)

With some intermediate knowledge of Visual Basic, you can easily make this game in Excel, and many people have done so before. If you search online, you can find thousands of sample setups that will do a number of interesting things when you run the simulation.

Source: http://dailydoseofexcel.com/archives/2011/04/06/conways-game-of-life-simulation-in-excel/ 

 5. Digital Flash Cards for Studying 

 Are you trying to learn a subject? Creating flash cards is one way that people have been learning for generations, but what if you could make it easier? By creating a ‘bank’ of questions on one worksheet, you can instruct Excel to quiz you by using Visual Basic to randomly look up one of the questions and display it in a “Question:” cell. If you want to get more advanced, you can build a button that will run the macro when it is clicked, and have another button to display the answer.


Source: http://www.youtube.com/watch?v=WhmplmELrfs

 6. Play Monopoly 

Moving even further into the Excel gaming realm, you could even build board games like Monopoly into Excel. This is easier than you think, since Excel’s grid structure can be modified to make the Monopoly board easily, and it has even been done online. Even though it might not replace the real thing, programming Excel to play Monopoly will help you learn how to really build a game in Visual Basic, and you can use that experience to help build things that are more useful in everyday life.

Source: https://www.dropbox.com/s/rps9m00rlfkbavf/Monopoly_v5.xlsm

 7. Create Mockups 

 Are you building a website, designing a store, or building a home? If so, then you can easily use Excel to build a mock-up of what you want it to look like. This is incredibly easy to do by highlighting cells and either using fill colors or borders to designate certain shapes and edges, so that you can get an idea of what the finished product will look like. If you are planning on working with a designer to build a website, you can easily snap a picture of the image and email it to him, or just send him the Excel document and let him get to work.

Source: http://chandoo.org/wp/2010/06/23/ui-prototyping-in-excel/

 8. Plan A Wedding 

Worried that your mother-in-law is going to be sitting next to her least favorite cousin? Want to make sure that your guest list includes everyone? This won’t be a problem if you plan your wedding in Excel. Use it to keep track of costs, manage guest lists, and even create a seating chart using some of the mockup ideas that we discussed above.

Source: http://www.myspreadsheetlab.com/2013/05/free-excel-template-wedding-planner/

 9. eBook Publishing 

Most authors begin writing their novels in a word processor like Microsoft Excel but if you’re writing non-fiction and going to have the same style and format of pages over and over you can begin writing your book inside Excel. When your data is ready, use a macro to export each row of information in your Excel to individual pages in Word. Add the front matter and you've got a hundred page book that just needs editing and a cover page.

 Source: http://excelspreadsheetshelp.blogspot.com/2014/04/unique-excel-uses-writing-book.html

 10. Graphic Design 

Did you know that Excel could be used to make incredible paintings? There is an entire group of Excel users that does nothing else but designing incredible pictures in Excel. A quick visit to YouTube will even let you see some of these masters in action. If you’ve been thinking about a creative outlet for all of your Excel knowledge, then painting might just be in the cards (or boxes in this case). All you need to start painting in Excel is the program itself. By filling in the boxes with different colors (make sure to resize them so that they’re square first), you can quickly begin drawing in a way that you might never have expected when first opening up the program.


Source: http://www.youtube.com/watch?v=4YG_WWZYqUs

11. Scrabble Scoring System

Not interested in wasting time adding up scores during your heated Scrabble game? Build a scoring chart in Excel and you can quickly SUM() all of the scores from each round to keep a running total of who’s in first and who needs to work on their vocabulary.

Source: http://www.calgary374.org/scoresheets/s03/sheet023.xls

12. Wine Inventory 

If you’re a wine connoisseur, then knowing the bottles of wine in your cellar, and tracking your wine ratings is incredibly important to you. Excel can not only help you create a quick database of wine listings, but it can also tell you just how many bottles of wine are left in your collection. You can even add tags to your database to separate the bottles that are for daily drinking and the ones that are for special occasions, so that you know when it’s time to head down to the local store and restock your collection.

Source: http://www.wine-lovers-page.com/rosswine.shtml

 13. Website Creation 

Excel can be used to help you create a website. Use a macro to convert a row or column of data into html code exported to a .txt or .doc file. If you’re a Wordpress user copy and paste the html into a new page and publish. This is how I created the Observation Wheel Directory to track the world’s largest Ferris wheels. If you want to start your own website, download our website creation checklist for free.

Source: http://excelspreadsheetshelp.blogspot.com/2013/03/macro-to-export-hyperlinks-from-excel.html

14. Energy Tracking 

If you work in IT, you’ll know that energy costs can really add up. Tracking the number of computers that are on at any given time in Excel will allow you to fully understand your electricity costs and work to reduce them over time by using scheduled shutdowns. While this might not be for someone with only one or two computers, the money that’s saved by turning off thousands of computers for an extra hour every day will pay dividends for years to come. If you are more interested in tracking your home energy use, you can use things like Kill-a-watt meters to calculate the watts being used to see where you are spending the most money. Shutting some of those ‘energy hogs’ down or unplugging them completely can save you hundreds of dollars a year.

Source: http://valueofsimple.com/spreadsheet-spotlight-intro-home-energy-audit-and-simple-tracking/

15. Play Pac Man 

That's right, someone actually sat down and made Pac-man run in Excel (called Pacelman). All actions are created by changing the fill color of each cell in the spreadsheet.

Source: http://www.geocities.jp/nchikada/pac/

16. Design a Roller Coaster 

Here’s a very thrilling example of what Excel can be 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! I've heard real roller coaster designers like the Gravity Group use Excel too.

Source: http://excelspreadsheetshelp.blogspot.com/2012/09/unique-excel-uses-designing-roller.html

17. Apartment Comparison 

Apartment Comparison is another one of my custom spreadsheet creations to help solve a recurring problem. I had to move 16 times over a five year period due to moving back and forth between a semester of school and a semester of co-op. It got tiring always trying to figure out what apartment was the best one for me, so I developed this template to help me out.

 Source: http://www.apartmenthunterhq.com

18. Horseshoe League

I've played horseshoes on occasion for fun but I never knew there was a horsehoe league complete with scoring and standings until a reader asked for help with a template to track everything. It's probably the most unique sports related template out of all the ones I've worked on or created. Horseshoes can be an individual or team sport, similar to bowling.

Source: http://excelspreadsheetshelp.blogspot.com/2012/05/sports-league-excel-spreadsheet.html

19. Newborn Checklist for New Parents 

Excel is great for keeping To-Do lists organized. One unique one I personally created was the newborn checklist for new parents. With a new baby on the way last year, there were so many things to do before the big day and it was hard keeping everything organized. The checklist I created in Excel enabled me to track what was done and if it was assigned to my wife or my responsibility (that way she couldn't yell at me if it didn't get done because it was assigned to her).

Source: http://excelspreadsheetshelp.blogspot.com/2013/10/newborn-checklist-for-new-parents.html

20. Pilot Training

Some pilots have starting using Excel to provide training for pilots around the world on planes ranging from regional jets to Boeing 747s. Diagrams of all the switches and indicators are mocked up in an Excel worksheet. The clever spreadsheets utilize comment boxes, which within cells provide pop-up details of what the lights and indicators do and what their different states mean. It's a simple learning aid that can run on just about any computer and requires only an Excel viewer application to use. Amazing!

Source: http://www.redtriangle.com/

Conclusion 

Even though you might spend most of your time using Excel to make lists, crunch numbers, and accomplish general tasks, that doesn't mean that there aren't other uses for this incredible program. Try out some of the ideas above and take your Excel skills to the next level! I hope this post has inspired you to create an awesome spreadsheet. If you have a unique or unusual use of Excel I would love to hear about it. 

Interview with an Excel Engineer

Reddit recently featured an interview with an Excel engineer who worked on Excel 2010 and Excel 2013. He answered numerous questions about everything ranging from tips and tricks to how to get a job working for Microsoft on Excel. The post is quite lengthy and there are many unanswered questions to sort through so for your convenience I’ve transcribed some of the most interesting and and informative Excel questions and answers here:

What would you say is the most under-utilized aspect of Excel?

The most underutilized feature IMHO is the Pivottable - the power it gives you is limitless and when people figure out how to use them it blows their mind. Traditional pivottables could only be created on a single table - with the new data model feature in Excel 2013 this limitation is gone and you can now join data from multiple tables/sources.

How does one become a data visualization MVP anyway?

Usually its awarded after years of experience, blogs, books written on the subject, etc.. These guys help us a lot when it comes to planning.

What is your background? How did you get the job?

Software engineer specializes in business intelligence and financial engineering. Got recruited out of school. There is all sorts of backgrounds though from ex flash game designers to mathematics phds to a guy who graduated from vet school. There are four disciplines that work directly on the Excel teams: developers, testers, program managers, and designers. There is also a ton of supporting staff like technical writers, support engineers, etc.. Each discipline had its own requirements. We're always hiring and you can specifically look inside the Microsoft Office Division.

How much do you study actual users and who are they?

A lot. We visit customers small and large shadowing them for days understanding their workflows. We conduct usability studies where we test out new features, designs, etc.. We comb through those crash reports (those popup dialogs that ask you to send info to MSFT) all the time to figure out what problems people are hitting. That said with 250+ million active users we always have to generalize. My first boss once told me, "When developing Excel any decision you make will probably piss off at least a million people, just make sure the rest are happy".

What are some lesser known tips shortcuts or tricks of Excel? What advice would you give to someone wanting to learn more?

• When inside a range of data Ctrl + A selects it all, Ctrl +T creates a table. Learn more keyboard shortcuts here.
• Macro recording to learn VBA is probably the trick that I have gotten the most out of. Or take an online class.
• Read this book: Excel 2010 Bible by John Wallenbach

To read through the entire Q&A session visit this link.

Unique Excel Uses: Video Game

We’ve seen some very unique Excel uses in the past but this just might be the best (or most fun) application: Excel as a video game! Cary Walkin, a Canadian accountant, has transformed a Microsoft Excel spreadsheet into a playable video game. Arena.xlsm is a turn-based fantasy role playing game where the goal is to collect loot to become more powerful all the while warding off increasingly difficult  monsters. In fact, there are currently over 2000 possible enemies with different AI abilities. Sounds complex! Remarkably, it only took Cary four months to completely program this game.

Cary has received a lot or press and attention since creating his Excel game. Recently, he was featured on one of Reddit’s “I Am A” features. A few quotes from Cary:

“I'm an accountant by profession, I use excel every day of my life. I simply worked with what I knew. Also there is an old adage that underneath every RPG is a massive spreadsheet, so now the massive spreadsheet IS the RPG!”

“...a number of calculations are dependent on the background colour of a cell (such as if you are standing on fire).”

“Never stop learning.”

Read the entire interview feature here. Additionally, to see an example of how one would even begin to go about coding this massive gaming macros see Cary’s great VBA tutorial here.

Visit Cary’s site and download Arena XLSM today and try it out for yourself! But be warned, your productivity may decrease greatly after playing a few levels.  Please note you must have macros enabled in order to play the game. Arena.Xlsm will only work in Excel 2007, 2010 and 2013. It will not work in other spreadsheet programs either. Have fun!

-Nick

Of course I’m not playing this at work ;)

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.

How do you create hatching in Excel?

The pattern fill is a great tool to create hatching in Excel 2010 (hatching is when you add fine lines to graphics to represent shading or other factors). However, for some reason the option of filling a series with a pattern was taken out of the user interface in Excel 2007.  The good news is the Excel pattern fill was not taken out of the 2007 object model so you can use VBA to apply a pattern to a series. Andy Pope has done just that by creating an add-on to return the tools for hatching in Excel 2007. We recently had the opportunity to talk to Andy about creating the Excel fill tool. Thanks to Andy for taking the time to answer a few of our questions.

ESH: How long have you been using Microsoft Excel?
A: I started using Excel 5.0 around 1998.

ESH: Could you please explain why the hatching/pattern fill feature can be found in Excel 2010 but was left out of 2007?
A: That is really a question for the people at Microsoft. For Excel 2007 the functionality was there, for backward compatibility, but they did not include any UI for users to employ it. This was either an over-site or a deliberate move to depreciate the feature. The negative feedback to the removal of this feature made Microsoft reinstate the UI for Office 2010.

Excel 2007

ESH: What is an Excel add-on? Why are they needed?
A: Add-ins are hidden workbooks that contain code to extended the functionality of Excel. Normally the functionality they provide is not specific to any one workbook or data set. As with the Pattern Fill add-in it provides the functionality to fill any shape or chart element.

ESH: Could you describe the process of creating the fill add-on? What were the steps involved?
A: First identify a problem or task that can be made easier and or quicker by using VBA code. Create the code required to perform the task. You need to bear in mind the following,

• References should be to the active workbook
• You need to provide UI elements so the user can interact with your code
• You need to handle errors that your code may encounter as you cannot
• control how the user will attempt to use your add-in

Excel 2010

ESH: Great information! Thanks again to Andy for taking the time to answer our questions and for creating such a useful plugin. To download the pattern fill plugin visit Andy’s page here.

2012 NCAA Football Helmet Schedule

Doug Woodworth is the creator of the 2012 NCAA college football helmet schedule spreadsheet. I recently had the opportunity to ask him about the origins of the helmet schedule and how he creates this wonderful college football spreadsheet every fall season. Thanks to Doug for taking the time to fill us in on the details!

I have been creating football helmet schedule spreadsheets for MGHelmets.com since the 2007 season. I created only the FBS schedule in 2007 and asked MG for permission to share it with my friends. MG happened to like the schedule and asked for my permission to post it on his website. The following year I added the FCS, and the NFL. In subsequent years I have created numerous special requests to include high school, NCAA Div. II, Div. III, and NAIA conferences, plus the annual bowl games schedule.

As I became more familiar with Excel, I tried to add a new feature or detail annually.

In 2007 I locked the schedule with a password because I did not want users to accidentally move or delete the helmets or text, like I had done many times while creating it. I was informed that many users wished to edit or color-code the schedules to keep track of wins-losses and other statistics, which could not be done if the schedules were locked. The overall design was unimpressive as there was very little color and I had chosen Arial font throughout.

In 2008 I unlocked the schedules and added a 'Place in This Document' hyperlink to the helmets, which allowed users to click their way through the different conferences or sheets. Adding the hyperlinks somewhat locked the helmets to the cells as they could only be moved with a right-click. ScreenTips were added to the helmets to display conference affiliation. The header was changed to display MG's official header. I added some color to the dates and conference/division rows, which gave a better look. Non-formal game indicators were changed to a letter code rather than the MM/DD format, which created less clutter at the top of the cells.

In 2009 the helmets were hyperlinked using 'Existing File or Web Page' which automatically opens the file or workbook of the selected helmet. With many FBS vs. FCS games being played regularly, this feature made it easy for users to click back and forth between the two schedules. This feature was especially helpful when I created schedules for every team of the Ohio High School Athletic Association. 722 teams were divided among 70 conferences, which were divided among 6 separate regions or files, yet they were all interconnected. To accomplish this feature, the hyperlink address must be typed to include the file name, followed by #, followed by the sheet name, followed by !, followed by the cell reference.

For example: example.xls#example_sheet!A4

In 2010 MG updated his helmet template, which displayed very sharp in the schedules. I added new or changed color to several details. The font was changed to Calibri. Comments were added to cells to easily hover and display the venue and location of neutral-site games. I realized that by dragging the helmets into the sheet rather than copying and pasting, the helmet backgrounds showed transparent rather than a white box. When users color-coded their printable football schedules, the entire cell would now be colored. I was finally pleased with the overall design, information, and color scheme.

The design has remained the same since 2010. My method for creating the football schedules has also remained the same.

1. drag the helmets from MGhelmets.com into the home cells in column A for each conference
2. select all helmets and resize
3. select all helmets and center within the home cells
4. type the hyperlink address for the first helmet
5. copy the hyperlink address from the first helmet minus its cell reference
6. paste the hyperlink address to the corresponding helmets adding the correct cell reference
7. type the team name into the home cell after horizontal and vertical alignment are set properly

With the helmets and text in place I begin to copy and paste until the schedules are complete, referencing several websites along the way. I then go back to notate the non-formal and neutral-site games. I also edit the ScreenTip of the helmets in the home cells to display the team nicknames. The 'Freeze Panes' feature is utilized so that the header, dates, and conference/division rows remain at the top when scrolling down.

The schedules have become rather popular. In fact, shortly after the BCS National Championship Game and Super Bowl, I receive many emails from fans asking when the schedules will be complete for the next season. The schedules are usually released in early summer.

I would like to thank the many fans who email with their kind comments and suggestions. I would also like thank MG for allowing me to display my work and creating these awesome helmets. Without him, these schedules would not exist.

Thanks again to Doug for the very informative description. As I said earlier, this is probably the best NCAA football spreadsheet out there and is very helpful for my office football pools. About the only thing it is missing is the NCAA football television schedule.Maybe an addition for next year?

To download the NCAA 2012 helmet schedule spreadsheet visit MGHelmets. Then visit our downloads page for more sports Excel templates. 

You can also download the 2012 NFL helmet schedule here. Which teams are you cheering for?

-Nick
Let's Go Buckeyes! 

Tags: 2012 ncaa football schedules, ncaa football 2012, football schedules ncaa, ncaa football scores ncaa football scores, ncaa football 12 spreadsheet

If you found this blog to be useful at all please use the share buttons below to help spread the word...