J-Walk & Associates, Inc.

Your Most Recent Excel Task?

Just out of curiosity... post a comment and tell us about the last time you used Excel. How long ago was it, and what exactly did you do?

In my case, I used Excel about two hours ago to double-check a file that will be included on the CD that comes with my Excel 2010 Formulas book. The tech editor, Niek Otten, noticed a discrepancy between the text and a figure. So I had to check it. Niek's really good at tech editing books. I'm often amazed at the stuff he points out. Too bad it's such a low-paying job.

Before that, I received a PUP v7 upgrade request, and checked my PUP sales workbook to ensure that the person was eligible for an upgrade. She was. Yes, those upgrade requests are checked manually. Low-tech, but that's the best I can do.

So what's the last thing you did with Excel?

- Reader Comments -

Following are comments in response to this item.
The most recent comment is at the bottom.

1. By gkeramidas. Comment posted 11 February, 2010 7:28pm

   ran an app i wrote for a client to see his daily sales in a pivot table and chart
2. By John Keegan. Comment posted 11 February, 2010 7:54pm

   The last thing I did was solve a problem I had in the back of my mind for a long time. I created a mega formula that had several nested IF statements and concatenated text.
   
   I am an accountant and one of the key financial statements I have to create is a Statement of Cash Flows. The text used as row labels changes depending on the sign of the values on that row. For example, the row that shows the change in the ending balance of Trade Accounts Receivable over a two year period could be any one of the follow:
   (Increase) decrease in trade accounts receivable
   (Increase) in trade accounts receivable
   Decrease in trade accounts receivable
   
   I created a formula that compares the values on the row to see if the current year's change was an increase, decrease, or stayed the same, to see if the prior year's change was an increase, decrease, or stayed the same, and concatenate the remaining required text.
   
   Previously I edited the labels manually as the values changed. Now it is automatic.
3. By Debra Dalgleish. Comment posted 11 February, 2010 8:04pm

   An hour ago, I updated a client's price lists. I click a few buttons that run macros, to create customized versions of the list for different regions.
4. By chip. Comment posted 11 February, 2010 8:34pm

   I used Excel about 4 hours ago for the most mundane of tasks. I opened an attachment to an email. The Excel doc has our company's senior employees vacation schedule for the current week, and I was trying to see if the reason a colleague was not replying to emails, calls, and cell phone calls was because he was out of town or on vacation. He wasn't. Never found him.
   
   And actually I wasn't looking for him myself. I was birddogging for an employee working from home who had IMed me wondering if I knew where the guy was.
5. By Charles Urban. Comment posted 11 February, 2010 8:40pm

   just now I used it as a data storage for information of a game I am playing. And I use it every day a work for data mining and organizing. Just five hours ago organizing information for a project I am working on
6. By Jack Freeman. Comment posted 11 February, 2010 9:33pm

   I just purchased your book Excel 2007 Power Programming with VBA. At the bottom of page xiii, there's a reference to an URL with a list of errors in the book: http://j-walk.com/ss/w. This link is apparently no longer in existenc, and search as I might, I can't find it on your new site.
   
   I have found what I think are some typos, and just wanted to get a complete listing of those that others may have found.
   
   As an aside, I'm new to VBA programming, but am an experienced programmer and Excel user from way back. I have some older spreadsheet tools I've developed and want to anhance their functionality. I am in the process of developing some training materials for civil engineers. I want to include these enhanced Excel spreadsheet tools, so that the student can focus on the concepts rather than the software, as many many versions of the proprietary software exist. The concepts learned can then be implemented by the student using the proprietary software that their employers use.
7. By Jack Freeman. Comment posted 11 February, 2010 9:40pm

   To continue, the last thing I did in Excel was to build in error-trapping routines for a spreadsheet system that is used by some 1,000 users in my company, to perform their project management and accounting functions. This required multiple nested if statements to identify and report the errors locally within the spreadsheet itself to avoid failure of the submitted data to Oracle Projects. I have worked with updating, enhancing, and maintaining for two years now. I also support the users via a company-wide help desk. When the systems are updated, it requires automatically pushing the spreadhseet package via internet to some 4,000 computers.
8. By Mathias. Comment posted 11 February, 2010 9:47pm

   Just finished writing an Excel VSTO app which forecasts the uptake of products soon to be introduced on the market, under various scenarios - sent it out yesterday evening, taking a break from Excel today - and tracking the time spent on the project in a worksheet, of course!
   In parallel I am reading the Carter and Lippert VSTO book, and playing with a VSTO Excel add-in idea which hopefully will end up being a tutorial...
9. By Patrick. Comment posted 11 February, 2010 10:01pm

   My last Excel task was rewriting a VLOOKUP formula to use a MATCH expression for the return column in the lookup table instead of COLUMN so I could avoid physically reordering some data.
10. By MikeD. Comment posted 11 February, 2010 11:14pm

    Putting a friendlier interface on an existing sheet so my son can analyse Lat/Lon data from GPS. The sheet already worked, but I couldn't explain it to anyone else.
    
    - Scale 1:1 scatter chart of the track line
    - Gradient-colour the track line with speed (red - fast, blue-slow)
    - Pick segments of track with mouse to average data
    - Save data as labels, if req'd
    - Copying formulae to match input data rows
11. By Gordon. Comment posted 11 February, 2010 11:28pm

    I'm just up, so the last time I used Excel was about 13 hours ago. I was using PerfMon (supplied with PED) to tweak an existing application's database exchanges. Worked well too, got a report that was taking about 57 seconds down to under 4
12. By Joe. Comment posted 12 February, 2010 12:25am

    I'm using Excel right now - I'm trying to figure out a way to show, on a scatter chart, not only the X & Y co-ordinates, when I hover my mouse over the data points, but the locations next to the data as well . . .
13. By Jeff Weir. Comment posted 12 February, 2010 12:38am

    I just crashed it. It's the only thing I drive while drunk.
14. By Mike Woodhouse. Comment posted 12 February, 2010 1:26am

    I just started off a task that downloads a pile of daily files from S&P relating to a set of stock indices that we created. The workbook wil load the data into Oracle, then recalculate the indices one at t a time so I know what S&P got wrong this time...
15. By Raymond Allan. Comment posted 12 February, 2010 1:27am

    Just finished writing a query / program to track sales forecast changes on a day to day basis, the program also acts as a safety net in the event the demand analyst has made an error.
16. By Doug jenkins. Comment posted 12 February, 2010 3:49am

    Finished an application to calculate creep and shrinkage effects over time in a reinforced concrete section, and posted it to my blog.
    
    5 minutes ago.
17. By Stephen. Comment posted 12 February, 2010 6:07am

    3 hours ago I updated my daily weight log - same as yesterday, down on week start, but up on year start. Oh well.
    
    Yesterday I updated my list of CDs (remember them?) and LPs (remember them?). Lifetime spend is in five figures (UKP). Ouch!
    
    2 days ago I generated (pseudo-)random lottery numbers for the Eurolottery. At well over 100 million UKP this weekend, you'll know if I win by the absence of future comments!
18. By Glenn. Comment posted 12 February, 2010 6:38am

    I used it 2 days ago to do check digit schemas with modular arithmetic to calculate ISBNs and UPC codes.
    
    That same file also does Caesar ciphers, random Caesar ciphers, rail fence encoding, linear shift encoding and I am trying (and failing) to get it to do matrix encoding.
    
    Yes I am a teacher who uses it in the classroom!
19. By Andy. Comment posted 12 February, 2010 6:57am

    Just update my weight tracking spreadsheet to include time spent on my new elliptical machine. I had to update my chart from a pivot chart to a regular chart with dynamic series to be able to plot my weight and exercise time on the same chart but two axes.
20. By Dick Kusleika. Comment posted 12 February, 2010 7:06am

    30 minutes ago I updated Results.xls for my Thursday night four-point pitch league. Still tied for first
    
    http://dailydoseofexcel.com/excel/Results.pdf
21. By SilverSTreak. Comment posted 12 February, 2010 7:27am

    I used Excel yesterday to record service tasks done to my motorcycle and record refuel information for mileage tracking.
22. By Mike Alexander. Comment posted 12 February, 2010 7:52am

    1 Day ago. To Figure out how many pages and screenshots on a word template equals a 350 page book.
23. By Dave Roberts. Comment posted 12 February, 2010 7:55am

    I converted weekly sales into quarterly sales for the last six quarters and created a pivot table chart. I wish it was easier to convert the data to fiscal quarters versus calendar quarters. Or maybe my boss could convert the fourth quarter of 2009 into the first quarter of 2010 in his head obviating the need for conversion.
24. By Hui.... Comment posted 12 February, 2010 8:00am

    I answer Question on Chandoo's Pointy Haired Dilbert Forums.
    3 hrs ago, The last thing I did was a quick check of
    =+SUMPRODUCT(1*(A2:A11>=E3)*(A2:A11<=E1)*(C2:C11="Closed"))
    against the data set that had been put forward
    I check each answer I write just in case.
25. By Ben Welman. Comment posted 12 February, 2010 8:18am

    I worked at my VBA module "Sudoku helper" in Excel. I made a lot of progress today.
26. By Bill. Comment posted 12 February, 2010 8:31am

    Just a few minutes ago at work. I copy and pasted today's foreign exchange rates (from on internet site) into a spreadsheet, which I then manually key into our computer system. The spreadsheet is kept for a record.
    
    I use Excel all day at work for various tasks. Sure beats a 10 key calculator and column paper that I used when I first started in accounting 30yrs ago. It could take half a day to do what I do now in minutes, ei. getting a spreadsheet to balance.
27. By John Walkenbach. Comment posted 12 February, 2010 8:41am

    Bill, have you tried using a refreshable Web query to get those rates? It could save you 30 seconds every day!
28. By Omar. Comment posted 12 February, 2010 9:16am

    I had a spreadsheet refreshing its data (pulling sales data out of our business system so I can do the monthly compensation reports for our sales people) so I dropped into my news reader and read this post. Earlier today, I reviewed a new spreadsheet with my boss for his input on changes to make (some call that critique) before going live with it.
29. By Gregory. Comment posted 12 February, 2010 9:40am

    Yesterday I finished requested changes to client workbook by making VBA programming revisions and additions. (3.2 hours)
30. By Bill. Comment posted 12 February, 2010 9:42am

    John, unfortunately I don't know how to set up a web query but I'll look into it. Thanks.
31. By Jon Peltier. Comment posted 12 February, 2010 10:31am

    I took the "Microsoft PowerPivot Excelerators Quiz" and it claimed I got one wrong.
    
    Q: What's the shortcut for Inserting a Row.
    
    Well, everyone knows that it's Alt-I-R, but the "official" answer is Alt-H-I-R. Turns out, they were asking specifically about Excel 2007, and everything in 2007 takes more effort, either more clicks, or moving the mouse further, or in this case, typing an extra character.
    
    Alt-I-R still works in 2007, so it's actually the correct answer, since it's the shorter shortcut.
32. By m-b. Comment posted 12 February, 2010 10:31am

    A couple of hours ago I used it to convert monthly budgets for 2010 to weekly budgets at work.
33. By JIm. Comment posted 12 February, 2010 12:02pm

    I created a lottery analysis worksheet based on conversations at lunch - it uses web queries to update the jackpot information from the Illinois lotter website, and web queries from Bloomberg.com for current US Treasury quotes used to calculate lump-sum payouts. Added a simple form control button with a macro that executes the 'refresh all' when clicked.
34. By Halford. Comment posted 12 February, 2010 2:37pm

    Recreated a set of pilot points for a groundwater flow model, estimated likely transmissivity between two streams, and plotted a bar chart of operating periods of evapotranspiration sites for a poster.
35. By Murray. Comment posted 12 February, 2010 2:39pm

    Tabulated applications (rows) for a vacant position in my MD group against criteria (columns). Filtered on criteria. Used the workaround for saving a custom filtered view: View>Custom>Add View.
    
    Gripe: This is OK until you add a new row to the sheet, then the
    custom view fails to include the new rows in the filtered view.
    
    Any fix for this limitation of Excel 2007?
    Murray
36. By Tom Gleeson. Comment posted 13 February, 2010 5:47am

    Two days ago; spent the afternoon trying to better understand a problem I've encountered with the Application.Caller feature in Excel 2010 and wrote a quick blog post detailing the problem http://blog.gobansaor.com/2010/02/11/excel-2010-application-caller-bug/
    
    Tom
37. By Michel. Comment posted 13 February, 2010 7:55am

    10 minutes ago I updated my exercise log and learned from the various charts that my weight loss program is on pace for me to win my bet.
    
    Last night I managed various lists of user stories/requirements and story points from an important client. For this, I used my favorite PUP function (alternate row shading), filtering, pivot tables and charting to validate the team’s burn-down and burn rates. The client is very happy!
38. By OmarF. Comment posted 13 February, 2010 8:30am

    Dave Roberts, #23. Your issue with business year is what singlehandedly stops me from using pivot tables for many of the things I do.
39. By John Walkenbach. Comment posted 13 February, 2010 10:09am

    OmarF and Dave, can't you create groups in the pivot table? Or maybe use a calculated field?
40. By Ken. Comment posted 13 February, 2010 3:43pm

    I had a friend that had a mailing list, and wanted to print envelopes and postcards from the list, and keep track of what had been printed and when.
    
    I created a sheet based Excel program that would allow him to print a single envelope or card, or a batch.
    
    He can now print a range of records say 1-21 or 1,015 to 1,175, etc, or 1-21, 83-95 and it keeps track on the mailing list sheet when and what was printed.
    
    I also made him a program that would create financial scenarios and then create a Power Point Presentation from the entries in Excel.
    
    What use to take him four hours to complete now takes a whopping 15 seconds.
    
    My friends say I'm amazing, I just tell them it's because I have bought and read all of John Walkenbacks books. It's so much easier when you learn from the master!
    
    Thanks John!!
41. By Anibal Fraquelli. Comment posted 14 February, 2010 9:20am

    Was programming (re-doing) some modules to use a different connection string to a server (we moved from SQL server to Oracle databases for some payroll reporting) 1 hour ago
    
    And (on a side, we usually dont do this staff at work time updated my personal workbook of tango related to class students.
42. By simlaoui elhassan. Comment posted 14 February, 2010 10:38am

    right now!
    i am creating an example for a friend to explain why index and equiv are more useful than vlookup.
    long life to your books and to your style of writing.
    by the way John ; is it difficult to write a book about excel?
    what is your story about your first book ?
    i plan to beggin writing in excel field.
    yours
43. By Stephen. Comment posted 15 February, 2010 6:08am

    Further to #17: my Excel-generated numbers won the Eurolottery! Unfortunately it was only 6.70 UKP = 61 pence per syndicate member.
44. By Phil. Comment posted 15 February, 2010 7:40am

    I am checking contracts entered into an Excel list. To be honest, it's not that nice, because the data quality is very poor.
    
    Hope to do some dashboarding in the evening (sounds like a new extreme sport, related to snowboarding or so, right? )
45. By Daniel FR, Germany. Comment posted 26 February, 2010 9:50am

    I overlooked a movie script in an Excel file a few days ago, that was offered to me as a translation job (english-german). I declined because I had the choice to take this or another job, which I finally took. The other job was actually a Word file.
46. By John B. Comment posted 05 March, 2010 11:48am

    I was trying to write a custom function that would count the number of italicized cells in a range. I discovered that a text string italicized by conditional formatting is not that same as actually italicizing the text.
47. By K.D. Bryant. Comment posted 08 March, 2010 12:02am

    A work colleague just came by and asked if I knew how to put a bullet list in to one Excel cell. This is what I came up with:
    
    Use Alt-Enter to add the second line of text in to the cell. Start each line of text with a lowercase t. Format the lowercase t with Wingdings and voila, you have a diamond shape bullet list.
48. By Bob Phillips. Comment posted 10 March, 2010 8:17am

    John B, you can still count them. I have an example at http://xldynamic.com/source/xld.CFConditions.html which counts those cells that are coloured a given colour, but it can be adapted.
49. By Bob Phillips. Comment posted 10 March, 2010 8:18am

    @K.D. Bryant
    
    it would be simpler to tell them to use the right tool!
50. By K.D. Bryant. Comment posted 24 March, 2010 1:00am

    Bob,
    You are correct, but in this case the person was doing a presentation to his work group where most of it was showing the results of his modeling, then working thru different scenarios; all of it from within EXCEL. It didn't make sense to pop over to Power Point for just one slide then pop back to EXCEL.
51. By Georg. Comment posted 27 March, 2010 2:19pm

    I am currently programming a competence evaluation system for use in a small government institution in Denmark.
52. By Fred C. Comment posted 14 September, 2010 4:35pm

    Just did a reify on our home wanted to check the payments, the rate was too good to pass up (4.375) came out great. However, I did note and am really wondering, what the, I noted that the posting dates are in March 2010 - this just appear on my RRS feeds?
53. By John Walkenbach. Comment posted 14 September, 2010 4:46pm

    I turned comments off when I was on vacation, and forgot to them on again until yesterday. Maybe that triggered it?
    
    It's not in my Google Reader.
54. By Wendy!. Comment posted 17 September, 2010 5:45am

    On Which Computer? I Excel at Multi-tasking.
    I am comparing two proposal prices on one and reconciling enrollment data on another.
    I think I have set a new record. I just opened a new workbook and it is Book 64. Since I use a laptop I reboot twice daily and it never gets that high! Working too hard.
    I am pivoting tables galore!