Very Scary Fall Giveaway For Excel Nerds

It’s time for another Excel giveaway! Hallowe’en is just around the corner, so let’s find out what Excel Nerds do to celebrate. I’ve been given some awesome Excel utilities, books, and ebooks to give away, so let’s have some fun. Rules are at the end of this article.

To enter, write an original (and suitable for work!) comment below, describing either:

  • A) an Excel related costume (real or imagined). For example, here’s a picture of my daughter and son, many years ago, ready for some trick-or-treating. My daughter was dressed as The Formula Doctor and my son was an early prototype of an Excel Web app. I’m sure they won’t mind me posting this picture, but if you see them, maybe you shouldn’t mention it. ;-)

 

DoctorSpider

OR

  • B) your scariest Excel related experience. I’m sure you’ve had horrifying days, buried under spreadsheets, and workbooks that come back to haunt you. Tell the other readers about one of those gruesome times. Remember, misery loves company. We’ll feel sorry for you, as soon as we stop laughing.

paperscream

 

The submission deadline is 12:00 noon (Eastern Daylight Time) on Tuesday, October 27, 2009. Winners will be announced here on October 28th.

Note: You can make as many comments as you like, but only the first one will count as your entry.

The Ghastly Goodies

Some scarily smart Excel authors, developers and publishers have contributed a monstrous mound of books and utilities for me to give away as treats.

Thanks to all the contributors – Patrick “Ogre” O’Beirne, Jan Karel “The Creeper” Pieterse, Charley “Crypt-Kicker” Kyd, John “Walking Dead” Walkenbach, Jon “Petrifier” Peltier, “Chiller” Chandoo and Matt “Killer” Kennedy.

Systems Modelling Ltd

From Patrick O’Beirne of Systems Modelling Ltd

XLTestCF

  • PATRICK’S TREAT for everyone who enters, a pdf file — “The Devil’s guide to creating spreadsheets” (I’ll email you a link to the download.)

JKP Application Development Services

From Jan Karel Pieterse, of JKP Application Development Services:

ProExcel2 VBA2007Dummies jkpDrive

ExcelUser, Inc.

From Charley Kyd of ExcelUser, Inc.

is-db-250

J-Walk & Associates, Inc.

From John Walkenbach of J-Walk & Associates, Inc.

  • one copy of the Power Utility Pak (PUP). — “Power Utility Pak Version 6 (PUP v6) is a useful collection of add-ins that brings significant new functionality to Excel. When PUP is installed, you can do things with Excel that you never thought were possible.”
  • The winner can select one of the two versions available:

PUPv7

Peltier Technical Services

From Jon Peltier, of Peltier Technical Services:

ptstilemekko ptstileboxplot ptstilecluster
ptstilewaterfall ptstiledot

Pointy Haired Dilbert

From Chandoo of Pointy Haired Dilbert

  • one copy of the newly released Project Management Bundle of templates for Excel — “The bundle contains 24 highly reusable excel templates for project planning, task management, timesheets, issue tracking, risk logging, status reporting and more.”
  • The winner can select one of the two versions available:

project-management-bundle-excel

Apress Publishers

From Matt Kennedy of Apress, 2 prizes – e-books that you can download from the Apress website:

ProExcelVBA ProExcelFinMod

Contextures

And finally, from Contextures – 3 prizes. The 3 winners can each select one of my pivot table books:

  • Beginning Pivot Tables in Excel 2007
  • Excel 2007 PivotTables Recipes
  • Excel Pivot Tables Recipe Book

BeginPT_2007 PTRec2007 PTRec

The Rigid Rules

  • To enter, submit an original (and suitable for work!) comment below, describing either an Excel-related costume or horrifying Excel-related experience
  • The comment must be submitted before the deadline of 12:00 noon (Eastern Daylight Time) on Tuesday, October 27, 2009
  • One entry per person – any additional entries will be deleted from the draw
  • A random draw will select each prize and its winner. No substitution of prizes.
  • Winners will be notified by email, so please provide a valid email address. This will not be publicly visible, but may be shared with the contest sponsors, so they can contact prize winners to arrange delivery.
  • Physical prizes will be shipped, postage paid, but taxes or other charges (if any) will be the responsibility of the recipient.

______________

You may also like...

80 Responses

  1. Hi Debra,

    You forgot my offerings…..

  2. Hi Jan Karel, sorry I didn’t get your email to confirm. I’ve just added them, thanks!

  3. govi says:

    At work we use Excel 2003. I wanted to use Excel 2007 conditional formatting on a dashboard.
    So I downloaded a portable version of Excel 2007,not quite legal ;-)and started creating a this very fancy dashboard.
    I was very enthusiastic about it and took it home and worked some more on it.
    Next day I opened is it and all changes I made at home were gone(4 hours of work!)
    I later found out that you have to put is in a designated folder if you use a Thinstalled version of software.

  4. Christine McManus says:

    I received a toolbar at a forecasting class that was very helpful in creating growth curves so I created a great forecast for a new product and sent it on to senior management not knowing that they had to have the toolbar installed on there computer for the formulas to work so the worksheets crashed and I did not look too cool!

  5. Keith says:

    Debra,

    Not my own nightmare but….

    A friend, who is a school deputy head master, asked me several years ago to look at an Excel spreadsheet his school used for pupils yearly reports.

    This had a front worksheet for the pupils name, class, date of birth, etc, with about 130 pupils, each one on a row.

    There were other sheets with scores for Attendance, Behaviour, Maths, English etc. and on each; the pupils names were repeaated. It had been written by the schools I.T. expert

    The spreadsheet had a macro behind a command button such that if you highlighted a pupil on the front sheet then a report was printed out summarising the various sheets onto a single page report for that pupil. But to produce the entire class year reports was a laborious down one row, press the button, 130 times

    So, he asked if I could write a macro to automate this, which I did using a simple loop that called the existing code (which I deliberately left untouched)

    I did however look at the code (to pick up a few tips), and all the VBA did was remember the row number of the current pupil, go to each of the various sheets & pick up the corresponding data.

    Unfortunately, it didn’t use the pupils unique I.D. but the row number, and as several pupils had left & had carefully been deleted from the front sheet it mixed up the puipls marks with the those of others on nearby rows.

    The net result was that most pupils annual report wasn’t their own but a mixture of other pupils, & they had used it for several classes & years.

    I’ll always remember the ashen look of disbelef on the face of my friend when I explained the spreadsheet was wrong & had been for a long time.

  6. Doug Smith says:

    I was in an interview with a potential client who had advertised that he needed an Excel expert. Surprisingly, all his questions were about Access. I finally asked at what point in the process the data would be exported to Excel to do the reporting and analysis he wanted. He gave me a confused look and said, “Why would we need to do that? Excel and Access are the same program.” And as scary as that is, that’s not the only time I’ve had someone tell me that.

  7. Steve Porter says:

    Here is my most memorable Excel horror story…Several years ago when I was in graduate school working on my MBA my study team was working on a project for a course that included building a forecast model to support our recommendations. The base model was built by a team member who was a CPA, and fairly proficient at using spreadsheets. We had only a week to complete the project, so the team took a divide and conquer approach, with each team member being responsible for updating a section of the model. As the project progressed we began to notice strange things occurring with the model output. It turned out that one of our team members did not know how to use Excel (even something as basic as a SUM formula) and had been using a calculator to arrive at values and manually inputting data into the model; overwriting any existing formulas.

  8. […] just announced the Very Scary Fall Giveaway for Excel Nerds on my Contextures Blog. Lots of great Excel utilities, books and ebooks to give away, thanks to my […]

  9. Kurt says:

    For a small project I geocoded a few hundred addresses via google. the quality seemed ok (google returned indices of 8 and 9 – the highest possible values). The computations were done in excel – after finishing the work (including some graphs) I deteteced, that geocoding databases for European countries are not very accurate ( there where in some extreme cases differences of a few hundred kilometers)

  10. Michael says:

    On a consulting gig a VP wanted an App developed in Excel that would recursively loop through a folder and e-mail pictures to a specific group. We had developed similar code built into workbooks for e-mailing project files. It was not our recommendation to do this but he wanted it and was willing to pay for it.

    Everything worked as planned until he copied the wrong pictures into the folder used as the source for e-mailing pictures. We had never seen nor thought to ask….what pictures? Everyone in the company soon received pictures of him with his bosses wife.

    We heard later the VP tried to blame the App….oblivious to the nature of the content!!!

  11. Andy says:

    Ha! This is a great idea! I’ll have to think of a costume idea and get back you on it. You should tell the Excel community on Facebook about this. Check it out here http://www.facebook.com/office

    Cheers,
    Andy
    MSFT Office Outreach Team

  12. David Hager says:

    I once accepted a job from another Excel developer. He wanted me to “fix” a workbook made by another developer. I found out that there was several thousand line of VBA spaghetti code to go through and try to understand the logic behind it. After a couple of long, scary days I called him and said “This job you gave me is hard”. He replied, “That’s what I’m paying you for”.

  13. Dave says:

    I had a co-worker who had a circular reference and he couldn’t determine where it is so he decided to just live with it.

    When he asked me a question, I noticed the warning in the statusbar. He said “oh, it always does that. I just ignore it.”

    I hit F9 to recalculate a few times and he watched as the cell that he trusted changed values over and over and over.

    A few days later, he came by to tell me he found the problem and fixed it.

    (Deb, Please don’t enter me in the drawing.)

  14. Thanks, these are great stories! I look forward to reading more of them.

    Andy, I’m sure you’ll come up with an interesting Excel costume to share here, and I’ve added this link on the Excel Facebook page — thanks for the tip.

    Dave, when a warning appears, I just turn up the radio. Oh wait, that’s what I do in the car. Never mind.

  15. chrisham says:

    After several hours of after office hours of Excel study, I finally was begining to make a niche in the Company as “The Office Excel Guru”. After wowing many with some fancy reports, I finally was summoned by the Head Bannana to churn a report within 20 minutes. I agreed that time would not be a problem, afterall I had done it the past and besides I knew all these cool XL formulas and functions, and this was going to be my big day. I envisioned that this was the going to be the day when I would look back and say “This Report made all the difference”. But my joy was short lived, 2 hours later and after several edgy phonecalls from my boss, my report was far from even half done! Finally when I did present it, they were some flagrant errors with the figures! Needless to say, my Report found its way to the trash bin quite effortlessly, and since then I have had to face the wrath of a disgruntled boss for a time.
    But I have survived to tell my story…….

  16. meep says:

    Some years ago I was doing some Monte Carlo runs in Excel [that’s not the horror part, though in retrospect…. yeah, bad choice of tool for the job].

    At the end, two graphs were created using the results of the million scenarios I had generated.

    Now I had 5-8 different set ups to run, and I needed the graphs from each of these.

    To put in a Word doc report.

    This was before I knew about “Paste as a picture” [I didn’t find out about that for two more years!]

    Long story short, I crashed the email of all the senior managers on the project when I emailed my report to everybody. I then cut it up into a page at a time and asked them to let me know when they had gotten it and deleted it, so I could send the next page.

    Whups.

  17. Tony says:

    My scariest experience with Excel was about 4 years ago. I was working on an incentive file for Sales Reps. It was a large spreadsheet with a lot of information and formulas. After finishing the file, I submitted it to the payroll person so each Sales Rep would receive their incentive.

    After the Reps got their checks or direct deposit, the calls started to come in. Apparently during my sorting there must have been a break in the columns (empty column) and the payments were no longer correct for each person. In essence, the payments got jumbled during the sorting.

    The rework of finding out who got what amount, what their correct amount should be and submitting another payment was enough to NEVER make that mistake again. BE CAREFUL when sorting to ensure the whole dataset is being sorted.

  18. […] Debra at Contextures is having an Excel Fall Giveaway and you could get a free copy of Project Management Bundle by sharing your excel nightmare or excel costume idea. It is that simple. Go here. […]

  19. Othmar says:

    My excelnightmare are mostly crashed excel with pivottables. Unfortunately the automatic repairing just deleted all the privtinforamation and left just a plain exceltable. So I had to redue all the pivot with all the formating etc.

    That was really a pain :-(

  20. Peter Sestoft says:

    Five years ago I was co-chair for a conference to be held in Italy,
    sponsored by a rather large and respected professional organization.
    We obtained permission to do the budget in Euros, since almost all
    expenses would be local. So I filled in the organization’s Excel
    budget model, budgeting with a loss of less than 600 Euros, which was
    justified by an accounting mistake (made by others) in previous years,
    and sent it off to the sponsor in New York.

    After a week I received this terse message in response: “Upon
    conversion into US dollars, the budget submitted is at lost,
    approximately (-3230.87). Please advise. Thank you.”

    Puzzled that 600 Euros could turn into that many US dollars, when the
    exchange rate was 1.25, I asked for and got their version of the
    budget. In the Excel file somebody had added a “US dollar” column
    next to my Euro column. Then they had procedded to use a pocket
    calculator to convert all the Euro amounts to dollars and enter the
    results by hand. The sums has been done by hand too, so by mistake
    one expense line had been added twice, thus creating the artificially
    large dollar loss.

    It surprised me that a budget office might prefer to use a pocket
    calculator rather than enter =RATE*I7 in J7 and copy that formula down
    the column.

  21. Nimesh Thakkar says:

    I can’t this as the scariest but its the funniest till now for me.
    I came to knew about this colleague having problem in some excel formula.
    When I checked, I was shocked to see this….
    =vlookup(A1,B1:B20,0,false) (ranges are just for the ref)
    anyone can figure out that the third parameter can never be 0, after this they get the error (#ref or #val something) then they use to filter on that error and then copy the ranges from the source columns
    also the source and destination columns were exchanged.

  22. Tyler Ellis says:

    I created several reports illustrating data points in our sales organization. Boss sent said reports to each salesman to add some information. All of the additions contained mostly the same information, but in different formats. Boss sent the info to another manager who tried to sort it. Then it was up to me to aggregate the info and prepare it for a meeting. Too many steps and too many hands that were unfamiliar with excel destroyed the quality of the data. I am still trying to straighten it all out.

  23. Chechu says:

    Around 12 years ago when I started to work with Excel, my boss requested a powerful tool to be used by all the managers in the company. I worked for weeks on this, and when finalized it, the size was 10MB!!! IT department locked my e-mail account due to the average e-mail size and I crashed the email of all the senior managers on the company…. Years later I learned more, and now the same tool is 0.3MB :)

  24. Gregory says:

    Costume idea: I would only down a drink if anyone presses big F9 button on my belly…

    …on my back I would wear a checkbox with “recover if crached” =)

  25. Gregor says:

    I needed to produce a long report on the basis of pieces of text that various contributors had written into Excel sheets. I created a new Excel 2003 workbook which referenced the text cells in the contributor’s workbook, concatenated the texts, removed excessive whitespace, and applied some neat formatting. Everything worked fine, print as PDF and a 50-page report ready to go! Next time I opened my report to make some last-minute changes, the contributor’s text blocks were cut off in the middle (after 250 characters), and the report was useless. After doing nothing (just opening the contributors’ Excel files and looking at them), the full texts appeared again and the problem was fixed. Possibly the texts are cut off if I reference cells in closed worksheets, but not if the worksheets are open. In any case, it was very scary now-you-see-it-now-you-don’t scenario.

  26. laguerriere says:

    hi all,
    my scarest Excel experience was on a college computer which did not save my excel model I created for my thesis and did not even recover the file!!! i had to work on it again and to remember my modifications. now i use ctrl S every minute :-)
    best, La Guerrière

  27. Jim Cone says:

    I once accepted a programming job from a client in another English speaking country.
    After working on the job for a while (the specifications were very complicated), I found out that the work was to be used by a relative in a non-English speaking country.
    The relative was also using a different version of Excel.

  28. Having developped a strong & good-looking efficient dahsboard for ony of my client only to find out that when demo times came up, automatic compilation of data didn’t work at all due to a modification done by the IS team on the structure of data output from their central systems… Excel Demo daemons striked again…

  29. Rick says:

    My imagined Excel Costume would be Dashboard Man. An Excelian superhero from the planet MSoft:
    At first blush people would notice my protruding abdomen. No, dashboard man is not overweight, he is pregnant with a Kyd (both genders of the Excelian race produce Kyds), named Charley whom I will nickname IncSight for its ability to peer into the data of corporations and see relevant information. People will notice that at times I walk strangely then suddenly start speaking native VBA and deep Excel knowledge, this walk is known as the J-Walk on MSoft. When I see an Excel user looking cofused and uable to chart I Peltier (I believe its called pelt in your language) them with magnificent charting addins that solve their problems. My Pointy Hair immediately gives Earthling Excel users a sense of community and lets them know that I can share lofty Excel information in terms that all can grasp. You will notice a large red button on the back of my costume that says “Push To learn more about Excel”, if you give it Apress volumes of book-like knowlege will stream forth solving your problems and expanding your Excel knowledge. The Excelian people understand to keep a dashboard current and in proper Contexture (I beleive Earthlings says context) a vast knowledge of pivot tables is required. On MSoft we have perfected turning Pivot Tables into food and by doing so the knowledge of them is in our very being. Over the years we have created many recipe books to make them more palatable. Knowing that Earthlings cannot consume a Pivot Table, Dashboard Man hands out the recipe books so that human Excel users can use this knowlege and keep their dashboards current and in Contexture, I mean context. Lastly, when Dashboard Man answers the door on Halloween his dish is filled goodies for those get a Patrick (is it just trick on your planet?), which is a test that instantly checks the integrity of an Excel Spreadsheet or a treat…the better than popcorn balls JKP (some Earthilings have nicknamed it a jackpot!)…the JKP has something for everyone. Such as instant knowledge of Excel development…heck, the JKP even imparts VBA knowledge to dummies!

    Ha ha ha…nerdy as can be…but fun…back to work now!

  30. D Sinclair says:

    We created pivot charts and copied and pasted them into a Word document.

    But when we could make changes and a lot of people started yelling – we noticed the pictures were pasted into the Word document – inserted objects. Duh!

    Like to laugh at charts and graphs? See http://graphjam.com/

  31. D Sinclair says:

    We created pivot charts and copied and pasted them into a Word document.

    But when we tried make changes and a lot of people started yelling – we noticed the graphs were pasted into the Word document and not inserted objects. Duh!

    Like to laugh at charts and graphs? See http://graphjam.com/

  32. Kyle says:

    There was a stage in my job about three years ago where i was working 6-8 hours straight on Excel each day, for weeks on end, employing a whole bunch of different formulas to give shape to raw data that was being spewed at me. It got to a point where the modus operandi of Excel got so integrated into my subconscious that I started serially dreaming in Excel format. In my dreams, in order to do anything I would have to enter a formula to take me to another range of cells where I could do what I had to do, or speak with the people I wanted to. I could format people, hide things, slip inter-dimensionally into a new sheet or border great ideas in bold red. My dream life began to regularly take place in an Excel matrix. Luckily, my job moved on to a new phase which didn’t require such heavy Excel usage and I am glad to report that I have not an Excel spreadsheet dream in years.

  33. Phil Smith says:

    An Excel costume.

    It’s a sub (the opposite of Super) hero cape. It’s in bright colours, it gets new bells and whistles every year. It got a neat flying ability in 2003, but that was removed in 2007 as it was too stable and too many people liked it. Now every time it’s put on the wearer falls over or attempts to hang himself. The worst thing is, once it’s on you can’t take it off…. Or was that the dream?

  34. heather says:

    debra, this has to be the best post ever. excel costumes!?!? i love it.

  35. J.R. says:

    What a great idea! Thank you for this wonderful offer and thanks to PHD for referring me to the site.

    My scariest Excel story is not one of complex calculations or impressive VBA code. It is, however, a great reminder that the simplest of mistakes can “haunt” you forever!

    It was about 3 years ago and I had just started working for my current employer, an oil and natural gas company. The company was looking at a sizeable oil project and management asked me to run some sensitivity analyses factoring in forecasted oil prices. Sounds simple enough, right?
    Oil was trading around $55 a barrel at the time and most forecasts were relatively flat. I ran a few scenarios using conservative estimates of +/- 1% change in prices. This was reasonable, as the price of oil had only increased an average of 0.5% per month since the early 80’s. I was eventually asked to settle on what I deemed the most realistic estimate. Given the exposure of this project and the fact that management was relying on me for not only the financial analysis, but also my opinion on the direction of the market, I was putting a lot of pressure on myself to make my report impressive.

    In my haste, I made the simple mistake of calculating the forecasted price of oil at +5% per month instead of +0.5% per month! I “fat fingered” the formula and then copied and pasted down for the entire forecast. This made a HUGE difference in the oil price over the next 24 months! I presented the report with confidence and was absolutely terrified when I looked up on the projection screen and saw that I was forecasting oil prices at $131 per barrel by mid-year 2008! At this point in my presentation, I got a few laughs and even a comment that “oil prices above $100 a barrel is absurd!” I explained my mistake and tried to move forward. I was so embarrassed!

    Fast forward to July 2008, and oil hit an all-time high of $147 a barrel! This was unheard of. Remember my Excel GOOF that forecasted oil prices to increase at a rate of +5% per month? Well, when oil hit this record high, my erroneous calculation forecasted oil prices within 2% of actual, when most estimates were off by 50% or more! Of course, no one would have predicted the run up in oil prices that we had, but I did get a few calls from people that were in the meeting that day in early 2007 and we all had a good laugh!

  36. Phil Smith says:

    I once used excel and a mail merge to send staff a record of their absence.

    I checked the first few, and a couple of random ones and all was fine.

    Staff had between 1 and 5 days off throughout the year, and I wanted them to know that I was using an open system for tracking them – that they could have full access to.

    However, one woman received a letter stating that she had had 99 days off this year.

    She was very upset to find out that our records showed she had had so much time off.

    It was a problem, however, sharing the information like this meant we could rectify the input error.

    I now always include a cover letter explaining that it is quite possible there could be an error, and it is important the people check the data and let me know of any discrepencies.

  37. Here are a few costume ideas:

    1. Draw a big letter V on your shirt and look up. Your VLOOKUP.

    2. Write Sheet1 on a white shirt. You’re Sheet1

    3. Write “Ceiling() is #1 !!” on a white shirt. You’re a Ceiling fan.

  38. Howard says:

    Not sure if I have any stories to compare with those provided, but
    I do know that any Excel costume has to be made of SYLK….or sheets.

  39. lawrence says:

    i was a rookie one year ago, and a scary pricing job is assigned to me.it’s full of price,ours’ and our’s main competitors, i need to calculate the mix price, the difference, and how to reprice
    oh,it’s so awful that i won’t forget in short time
    later, i improve my skill and knowledge on excel,thanks to a lot of excel masters like you, chandoo, and others, i am sorry to omit

  40. S Fletcher says:

    I am Macro Boy – I run really fast and make it my mission to rid the world of bugs!

  41. Joe Mako says:

    A company I worked for acquired a smaller company, so we had to migrate their customer record data into our our database. The bad news, their record storage system for the past nine years had been a separate Excel workbook for each customer, and it only gets worse from there.

    We knew they had 2,000 active customers, but they had over 3,000 Excel files, each with anywhere from 1 to 40 sheets that looked to be organically developed over the years. Their data storage system had the least structure I have ever seen, free-form layout, free-form naming, and free-form values.

    Every error that you can imagine was in the workbooks. Including, human data entry issues such as customer name where the phone number should be, and their address where the product name should be, custom structure to fit the custom situation for each sheet with #REF errors resulting from I guess randomly inserted columns and rows, one day I will have to look up what ERROR 23 means, but there was a bunch of those, form elements like check boxes stacked on top of each other all named checkbox1 with no caption (was real fun when I would look at a sheet and see 3 check boxes, but VBA would tell me there were 57 all named the same with 17 of them checked) and just about anything else you could imagine in 15,000 worksheets (I could go on for hours detailing all the things that were messed up in those files).

    I was given a week to extract, error check, correct, and transform all the data to fit our normalized database.

  42. Sal Paradise says:

    Sign on Front:
    #N/A

    Sign on Back:
    =INDEX(Costumes,MATCH(“Excel”,List_Of_Costume_Themes,0),)

  43. Rohit Chadha says:

    Actually I had couple of scary instances ;)

    1. Making company’s board presentation with excel charts and graphs (where regular data updates are required).. but with run of time pasted charts as Enhanced Metafile and Not Links (OOOpppsss !!!)

    2. In preparation with Annual Budget for the entire group, linking around 50 excel files (Oh My Goodness !!!) tracking changes through each was ONE NIGHTMARE.

    3. And most hilarious — linked LOOKUP values with Lotus TEMP file .. he he he.. thanks to GOD that submission never happened :P.

    Now I have come a long way through excel.. on to my next mission of “DASH-BOARDING” .. will notify on them soon…

    Cheers !!!

  44. Contextures Blog » Stop Automatic Hyperlinks in Excel says:

    […] Very Scary Fall Giveaway For Excel Nerds […]

  45. exlguru says:

    One of the scariest expereiences I had was working on a excel 2003 large file with macros was around 25MB. Since the file size was large was slowing down the computer. I decided to move it to 2007 and the file size came down to 5 MB adn increased the speed drastically. It was all working fine when on my PC. I then had to take it for client location where they were not using 2007 and it slowed down the whole file. It took me ages to get it sorted post that.

  46. Dermotb says:

    A few years ago, I saw a medium size listed company using 15 interlinked spreadsheets to calculate board reports each month.

    After finishing the figures each month, they copied all the spreadsheets into a new folder with strict instructions not to recalculate them.

    Why? Because there was a circularity which would change the figures if recalculated. Somehow they didn’t see the danger!

  47. Vasudeva.S says:

    I have been working with a Japanese Company, They are so used to Excel, they want the entire System documentation of a Web Application in Excel, I am really not sure how to get all the formatting of text what can be achieved in Microsoft Word.

  48. Prashanth says:

    The Worst Nightmare,I experienced was while using an Excel File Linked to another file updated by another department,in a different location.

    The contents were updated to a Graph at my end.
    It used to show the material stock and availability,Re order level and so on…

    Once the guy at the other end didn’t update the table,however at my end the date was shown updated.Later I came to know that the person used the formula TODAY() in his file,so the date got updated automatically.

    Next,the person deleted the file to which,I linked my file.The data and Graphs on my Excel Sheet was showing the previous entry.
    As a result the stock went below the Danger Level (Safety Stock),when I could spot the problem.

  49. David Miles says:

    My worst nightmare was about 9 years ago and I was just getting started with Excel. I knew how to do a few things with it, I was a maths teacher who used it from time to time with the kids, etc.

    So the High School Principal decides to do a survey of students impressions of their teachers, and he decided to use technology to do it. Fine, he knew what he was doing, sounded good, and myself and the librarian, also a techie-type, agreed (nay, volunteered!) to give him a hand collating the results.

    Ok, so a couple of weeks later, he’s applied the test to all 150 students, he tells us where the results are. Shouldn’t take more than a couple of weeks, we said. (Remember, we’re volunteering our time here, this is on top of everything else we had to do)

    Then we took a look at it. !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!W T F !!!!!!!!!!!!!!!!!!!!

    Imagine this. There are 150 students, each of whom studies 12 subjects. So there are 150 Excel files, each with 12 pages in it. One each page there are 24 questions. No, wait a second. Originally, on the first page there were 24 questions, on every other there were 23. Why? Because he forgot a question, so when students realized this, he had them add the missing question to each sheet.

    Keep imagining. Next up, he’s used Excel as a sheet of paper. You know how when you underline the blank where you want people to put things when filling in a paper form? Well, he’d underlined the blanks where he wanted people to put things. But he didn’t use bottom cell border, oh no. He went into the cell and type ‘——-‘. Or something like that. In every question. 5 or 6 times. 24 times a sheet, 12 sheets to a file, 150 files.

    And what did the students do? They sometimes entered their result into the cell. And sometimes they went in very carefully and placed it in the middle ‘––1––’. Oh, and when he asked them to rank things from 1 to 5, sometimes their answers were x x x x x. Oh, and sometimes, they’d put their response on the line above the cell it was supposed to go in. And sometimes next to it. Or if there was a 1-5 response, they’d answer 1 1 1 1 1.

    I can’t even begin to describe the mess. It was horrendous. There was a free response section. Kids had started writing a comment, run out of screen width, hit enter, and started again on the cell below. It was a nightmare. Absolute nightmare. 150 files of complete and utter crap.

    Well, I learnt an awful lot over the next 2 months, about macros and how to get something meaningful out of complete junk. I found a wonderful program called Macromaker which saved my sanity. I learnt a lot about writing Excel Macros, exporting to csv files, oh, you name it, I probably played with it. Two months later we had something relatively meaningful. And you know what I said?

    ‘Next time you want to do a survey, tell me. About 2 months in advance. And I’ll write it for you.’ And I did. I locked that thing down so tight you couldn’t breath if I hadn’t given you permission to do so. And a couple of days after he’d applied the survey, he got his results.

    That’s my scary story. Of all the screwups I’ve seen people commit since then, nothing comes anywhere near close to that one.

  50. Chandoo says:

    I know I shouldnt even enter the draw, but couldnt resist the temptation.

    Here is an excel costume idea:

    “A spreadsheet rubber sheet” – a rubber sheet with spreadsheet pattern that would protect your kids from wetting the bed. What more, the cells glow conditionally if water (pee) reaches them. :D

  51. Spike says:

    An ongoing horry story …

    Just over a year ago I was asked to take on a spreadsheet he had written so that people across the country could use it. The author told me what it was – basically something that tracks historic movements in assets and liabilties (details of which don’t really matter!). I thought no problem since I knew these kinda calcs backwards and had seen numerous similar tools before, even written a couple myself. So it arrives. Normally these things are pretty simple, say 3 or 4 sheets and even the most exotic one never creeps over 2MB. Imagine my horror when I found it was 11MB and comprised at least 15 sheets, all chock full of either statistics or formulae. The thing took almost 15 minutes to recalculate. I balked but all my attempts to escape its clutches were to no avail.

    So I figured I should try and make it more user-friendly. Think of a common problem with spreadsheets and it was probably there. Overly long formulae – tick (in abundance). Circular references – tick. Incomprehensible stuff that even someone familiar with the task at hand cannot understand – BIG tick. It was just awful. I’ve now got it to a state where it recalculates in a few seconds but it’s still an absolute nightmare to follow through. How can you use a spreadsheet that you can’t check? I even offered to rewrite it from scratch but that idea hit another brick wall.

    Sadly this thing is still my responsibility. Fortunately I have been promoted since then so I may have to find a suitable patsy volunteer to delegate its ongoing maintenance to!

    The one small saving grace is that very few people actually seem to be using it. So there you have it. Lesson for the day … just say no! ;-)

  52. luizfbfs says:

    My worst excel nightmare was in a project away from my country… the client set me up with a brand new shiny pc, fully installed but… windows and office in spanish…

    All my excel sheets worked perfectly until I had to do some editing… ¡then all went awfully wrong!

    ¿VLOOKUP in spanish anyone?

    P.S. If you really must know VLOOKUP == BUSCARV

  53. Pip says:

    Did a ghastly thing on a work colleague last week…don’t think he will ever forgive me as had the whole department is fits of laughter…
    He left his desk for a meeting & I was in a naughty mood so went over to his desk, opened up his excel 2003 to install an add-in called ASAP Utilities. Went ahead with that successfully. ASAP Utilities is a great add-in for Excel users so whenever ya get a chance, install it…anyway, getting back to my point…On the add-in, they have a feature called funny messages where it activates pop up messages saying things like “Earth moved 15 degrees north of the gravitational north pole!” Ya get the idea…Knowing Jimmy to get frustrated easily, I opened a series of messages on the PC & they were scattered all over his screen. Copied the interface on the PC to use as a desktop background with the icons hidden & I leave the imagination to you when Jimmy came back from his meeting!

  54. Emma/FW says:

    As a new employee, I had to report work progress in a prepared excel file. This format had hidden macros and a lot of formulas which I did not understand. I was supposed to just type in a certain letter on certain cells and this would import some data into the cells. I thought I could look at how the formulas were written and learn from them. I did try, but in the process, the file got corrupt. That was when I realized I was working on my original file and not a copy. I had to call my boss to explain the mess. It was most embarrassing.

  55. anthony says:

    Not really my worst excel nightmare, but definately for my colleagues. In my office we like to play practical jokes and when I learned a little vba, it was all over. My first useable/destructable program, primarly created from lifting lines of code from around the web, closing down whatever worksheet my colleagues we working on after 2 minutes. Since this trick was deployed on April Fool’s Day, most people realized fairly quickly and no one’s time was truly wasted. The toughest part was getting onto each machine and putting the code into the personal workbooks.

  56. Dave says:

    I remember when I found out that text strings in VBA are case-sensitive. It was almost as scary as the day I found out that public variables aren’t reset with the next run of the macro.

    The real scary thing is that I found these out while updating our pricing workbook (which had been in production for a year!).

    Oooooooo.

    (Deb, Please don’t enter me in the drawing.)

  57. Susan says:

    A number of years ago I worked on a Performance report that went out monthly to executives in the firm. A coworker made an update to one cell in the report with all of the tabs selected and saved the report. When I 1st opened the report, I couldn’t figure out why almost every graqph and chart had errors. Very frightening until I figured out what had happened, especially since I only had a day to correct it.

  58. Andy Wall says:

    A few years ago I returned from lunch to be told a senior manager had been looking for me and was annoyed I wasn’t there. He needed to see me urgently about an Excel issue. I went to see him expecting go d knows what. ‘How do I turn the background in this cell red?’ Now that is scary!

  59. greg kramer says:

    I was working for a large oil & gas company, in that industry all the setting up for trades gets done on a specific date. A person in who had authority to wire funds was making some “back of the envelope” calculations in one of the totals columns in excel & proceeded to “wire” another trading partner an extra $10 million…whoops!…of course, in big oil $10 mill amongst friends is no big deal & the counterparty sent the funds immediately back but still over paying by $10 million is not a best business practice

  60. David N says:

    A child/parent “costume”…

    Based on the Excel 2007 Ribbon (let’s not go there…)

    The child wears an ‘Insert/Candy’ portion of the Ribbon, while the parent has a ‘Review/Candy’ Ribbon button.

  61. General Ledger says:

    I travel the U.S. giving lectures on Excel. I woke up one morning and went to the location where the lecture was to be held. I found the room and began to set up. As time went by, I wondered why no one had arrived. I inquired at the front desk. “What Excel seminar,” the clerk responded. Surprise #1! Wrong hotel.

    I scrambled across town during morning rush hour to arrive just seconds before the seminar was to begin. I started to lecture while setting up my computer. Surprise #2! Where is my power cord?

    I excused myself for a moment and spoke to one of the staff. “Mike, here is $100. Go to the nearest computer store, and get me a power cord.”

    “But it’s 8:00 in the morning,” he said. “The stores won’t be open for at least an hour.”

    “Then I guess you’ll be the first customer of the day,” I frantically replied. “If you are not back before my battery dies in two hours, we will have a room full of very irate Excel users.”

    Mike ran off to get a power cord and I went on with the lecture. Minute by minute I watched as my battery indicator slowly displayed more and more red. Where is Mike with that cord?

    OGM!!! The 10 minute warning from my battery and it’s 15 minutes early. I am about to be ripped to shreds by a room full of geeks.

    Then, the door in the back opens. It’s Mike! I’m saved. He makes his way up to the podium. I plug in. Success. No one in the room was the wiser as to the horrible start of my day.

    Finally, we break for lunch. I find Mike and thank him for saving the day. Mike’s response, “Your fly is wide open.”

  62. M Frank Friedberg says:

    I started a new job (many years ago) with a construction company. While investigating a widespread complaint that their excel-based bid sheets were taking too long to load, I discovered that they had literally thousands of excel XLS files in a single directory!
    Upon further inspection, I found that all of these spreadsheets had IF statements 10 to 12 levels deep! Apparently, the author, company president, and now my boss, had never heard of a VLOOKUP. I had a time straightening up that mess. However, doing so would still not have any effect on the sheer volume of files.
    I took a two-pronged approach. A simple batch file was sufficient to organize the pre-existing files into a directory tree structure based on an alpha grouping of the clients’ name. These relieved some of the pressure on the OS from having to scan so many files at once. Notwithstanding, the files were still large and hence did require some amount of time to open. Therefore, for future bids, I optimized the existing “master” workbook (i.e. unused – from which new bids were derived) and converted it into an XLA. I provided VBA code, which “read” the XLA one row at a time and presented the questions and selections via a dynamic dialog box. Thenceforth new bids contained only the relevant data without any formulas or instructions. This made for much smaller bid files viable as a sustainable solution.

  63. Mr. K says:

    My boss calls me into his office to help him with an Excel problem. He is an accountant that has been using Excel every working day for ten years. He explains his problem is that no matter how wide he makes the blank column on the left of his data, he can’t get the table to print centered on the paper.

    Are you freaking kidding me?!?! And you make more money than me?!?! I tell him to go to File, Page Setup, select the Margins tab, and check Horizontally under Center on page.

    I then tell him, “For a raise, I’ll show you how to change the page from portrait to landscape.”

  64. Contextures Blog » Your Sheet Names Are Killing My Formulas says:

    […] Very Scary Fall Giveaway For Excel Nerds […]

  65. Kieran Nayak says:

    My client (farmer) wanted something simple to do his cashbook. We asked him if he had Excel and he said yes. So we quickly rigged up a template and sent it to him with instructions over the phone.

    When he next came to town he brought his wife and the CPU and blasted us saying no matter what they tried the damned cashbook would not work. Well, to cut the long story we connected his machine and yup, he was trying to open the cashbook with Lotus123 although he did have Office and Excel.

  66. Fred Chidester Sr says:

    Think about it, because on this day there was total brain blockage. What were you thinking and man it was sure that you were not. Having spent two weeks of input and adjusting train schedules into Excel 2000, which in short contained approx. 22,464 cell entries. The times were anywhere from 3 minutes to 10 minutes apart are linked to another workbook for review. Some – nice kind person who had NO BUSINESS – touching the – nice little workbook while I was out to lunch, went and sorted the Stations into alpha order. Because they were linked the master workbook, it also sorted. Since brain dead here didn’t, which I also now do, did not have a sort number before the stations, each row had to be cut and pasted back into its proper order. Oh the problem, the schedules changed on Sunday, it was 2 o’clock on a Friday before. This data is exported to Access which in turned gave the times to the PA system. Guess who worked all Friday night and most of Saturday fixing it? Smiles

  67. Glen Young says:

    Scariest experience – locking up Excel 2003 with too many fonts error (surpassed the 5000 limit)….55+ sheets of the company’s financial model…days before a board meeting….hadn’t backed it up for a couple weeks (shame on me)….and resurrection cleared all of the fonts destroying months worth of formatting and prettiness.

    Scared me so bad I pushed the whole company to adopt MS 2007 for which I suffered much verbal abuse from colleagues…for months….plus I had to be the “go to” guy anytime anyone had an issue with anything related to MS 2007…..and the new menu setup alone kept me hopping faster than a one legged man in a butt kicking contest!

  68. DeltaD says:

    I once did employee scheduling for my department’s frontline employees. It was based on a 28 day rotation which I plotted horizontally (names on the vertical). I had this nice macro that would cut the first day’s column and paste it after the last column and move everything up a column so I could run the macro and see whichever day I needed in the first column. The only problem was if I had something on the clipboard that I forgot to paste before I ran the macro it would paste that data in every cell in the last column, and you can’t undo that sort of thing without closing and reopening. It didn’t take long to learn to save every few minutes instead of at the end of an edit.

  69. Very Scary Fall Giveaway For Excel Nerds

    Here’s Excel Costume idea from Perth, Australia:

    An “Excel Wizard” – Green full body spandex suit, Superhero Excel Vest, Green runners, green cape and of course a green wizard hat. Naturally the Excel Wizard has full Excel Powers in the form of shredded spreadsheets, that on contact with skin have the potential of a powerful macro to conditionall format any evil power that attempts to come in its way (***imagination required ***. laughs!

  70. Gautam says:

    Hi,

    This is something very interesting and exciting contest.I was working with excel 2007 version and i had made whole dashboard and everything.Finally when the moment came to show it off during a presentation i realised that my audience has excel 2003 and the file just crashed and crashed.But the good thing was they liked (snapshot ,concept )the whole work so much that they are now planning to install excel 2007.I am relieved that this good work really paid.

    Cheers
    G

  71. Swapnil says:

    Hi,

    I am relatively new to excel or you can say I still am an Amateur in MS Excel….So once I was working on this huge database….I create a pivot out of this so that duplicates would be removed….but because of another column which I had inserted in the pivot…there were lots of blanks when I copied the pivot data in another sheet. Now come the big task of filling in the blanks…I had no clue how to get this done quicker and the only option which was left was to fill them manually – which was utter pain staking…:(

    That night I scratched my brain from all direction in thinking how it can be done quicker to save time…and after doing loads of search on line…I found a solution by going to Edit –> Go to option.

    Work which took me 2 days to finish (manually) took me 10 sec…I was happy and sad.. :)

    That’s what has geared me to learn more about excel & analysis.

  72. Tom says:

    Excel Nightmares always comes when time is shortening fast. I once had to make 800 excel-files with a different name. These files where then distributed to 800 different users to centralise afterwards. But….

    Once these files where already up and running, I’ve noticed that these files where the old version…. so a lot of work had to be redone. Fortunatly my collegues where so kind to forgive and forget the error.

    Try to keep the bad excelghost away.
    Tom

  73. Sol says:

    Bad Random Excel Limit
    I use excel to analyse to seperate but related plans held in an ODBC source. Since the tasks are manually entered with little or no vaildation I use some logic and text formulae to extract a reference code that I can then use to cross-reference the two plans. The workbook is fairly simple with two 5 column OBDC tables and about 5 columns of formulae on on of the tables. Normally the spreadsheet works just fine with very little issues, however sometimes the formula will just fail to autofill. This tends to happen when the tables pass around 12000 rows, but it is not repeatable.

  74. Matt says:

    Several years ago, I was asked to create a pseudo-dashboard for HR to track which employees completed a various list of roughly 80 different topics. The source of the data was each employee file, with a signed copy of the particular work instruction or training they had completed. With 1000+ people in the organization, the task of tallying up the training fell to an intern, who I asked to make a matrix in Excel with the employees names down one side, and the list of each training topic as the columns. As she went through each corner, I asked her to put a check in each cell where she found a match.

    After a long two weeks, on the final day of her internship, and two hours prior to my rollout of the HR dashboard (which was ready to go, it just had to SUMIF all the “x”‘s or nonblanks in the original tally matrix), the intern tells me she can’t email me the file. It is too big. Huh?

    At her workstation, she proudly opened up her “huge masterpiece” file for me, and to my horror realized she did indeed put a “check” in each cell, that is…a bitmap picture of a checkmark she copied and pasted from Paint for each match…

  75. John says:

    Error Code Man. His cape has a list of errors and what they mean. For example:

    PICNIC – Problem In Chair, Not In Computer
    ID-10-T – idiot

  76. The deadline has passed, and I’m compiling the lists for the random draw. (You can still add comments — but they won’t be entered in the draw.)

    Thanks for your entries — some very scary stuff happens in Excel!

    Check here tomorrow, October 28th, for the giveaway winners.

  77. Adrian Massimo says:

    Worst Excel experience was trying to pull together my first Dashboard report for management reporting…I was pasting in reports produced from more than three systems and had array formulas running on so many rows that recalculating used to take >40mins to process!! Thank God for the ‘Sumifs’ formula in the new version of Excel!

  78. Contextures Blog » Very Scary Fall Giveaway for Excel Nerds Winners says:

    […] Very Scary Fall Giveaway For Excel Nerds […]

  79. […] the Contextures Blog, I ran a week long Excel giveaway, with a nice collection of prizes from some generous […]