Excel Frequently Asked Questions

Is there a way to determine the last active column in a spreadsheet?
In a macro, you can use:
LastCol = ActiveCell.SpecialCells(xlLastCell).Column
If you have a rectangular section of data which you know starts in Row 1, then you can use this to find the last column:
LastColumn = Range("IV1").End(xlLeft).Column
Use this to find the last row:
LastRow = Range("A65000").End(xlUp).Row

I have a column of numbers in the format 99999-99999. What is a simple macro to remove the dash?
You don't need a macro. Highlight the column and use Edit - Replace. In the "Find What", enter a dash. Leave "Replace With" blank. Click "Replace All".

Hey! Excel used to have letters to indicate the columns. Now, all of a sudden, there are numbers for the columns.
You have Excel set up to use the R1C1 style of cell notation. Go to Tools > Options. Pick the General Tab. The first box in the settings section is checked ("R1C1 reference style"). Uncheck this box. Click OK. Your problem should be solved.

How can I copy the name of the worksheet tab to a cell in the worksheet?
Run a macro that has this line:
ActiveCell.Value = ActiveSheet.Name

How can I show the complete path and filename in the Excel title bar?
Run a macro that has this line:
ActiveWorkbook.Windows(1).Caption = ActiveWorkbook.FullName

How can I format a worksheet to have alternating green and white rows like a "greenbar" report?
Select the range you want to format. From the menu, select Format > Conditional Formatting.
In the left dropdown, change "Cell Value is" to "Formula Is".
In the formula box type =MOD(ROW(),2)=0.
Click the "Format..." button. Click the Patterns tab and pick a color.

How do I have EXCEL execute a macro automatically upon starting EXCEL?
Rename the macro to be AUTO_OPEN.

Or - add the macro to the Workbook_Open procedure on the code pane for ThisWorkbook.

How can I speed up my Macro execution?
Turn off the screen updating during macro execution. Insert this line at the beginning of your macro:
Application.ScreenUpdating = False

How can I get Excel to accept an entry like "5+5" without the leading equals sign as a formula? I've done this is the past but can't remember how.
Go into Tools>Options. On the Transition tab, check the box for Transition formula entry.

How can I make the screen stop flashing during macro execution?
Turn off the screen updating during macro execution. Insert this line at the beginning of your macro:
Application.ScreenUpdating = False

In visual basic, I can set the color of a cell with the .interior.colorIndex property. How can I read the color of a cell before I change it?
Use a line of code like this:
OrigColor = Selection.Interior.ColorIndex

Can I see a list of the ColorIndex and the corresponding color
To see the colors, go to the visual basic editor. Hit the help question mark. Type ColorIndex. From the list of topics, select the one called "ColorIndex Property". They have a nice visual table at the bottom of this help topic.
You could also build a color table with a quick macro

Public Sub ColorTable()     For i = 1 To 56         Range("A" & i).Interior.ColorIndex = i         Range("B" & i).Value = i     Next i End Sub 

There are a lot of Excel books in the bookstore. Can you recommend some good ones?
Yes! See my List of the Top Excel Books page.

Maybe you can help.... I use Excel not for calculation but for maintaining large mailing lists which I usually import from text files.Simple question... how do I keep Excel from dropping the leading zero when I import a 4-digit mail code??? I NEED that zero there. I understand that I can format the cells to text and THEN TYPE the number in and the zero stays... but who has time for that? Is there a way to IMPORT or OPEN a text file in excel and keep the leading zero?
In Step 3 of 3 of the text import wizard, you have to specify that your Mail Code field is text. In the step 3 dialog box, go down to the preview panel. Scroll right until you can see the Mail Code field. Click the grey box at the top of this column which probably says "General" right now. The whole column will highlight. In the upper right corner there is a box called Column Data Format. Click Text for that particular field (and for any other fields that need the leading zeroes).
Excel will keep the leading zeroes. It will *not* keep leading spaces, so I hope you don't need those.
If you have vast amounts of data that you have already imported and you need to quickly fill will leading zeroes, you can use the formula: If mail codes are in H2, enter this in I2:
=right("0000"&H2,4)
Copy this from I2 down to I9999. Highlight column I. Hit Ctrl-C to Copy, the Edit, Paste Special, Values, OK to change the formulas to text. Then copy column I over top of H.

I created a worksheet in Excel 95. In Excel 97, Excel ignores my page breaks.
If you set the Page Setup Scaling Options to be "Fit to: 1 Pages wide and (blank) Pages tall", you could insert page breaks in Excel 95. Excel 97 ignores all page breaks if you have scaling set to this option.
Well, then how do I fit something to one page wide?
Set up the document. Change the scaling to "Fit to 1 pages wide, blank pages tall. Do a page preview. Close the page preview. Back on the Page Setup dialog, Excel has now changed the "Adust to: x%" value to be the proper percentage to make your document fit on one page wide. Change your scaling option from "Fit To:" to be "Adjust to:" and leave the percentage where Excel set it. If you add columns or adjust the size of columns, you will have to repeat this trick.

I want to count the number of cells in a range that have a particular interior color. The 'countif' function seems like the right kind of answer but how to I return the color of the interior for each cell in a range? I would like to do something like: =countif(b1:b10, cell("interior","Red"))
As long as you are not using conditional formatting to turn your cells red, this will work.
It assumes you have used the red which is in the first column, third row of the interior color dropdown in Excel 97. This is known as colorindex number 3.
In Excel 95, use Insert Module. In Excel 97, use Alt-F11 to open the visual basic editor. Paste in the following code:
Public Function SumRed(Inrange As Range)
SumRed = 0
For Each cell In Inrange
If cell.Interior.ColorIndex = 3 Then
SumRed = SumRed + cell.Value
End If
Next
End Sub
Now, enter the function =Sumred(B1:B10) in your worksheet.

I've been working with downloaded data and pasting a years worth at a time into a "template" that I want all the resulting spreadsheets to look like. Each year's data has approximately the same amount of data in it. Seems each spreadsheet is taking more and more space as I work on them?? Any idea's what I've done?
Are you getting just a few percent increase with each file, or was there one file where you picked up a huge increase?
One thing to check it to see where the last active cell on the sheet is. Hit the end key, then hit the home key. (don't hold them both down together. Hit one, then the other). This will take you to the last active cell on the worksheet. If you data extends out to column H1 and down to cell A366, the End-Home should take you to cell H366.
If, for some reason, End-Home takes you to WAY below where you think your data ends, then check to see if you have a blank entry down there. Or, just delete the rows between then end of your data and that last active cell. You then have to save and re-open the file to reclaim the space previously used by those rows.
The other possibility under Excel 97 - do you have Excel set to "Track Changes"? This would be located under the Tools menu.

Where can I find some really good information on how excel treats year 2000 dates? I need simple (and that is what u guys provide).
Any 2 digit dates since January of 1930 will be assumed to be 1930 or after. Microsoft converts an entry of 1/1/30 to be 1/1/1930. (It does the conversion in memory - it may not show it in the spreadsheet)
Any 2 digit dates lower than 12/31/29 are assumed to be 2029 or before.
You can avoid the problem altogether by entering the dates with 4 years. Enter 5/27/1922 to enter a date in 1922, or 5/27/2034 for a date in year 2034.

Somehow, my worksheet has external links to files that are no longer around. How can I delete the links?This happens all of the time to MrExcel. I downloaded a little macro from Microsoft which helps you delete these links. Microsoft has now upgraded the macro to a full blown add-in. Read about it here.

I use a large spreadsheet on a daily basis with 31 worksheets (one for each day of the month). Each worksheet is huge.I would like to insert another sheet in the workbook and insert a button to copy another worksheet to the end of the workbook and then rename the sheet to the correspondingday of the month. If you worksheet names are numeric, this will do the trick:
Public Sub CopyIt()
' This macro will only work if your sheet names are numeric
' Example: the sheet for the first day is 1
'
' Count how many sheets are in this workbook
LastSheet = ActiveWorkbook.Sheets.Count
' What is the name of the last sheet?
LastName = ActiveWorkbook.Sheets(LastSheet).Name
' Add one to the last name to get the new name
NewName = LastName + 1
' Make a Copy of the last sheet
ActiveWorkbook.Sheets(LastSheet).Copy after:=Worksheets(LastName)
'Rename the sheet for today
NewLast = LastSheet + 1
ActiveWorkbook.Sheets(NewLast).Name = NewName
End Sub

I have a spreadsheet where I want to sum cells from column E if the corresponding date in column A is within the last 60 days.
First, I set up a cell in an out-of-the-way section of the worksheet. In cell Z1, enter the formula:
=now()-60
This formula calculates a day which is 60 days prior to today.

Then, the formula you want in cell E101 is: =sumif(A1:A100,">"&$Z$1,E1:E100)

I want to add 25 to all of the cells in column A. I don't want a new column, I want to add it right to the cells in column A.
Temporarily enter 25 in a cell in an out of the way place. Highlight that cell and hit Ctrl-C to copy the cell. Now, highlight your range of cells in column E. Select Edit>Paste Special. Click the add radio button and click OK. The contents of the clipboard (25) will be added to each cell in column A.

How can I enter the symbol for the new Euro currency in my spreadsheet?
Microsoft offers a new Tahoma font with the Euro symbol.

I download data which comes to me in a column. I want to use it in a complex sheet that needs data in a row. How can I convert the column data into row data?
Great question. Easy answer: Highlight the data in your column. C to copy it.
Then go to a blank section of your spreadsheet. pick Edit > Paste Special.
In the paste special dialog box, check the little box that says "Transpose". Then click OK. Your column data now turns into row data.
Thanks for your great answer, but this results in my data be backwards; i.e. rather than 12345 I need it to be 54321. Any further thoughts?
Before doing the copy, paste special, transpose, could you sort the original dataset so that it is in reverse order?
Say your original data is in A1:100. Add a sequence of numbers in B1:B100 that run from 1 to 100. (This is easy - enter 1 in B1, 2 in B2, highlight B1:B2 and double click the little square in the lower right corner of B2.)
Now, highlight A1:B100 and use Data Sort to sort by column B, Descending.
Now do the original trick. Highlight A1:A100, Copy, move to D2 and Paste Special, Transpose.
Finally, go back, resort A1:B100 by column B, Ascending, erase the values in B and you are all set.

I am new at this Excel game. How do you figure elapsed time? I've formatted the cells in military time, create a start time column and an end time column and would like to know the difference.
Enter a time in cell A2 as 1:23:45. Enter a second time in cell B2 as 7:45:08. Then, in C2 enter =B2-A2, you will get a series of pound signs. It is because column B isn't wide enough to display a time. Make column C wider. Format C2 as time, if necessary. Now, you should have an elapsed time.

How do I make a copy or a back up of a workbook that will not fit on a single 3.5 disk, so that I may transfer from home to office?
I run into this a lot. I save the file to disk, then use the shareware utility WinZip to copy the Excel file into a .zip file. Copy the Zip file to a 3.5 disk, bring it home, copy the zip file to my hard drive and then use WinZip to extract the files. I am usually able to fit 3-4MB of files into a Zip file which will fit on a 3.5" disk.

I am trying to get the percentage of two cells by dividing one cell into the other. Some of the time both cells might have a Zero in each. Therefore the DIV\0error shows up in my spreadsheet in the result cell that I have the formula in. How can I get it to recognize 0 divided into 0 so I can get a 0 answer in my total column not the error message?
This seems like an incredible pain, but I have to use it all of the time.
If your spreadsheet is set up with the numerand in column A, the divisor in B, and in C you are trying to calculate A/B, then you would enter this formula in cell C2:
=IF(B2=0,"NA",A2/B2)
At my job, we use NA to indicate that a number divided by 0 is not applicable. If, as you say, you want a zero there, then you would use this formula:
=IF(B2=0,0,A2/B2)

There are many monetary signs in Excel97 but I haven't found one yet for the new euro currency. Any suggestions ?
Excel 2000 (due out later this year) adds support for the Euro.

I wish to count the number of cells in a col or row that have a value > 0. How is this done? The zero is displayed in the cells. Count() will only ignore a blank cell.
In Excel, you would use something called an array formula. If your range of cells is in A1:A100, you would type the following formula:
=SUM(IF(A1:A100>0,1,0)) instead of hitting enter, you would hit Ctrl+Shift+Enter.
This keystroke tells Excel it is an array formula. It will evaluate each cell in the range, and if it is greater than 0, add 1 to your result. Otherwise, it will add zero to your result.
A second solution in Excel 97 is to use Countif. =COUNTIF(A1:A100,">0")

I have a list of 3215 product names on a spreadsheet. Each resides in a single cell. I need to put each name in quotes. The names are all in one column.
If all of your names are in column A, insert a new, temporary column B. The formula for cell B2 would be:
=""""&A2&""""
Yes, there are four sets of quotes there. The first and fourth quote indicate that you need to append text before the value in A2. The 2nd and 3rd quote is a special Excel code to put in a quote mark. Copy the formula from B2 down to B3216. Then, copy column B, and use Paste Special, Values to change the formulas to values.

My question is when your working with multiple worksheets how do you transfer added figures from one worksheet to another without losing the total?
First method: Copy the cell from the original sheet. Go to the new sheet, but do not paste. Do a Edit>Paste Special>Values. This will copy the value instead of the formula to the new sheet.
Second method: set up a formula on the new sheet that points to the total on the back sheet. If your total is on Sheet2, cell D20 you would enter this formula on sheet 1:
=Sheet2!D20
Then, as you change data on Sheet 2 and the totals change, they will automatically update on Sheet 1.

I am trying to open files called "Excel 97 Templates" with my Excel for Windows 95, Version 7.0. I get the message: "(filename).xls: file format is not valid". What gives? Is the "Excel 97" saved file just unreadable by my version?Excel 97 is not backwards-compatible with Excel 95/7.0. Files saved as Excel 97 files do not open in Excel 95/7.0. The standard (and not very friendly) message offered by Excel when you try to open a 97 file in 95 is the "File format is not valid".
If you know someone with Excel 97, they should be able to open the files and then do a "Save As" Excel 95/7.0, but any new features from 97 would be lost.

I am converting from Quatro Pro to Excel. What happened to my cell comments?
Excel comments are indicated by a small red dot in the upper right corner of the cell. Float your mouse pointer over the dot and the comment pops up.