Daily Dose of Excel

I run on a wide screen 22? monitor (1680 x 1050). More than once I’ve delivered a prototype to the customer, and they’ve needed to scroll to find all the buttons, because their monitor was smaller than mine.

Here is a procedure for resizing the Excel Application to a specified dimension (measured in pixels).
Now I can develop on my screen, knowing what it will look like on the customers.

Simply change the constants cDesiredWidth and cDesiredHeight to the target screen resolution.

Note: It’s an approximate resize – it resizes to a few pixels wider than it should.

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 “last used row” to be hundreds of rows further down than necessary. Click print and you waste a forest. It could also cause your workbook file size to bloat! In any case the vertical scrollbar’s usefulness takes a serious blow.

The answer is to format not individual cells, but the whole column.
- Select the worksheet column (or columns). The short-cut key is Ctrl+Space. In the above example, we’d highlight columns A to F.
- Right-click the selection, and choose Format Cells…
- Apply formatting the way you want.

If we did this exercise on the above example, you would notice that the column headers would also be formatted the same as the content, which is often not what we want. The trick is to format them last.

So the general order of formatting goes:
- Format the whole sheet
- Format the whole column
- Format just the column header (label)

Hopefully this diagram explains it.

This method allows your worksheet to grow while maintaining consistent formats for new rows.

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, but it may have legit uses.
Data Validation cell dropdowns also use this format. This makes it possible to select a text value from a list, but it will store the number instead!

There’s probably a limit to the number of custom formats. I’m not sure what that limit is, but I reckon it’s big, because in many of the spreadsheets I’ve worked on there are way, way too many.

I recently discovered how quick it is to create a simple Gantt chart in Excel.
The following example was created for Excel 2003. It should work the same for other versions.

Create a worksheet similar to the above table.
Note: the formula in column D2 is =C2-B2. You’ll need to set the Cell Format back to General, or column D will look like a date.

Select just the Activity and Start Date data (eg. A1:B5), then click the Chart Wizard.
Step 1 of the wizard:
Select the “Bar” Chart type, and the Stacked Bar sub-type, then click Next.
Step 2 of the wizard:
Click the Series tab, and Add another Series.
For Name, select the cell for the Duration Column Header (cell D1)
For Values, select the cells for for the Duration data (cells D2:D5)
Click Next
Steps 3 and 4 of the wizard can be skipped, though, on Step 3 you may want to hide the Legend.

The idea from here is to make the Start Date bar invisible, so only the Duration bar shows.
Double click on any one of the Bars for the Start Date data.
From the Patterns tab, set the Border and Area to None.

You may also notice the Activities are in reverse order.
Double click the Y axis (Category axis). A “Format Axis” window should appear.
From the Scale tab, tick the “Categories in reverse order” and “Value (Y) axis crosses at maximum category” are ticked.

You’re finished!

As an extra, you can force the chart to graph only between certain dates.
Double click the X axis (Value axis). A “Format Axis” window should appear.
From the Scale tab, override the Minimum and Maximum boxes with Dates (I didn’t know it could take dates, did you?!)

Sometimes I find myself copy-pasting between Excel and another application.
In this example, I have a table of three columns: First Name, Last Name, Birth Date.
My external application has 3 text boxes, one for each of those values.

I can’t just copy the 3 cells from Excel and paste them to my App, because they would all end up in the first text box!
But, by running VBA SendKeys with a specially crafted string, I can send keystrokes for tabbing to the 2nd and 3rd text boxes.

I also need to activate the SendKeys procedure only when my cursor is positioned correctly, or things could get messy.

My approach is to run a macro that sits there listening for F6 before activating SendKeys.
I’ve also included listening for the Esc key, just in case I change my mind.

I had a worksheet table with blank rows separating the groups.
I needed to add another column – a formula – but wanted to retain the blank rows for formatting tidiness.

The table after adding the formula column:

Notice the formula produces zeros for the blank rows. I could just select each cell (D3, D5, D8, D10) and hit delete, but for thousands of rows that would be time consuming.

Here’s how I did it:
- Select column C.
- From the Edit menu, select Go To…, then click Special…
- Select Blanks, then click OK

Then I ran a macro which allows me to move the selection over one column.
In this example, I typed 0, 1 for the Input to SelectionOffset.
After the Selection was moved, I hit the delete key.

El Says:
“I have a spreadsheet with dates and on each day, there are several activities with start time and end time. I want to add a row between each day, and also to add up the total time for all the activities in each day.”

This is where the Subtotals feature is quite useful.
It can add a variety of footers to appear at breaks in the group. You get outlining too.
Select your range, then from the Data menu, choose Subtotals…
At each change in StartDate, Use function Sum, Add subtotal to Duration.

Here is one for the Code Library.

Somehow I end up misplacing this bit of code. So every time I need to do it, I end up re-writing it.
Perhaps I’ll save someone the same frustration along the way.

This code snippet will loop through each file in your folder (and subfolders).
For each workbook opened, it will unprotect each worksheet using the supplied password.

From time to time, I am asked to audit a spreadsheet.

One of the common mistakes I see is an inconsistent formula inside a group of cells.
For example:
- A1 has the formula =G1 * 0.105
- A1 has been formula auto-filled to A1:E5
- Then some time later, cell A3 has been changed to =G3 * 0.107

So the assumption is that A1 can be safely auto-filled to the region of cells. However, we have an exception which should be noted.

It’s nice to be able to highlight these exceptions. Here are 2 ways.

1. If you have Excel XP and above, you can use the Error Checking options. A little green triangle appears telling you that “something strange” is going on.
One downfall of the Error Checker is that it wont trigger if the inconsistent formula is on the corner of the checked region. In our example above, if the changed formulas was A5 instead of A3, it would have ignored that inconsistency.
I assume that this is to accommodate subtotals and grand totals?

2. Write a bit of VB code using the idea that the R1C1 version of the formula should be identical for all cells in the selection, so it should be a simple loop to check all of the formulas.
Here is an example:

To use it, select the range A1:E5 then run the macro. It would colour the inconsistent formulas yellow (in the first example, cell A3).

I often use Excel’s macro recorder to perform repetitive tasks.
It’s usually a list of cells with a handful of exceptions. That is, I cant just run the macro from start to finish – I have to give each item a brief glance before the macro runs.
At the end of the macro, it’s handy to position the selected cell as the start of the next item in the list.
I’ll assign a shortcut key to the macro, such as ctrl+w, then use it on demand.
The process becomes simple: look at the item, is it ok? yes, press ctrl+w, next item, is it ok? yes, press ctrl+w… over and over.

You can reassign your shortcut keys from the Macros window:
From Excel’s menu: Tools, Macro, Macros… (or hit Alt+F8)
Highlight a macro, then click Options.

More often than not, the macro recorder will give me a good first draft but I’ll have to edit it some more from within the VB editor.
While coding the changes, I wondered where Excel stores the shortcut key.

Could it be that Excel recognises the code comment?

So I deleted the comments to be sure. No, it wasn’t the comment.

It turns out that the shortcut key is stored in the Code Module, but it’s hidden from sight.

Export the Code Module (right-click the Module, click Export File) then open it in Notepad.

You will notice a line that looks like this:
Attribute Macro1.VB_ProcData.VB_Invoke_Func = “w
14?