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

October 2009 - Daily Dose of Excel

Sort by: Most Recent | Most Viewed | Most Commented

  • Dynamic Ranges in Charts

    You may remember the Bang-Bang Servo diet (that I mistakenly called the Big Bang Servo diet). Well, it’s a hit. I was setting up a spreadsheet for someone this week and I noticed that I didn’t make the chart ranges dynamic. That is, it always charts 90 days because that’s how I [...] Read More...
    Filed under:

  • Borders on Single Columns or Single Rows

    I found a bug this morning (my bug, not Microsoft's). I have a macro that creates the illusion of gridlines for when I fill the interior of a range. It does so by adding gray borders everywhere. Before After> The old macro looked like this Private Sub cmdGridlines_Click()       With Selection         Selection.BorderAround xlContinuous, xlThin, [...] Read More...
    Filed under:

  • A quick tip - Working with arrays with unknown bounds

    Two recent instances required transferring data from one array to another. One was a solution to speed up the use of COUNTIF for each element in a large range testing against another large range. In the process I had to build several building blocks including a replacement for the native Excel TRANSPOSE function, [...] Read More...
    Filed under: , ,

  • The Whole Column

    Nothing wrong with this sheet, right? Until you get to the end… The teeny-tiny vertical scrollbar handle is usually a give away. Hundreds of rows tacked onto the end of the list to give the illusion that the whole column has been formatted. The trouble with formatting columns as per the above image is that it causes the [...] Read More...

  • Arranging Multiple Windows

    Surely you know that you can Windows - Arrange to put two workbooks side-by-side or top-to-bottom. But sometimes I have a bunch of workbooks open and only want to look at a couple of them. If I minimize the windows I don’t want, I can arrange the non-minimized. In this video I want to [...] Read More...
    Filed under:

  • Numbers in Disguise

    It is possible to get a cell to display one thing, but store another. Create a Custom Format by right-clicking the cell, select Format Cells…, then from the Number tab select Custom. In the Type box use whatever text you want displayed, but be sure to enclose it in “double-quotes”. I admit, this is a pretty dirty trick, [...] Read More...

  • Converting Cells Formatted as Text

    Excel has a Text format that can be applied to cells. When a cell is formatted as Text, anything you type in the cell will appears in the cell. That may seem obvious, but it applies to numbers and formulas too. For instance, if you type a formula in a cell formatted [...] Read More...

  • The Currency Format

    If you type a number into an unformatted cell in Excel, that number is stored in the Double data type. When you format that number, you show it in a specific way, but you don't change the number. For instance, if you format the number 1 as a Comma style, you get 1.00. [...] Read More...
    Filed under:

  • QueryCell

    QueryCell is an Excel add-in that let’s you use SQL statements against tables in a workbook. I downloaded the trial and tested it out. Here’s what I found. Email on Download QueryCell requests your email when you download the trial. Sam Howley is the owner of the company and a regular at the [...] Read More...
    Filed under: ,

  • Summing the Digits of a Number

    Summing the digits of a number is a chore I’ve been doing alot lately. Originally I’d parse the number out over the columns. And since SUM() ignores text, I’d turn the characters into digits by applying an arithmatic identity operation, like this: =- -MID($A10,COLUMN(),1) That’s double minus signs before the MID() function. The reasons [...] Read More...
    Filed under: ,

  • MVP-ness Envy

    It appears that someone at Microsoft has dropped the ball and awarded me again as an MVP. I remember the days when I would spend hours in the newsgroups. Now I get in there a couple of times a month. Once there, I realize that the questions are the same as they [...] 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.