Spy Journal Excel Tips

Thursday, March 31, 2005

Creating Subtotals in Excel

To create subtotals you need to ensure the following parameters are correct.

1 You need to have a database (fields and records or columns and rows) filled with data. For example this could be a list of stock items, their cost value, quantity on hand and total value on hand (Cost x Qty). They might be categorised into product types and locations.
Eg 6 columns named Item Name, Item Type, Location, Cost Value, Qty on Hand, Total Value
2 The database needs to have a header row with the header labels in it as per the example above.
3 The data needs to be sorted by the subtotal fields. In other words if you were going to subtotal this example by each Item Type, then you would need to sort by Item Type. Further sorting by any other columns is entirely optional in this case. However to make the second part of the exercise work sort also by Location. Thus the data is ordered by Item Type, and then within each Item Type sorted by Location.
4 Click anywhere in the data and and then select Data | Subtotals.
The subtotal wizard will automatically recognise the headings and give you the option of selecting which field you want to subtotal by. In this case select the following:
- Item Type in the box labeled At each change in,
- Sum In the Use Funtion box,
- In the Add Subtotal to ensure that the Qty and Total Value fields are checked. In other words check every field that you want a subtotal for.
- Leave Replace current subtotals and Summary below data checked and optionally select to Page break between groups.
- Select OK and the subtotals will be created. Note that the the subtotal rows have been inserted using the Subtotal function. On the left hand side the grouping lines will have been added with the outline levels indicated at the top.

For a more complex subtotal, eg by location as well simply click Data | Subtotal again.
- This time turn off the Replace Current Subtotals,
- Ensure that you are subtotaling at each change in Location.
- Selecting OK will add another level of outline and subtotals for each location within each item type.

An example is shown below.

Monday, March 28, 2005

Turning Excel Auto Filters on and off in VBA

I often have to manipulate large tables of data in Excel using VBA code. These tables often will have an autofilter on them, and it is possible that they have been left with a filter on thus hiding cells.
Here is the code I use to get around this.

If ActiveSheet.AutoFilterMode = True Then
Selection.AutoFilter
End If
Selection.AutoFilter

This turns the filter off if its on effectively showing all data and then turns it back on.

This is not the most efficient code as using error traps on ActiveSheet.ShowAll would be a better way of doing it. But for most applications this is the quickest easiest way to do it.

Saturday, March 26, 2005

Selecting a variable range using VBA code

I often have to use VBA code to select a range of cells that I don't know the exact shape of in Excel.
Sometimes I might know the size of one of the two dimensions, Columns or Rows. Sometimes I know neither.

How I do this is using the following steps.

First I name a range in the sheet of the starting cell (usually the leftmost top cell).
To do this I click on the cell and use Insert | Name | Define. I would usually name the cell range_start or similar.

I also make sure that the data is contiguous. That is there are no blank spaces in the ranges. If this is not possible I at least do it for 1 column and 1 row (preferably the top row and the left column but can be any ones.) If not then I hope that I know the maximum dimensions of either columns or rows.

The code is fairly simple.

Application.Goto reference:="range_start"
Range(ActiveCell, ActiveCell.End(xlDown).Offset(0, 14)).Select
Selection.Copy

This piece of code will select from the starting cell down to the bottom of the range then 14 columns right (giving 15 columns in total). It will then copy this selection to the clipboard.
The main part of the code is using the Range.Select method.
This uses the syntax Range(start cell,endcell).Select
I use starting cell as the activecell, then use the activecell and a combination of Offset and End functions to locate the end cell.

There are many more complex ways to do this including selecting CurrentRegion etc. However this way is probably the simplest easiest way to do it.
The most important thing to do is not in the code at all but to ensure that the data you are working with is clean and organised properly.

Thursday, March 24, 2005

Backing Up Customized Changes to Toolbars and Menus in Excel

This weeks tip comes from exceltip.com

The customized changes made to menus and toolbars are saved in a file with the extension .xlb.
In Excel 97 and Excel 2000, the file name is the user's name followed by .xlb.
In Excel 2002 and Excel 2003, the file name is Excel.xlb.

To backup the file:

1. Search for a file with the extension .xlb.
2. Copy the file and store it in a safe place.

Monday, March 21, 2005

Colouring Sheet Tabs in Excel

Lotus 1-2-3 has had coloured sheet tabs for years. However Microsoft finally added this feature (admittedly cosmetic only) in Office 2003.
To colour your sheet tabs click Format |Sheet |Tab Colour. Choose your colour and click OK.

You can colour mutiple sheet tabs at once by selecting multiple sheets first.
When a sheet is selected (active) then you cannot see the colour; however a small thin line under the sheet name shows.

For the Americans - colour is spelt the English way :)

Friday, March 18, 2005

Printing repeating rows and columns in Excel

Excel has a great function to assist with printing large spreadsheets that are going to cover more than one sheet of paper.

It is often useful to be able to repeat the left column(s) and top row(s) on each page.
The way to do this is to go to File | Page Setup and click on the Sheet tab.
Now click in the Rows to repeat at top section and then highlight the actual rows in the spreadsheet (behind the dialog box) that you want to repeat at the top of every page. Do the same for the columns if you want columns to repeat at the left.

Its always a good thing to use the print preview to check this.

The VBA code for this is:

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$2"
.PrintTitleColumns = "$A:$B"
End With

This will repeat rows 1 & 2 and Columns A & B on each page printed.

Wednesday, March 16, 2005

Recovering Data from a Corrupted Workbook That Cannot Be Opened

Todays tip comes from ExcelTip.com

To recover data from a corrupted workbook:

1. Add a new workbook, and save it using the name of the corrupted workbook, but in a different folder than the original (in the screenshot, the workbook name is Financial Statements.xls).
2. Change the new sheet names to those in the corrupted workbook (try to remember all of them), and then save the workbook.
3. Add a new workbook. In cell A1 of the first sheet, insert a linked formula to cell A1 in the newly created workbook (steps 1 and 2).
4. Copy the formula to all cells covering the data area (as you remember it to be) in the first sheet of the corrupted workbook.
5. Repeat steps 3 and 4 for all sheets, and then save and close the new workbook.
6. Change the linked source address. From the Edit menu, select Edit Links, and then click Change Source.
7. Locate the corrupted workbook, select it and then close the Edit Links dialog box.
8. Check the result. If the data appears in the new workbook, copy all cells in each sheet and paste them back as values.

Tuesday, March 15, 2005

More on arrays in VBA

An array can be declared using either the Dim or Public statement. To show that the variable is an array () are placed at the end of the name. Eg MyArray()
If you don't know how big the array needs to be for example when working with a table of a variable size then there are some simple ways to determine the size and then the Redim command is used to redimension or resize the array. Eg Redim MyArray(3,5).

To determine the size of a variable array I would use something similar to the following code most times.
The reference "array_start" is a named range in the file at the upper left most cell of the range to be selected for the array. This also assumes that there are no empty cells in the data.

Dim MyArray()
Dim lowercol, uppercol, lowerrow, upperrow As Single
Sub test_array()
Application.Goto reference:="array_start"
lowercol = ActiveCell.Column
uppercol = ActiveCell.End(xlToRight).Column
lowerrow = ActiveCell.Row
upperrow = ActiveCell.End(xlDown).Row
Range(ActiveCell, ActiveCell.Offset(upperrow - lowerrow, uppercol - lowercol)).Select
ReDim MyArray(upperrow - lowerrow, uppercol - lowercol)
MyArray() = Selection
End Sub

Thursday, March 10, 2005

Excel VBA Array examples

Microsoft has created a support page of examples of arrays in VBA.
I was explaining this stuff to one of my clients yesterday.
Basically an array in VBA is simply a collection of rows and columns held virtually in incrementing variables.
Eg my_array(3,5) would contain 3 'rows' and 5 'columns' of data. In total 15 pieces of data can be held in this array.
Thanks to Mark from Automate Excel for the link.

Tuesday, March 08, 2005

Using the F9 key in the formula bar in Excel

If you have a long formula in the formula bar and you want a quick way to see the results of part of the fomula then you can use the F9 key. WARNING! Be careful how you use this because this will actually change the formula if you save the changes.

Select the part of the formula you wish to resolve in the formula bar and press F9. The result will replace the formula. Eg =IF(C4<(C5*1.5),TRUE,FALSE) where C4=10 and C5 = 14.
Select the C5*1.5 section and press F9. The selected text will be replaced with 21 which is 14*1.5.
Press ESC (Escape) to undo the changes and stop editing the formula.

Alabama Paradox

Managing statistical results in Excel can be tricky.
The most important thing to do is to work out what the data you are manipulating means and what result you are trying to get.

The Alabama Paradox is a statistical nightmare and has resulted in numerous debates of the American Senate.
Find a full pdf doc here that explains how averaging and rounding can really not be quite as simple as it sounds.

Monday, March 07, 2005

Compare Cells Answer

Shantanu Oak has commented on my post regarding comparing two cells.

It seems that "Erlandsen Data Consulting" has VBA Code to compare two ranges or two worksheets.
http://tinyurl.com/4rap9

I myself, use Spreadsheet assistant for that purpose.
Spreadsheet assistant can be downloaded from...
http://add-ins.com

If you have the "Spreadsheet Assistant" installed, follow these steps.
1. Select the range you want to compare. Choose ""All the Assistants"" from Quick Menu.
2. Under Special Assistants choose ""Compare Two Columns - Stop When Not Alike...""
3. Click on Ok to accept the first range. Select the other cell range in the next dialog box. You can color the cells green if the contents are different.

Thanks Shantanu

Friday, March 04, 2005

How do you make Excel show the difference between two selected cells

I was asked this very interesting question a couple of days ago.
I have to admit I am stumped. I have also wondered this for many years and I have never found a way to do it. Of course you can subtract one cell from another but often I will have two cells on a spreadsheet with values in them that I am trying to reconcile. If they do not balance then I want to know how much I am out, or the difference between the two cells values.
If one is a negative and one a positive number then selecting them and using the sum in the status bar will give you a result.

The task - determine the difference between two positive numbers in selected cells.
Do any of you readers know how to do this? UDF's (User Defined Functions) are an acceptable solution.

Tuesday, March 01, 2005

Autumn in Excel

Welcome to the first day of Autumn (for those on this side of the planet).
Make Excel come to life with the season.
Add a colorful background to your excel spreadsheet.
I recommend the Autumn.jpg file (one of the default desktop backgrounds in Windows XP - found in C:\WINDOWS\Web\Wallpaper) I have also attached it here.

How do you do it?

In Excel click Format | Sheet | Background and then browse for the picture you want.

Remember adding backgrounds will add to your Excel file size so be careful if this is an issue.

Calendar Picker Addin for Excel

Thanks to Mark from automate Excel for this one.

Sam Radakovitz , the guy who created the Halo Spreadsheet(recently updated) has made an extremely nice Calendar Picker Addin.

Load it up like a regular addin, then any column with a date as the column header presents the user with a popup to choose the next date, instead of having to type it manually.

Many nice features, too bad it's PW protected (share the love Sam!), Transparent effects, fade in, fade out, a cool Smart Tag like icon that is embedded in the cells to activate the Calendar.

One of the nicer Calendar addins available, worth the download to use, or get some ideas.

Copyright Notice. All documents and text contained in this web site www.spyjournal.biz and sub pages is copyright material of Jethro Management (c) 2004 unless noted as being copyright material of someone else. No public reproduction of the content on this site in any form is permitted without express written permission.

Unless otherwise expressly stated, all original material of whatever nature created by Tim Miller and included in this weblog and any related pages, including the weblog's archives, is licensed under a Creative Commons License.