Monday, September 8, 2014

Getting started with Microsoft Excel Macros

I’ve had many readers contact me explaining how they want to start creating macros for Excel to automate their work but they just don’t know where or how to begin. In order to help out I’ve created a short PowerPoint presentation called “Getting started with Microsoft Excel Macros.” I uploaded the ppt to SlideShare where you can download it for free and it’s embedded below.



This introduction to macros quickly covers topics such as why use macros, programming basics, how to create your first macro, the drawbacks of using the macro recorder, troubleshooting, and more.

I’d love to get your feedback on this. Would you like to see more PowerPoint presentations like this or not? Do you have any questions about getting started with Microsoft Excel macros?

Thursday, August 14, 2014

Quick Excel Tips: Dynamic Lookup

Today, I’m going to show you one of my favorite Excel tips: Dynamic Lookup or Dynamic Searching by using a combination of VLOOKUP and MATCH functions. Bascially, this function combo makes it so that the column that you pull the data from is dynamic based on the header making it more flexible than VLOOKUP by itself because you don’t have to rely on knowing the index column number.

In my example (that you can download below), in cell B2 I have this formula that combines VLOOKUP and MATCH:

=VLOOKUP($A2, $D$2:$G$14, MATCH($B$1,$D$1:$G$1,0),FALSE)

excel dynamic lookup formula


The column header in B matches one of the column headers in D, E, F, G - it doesn’t matter which one, you can change it and the values update automatically, which is the beauty of this formula. This makes it easy to add or remove columns without having to update your formulas. Not only is it more dynamic, the index column need not be on the left. Try it yourself!

Download my Dynamic Lookup formula example spreadsheet here.

Monday, August 4, 2014

Personal Finance Tracking Template

I think I've talked about posting this before (and never did) but after a reader’s request I've finally made my personal finance tracking spreadsheet available to download. Since becoming a new parent it’s even more important than ever for me to track my finances due to all the additional expenses, like doctor visits, buying diapers, more groceries, daycare (holy crap it’s expensive!!) and more. You don’t need to buy any expensive personal finance software, just use a simple Excel spreadsheet and I'll show you how.


The first sheet in the workbook is the Categories page where I list what I want to categorize each of my bills as: gas, electric, mortgage, cable, etc.


Then there is an Income page and an Expenses page with drop down lists driven by what is entered on the Categories page. Finally, a Totals page shows you how much you’re bringing in and how much you’re spending each month. I left a few rows filled in on the template so you can easily see how it works (data is made up and not real).




Pie charts are a great way to visualize your monthly expenses to see what you’re spending the most money on. Sometimes it's quite eye opening, like "wow, we spend a LOT of money eating out! Maybe we should start to make more home cooked meals."
 



I use my spreadsheet to see how much we’re spending on food, gas, entertainment, etc. and you can use it as a free budget planner tool too! You can set a montly goal on the Totals page and see if your spending stays below it.


Personal Finance Tracking Spreadsheet.xls download



This template uses:

  • Data Validation
  • Name Manager
  • Pie charts
  • Line Graphs
  • =Month()
  • =Average()
  • =Max()
  • =Min()
  • =Sumif()
 





As you can see on the example income page, I'm a big believer that in this day and age everyone should try to have more than one source of income, even if your secondary income is much smaller than your primary income stream.
 



This can be accomplished by anyone who has access to the internet, because there are multiple ways to make a little extra in your spare time: online survey taker, freelancer, writer, affiliate marketer, social media manager, graphic designer, Fiverr gigs, blogging, Amazon buyback program, etc. All it takes is a little extra effort.

Tuesday, July 8, 2014

2014 NFL Helmet Schedule

Last week I noticed the start of the 2014 college football season is only two months away, which means the 2014 NFL season is not far behind (and actually preseason games start in less than a month). I’ve updated the 2014 schedule spreadsheet that includes all NFL helmets from every team. It’s a fun way to look at your favorite team’s NFL schedules 2014.

2014 nfl helmet schedule

Even though basing a team's supposed “ease of schedule” on the previous year's record is a faulty premise, it's still fun to do and happens all the time. For instance,  the Indianapolis Colts have the easiest schedule based on 2013 winning percentages. Don’t be surprised if the Steelers are back on top of the AFC North this year due to what should be an easier schedule than last year. Statistically, the Raiders have the toughest schedule in the NFL this year, based on 2013 records.

Download the helmet schedule using the link below and let me know what you think about your team’s chances this year:


Yes, it takes quite a long time to assemble all the NFL helmet logos each and every season but its a fun way to look at the schedule and all NFL helmets at the same time. The NFL doesn't see as much change year to year as college football does but in the near future it looks like we could have a team in Toronto, LA, and London, as well as a new logo in Washington.

Wednesday, July 2, 2014

NCAA 2014 College Football Helmet Schedule

It's hard to believe but at the time of writing this post at the beginning of July the college football season kicks off in less than two months! Ohio State fans like myself can finally move on after those two devastating losses to end the season. This college football helmet schedule spreadsheet is not a lot of fun to update because there are so many helmets to move around and to make things more difficult the teams in the college football conferences are never the same, like with the introduction of Rutgers and Maryland to the Big Ten for example.


This 2014 college football helmet schedule in Excel includes every team from all ten conferences plus independents. Every game is listed as either home, away, or neutral site (denotated at the bottom of each sheet).  A college football helmet schedule spreadsheet may be available on other websites but, to my knowlegde, this is the only downloadable Excel version and unlike some of the others is 100% FREE! Download it today using the link below:

Download the 2014 NCAA College Football Helmet Schedule.xls

In the future I think I will look for a way to make the spreadsheet easier to update and more maintainable. At least maybe figure out how to make the football helmets automatically populate. Maybe there is also a way to import the schedules from ESPN's website. Any ideas how to make the yearly update go quicker?

Sunday, June 15, 2014

2014 World Cup Bracket in Excel

I’m a pretty big sports fan but I have to admit I’ve never really followed the World Cup before. I had many readers asking if I’ve ever made a World Cup bracket in Excel so this year I decided to do a little research to learn more about the world’s most popular sporting event. If you're an ignorant American like me you can use this template to learn more about the World Cup format. Here's what the bracket looks like, then I'll explain how it works:


2014 world cup bracket in excel


The FIFA World Cup tournament begins with 32 teams from six continents and takes place once every four years. The teams are initially placed into a group of four. The team groupings are shown below:

2014 fifa world cup team groupings


The host country, Brazil, has won five World Cups and is one of the favorites to win this year. After all group matches are finished, the top two team of each groups will advance to the round of sixteen. Here's the format of the remaining rounds:

world cup format


That's how it works! The spreadsheet also includes the FIFA World Cup schedule, TV stations you can catch the games on, and where each game is going to be played. Few are giving Team USA a chance to advance past that stage, let alone make a run toward the final.

World Cup time means it's time for that weird guy at work to really shine because he knows all the special rules. In the spreadsheet I even included a sheet on World Cup terminology:  it’s not a uniform, it’s a “kit”, they’re not shoes; they’re boots, it’s not soccer, it’s football! Now you won’t be clueless when watching the games in a bar throughout the next month.

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


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! 

excel 3d graphics engine

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. 

weight tracker in excel

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.

game of life in excel

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.

monopoly in excel

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.

excel mockup creator

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.

wedding planning spreadsheet

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.