Daily Dose of Excel

Language Detection

Daily Dose of Excel — Mon, 15 Mar 2010 13:00:05 GMT

Carim asked an interesting question: Do you think a UDF could determine the string language … ? In Switzerland, there are three national languages (German, French, Italian) and it would be very handy to have an UDF return in which language a given string is written My first thought was to create a group of common words in [...] Read More......(read more)

Importing text files in an Excel sheet

Daily Dose of Excel — Thu, 11 Mar 2010 09:27:54 GMT

Hi there! Many times it is a newsgroup post which triggers me to do a writeup on a specific subject. This time, a user asked how he could import a csv file every month, without having to go through the hassle of renaming the file and re-defining the import settings each time. Opening the file in Excel [...] Read More......(read more)

Using the MATCH Function for a Linear Search and a Binary Search

Daily Dose of Excel — Wed, 10 Mar 2010 20:43:01 GMT

Given the problem I have with images and blog posts, I’m trying something new. I uploaded the content of the post to my website and am using an iframe to post it here. Yes, the UI experience is somewhat different than scrolling the entire browser window but hopefully it is something most people [...] Read More......(read more)

Keno Odds

Daily Dose of Excel — Tue, 09 Mar 2010 21:37:44 GMT

We were trying to figure out the odds on keno the other day. There’s something about statistics that doesn’t create appropriately strong synapses in my brain. I got an A in stats in college, so I know I can retain the information long enough to be tested on it. But put me [...] Read More......(read more)

Getting Array Data from a Filtered List in VBA

Daily Dose of Excel — Mon, 08 Mar 2010 21:50:31 GMT

Getting data from an Excel sheet into an array is usually best accomplished with a statement that looks something like this: vMyArray = Sheet1.UsedRange.Value I thought it would be keen to fill an array from a filtered list, so I coded Sub ArrFilteredList() Dim vArr As Variant vArr = Sheet1.UsedRange.SpecialCells(xlCellTypeVisible).Value [...] Read More......(read more)

Plain and Pretty

Daily Dose of Excel — Fri, 05 Mar 2010 11:30:44 GMT

Mike Alexander tells me that I have to sell, so sell I shall. First, the pretty: Microsoft MVPs Jon Peltier (Peltier Technical Services) and Mike Alexander (DataPig Technologies) are joining together again to bring you our acclaimed Excel Dashboard and Visualization Bootcamp! This 3-day boot camp is designed for Excel users who need to more effectively synthesize [...] Read More......(read more)

Office Automation

Daily Dose of Excel — Thu, 04 Mar 2010 13:00:58 GMT

Someone asked if anyone automates Office applications any more. That struck me as kind of funny. If I had to guess, I would guess that there are more lines of VBA code (poorly written or not) that automate Office applications than all the COBOL, VB6, PHP, and any other language you can think [...] Read More......(read more)

PowerPivot - Part 3 of 4: Conditional Shape Colors

Daily Dose of Excel — Wed, 03 Mar 2010 02:13:11 GMT

Part 1: http://www.dailydoseofexcel.com/archives/2010/02/26/powerpivot-part-1-of-4/ Part 2: http://www.dailydoseofexcel.com/archives/2010/02/27/powerpivot-part-2-of-4-prepping-the-census-data/ As mentioned in the Part 1, I had developed this technique to conditionally color shapes some time ago. Based on customer feedback and my own experience I made changes to simplify the various connections required to make it all work. Also, documenting the technique was easier to do with a webpage [...] Read...(read more)

Web Query Lessons

Daily Dose of Excel — Tue, 02 Mar 2010 14:57:31 GMT

I’ve recently learned some things about Web Queries that I’d like to share with you. But first a little background. You create a Web Query in Excel 2007 by clicking From Web on the Data tab. This launches a browser within Excel through which you navigate to a web page and select a [...] Read More......(read more)

IsHex Function

Daily Dose of Excel — Mon, 01 Mar 2010 22:18:14 GMT

I have a need to verify some user input is hexadecimal (0-9 and A-F). Public Function IsHex(sInput As String) As Boolean Dim lResult As Long On Error Resume Next lResult = CLng("&H" & sInput) IsHex = sInput = "0" Or lResult > [...] Read More......(read more)

PowerPivot - Part 2 of 4: Prepping the census data

Daily Dose of Excel — Sat, 27 Feb 2010 21:24:35 GMT

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......(read more)

PowerPivot - Part 1 of 4

Daily Dose of Excel — Sat, 27 Feb 2010 03:33:07 GMT

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......(read more)

Listing Formulas

Daily Dose of Excel — Fri, 26 Feb 2010 13:00:39 GMT

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......(read more)

Drawing in Excel

Daily Dose of Excel — Thu, 25 Feb 2010 16:02:16 GMT

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......(read more)

Sheet Tab Colors

Daily Dose of Excel — Wed, 24 Feb 2010 02:16:02 GMT

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......(read more)