Daily posts of Excel tips…and other stuff
Archive for the ‘User Interface’ Category.
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. [...]
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 [...]
Let’s say that you want to open a specific file when you start Excel. Here are some options:
XLSTART
If you have Excel installed, you have a folder called XLSTART. Mine’s here C:\Documents and Settings\Dick\Application Data\Microsoft\Excel\XLSTART. On my Windows 7 machine running Office 2010 Beta, it’s here C:\Program Files\Microsoft Office\Office14\XLSTART\. (Are we putting [...]
On Simon’s blog, sam comments regarding OFFSET vs. INDEX:
There is a huge performance hit.
…
You will notice hardly any difference in the Calculation times
But the recalculation times will significantly different in case of OFFSET- to the tune of 25-30 times slower compared Index.
I’ve heard this many times before, but I guess I’m just lazy. I’ve [...]
Hi all,
I decided I need a bit more content on my site for the average to intermediate Excel users:
Using controls on worksheets
Apart from input cells chained to cells with formulas, there are other options in Excel to make your spreadsheet model more interactive. You can add option buttons, check boxes and list boxes to your [...]
In New Zealand, we format our dates dd/mm/yy. However, the United States format as mm/dd/yy.
When I see a date by itself, like 12/01/09, I ask “is this the 12th of January or the 1st of December”?
When I started working with Oracle RDBMS, a habit I picked up was to format dates dd-mon-yyyy.
This avoided the dd/mm [...]
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 [...]
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 [...]
