Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

February 2010 - Daily Dose of Excel

Sort by: Most Recent | Most Viewed | Most Commented

  • PowerPivot - Part 2 of 4: Prepping the census data

    Part 1: http://www.dailydoseofexcel.com/archives/2010/02/26/powerpivot-part-1-of-4/ Since Excel 2007 (and later) can handle a million rows of data, I figured it made sense to explore PowerPivot (PP) with a data set larger than that. In addition, I have had a long time curiosity about the US census data. So, this seemed an appropriate time to combine the [...] Read More...
    Filed under:

  • PowerPivot - Part 1 of 4

    After the MVP Summit I was motivated to further explore a Microsoft add-in for Excel 2010 called PowerPivot. I don’t know all its capabilities but at the very least it can analyze millions of rows of data. If you are using Excel 2010 you can download the add-in from http://www.microsoft.com/downloads/details.aspx?FamilyID=48a5b47b-8c9c-450f-ab6e-178600a733ca&displaylang=en Do keep one thing [...] Read More...
    Filed under:

  • Listing Formulas

    This code will list all the formulas in the selection in the Immediate Window. Sub ListFormulas()         Dim rCell As Range         If TypeName(Selection) = "Range" Then         For Each rCell In Selection.Cells             Debug.Print String(4, " ") & rCell.Address(0, 0), rCell.Formula         Next [...] Read More...
    Filed under:

  • Drawing in Excel

    Brad sent me a link to this cool YouTube video http://www.youtube.com/watch?v=4YG_WWZYqUs Libro1? Is that Italian Excel? Then there’s this: http://www.youtube.com/watch?v=8oR1_PA3yTw And you thought 3D charts were useless. Read More...
    Filed under:

  • Sheet Tab Colors

    CR rants: One thing about Excel 2007 that really bugs me are the tab colors. Most of the choices look so washed out, rather than a nice solid color. One does not have a choice when Excel makes the tab text white or black. Sometimes, Excel chooses a white font for the text [...] Read More...
    Filed under:

  • New RDBMail Mail add-in for Excel/Outlook 2007-2010

    Hi all FYI I upload a new add-in named RDBMail for Excel/Outlook 2007-2010 this week http://www.rondebruin.nl/mail/add-in.htm The add-in create a new tab on the Ribbon named RDBMail with a lot of mail options and a option to insert a worksheet template to your workbook so you can send more then one mail with one click because the info for each mail [...] Read More...
    Filed under:

  • Consolidating Timesheets

    PHD did a post recently about consolidating data. I do this every other week with timesheets, so I thought I’d share my story. I get timesheets as Excel workbooks via email and I save them to a special folder. I open them one-by-one and record the data on a summary sheet. Here’s the main [...] Read More...
    Filed under:

  • Web Apps

    I came across a couple of interesting web applications recently. The first is AccountingASAP.com. Offering free invoicing is a great way to get their name out there and encourage users to check out their for-pay stuff. It reminds me a little of the free version vs. pro version model, but there are differences. [...] Read More...
    Filed under:

  • OLAP Catastrophic Failure

    I’ve never needed to use an OLAP cube before recently. I’ll admit that I didn’t see what all the fuss was about. But I came upon a problem for which an OLAP worked perfectly. Theoretically. In practice (Excel 2003), I experienced error after error. I rebuilt the cube from scratch several times. [...] Read More...
    Filed under:

  • Formula Tips

    Al sent me a formula to revise. Before: =IF($B11<>0,IF(OR(ISNUMBER(FIND("-",J11))=TRUE,ISNUMBER(FIND(" ",J11))=TRUE,ISNUMBER(FIND("_",J11))=TRUE), IF(ISNUMBER(FIND("-",J11))=TRUE,TRIM(SUBSTITUTE(J11,"-","")),IF(ISNUMBER(FIND(" ",J11))=TRUE,TRIM(SUBSTITUTE(J11,"  ","")),IF(ISNUMBER(FIND("_",J11))=TRUE,TRIM(SUBSTITUTE(J11,"_","")),TRIM(J11)))),TRIM(J11)),"") After: =IF($B1<>0...

  • VBA Assessment

    Patrick O’Beirne sent me a link to Alex Palfi’s VBA Assessment. I assumed I would ace it, but I got 7 wrong. That’s 80%. I knew I was in trouble when I read the first question. The following figure illustrates a help system in Excel. The user can click on a tab [...] Read More...
    Filed under:

  • List Custom Lists

    DataPig posted about Custom Lists in Excel and his inability to delete them. I agree on all points; I’ve never needed Sun-Sat and we should be able to delete them if we want. Here’s a macro to list all the Custom Lists to the Immediate Window. Sub FindCustomListNumber()         Dim i As Long, j [...] Read More...
    Filed under: ,

  • Google Chrome

    This week I made the switch from Firefox to Google Chrome. The latest stable release of Chrome supports “extensions” so all those pesky annoyances that kept me away from Chrome in the past can be fixed. My only complaint with Firefox is that I have to restart it a couple of times a [...] Read More...
    Filed under:

  • ISO Year

    Ron de Bruin has an ISO Date page for computing ISO Week numbers, start dates, and end dates. I needed to compute the ISO year, which should just be the YEAR() function wrapped around the end date formula. But I was getting strange results for the first three days of January this year, [...] Read More...
    Filed under:
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.