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.
This page lists the answers to questions that come up frequently in the Excel
newsgroups.
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 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.
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.
Other Date Related Procedures are described on the following pages.
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: Are passwords case-sensitive?
A: Yes. "PASSWORD", "password", "PassWord" are 3 different
passwords.
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
MsgBox "Put Your Code Here"
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 ?
A: Application.DisplayAlerts = FALSE
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
' Your code here. E.g.,
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.
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.