Excel Q & A

NOTE:  This page is no longer updated.    Most of the topics here are now covered on other pages, or have pages of their own.  However, I will leave this page intact and available.   See the Topics page for a complete list of topics covered on my web site.

Eventually, this page may become a full-blown FAQ for the Excel newsgroups.  Until then,
it is just a collection of questions and answers.

Q: How do I count only those values in a range that are between two numbers?
A: Use the array formula =SUM((A1:A10<=High)*(A1:A10>=Low))
where High and Low are the limits you want.

Q: How do I sum only those values in a range that are between two numbers?
A: Use the array formula =SUM((A1:A10<=High)*(A1:A10>=Low)*A1:A10)
where High and Low are the limits you want.

Cell Formatting

Q: I want my cell to be display like "Result: " and then the value of the cell. How do I do this?
A: Format the cell as "Result: " #,##0 Note that the quotes go around the text
string (Result: ) and not the numeric format string (#,##0).

Q: I want to permanently multiply some cells by 2. How do I do this?
A: Enter 2 in some blank cell.  Copy this cell using Edit->Copy. Select the range of cells you want to multiply by 2. Choose Edit->Paste Special and choose Multiply in the Operations section. Click OK. This will change the values of the selected range.

Q: When I enter 123 in a cell, it appears in the cell as 1.23. What's going on?
A: You've got the "Fixed Decimal" option turned on. Go into Tools->Options->Edit and uncheck the Fixed Decimal option.  This feature is intended to allow you to enter dollar-and-cents amounts without having to enter the decimal point.

Q: How do I combine text and numeric values into a single cell?
A: Use the '&' concatenation operator. For example, ="The Sum Is: "& SUM(A1:A10)

Q: Can I change to color of the sheet-tabs in my workbook? Can I change the fonts?
A: Colored tabs were introduced in Excel 2002. You cannot change the color of the tabs in earlier versions. In Excel 2002 and later, right click the sheet tab and choose Tab Color from the pop up menu.  In no version of Excel can you change the font of the tab.

Q: Can I change the name of a worksheet to a cell value?
A: Yes, you can do this with VBA. Use Activesheet.Name = Range("A1").Value.

Q: What are Custom Format Strings? How do I use them?
A: In addition to the standard formats that are available in the Format->Cell->Number options dialog, you can create your own, customized formats. A custom format has four components, separated by semicolons: <positive>;<negative>;<zero>;<text>. Excel will use the appropriate format component, depending on the value of the cell. See the on-line help (search for Customizing, Number Formats for more details).

Q: How can I hide a single cell, without hiding the entire column of row?
A: Create a custom number format of ";;;" (three semicolons).  Your cell will contain the proper
value, but will appear empty.  Another solution is to format the forecolor of the cell as the same color as the background (normally white).

Q: How can I hide a cell if it is 0?
A: Create a format string with "0;0;" or "#,##0;-#,##0;".   This will hide zero values.

Q: How can I enter multiple lines of text in a cell?
A: Type the first line of text, press ALT+ENTER, and enter the next line.

Q: How can I apply formatting to a range of cells on more than one worksheet.?
A: First select the sheets that you want to format, by clicking on the sheet tabs while
holding down the Ctrl key or Shift key.  Then, apply your formatting to the cells on the active sheet.  That formatting will be applied to all of the selected sheets.

Dates And Times

Other Date Related Procedures are described on the following pages.

The DATEDIF Function

Date Intervals

Dates And Times

Date And Time Entry

Holidays

Q:  What about entering 2-digit years?
A: Excel treats 2-digit years between 00 and 29 to be 2000-2029, and years between 30 and 99 to be 1930-1999.  Click here for more details.

Q: When I add up times, the sum "roll's over" when the total reaches 24 hours. How can I display "25:45" rather than "01:45"?
A: Simple. Just format the SUM cell as "[h]:mm" or "[h]:mm:ss".

Q: What about adding up minutes and seconds, and stopping the minutes from rolling over into hours?
A:  Just as simple. Make sure your data has a leading "0:", as in "0:10:23" for 10 minutes and 23 seconds. Then format your SUM cell as "[mm]:ss".   This format will display "73:10" rather than
"1:13:10" for one hour, thirteen minutes, ten seconds.

Q:  How do I get the day of week from a date?
A:  Format the cell as "ddd" to get "Wed" or as "dddd" for "Wednesday".

Q: How do I add a number of hours and minutes to a date?
A: Suppose your date is in A1 and you want to add 3 hours 15 minutes 10 seconds to this date, with the result in B1. Set B1 to =A1+TIME(3,15,10) Also, you could enter "3:15" in cell C1, and set B1 to =A1+C1.

Q: How do I add a number of months to a date?
A: Suppose your date is in A1 and you want to add 3 months to this date, with the result in B1. Set B1 to =DATE(YEAR(A1),MONTH(A1)+3,DAY(A1)). Excel will automatically handle this situation when MONTH(A1)+3 exceeds 12.

Q: Is the year 1900 a leap year?
A: Officially, no, it is not. The rule for leap-years is as follows: A year is a leap year if it is evenly divisible by 4 (1984, 1988, 1992), unless it is a century year (1800, 1900, etc). The exception to the "century rule" is that every fourth century (1600, 2000, 2400) is a leap year. By these rules, 1900 is not a leap year. However, there was a bug in Lotus 123 that treated 1900 as a leap year. To make Excel compatible with 123, Microsoft decided to treat 1900 as a leap year. Did 29-Feb-1900 exist in reality? No. Does it exist in Excel? Yes.

Q:  Is the year 2000 a leap year?
A: Yes, it is, and Excel handles this properly.

Q: What about the so-called "Year 2000" bug?  Is Excel affected by this?
A: No.  As long as you use the standard Excel date formats (serial numbers, click here for more information about dates and times), the year 2000 does not pose any problems for Excel.  Most of the date formats in Excel will display only a two-digit year, so the year 2001 will display as "01", but the underlying date values reflect years past 2000, so calculations and formulas based on these
values will perform properly.

Q: Excel's date system only goes back to 1900. How can I use dates before this time?
A: Carefully. Excel supports two date systems. The first (and default) is based on 1900, and the second is based on 1904. If you use the 1904 date system you can use dates prior to 1900, since this date system supports negative dates. Don't change date systems once you've got dates in a spreadsheet, though, because existing dates will not be properly translated. In any workbook, all dates must be either 1900 or 1904 based. They won't mix.

Q: I've heard things about a =DATEDIF function, but can't find anything in Help about it. What the story?
A: There is an undocumented function called =DATEDIF(Date1, Date2, Interval) that computes the difference between two dates. Date1 must be earlier than Date2. Interval is one of the following "m" for months, "d" for days, "y" for years, "ym" for the months as if the dates were in the same year, and "yd" for the days as if the dates were in the same year. If Date1 is later than Date2, you'll get a #NUM! error. If either Date1 or Date2 is not a valid date, you'll get a #VALUE! error.  See the DateDif page for more details.

Q: I've lost the password for a workbook. How can I open it?
A: This is perhaps the single most common question on the Excel newsgroups. The short answer is that you cannot. There is no simple "hack" to get around Excel's password protection. However, there are firms who can do this. See
http://www.crak.com
http://www.accessdata.com

VBA Macros

Q: I'm running Excel97.  What version of Visual Basic For Applications (VBA) do I have?
A: In any of the Microsoft Office 97 applications, you're running VBA version 5.

Q:  Okay, what the difference between Visual Basic For Application and just Visual Basic?
A: Visual Basic For Applications (VBA) is a subset of Visual Basic (VB).  Anything you can do in
VBA can be done in VB. VB gives much more control over the environment, since it is designed
to create stand-alone applications.  Also, VB gives you access to many more controls (e.g.,
drive and directory list boxes, progress bars, status bars, etc).  VBA is designed for the automation
of the Office suite of applications, not creating stand-alone applications.

Q: Can I run a macro from a cell function?  Something like =IF(A1>10,Macro1)?
A: No, you cannot.   However, you can use the worksheet's Change event to do something
like this.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target = [A1] Then
If Target.Value > 10 Then
End If
End If
End Sub

Q: I want to display a message in the status bar while my macro is running.  How do I do this?
A: Use Application.Statusbar = "Running macro...".  To clear the message, use
Application.Statusbar = FALSE.

Q: How do I suppress warning and confirmation messages ?

Q:  How do I keep the screen from updating as my macro runs?
A: Application.ScreenUpdating = FALSE

Q:  How do I set the value of one cell to the value of another cell?
A:  This is the simplest of VBA operations. Use something like:
Range("A1").Value = Range("A2").Value

Q: How do I loop through the cells in the selected range?
A: Very easily:
Dim C as Range

For Each C in Selection.Cells
C.Value = 1237
Next C

Inside the FOR EACH loop, C will reference each individual cell in the selected range.

Q:  I'm tired of typing Range("A1") to refer to that range.  Is there a shortcut?
A:  Yes.  Surround the range name with square brackets, and don't use quotes. For
example, Range("A1") and [A1] are equivalent.

Q:  How do I run a macro when my workbook opens? Some sort of AUTOEXEC procedure?
A: Create a macro called Auto_Open. It will run each time your workbook is opened. You can also create a macro called Auto_Close, which will run when you workbook is closed.

Q: How can I speed up macro execution?
A: There are as many answers to this as there are macros. However, you can usually speed things up a great deal by turning off screen updates using Application.ScreenUpdating = FALSE, and by setting the calculation mode to manual, with Application.Calculation = xlManual. If you need to get updated values at some point in your macro, use the Application.Calculate method to force a calculation. Be sure to reset these values at the end of your macro.

Q: Is the a way to convert Excel 4.0 macros to Visual Basic For Applications?
A: No, there is not. You have to convert each one, step by step, yourself.

Q: How do I return an array from a VBA function to a range of cells in Excel.
A: See Returning Arrays From VBA for details.

Q: My user-defined function returns a #VALUE! error.  What's going on?
A: The most likely reason is that you've tried to change the value of some other cell.  In
a user function, you cannot change the value, formula, or formatting of any cell. The reason
for this is fairly simple.  Excel must keep track of the dependents and precedents of each cell, so
that it can properly calculate your worksheet.  If a user defined function alters the contents of
another cell, Excel could not properly track its dependents and precedents.  Another common cause is that your function encountered an untrapped run-time error, perhaps a problem with data types.  Put a breakpoint at the first line of code and step through the function in the debugger.

Q: How do I copy the contents of a variable to the Windows clipboard?
A: See the Clipboard page for details.

Miscellaneous
Q: I see cell references in two formats, "\$A\$1" and "A1".   What's the difference?

Q: What is the internal format for Excel files?
A:  It is very complicated. Actually, the internal format of Excel files is an OLE data stream. If you really need to know, the format is documented in the Excel SDK (Software Developers Kit, available from Microsoft Press for a hefty fee). Some 200 pages of the manual are devoted to the file format. Do you really need to know the actual format?  You're MUCH better off manipulating Excel files through automation.

Q:  How can I search for something across multiple worksheets in my workbook? The online Help says I can do this, but it doesn't work. What's going on?
A: The Help file is incorrect. You can't search across multiple sheets. This is a known problem. Let's hope that this is fixed in a future version of Excel.

Q: In Excel97, the maximum number of rows seems to be 65,535, and the number of columns seems to be 256. Can I get around these limitations?
A: No. There are few jokes in the on-line Help files for Excel, and this isn't one of them. These limits are absolute.

Q: What is the compatibility situation between different versions of Excel?
A: You can always read (import) one version of Excel in a later version.  For example,
Excel97 can read Excel95 files.  Later version of Excel can save files into previous versions of
Excel, but some features may be lost.  For example, Excel97 can save spreadsheets into the
Excel95 format, but Conditional Formatting will be lost.   Generally, files crated by a later version of
Excel cannot be read by an earlier version. For example, Excel95 cannot do anything with Excel97
files.

Q: Can I copy a selection of cells as an image?
A: Yes, you can.  Select the cells you want to copy.  Hold down the shift key and got to the Edit menu, and choose the Copy Picture option.  In the dialog box that appears, choose "As Shown On Screen"  for Appearance, and "Picture" as format.  This will copy the selection to the clipboard.   You can then paste it into a document or a program such as Paint.
This method will will also work with Excel charts.  Select the chart object and the follow the steps listed above.