Monthly Archives: July 2013

5:30am , Spain & Vlookup

Up at 5:30am today I was on a phone call with a small business in Spain (a six hour time difference).  They requested a price quote on automating some internal processes using Excel.  One item which came up was the usage of unique identifiers in functions like Vlookup. This function uses a “key” to match and pull values from different worksheets.  I explained to the potential client that using names as the key would not be ideal for this function as there maybe multiple John Smith’s or John Adams’.  A more thorough approach would be using an identifier like an id number.  The client proceeded to explain that in Spain names are very unique.  A name is a composite of first name, Mother’s last name, Father’s last name and in some cases Grandmother’s last name.  These combinations produced a truly unique name which could be used as a “key”.  In this country using a name as a key may pass the test.

You’ve heard the saying “when in Rome”, well “when in Spain.”

Spelling Helper In Excel

Grammar and spell checking is unfortunately the last thing people think about when using Microsoft Excel.  While this may be at the forefront with tools like Microsoft Word it is overlooked when using Excel.  This oversight is significant.  I can’t tell you how many spreadsheets I’ve received with “Totls”, “chrts” and the like.

Clicking the spell check tool available under Review tab is a simple but powerful approach to preserving the integrity of your document in the eyes of the reviewer.  Very often presentation is everything.

Business Cash Flow and Microsoft Excel

“A company that does not effectively manage its cash flow by balancing income and expenses on a day-to-day basis is poised for collapse.”  This quote by Timothy Hatten a business professors summarizes the importance of cash flow management for a Small Business.  Cash flow is the life blood of a business…it must be conserved and used wisely lest the organization perish.
In one of our recent Excel business workshops I discussed how this tool can be used to manage cash flow.   Excel is a great tool to begin to create a cash flow budget and monitor spending against that budget.  Cash flow must be a concern for startups and seasoned organizations alike.

Excel 2003 vs. 2007 Format

Many businesses have transitioned from Excel 2003 to Excel 2007 (and beyond).  The question then becomes what to do with those old Excel file formats?  Documents created in Excel 2003 are saved in an .xls format.  Documents created in Excel 2007 and 2010 are saved in a .xlsx format. This is no subtle difference.  Files saved as .xlsx cannot be opened in Excel 2003.  On the other side files saved as .xls can be opened in Excel 2007 but have limited functionality.  One solution to this problem is to convert old files into the latest Excel format.  This would require going to each file and saving it in the new format.  In many organizations this can be hundreds of files.  A quicker approach would be to use VBA (Visual Basic for Applications) a programming language to make these file changes in seconds.  Contact us today to learn more about this method.

It’s the Details

I drove down this street many times before.  This morning I decided to take my morning jog down that road.  A closure in the track I frequent inspired that change of events.  I noticed a few stores and shops I had not seen when speeding along in a car.  Speed is important but often you miss the details.  This is the same approach we take with our Excel workshops and related projects.  The details are key, if you speed you tend to miss a few things.