Excel Spreadsheets Help

Advanced Custom HLOOKUP Formula

2012-02-01T06:00:00.000-08:00

About a year ago I posted an explanation how to create an advanced custom vlookup formula. Recently, I had a reader ask me how to convert this custom code into an advanced custom hlookup formula. It's not as easy as changing all the columns to rows and vice versa as the offset function needs to also be applied. Here is the custom hlookup VBA code: Public Function HlookupNth(MyVal As Variant,

Printable 2012 Superbowl Squares Spreadsheet

2012-01-22T19:52:00.000-08:00

The NFL playoffs are over and the final two teams are set to go at it in the championship game: the New England Patriots versus the New York Giants in Superbowl XLVI (46). Want to make the big game more fun? Start an office pool using our printable Superbowl square spreadsheet. Fill in the squares then watch the game (and laugh at the commercials) and see who wins. It makes watching the game

An Interview with Bill Jelen, Mr. Excel

2012-01-16T07:30:00.000-08:00

Welcome to our first interview feature of 2012! Bill Jelen is known as Mr. Excel, a Microsoft Excel MVP, and the founder of, in my opinion, the best online source to answer your Excel spreadsheet questions - the MrExcel website. Bill is also an author of many fabulous Excel books such as Microsoft Excel 2010 In Depth. I’d like to thank Bill for taking the time out of his busy schedule to

2012 NFL Playoff Bracket Update

2012-01-15T11:31:00.000-08:00

My printable 2012 NFL playoff Excel spreadsheet bracket is now available to download and has been updated! I'd like to apologize for the delay in getting this online. Hopefully, the issue is resolved with the upload site and won't happen again. After the Superbowl competitors are determined look for my Superbowl squares spreadsheet to go online - the perfect fit for an office pool. Download

New Excel Spreadsheets Help Download Page

2012-01-03T08:00:00.000-08:00

For our first blog improvement for 2012, I’ve created a new page for the Excel Spreadsheets Help blog where I will be adding links to all the Excel files I’ve made available to download. You will be able to find all of our templates, tutorials, sport schedules, expense forms, project management files, etc. Now you won’t have to search through the whole site looking for one file. Simply go

Printable 2012 NFL Playoff Bracket

2012-01-02T09:03:00.000-08:00

After a crazy regular season that saw numerous passing records fall while a quarterback who could barely complete a pass created the most buzz, the 2012 NFL Playoffs are set to begin Saturday, January 7! I have created a printable playoff bracket complete with team logos. As a bonus, I've also included the complete 2011-2012 NFL Postseason Schedule within the bracket spreadsheet, including

2011-2012 NBA Schedule Spreadsheet Download

2011-12-27T23:11:00.000-08:00

I know this is a little bit late, I had wanted to get it out before the season started but things have been pretty crazy. Anyways, it's finally here, the full 2011 - 2012 NBA schedule. The spreadsheet includes the a complete list of all 66 games for each team. When the NBA playoffs roll around we'll have a new spreadsheet schedule for that too. This spreadsheet is very useful for 2011 NBA

Mechanical Engineer: Evolution of Computer Skills

2011-12-26T09:00:00.000-08:00

A friend sent me this graphic and I thought it was pretty funny, mainly because it's so true! Ironically, I never learned how to use Excel in school, or didn't even how to write a simple code. Excel is a great and powerful tool. I think you could group PowerPoint into that category too. Learn more about Matlab, CATIA, AutoCAD, Pro-E, and more over at my CAD Systems Help blog. I also find this

Basic Excel VBA Macro: Loop through all worksheets

2011-12-19T18:50:00.000-08:00

What is an Excel macro? If you perform a task repeatedly, you can take advantage of a macro to automate the task. A macro (or makro to some people) is a series of functions, written in a scripting language, that you group in a single command to perform the requested task automatically. You can record a macro but it is highly recommended that you learn VBA in order to write and use macros of your

Updated 2011 NCAA College Football bowl sheet

2011-12-13T16:51:00.000-08:00

With the help of David Tyler (creator of one of the best NCAA college basketball Excel brackets) I have made some modifications to my 2011 college football bowl sheet. · By using the sumproduct formula and conditional formatting we have replaced the VBA COLORFUNCTION, making the spreadsheet much more user friendly. No need to press crtl+alt+f9 to update the formulas! · I

2011-12 NCAA College Football Bowl Pool Spreadsheet Dowload

2011-12-05T17:35:00.001-08:00

Now that the2011-2012 NCAA college football bowl game match-ups have been announced, I was able to quickly put together a schedule in Excel spreadsheet form. I have made it available to download. Pictured below is the schedule of each and every bowl game as well as the time of the game and what TV channel it will be shown own. I have also created a simple college football bowl predictions

2011 NCAA College Football Bowl Schedule Announced - Spreadsheet Download Coming Soon!

2011-12-04T20:36:00.001-08:00

The 2011-2012 NCAA college football bowl game match-ups have been announced. I am currently working on a bowl prediction spreadsheet and schedule which you will be able to use for your betting pools at work or with friends and families. Hopefully, the football spreadsheet will be available for download within a day or two. Updates coming soon! Download the 2011 College Football Bowl Game

Holiday Gift Ideas for Microsoft Excel Users

2011-12-03T13:23:00.001-08:00

Shopping for a Microsoft Excel user or engineer? Here are a few gift ideas for you spreadsheet fans out there. Excel VBA Programming for Dummies. Now that you've mastered Excel basics, it's time to move to the next level—creating your own, customized Excel 2010 solutions using Visual Basic for Applications (VBA). The new edition of this non-threatening guide is your key to getting there.

Advanced Excel: Macro Examples

2011-11-28T17:51:00.001-08:00

Creating Excel spreadsheet macros is a great way to save time and reduce the possibility of human error by automating repetitive processes. Other reasons for learning how to write VBA macros include: standardization, improve efficiency, expand capabilities, and streamline tasks. Macros in Excel are created through two primary methods: the macro recorder or by writing custom code with the macro

Happy Thanksgiving Day Weekend!

2011-11-23T14:09:00.000-08:00

Having Thanksgiving Day weekend to all our Excel Spreadsheets Help readers! The holiday season is upon us! It's time to start thinking about what's on your shopping list. The easiest way to avoid all the inconveniences of holiday shopping lines and crowds is to order your gifts online. But what does this have to do with this blog? Well, why not help support this site and get your holiday

How to use the SUMIF formula in Excel

2011-11-14T15:20:00.001-08:00

SUMIF is a useful formula to know how to use when creating Microsoft Excel spreadsheet templates. An example I can give is from the world of engineering. Say you have to create a part list or a bill of material (BOM for an assembly, such as a snow blower. The snow blower has multiple parts with have standard parts, such as washers, bolts, nuts, etc,, attached to it. At the bottom of my BOM

How to Learn Excel VBA Syntax for Macros

2011-11-09T07:00:00.000-08:00

What do you do if you are trying to write an Excel macro for automatically centering the text in the spreadsheet columns but don't know the correct syntax for the code? This does not seem like obvious syntax naming, i.e. such as CenterJustify, etc. The primary technique for learning VBA syntax as it is implemented in any and all of the Office Suite of products, except Access (which does not "

Why doesn't center alignment macro work?

2011-11-07T06:00:00.000-08:00

Troubleshooting Excel macros I was recently working on an Excel spreadsheet macro to automatically center the text with a cell on the cells within a sheet. This is a VBScript macro executed from a CAD system and exports desired data to an Excel file. I used the following code: With Excel.Range("A"&"1", "F"&RwNum) .Font.Name = "Arial" .Font.Size = 9 .HorizontalAlignment = xlCenter .

Quick Tips: Drop Down List Shortcut

2011-11-03T16:48:00.000-07:00

Here's a quick keyboard shortcut tip to save a little bit of time and mouse clicks if you're dealing with many drop down lists in your Excel spreadsheet. Select the cell which contains the drop down list then hold down the alt key. While holding down alt hit the down arrow key and the drop down list appears. Release the alt button. Now you can use the arrow keys to navigate up and down the list.

How to delete a row based on condition?

2011-10-31T07:00:00.000-07:00

I created a macro to delete an entire row in an Excel spreadsheet based upon a specific given condition. I've seen a few others code for this same problem and I thought they overthought it and wrote a unnecessarily long code. Here is my quick and easy solution. Let's say I have a few cells in column A named "FINAL_BODY" where I want to delete the entire row. Sub Delete_FINALBODY_Rows()

Dilbert Comic Strip: How to do Formulas in Excel

2011-10-23T16:42:00.000-07:00

I saw these Dilbert strip today in the Sunday paper and thought I would share it as it mentions Microsoft Excel. (Click on the image to see the full strip)

VBA to Export Properties to Excel

2011-10-19T18:00:00.000-07:00

Excel is a powerful program and I often find myself using it in conjunction with other software programs. As a CAD engineer, I am often making parts lists in Micosoft Excel based upon 3D data files in CAD software, such as CATIA V5. Lucky for me CATIA has its own catscript VBA editor which allows you to write macros which can automatically export data from Catia to Excel, a huge timesaver!

How to view mpp files without Mircosoft Project

2011-10-10T19:15:00.000-07:00

This may not be an excel tip but it is in the Microsoft Office family of products. If you're searching for how to open a .mpp file without Microsoft Project you may have found yourself in a similar situation which I was in not too long ago. I needed to open a Microsoft Project file but I didn't have the software on my pc. I did what most people would probably do and browsed the internet for a

Make $100 from your apartment search

2011-09-26T18:15:00.000-07:00

For those using my apartment searchspreadsheet and shared living expenses spreadsheets be sure to tell your landlord when asked that you found their apartment through rent.com. Then, after you sign your lease you can go online to rent.com and they will verify that will the apartment's management. This can take up to three months but then you should receive a $100 Visa giftcard in the mail. It's