Monday, May 19, 2014

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.

unique excel use video game


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! 

excel 3d graphics engine


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. 

weight tracker in excel


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.

game of life 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.


 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.

monopoly in excel


 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.

excel mockup creator


 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.

wedding planning spreadsheet


 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.


 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.


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.


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.


 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.


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.


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.


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.


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.


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.


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


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!



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. 

Wednesday, May 7, 2014

Quick Excel Tips: How to align charts

In this edition of Quick Excel Tips with Nick, I'm going to show you how to align two or more charts. Dealing with two or more charts on a worksheet can make aligning them and matching their size a bit tricky. An easy way to get it done is by following these steps:

  1.  Select the first chart by clicking on it.
  2. Next, hold down the CTRL key and click on every other chart you want to align with each other. 
  3. When they are all selected, right-click on any one of them and choose “Size and properties”. It will open the “Format Shape” dialog box, where you have to enter the measurements which will apply to all of the selected charts. 
  4. When you have that confirmed with “Ok” go to the “Drawing Tools” tab and click “Format”. By using the “Align” drop-down menu you can align your charts and distribute them evenly the way you want (horizontally or vertically).   

That's how you can quickly and easily align your charts in Excel! I love making my spreadsheets look all nice and neat and tidy and this is a great way to help with that. Do you find this tip useful?