Excel Ninja

2010 goals

noreply@blogger.com (Jayson) — Sat, 02 Jan 2010 05:02:00 +0000

New Year's resolutions have been finding their way around the interwebs, so I thought I should chime in with some of my own.

2010 is going to be an interesting year. I've officially backed off of my ExcelNinja duties at my day job to focus more on tax (what I was hired for). This will hopefully let me focus on where I want my career to ultimately go. But ultimately I also want to be able to have more control over what I am doing in Excel and plan to be more focused on what I do with Excel this coming year.

My main goal in 2010 is to get some code out in the open. I want to build and release 6 Excel products during the year. They might be add-ins/workbooks/games/etc. I don't know yet. But I want to get in the habit of pushing myself, and publicly releasing code into the wild and forcing myself to improve by realizing that anyone could be downloading.

That's one item every 2 months. So by February we'll see the first project completed. I've got a few ideas I've been toying with. I'll keep you posted.

Compatibility (Backwards or Otherwise)

noreply@blogger.com (Jayson) — Thu, 03 Dec 2009 06:58:00 +0000

I've been working on some widely distributed Excel projects at work. This type of scale is something I haven't had to deal with before. During development I worked hard to make sure the files were Excel 2003 and 2007 compatible. We made a conscious decision to not work towards being compatible towards other versions of Excel on Windows machines, or any Mac version, not to mention Linux or any other Excel alternatives. The biggest limiter was the need for macros in the file. Out of hundreds of users, we've had only a handful of compatibility issues.

The first and most frequent we have come across is users still on Excel 2000. Really it's only about 5 users, but it got me thinking. Excel 2000 hasn't been supported by Microsoft since 2005 (if memory serves). Two new versions have been released since. That got me thinking, what keeps organizations from upgrading? For a lot of the organizations we work with, it is cost. This particular file is used purely by non-profit orgs. In fact, I'm actually surprised that only a handful are still on Excel 2000.

We also had a couple of users on Excel 2008 for Mac. These users can't really be faulted much. Microsoft crippled this version and completely left out VBA. No support at all. I can't imagine what they were thinking on that one.

We've had one case of a Linux user opening the files in OpenOffice, but with no luck.

With all of this, I've come to the conclusion that with Excel files that contain macros you cannot cater to everyone. It's just not possible. The exceptions above cover most of the computing world, but there are also users on GoogleDocs, Zoho, and countless other office productivity suites. So, after much thought I've decided to only worry about complying with Excel 2003 and 2007 when I have to and sticking to Excel 2007 when possible - for macro files only.

The reality is that Microsoft is going to start feeling more of a hit on their market share in the coming years. Feature sets for all competitors will grow and compete with Office. And worst yet, there really aren't any standards for productivity software other than file type. And there definitely aren't any standards for the automation aspect.

I've drawn my line, and will continue to move that line as new versions of Office are released. It's a decision that needed to be made. But what next? What are you doing to provide compatibility?

This doesn't really make sense

noreply@blogger.com (Jayson) — Mon, 19 Oct 2009 13:00:00 +0000

Adobe Acrobat pro: $449

Microsoft Office pro: $499

Adobe

one product
does a few things, but nothing incredible
has no viable competitors.

Office

one suite of roughly 7-10 products (too lazy to look it up)
does a lot!
competitors are beginning to gain footholds and take on this giant.

It kills me that I may have to find someone that works at Adobe, and beg them to let me use their employee discount. It is such a great product (for what it does), but it is really expensive! And kind of a one trick pony.

I always thought that Office was priced way too high, but it seems that Microsoft's pricing looks incredibly smart compared to Adobe's. Am I wrong?

Excel Help Pranks and Page Breaks

noreply@blogger.com (Jayson) — Sat, 26 Sep 2009 13:00:00 +0000

Coworker calls and asks how to add a vertical page break in a sheet. He walks me through the process he is doing.

Me: "No, you have to press the button with your left pinky."

Coworker: "What? Why would that matter."

I've always wanted to do something like that, fortunately it was a lot funnier than me retelling it here. It always is...

Now before you get too nasty, I did help him. He was actually doing everything right. The problem: he set the scaling to fit in 1 page high by 1 page wide. This negates any page breaks you've set. In this case he changed the setting to 2 pages wide and then adjusted where the page break fell in page break preview mode.

Most of the time I find my sheets are longer than they are wide. When I don't know how many pages my printed file will be I will set it to 1 page wide, but delete the pages high entry. This will automatically set the printout to fit one page wide and however many sheets it needs to print.

Microsoft Dual Screen tablet

noreply@blogger.com (Jayson) — Wed, 23 Sep 2009 13:00:00 +0000

Microsoft is denying this, but there are mockups and video out there that made me drool a little.

Gizmodo states that this is in production and is being developed very secretly (well, until now) by J. Allard, Microsoft's entertainment wunderkind.

The video shows a user interface that has a lot of potential. It seems intuitive and easy to use. I'd give the video at the Gizmodo site a view, but keep a napkin handy.

Serious Formula Mastery

noreply@blogger.com (Jayson) — Tue, 18 Aug 2009 19:01:00 +0000

If you were to ask me what my strength in Excel would be I would say VBA. I do formulas well, but not great.

If you want to see true Formula magic head over to the Get Digital Help blog and partake in the mastery of the Formula.

Project Euler

noreply@blogger.com (Jayson) — Tue, 18 Aug 2009 03:55:00 +0000

I came across Project Euler about a year ago, but didn't have the time then to really get into it. After reading a blog post recently that talked about it I decided to take a look at some of the problems.

There are currently 252 problems available to work on. They have been developed by volunteers and site members to test logic and math reasoning. Many programmers use these problems as exercises. I signed up and gave the first problem a try.

In the spirit of the project I won't reveal any answers but I'll try to post here some things I learn along the way.

Why?

noreply@blogger.com (Jayson) — Mon, 03 Aug 2009 19:20:00 +0000

So, I’ve inserted a row in Excel under another row that has colored cells and data validation. Then I select to clear formatting. Why does it clear the formatting and not the data validation? I know data validation is not formatting (the ribbon tabs make that very clear), but why would I want to clear one and not the other? Why doesn’t it give an option to clear both? What if I wanted to clear validation but not formatting? No luck for me…

Knowledge taken for granted

noreply@blogger.com (Jayson) — Fri, 31 Jul 2009 16:42:00 +0000

***EDIT: After re-reading, this sounds like I'm mister-know-it-all. Not meant that way at all (although I've been told I can come accross that way). By no means do I know everything, in fact, there is still much to learn, and I would venture to say that overall I know very little. I just try and learn when I can. Normal reading can now be continued.***

I've accumulated quite a bit of Excel know-how over the last 6 years. I never thought much about it, and really still don't as there is always something to learn.

But, yesterday, as I was teaching an Excel class here in the office, I realized how much I take some of that knowledge for granted. I find it amazing how people can use a tool for years and not know some of the most basic things (like how to remove cell coloring, or borders - of course they know how to color or border, just not remove).

The class went well overall, but I ended up covering half of what I thought we were going to cover as some time was needed to explain things that I assumed people already knew. It was a good reality check for me.

Oh I love tecnology...

noreply@blogger.com (Jayson) — Thu, 30 Jul 2009 15:12:00 +0000

but it just makes you hot you see,

but I still love tecnology...

Yesterday I learned that no piece of technology I have at the office or at home keeps me cool. (We did finally install the window AC unit and are now confined to 2 rooms of the house, so that doesn't count)

Especially in the hottest day in recorded history.

Sorry, but it's hard to think of much else when the inside of your house is 88 degrees at 8:30 pm.

Bill Gates and I are the same

noreply@blogger.com (Jayson) — Wed, 29 Jul 2009 02:00:00 +0000

Today I learned that I use the same number of monitors as the man himself (although I wish at times I had 4!).

http://lifehacker.com/5324454/how-bill-gates-uses-office-and-three-monitors?skyline=true&s=i

However, we use them in very different ways. My time is spent in a creation state. Programming or preparing tax returns I'm creating products, so my monitors reflect this. Email is hidden and only checked at certain times of the day. Each monitor has it's purpose so that I don't have to switch between programs very often (although Vista's Win+Tab is pure eye candy!).

Bill is at this stage of his career, a email guy. He's managing ideas and resources. Email on one screen. New message on another. Third screen for browsing.

More on the maker/manager difference in an upcoming post.

via Lifehacker and The Simple Dollar

One thing I've learned...

noreply@blogger.com (Jayson) — Tue, 28 Jul 2009 02:00:00 +0000

I'm going to try something new out. Each day I'm going to put out there something I've learned during the day (or at least recently). The only rule: it has to be technology related.

Today, was relative uneventful, so this is something I came across a few weeks ago. Excel has a built in CONVERT function. It's nothing revolutionary, but much handier (and way more accurate) than my attempts at doing conversions on my own (i.e. even though it works for in-the-head-calculations, you cannot get miles by multiplying kilometers by 3/5).

Where is the formula? If you have Excel 2007 it is available right out of the box. For Excel 2003 you must enable the Analysis toolpak add-in to see it.

So, here is how it works.

The formula takes 3 inputs:

The number you want converted
The current unit of measurement (using code found in help file)
The desired unit of measurement (using code found in help file)

Here is are some of the conversions that can be done (with codes included in quotes - make sure to include the quotes).

Weight and mass

Gram "g"
Pound "lbm"
Ounces "ozm"

Distance

Meter "m"
Mile "mi"
Nautical mile "Nmi"
Inch "in"
Foot "ft"
Yard "yd"

Time

Year "yr"
Day "day"
Hour "hr"
Minute "mn"
Second "sec"

Temperature

Celsius "C"
Fahrenheit "F"
Kelvin "K"

Liquid

Teaspoon "tsp"
Tablespoon "tbs"
Fluid ounce "oz"
Cup "cup"
Pint "us_pt" or for the UK pint "uk_pt"*
Quart "qt"
Gallon "gal"
Liter "l"

And the list goes on.

even better you can get down to many levels of metric measurement by adding an additional code and an underscore (_) to a unit. For example, centimeter is "c_m".

As an accountant, most of these conversions I never have use of. But there is a certain amount of coolness in the ability to calculate nautical miles so I can attempt to speak with pilots or boaters. And, while this might go without saying, you will get an error if you try to convert something that just doesn't make sense, like the time of day in to degrees farenheit for example.

*I had no idea that there were UK pints before writing this. And it is no surprise to me that the pints on the other side of the pond are bigger than their US equivalents.

CPA exam ... I win!

noreply@blogger.com (Jayson) — Mon, 22 Jun 2009 14:56:00 +0000

It's been a long 2 years, but I have finally passed all the sections of the CPA exam. (big sigh of relief.) In the words of my daughter "When daddy pass his test, then he play with me!" Yes, E, I will play with you.

(double post warning - for those of you, my wife, who subscribe to both of my blogs you will see this twice)

Could Excel ever become obsolete?

noreply@blogger.com (Jayson) — Fri, 06 Feb 2009 20:00:00 +0000

I read this post over at Smurf on Spreadsheets about the possibility of OpenOffice.org ever taking a significant market share away from Excel. It's a compelling possibility. OOo is free and has most of the functionality of Excel (at least the functionalities that are commonly used). The reason I personally haven't used it much is that there is a lack of support for VBA, which I do a fair amount of work in. Although, as Simon gestures, a few releases from now OOo could be VBA compatible.

Given the economic times, it is not a stretch to think that a free piece of software could begin to dent Microsoft's market share.

But, I'm just not seeing it happen quite yet. I work for an accounting firm that does a lot of work for non-profit organizations. You'd think that of all people/entities looking to cut costs, non-profits would be jumping at the chance to use a free office suite. None of the clients that I work with use OOo. I would venture to bet that none of them have heard of it. I think that OOo has a vast reach in very tech savvy communities, but outside those rings it finds itself very easily pushed aside. A while back they even had advertisements in many of the buses around town, but I don't think they really got their point across. People just don't know about it.

Web browsing

noreply@blogger.com (Jayson) — Fri, 06 Feb 2009 04:36:00 +0000

For those who don't know, Google came out with a web browser called Chrome. I've been 100% Chrome for months now. One of the reasons I switched was that it was so simplistic (not to mention fast). There are no toolbars, addins, or extensions. Just plain ol' fashioned surfing the web. If one tab runs into problems, just close it and the rest of your tabs remain open and functioning. The only time I use anything else is at work where we have a SharePoint site and other utilities that require Internet Explorer.

So, when I heard that Chrome was going to start allowing extensions, I felt slightly betrayed. I know I don't have to use or install the extensions, but it still seems to take the simplicity of it all away.

What browsers are you using, and why?

Welcome and today's bug

noreply@blogger.com (Jayson) — Thu, 05 Feb 2009 01:07:00 +0000

Welcome! I have to say first that I didn't come up with Excel Ninja, but I like it, so it stays. :-) I started doing work in Excel and VBA almost by accident, and now, spend 1/3 of my time at work on Excel based solutions. I wouldn't say I'm an expert, or even that great at it. But I enjoy doing it and have learned quite a bit along the way. Hopefully I can share some of that knowledge here, as well as ponder other ponderables such as my first bug report... Enjoy!

===================================

I have to say that for the most part I really like Excel 2007, but it has some bizarre bugs and missing functionality. Bug of the day - can't unhide columns. I hid some columns, then went back to unhide them and no luck. No worksheet or workbook protection, just hidden columns. I select the entire sheet, then right click > unhide, nothing. I select the columns to the left and right, same thing. So, I turn to my friend Google (slightly ironic that one of Microsoft's biggest competitors offers the best help when things go wrong!).

I found this link that solved my problem: "Perhaps the column isn't actually hidden, but its width is very small. Try selecting columns to left and right and use Format > Column > Width, putting something sensible (say 8)."

I already knew that hiding a column is the equivalent of setting it's width to zero, but why not be able to unhide? Who knows, but it worked. I've got my columns back, but I am still quite baffled.

Am I way off base here?