I've been doing contract work in process improvement/automation with Microsoft Excel (and the rest of the office suite although not as much) on and off for the last 5 years.

Some random thoughts:

i would estimate most people use <1% of excels true functionality, even I probably only use 40-50% there's just so many amazing things it can do. I find that VBa is one of those programming languages that is so easy to read and write and produces amazing results with minimal work

Programming, while allowing me to work in my boxer shorts in one of the greatest cities in the world, is not something that i could see myself doing for the rest of my life. I'd like to hear thoughts from others here who are further down the road than me. Ideally i'd probably like to move more into the consulting side and let someone else do the actual programming but have no idea

People often ask me to 'teach them excel' i don't think it is something that can be explicitly taught, as with most things in life it just takes experience, a lot of the work i do now i go back to an old macro i have written and just copy paste and change a few things. When i started out I contributed a lot on mrexcel.com. I asked a ton of questions and people would give detailed responses for free, its incredible. After a while I started doing other people's problems in my spare time and when I would post my solution someone more experienced would come in with an even better solution, it really is the best way to learn. Hopefully we can get some good questions going in here and everyone wins!

EDIT: pivot tables are far and away my biggest weakness in excel. I know how awesome they are and how fast they can display huge data sets but frankly i find the display of the data to be tacky and hard for end-users to find meaningful if they don't have experience with pivot tables (i havent checked this out in 2007 yet though so who knows)

Why did they name it Excel?

Not sure, but like most things microsoft they just ripped it off, repackaged it and business' ate it up

Is it true that the program is a copy of Lotus?

What new features in Office 2007 do you feel are the most useful? Are most of your clients still on the older version of office or have they mostly upgraded by now?

Never heard that before, the original spreadsheet application was VisiCalc. Although at it's core it is the same program, i guess it's a bit harsh to say excel is a rip off anymore it has added a lot

On pivot table, when you refresh it, after removing data from the source, the fillter popup still shows values no longer in source. Even after closing and opening workbook.

Is there a non vba way to hiilight a range and replace cells with exact value of "X" with null cells? , I know I can do replace "X" with nothing, but that doesn't make cell null for other things.

Great question!

Most of my original work was done in 2003, and all my clients were using it. This in itself is amazing because this list includes 3 of the biggest government service providers in australia and a couple of billion dollar fund management firms.

I have only recently made the switch to 2007, at first I hated every second of it and to some extent I still do. The ribbon is the most confusing thing to use when you are used to the standard file menu.

Most of my work is done in VBA and to that level i haven't noticed a great level of change (especially because most of the new functions i won't use because it will break if you open it in 2003)

The biggest changes are the massive increase in rows (from 65536 to 1mil+) and columns. One of my biggest clients was on 2003 until a few months ago and had several 65000 row work books that they use as databases despite me trying to move them off excel

I also love the new conditional formatting rules which allow you much more freedom than in older versions. Previously if you wanted more than 3 conditions on a single cell you had to write a VBA module to handle it. This also required using the on_change module which i'm not a big fan of messing with because i do a lot of template work which combines thousands of duplicate spreadsheets and if there are macros running on each spreadsheet it considerably slows down the whole process.

I'm sure i'll think of more as they come

In a pivot table is there anyway to use that data in a group of equations?

Aka lets say the data in a pivot is in E 3-30 and i want to multiple this data by data on another sheet that is lets say sheet 1 D 3-30. When i try this it never works. What am i doing wrong?

I should've mentioned this in the OP but pivot tables are far and away my biggest weakness in excel. I know how awesome they are and how fast they can display huge data sets but frankly i find the display of the data to be tacky and hard for end-users to find meaningful if they don't have experience with pivot tables (i havent checked this out in 2007 yet though so who knows)

I just re read your question and see its more about the actual data, if it's a one off thing the find and replace should work im not sure what your issue is ("that doesn't make cell null for other things") do you mean that if you do a find and replace then it is still showing up in the pivot table with blank values?

This is kind of off topic to your thread. But I bought Microsoft Project Professional 2010 and microsoft office professional plus 2010 at a discount through the company I work for. If I don't really need it what type of profession would use all of the things that come with it? Am I allowed to let someone else have it if I don't need it?

This is one of the reasons I don't use pivot tables, I'll run into something like this and realise that if i was not using pivot tables it would be much easier to display the data exactly how i want at the expense of a couple of seconds of processing time

Off the top of my head i'm sure you could write a user defined function (UDF) that you can then set the pivot table to have =mycustomsum(column). hopefully someone with a better understanding of pivot tables can chime in, i'd love to here more on it i'm sure there is an easier way

edit: you could add another column to the data with your calculation (you can hide it from view on the main data sheet if you want)

I like microsoft project at a basic level and so far i've never seen a company that uses all of it's intended uses (full budgeting, scheduling, resource monitoring etc) without relying on other applications. I've never used 2010 though so i have no idea what it offers

Are you saying that you bought it because it was on sale (or you just wanted office but didn't need project) but you don't need it and you want to sell it? sure why not, a quick search of ebay shows plenty of them for $200-$400 (or the academic version for ~$180)

Thanks, Ya I just bought it because it was so cheap. I got both with CD's for less than $50.

"It is tempting, if the only tool you have is a hammer, to treat everything as if it were a nail."

This saying is probably more true for Excel than any other tool.

How much could I earn in NYC with no job skills other than being awesome at Excel? I have no official experience but I know VB and practically every formula there is and I've been able to build spread sheets that self-proclaimed Excel gurus didn't think were possible. Could I land a job that would provide me a comfortable standard of living in NYC?

lol very fitting quote.

Is there an easy way to convert the European notation of money into American and vice versa?

E.G. one thousand dollars is $1,000.00 while one thousand Euros is €1.000,00

is OpenOffice or some other open source project ever going to be as good as Excel? at the moment imo the word processor is as good or maybe better than Word, but the spreadsheet is waaaayy behind Excel.

FACT, and it's not changing any time soon.

Firstly, I am assuming you mean VBA not VB. VBA is a stripped down version of VB that microsoft tailored for their office suite (visual basic for applications)

All of my purely excel work was done for companies in Australia that I either worked for briefly or through word of mouth. I believe that I (and probably you by the sounds of it) could save just about any company a ridic amount of money by automating the spreadsheets they use, the only hard part is getting your foot in the door.

I got my current contact in NY through craigslist (a long with most of my apartment) They have a huge access front end with tons of vba and a MsSQL backend, i didn't really have any experience in access specifically or MsSQL but I had done SQL at university so I knew the basics and just learned on the job

The money I make is up to me, I pretty much have a never ending amount of work at the moment but I'm still young and not looking to work 60-70 hour weeks. I do the minimum amount of work to support my lifestyle which involves travelling and partying e.g.
- i went on the road for 2 months in june to go to Norway, Denmark, Egypt and Vegas and probably did about 10 hours a week when i had spare time.
- i just got back from a week in DC/maryland and a few weeks before that i was in philly for a week
- I'm going to canada tomorrow for two weeks (toronto/montreal)

I have no problem not saving much at the moment, but IIRC you are older (at least you've been to afghanistan and back) and are probably looking for different things

cliffs: Do you have any other skills in the area? If not and you have no contacts i would say its hard to make a living here purely on excel, but it's definitely worth it, NYC is awesome.

At the moment i'm charging between $50 - $90 an hour depending on the client, my only problem is motivation, i'd rather experience the NY night life than program 12 hour days. What's the point in having an australian accent in NYC if im at home working

You are a man who understands his true assets.

Without exchange rates? Right click on cell > Format cells. Click on the number tab pick the "currency" category and you should be able to change the symbol with a drop down

IMO, no.. Business' are so reliant on office that I can't see it changing anywhere but the top innovators for a very long time. I would expect the change would start with the OS and trickle down... But even with the horrendous clusterf**k that was vista nothing changed*. I use a mac at the moment (ironic i guess) but any alternatives to office for mac are so far behind what excel can do. Even Office:Mac doesn't support VBA so i do all my work on parallels desktop (i cant recommend this program enough for anyone with an intel mac)

*Just wanted to add that i don't necessarily think vista was a horrible OS, it got owned by poor advertising. i would often hear someone telling me "vista sucks" even though they had never used it before.

Sorry, but I'll be a dick and mention that an ask me thread about Excel in which the OP can't discuss the use of pivot tables is laughable.

Stupid things American girls have said to me would probably be worthy of it's own thread, e.g.
- Do you have facebook in australia?
- What language do you speak in australia?
- Australia? wow you're from europe thats awesome
- OMG I LOVE YOUR ACCENT (my favourite!)
- OMG you said [xxxx]?! we say [yyyy], that's so cute
- Are you from [country that is no where near australia]? e.g. England, South Africa, New Zealand, Ireland

I'll add more when i think of them/they come up

When watching someone else use Excel do you get antsy cause you see like 12 different things you can do 10 times faster and more efficiently?