In MS Access, the long and short date option does not show 4 digit years. How do
I achieve the mm/dd/yyyy format?
In the date field properties of the table, form, or properties manually input the
format you would like. It is not required to choose one of the date formats in the
drop down box. Example would be mm/dd/yyyy.
How do I put password to protect my entire Spreadsheet so data cannot be changed?
1. Click Tools
2. Scroll down to Protection, then Protect Sheet
3. Enter a password, Click OK
4. Re-enter password,
What is Freeze Panes and how do I do it?
1. Row - Select the row below where you want the split to appear
2. Column - Select the column to the right of where you want the split to appear
3. Go to the Menu Bar
4. Click Windows
and then click Freeze Panes
How do I Format data in MS Excel?
1. Must Always highlight the data before formatting
2. Click Format
3. Then go to Cells
do I resize Columns and Rows to better fit the data in MS Excel?
1. Move the mouse in between any two labels (Rows/numbers or Columns/letters)
2. The pointer will turn into a vertical (letters) or horizontal (numbers) line
with arrows on both ends
3. Simply drag the column or row to the desired size
How do I combine different chart types into my Excel spreadsheet?
To combine chart types, follow these steps:
1. If the Chart toolbar isn't already displayed, right-click any Toolbar and
2. On the chart, click the series you want to change.
3. On the Chart toolbar, click the arrow next to the Chart Type button and then
select the new chart type for the series (in our example, a line chart).
Using Excel is there a way to close all open Excel files at once instead of closing
them one at a time?
Yes, you can close down all your Excel files at once by using the following instructions:
1. Hold down the Shift key.
2. Choose File + Close All from the menu. Holding down the Shift key changes Excel's
File + Close command to a File + Close All command.
I have converted my MS Access database to a current conversion. I am getting conversion
Design specs have changed in new releases of MS Access. Visit the on-line help option
for "conversion and compatibility" that explains different portions of
Access databases that will have difficulty converting. Some reprogramming may be
Is there a way to apply the same formatting to every sheet in a workbook in Excel?
Yes. To do this, you will need to right click on one of the worksheet tabs and then
choose Select All Sheets. After you do this any formatting that you apply or text
you enter will show up on all the sheets in your workbook. In order to eliminate
certain sheets from the changes, hold down the Ctrl key and click on the tab of
the worksheet you want excluded from the others. You can also group sheets by holding
the shift key and selecting the worksheet tab.
How can I identify which cells in my spreadsheet have a formula and which do not
in MS Excel?
1. Choose Edit + Go To (or press Ctrl + F5).
2. Select Special.
3. Select Formulas.
4. Click OK.
1. Choose Tools + Options.
2. Select the View Tab
3. In Window Options choose the check box 'Formulas'.
4. Click OK
How can I printout the formulas in an Excel spreadsheet - rather than the results?
The trick is to change the way Excel displays the worksheet before you choose to
print. Check the box Tools, Options, View, Formulas and you'll see the formulas
appear in each cell (with the columns changed to fit). When you print the sheet
the formulas will be printed instead of the values
Is it possible to change the color and font of the sheet tabs?
Yes we can change the color of sheet tabs. By right clicking on sheet tabs and you
will get option change color but i didn't find any option to change the font
of sheet tabs.
How do I find the first empty cell in column A?
If ActiveSheet.UsedRange.Count < 2 Then
MsgBox Columns("A:A").Find(What:="", LookAt:=xlWhole).Row
How can I unprotect a VBA project using code?
You cannot. A workaround is to simulate keystrokes with the SendKeys method
Some of the functions that we want our macros to accomplish are not possible without
first unprotecting the worksheet/workbook with the password?
'your code here
Be sure to protect your macro code to hide the sheet password.
Is it possible to call a macro from the condition true or false side of a worksheet
formula? i.e. If(A2="OK",Run macro1,run macro2)?
Basically, the answer is No. You can write functions in VBA that you can call from
worksheet cells, but these functions can only return a value. They can't modify
other cells or alter any part of the Excel environment. (You may be able to use
a worksheet change event to call the macro.)
Is there a way to protect the macros I create so people can not see or alter them?
Go to Tools > VBAProject properties, lock the project for viewing, and enter
I want to show a userform each time my file is opened?
Combine the two solutions above:
Private Sub Workbook_Open()
I want to add a toolbar to my spreadsheet that when clicked, brings up Userform1?
Assign the toolbar button to this macro, which should be in a standard VBA module:
Sub ShowForm ()
How do I run a macro every time a certain cell changes its value?
There is an event called Worksheet_Change which is triggered when a value is entered
(it will not fire when a formula result changes). One of the arguments to this event
is 'Target' which is a reference to what changed. Since this event will
occur whenever a value changes - you can use the target to see if it is the cell
you are interested in:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C5")) Is Nothing Then
'The cell you are monitoring has changed!
'Do whatever you need to do...
When a button is drawn onto a sheet the assign macro is not displayed. When right-clicking
on the button the "Assign Macro" context menu item is not present?
There are buttons from the Forms toolbar and there are buttons from the Control
Toolbox. If "Assign Macro" is not an option then it's from the Control
I have a user defined function that does not recalculate?
Include all the cells that your UDF depends on in the argument list. Or enter this
as the first statement in your Function:
This will cause the function to be executed whenever a calculation occurs in the
My Stop Recording toolbar has disappeared. How do I get it back?
To reactivate the Stop Recording toolbar:
1. Choose Tools | Macro | Record New Macro
2. Click OK
3. Choose View | Toolbars | Stop Recording
4. Click the Stop Recording button (the blue square)
The next time you record a macro, the toolbar should automatically appear.
Can I ask my user for confirmation before executing the macro?
If MsgBox("Are you sure ?", vbYesNo + vbQuestion) = vbNo _
Then Exit Sub
'Code goes here instead of
MsgBox "Actions here"
Can I have my Macro make Excel NOT ask "the file already exists, do you want
to overwrite" type of questions?
Application.DisplayAlerts = False
'code to save, overwrite, delete, whatever goes here
Application.DisplayAlerts = True
All of a sudden, when I open the file, it asks if I want to "Enable or Disable
a Macro". There are no macros in this workbook?
A macro has been added and then removed, leaving an empty module. Empty modules
trigger the macro query, as does an actual macro.
1. Warning: As a precaution, you should make a backup copy of the file, before you
remove any code.
2. Right click on any sheet tab and choose View Code, to open the Visual Basic Editor.
3. In the Project Explorer at the left of the screen, find the workbook. In the
sample shown here, Book4 is the workbook name -- VBAProject (Book4)
4. Look for a Modules folder, and open it. (If there is no Modules folder, go to
5. For each module in the folder:
1. Right-click on the module name.
2. Choose Remove Module1 (the name of your module may be different)
3. Click No when asked if you want to Export.
6. Open the Microsoft Excel Objects folder.
7. For each worksheet, and for ThisWorkbook:
1. Double-click on the object name, to open its code module. In this sample, you'd
double-click on Sheet1 (Sheet1)
2. On the keyboard, press Ctrl+A to select all the code (even if the code module
3. Press the Delete key.
8. Look for a Forms folder, and open it.
9. Delete any UserForms that it contains.
10. Look for a Class Modules folder, and open it.
11. Delete any class modules that it conta
12. Close the Visual Basic Editor.
13. Save the changes to the workbook.
How can I save a chart as a GIF file?
You can save your worksheet as an HTML file, and Excel will automatically convert
any charts to GIF files.
If that seems like overkill, you can write a simple macro that will do the job.
Press Alt-F11 to activate the Visual Basic editor. Select your workbook in the Projects
window, and choose Insert, Module to insert a new VBA module. Then type the following
four-line procedure into the module:
Sub SaveChartAsGIF ()
Fname = ThisWorkbook.Path & "" & ActiveChart.Name & ".gif"
ActiveChart.Export FileName:=Fname, FilterName:="GIF"
After the macro is entered, reactivate Excel and click the chart to be saved. Press
Alt-F8 to display the Macro dialog box. Select the SaveChartAsGIF macro and click
Run. The procedure uses the chart's name as the GIF file name, and the file
is stored in the same directory as the workbook.
This simple macro does no error checking, so it will generate an error if a chart
is not selected or if the workbook has not been saved.
For whatever reason, Microsoft continues to ignore what must be thousands of requests
for this feature. Although Microsoft Word offers this feature, Excel offers no direct
way to print a workbook's full path in the header or footer. The only solution
is to create a macro. The technique described below works with Excel 97 and later.
In Excel, press Alt-F11 to activate the Visual Basic editor. In the Project window,
double-click the project that corresponds to your workbook. The project list will
expand to show several objects. Double-click the item labeled Microsoft Excel Objects,
and then double-click the object labeled ThisWorkbook. Enter the following three
lines of VBA code into the code module for the ThisWorkbook object (usually in the
right pane of the window you're seeing at this point).
Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.LeftHeader = ThisWorkbook.FullName
After inserting the code, press Alt-Q to return to Excel.
This procedure will be executed before you print or preview your workbook. It simply
inserts the workbook's path into the left header position. If you prefer to
put the path in a different position, substitute any of the following for LeftHeader:
CenterHeader, RightHeader, LeftFooter, CenterFooter, or RightFooter.
How can I enter the current date into a cell so it does not change from day to day?
When I use the NOW function, it always shows the current date?
The easiest way to enter the current date into a cell is to use the Ctrl-; key combination
(press the Ctrl key and type a semicolon). Similarly, you can enter the current
time by pressing Ctrl-Shift-; (press the Ctrl and Shift keys and type a semicolon).
Both of these key combinations enter the information as a volatile value, not as
I need to determine the difference between two times. If the difference is positive,
it works fine. But Excel does not want to display negative time values. Why not?
Excel stores dates and times as numeric values, so it should be possible to add
or subtract one from the other. The problem occurs if you have a workbook containing
only times and no dates. As you discovered, subtracting one time from another doesn't
always work. Negative time values appear as a series of pound signs, even though
you've assigned the [h]:mm format to the cells. By default, Excel uses a date
system that begins with January 1, 1900. A negative time value generates a date-time
combination that falls before this date, which is invalid.
The solution is to use the 1904 date system. Select Tools, Options. In the Options
dialog box, click the Calculation tab and check the 1904 date system option to change
the starting date to January 2, 1904. Your negative times will now be displayed
If you use the 1904 date system, be careful when linking to date cells in other
workbooks. If the linked workbook uses the 1900 date system, the dates retrieved
by the links will be incorrect.
it possible to determine the day of the week for a particular date?
Yes, and Excel offers several ways to do so. Assume that cell A1 contains a date
value. The formula below uses the WEEKDAY function, which returns an integer between
1 and 7 (1 for Sunday, 2 for Monday, and so on).
If you'd prefer to see words rather than integers, modify the formula as follows:
=CHOOSE(WEEKDAY(A1), "Sunday", "Monday", "Tuesday",
"Wednesday", "Thursday", "Friday", "Saturday")
Another approach is to change the number format for the date cell. Activate the
cell that contains your date, and then choose Format, Cells. In the Format Cells
dialog box, click the Number tab. Choose Custom from the Category list, and type
a custom number format string into the box labeled Type. The trick here is to use
dddd as part of the format string. For example, a format string of dddd mmmm d,
yyyy will display the date and the day of the week, like this: Thursday November
I have a worksheet that shows total hours and minutes worked, along with the hourly
pay rate. When I multiply these vales, I do not get the result I am looking for.
What is wrong?
You need to multiply the result by 24, for the number of hours in a day. If cell
A1 contains the number of hours worked (for example, 16:45, for 16 hours and 45
minutes) and cell B1 has the hourly rate, the formula below will calculate the total
Make sure the cell that contains the formula is formatted as a number, not a time.
I have a range of time values, but when I try to sum them, the total is never greater
than 24 hours?
When you add a range that contains time values, Excel ignores the hours that exceed
24. The solution is to use a custom number format. Activate the cell that contains
your total time, and then choose Format, Cells. In the Format Cells dialog box,
click the Number tab. Choose Custom from the Category list, and type [h]:mm into
the box labeled Type. Using brackets around the hour portion of the format string
tells Excel to display hours that exceed 24 hours.
How can I calculate the difference between two dates?
Excel stores dates as serial numbers. The number 1 represents January 1, 1900, the
number 2 represents January 2, 1900, and so on. Formatting these numbers using a
date format causes them to appear as actual dates. Therefore, if you have dates
stored in two cells, you can simply create a formula that subtracts one from the
other to get the number of intervening days. You'll want to make sure that
the formula cell is formatted as a number, not a date.
You might also find the DATEDIF function useful. This function, which was not documented
prior to Excel 2000, returns the difference between two dates, expressed in years,
months, or days. You might use the DATEDIF function to calculate how many months
the payment on an invoice is overdue or to determine a person's age when you
know their birth date.
Excel's DATEDIF function takes three arguments. Its syntax is:
In the syntax, start_date is a date or reference to a date, end_date is a date or
reference to a date, and units is a one- or two-digit string (in double quotes)
specifying the units for the difference between the two dates. Acceptable values
for the units argument are shown below.
* y returns the number of full years in the period.
* m returns the number of full months in the period.
* d returns the number of full days in the period.
* md returns the number of full days in excess of the last full month.
* ym returns the number of full months in excess of the last full year.
* yd returns the number of full days in excess of the last full year.
For example, assume cells A1 and B1 contain dates. The formula below returns the
number of full years between the dates (useful for calculating a person's age):
The formula below calculates the number of full months between the two dates:
I find that sometimes my formulas do not get fully calculated. This often happens
when I use custom functions created with Visual Basic?
Microsoft has acknowledged some problems with the Excel calculation engine. In order
to be assured that all of your formulas have been calculated, press Ctrl-Alt-F9
to force a complete recalculation.
How can I make text in a cell display in multiple lines?
When entering text into the cell, press Alt-Enter to insert a line break. When you
do so, Excel will automatically apply text wrapping to the cell.
To reformat existing cells so they sport wrapped text, select the cells and then
choose Format, Cells. On the Alignment tab, select "Wrap text," and click
When I open a workbook, Excel asks if I want to update the links. I have looked
everywhere, and I can not find any links in my formulas?
I've never known Excel to be wrong about identifying links, so there's
an excellent chance your workbook does contain one or more links--but they are probably
not formula links.
* If you have a chart in your workbook, click each data series in the chart and
examine the Series formula in the formula bar. If the formula refers to another
workbook, you've identified the link. To eliminate it, move the chart's
data into the current workbook and recreate your chart.
* If your workbook contains any dialog sheets, select each object in each dialog
box and examine the formula bar. If any object contains a reference to another workbook,
edit or delete that reference.
If these two approaches don't solve your problem, follow these steps:
1. Select Edit, Links. (In some cases, this command is not available. If you can't
select it, skip to step 4.) The Links dialog box will appear.
2. Click the Change Source button and change the link to the active file.
3. Select Insert, Name, Define. Scroll down the list in the Define Name dialog box
and examine the "Refers to" box. Delete names that refer to another workbook
or that contain an erroneous reference (such as #REF!). This is the most common
cause of "phantom links."
4. Save your workbook. When you reopen it, Excel won't ask you to update links.
Can I write a formula that returns the number of distinct entries in a range?
First, let's clarify the question. We're hunting for a formula that, given
the range 100, 99, 98, 100, 98, 100, 98, would return 3. This type of counting requires
an array formula. The formula below, for example, counts the number of distinct
entries in the range A1:D100.
When you enter this formula, you must press Ctrl-Shift-Enter. Pressing only Enter
will give you the wrong result. Excel will place brackets around the formula to
remind you that you've created an array formula.
The preceding formula works fine in many cases, but it will return an error if the
range contains any blank cells. The formula below (also an array formula, so input
it with Ctrl-Shift-Enter) is more complex, but it will handle a range that contains
a blank cell.
=SUM(IF(COUNTIF(A1:D100,A1:D100)=0, "", 1/COUNTIF(A1:D100,A1:D100)))
I have created some clever formulas, and I don not want anyone else to see them.
Is it possible to hide the formulas but display the results?
Every cell has two key properties: locked and hidden. A locked cell can't be
changed, and the contents of a hidden cell don't appear in the formula bar
when the cell is selected. By default, every cell is locked and not hidden. But
it's important to remember that these attributes have no effect unless the
worksheet itself is protected. First, to change the attributes, select the appropriate
cell or range and then choose Format, Cells. In the Format Cells dialog box, click
the Protection tab and select Locked or Hidden (or both). Unlock cells that accept
user input, and lock formula and other cells that should stay unchanged (such as
titles). To prevent others from seeing your formulas, lock and hide the formula
cells: The results of the formulas will be visible, but the formulas will not.
Now, to protect the worksheet, choose Tools, Protection, Protect Sheet to bring
up the Protect Sheet dialog box. Make sure the Contents box is checked. You can
enter a password to prevent others from unprotecting the sheet. Locked cells in
a protected sheet cannot be edited, and other worksheet changes are disabled. For
example, no one can insert rows or columns, change column width, or create embedded
I have a price list stored in a worksheet, and I need to increase all prices by
5 percent. Can I do this without reentering all the prices?
Excel provides two ways to accomplish this. The "traditional" technique
goes something like this: Insert or find a blank column near the prices. In that
column's first cell, enter a formula to multiply the price in that row by 1.05.
Copy the formula down the column. Then select and copy the entire column of formulas,
select the original prices, and choose Edit, Paste Special. In the Paste Special
dialog box, select Values to overwrite the original prices with the formulas'
results. And finally, delete the column of formulas.
The other, more efficient approach also uses the Paste Special dialog box. To increase
a range of values (prices, in this example) by 5 percent, enter 1.05 into any blank
cell. Select the cell and choose Edit, Copy. Then select the range of values and
choose Edit, Paste Special. Choose the Multiply option and click OK. Then delete
the cell that contains the 1.05.
Is there a function that returns the name of the worksheet?
Excel's CELL function comes close. The following formula displays the workbook's
full path along with the worksheet name:
For example, this formula might return something like:
Returning only the sheet name requires a more complex formula:
How can I increase the number of rows or columns in a worksheet?
In Excel, every workbook has 255 columns and 65,526 rows. These values are fixed
and cannot be changed. If you need more rows, you might want to consider using a
database (such as Access) or another spreadsheet (Quattro Pro can handle a million
rows) for data storage. In most cases, you probably don't need to work with
all of your data at once. You'll usually need only a subset of your data. You
can use Excel's Data, Get External Data command to query the database and bring
in only the records you need.
I have saved my workbook with a password, but Excel does not recognize it and would
not let me open the file. Am I out of luck?
First, remember that passwords are case sensitive. So if you originally entered
your password as Xyzzy, typing xyzzy won't work. If you know you're entering
the password correctly, then it's time to start looking for a password recovery
utility. Several utilities exist, and none of them are free. Do an Internet search
for "Excel password recovery," and you'll find several products that
will come to the rescue. By the way, the fact that these products exist might raise
some alarms for the security-minded. Bottom line? Excel password protection isn't
as secure as you might expect.
Can I change the color of the worksheet tabs in my workbook?
In a word, no. It would certainly be helpful to be able to color-code your worksheet
tabs. For some reason, Microsoft hasn't implemented this feature, which has
been available in 1-2-3 and Quattro Pro for quite a while.
When I enter a value, it appears with two decimal places. For example, when I enter
154 it shows up as 1.54. Whats wrong?
Somehow Excel's fixed-decimal mode was turned on. To return to normal, select
Tools, Options to display the Options dialog box. Then click the Edit tab and remove
the check mark from the "Fixed decimal" option. Of course, this feature
can be useful when entering some types of data, but most of the time, you'll
want to keep the fixed-decimal mode turned off.
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.
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
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.
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
Then, as you change data on Sheet 2 and the totals change, they will automatically
update on Sheet 1.
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:
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.
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
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")
There are many monetary signs in Excel97 but I have not found one yet for the new
euro currency. Any suggestions?
Excel 2000 (due out later this year) adds support for the Euro.
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?
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
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:
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"
I am new at this Excel game. How do you figure elapsed time? I have 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.
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,
Finally, go back, resort A1:B100 by column B, Ascending, erase the values in B and
you are all set.
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.
can I enter the symbol for the new Euro currency in my spreadsheet?
Microsoft offers a new Tahoma font with the Euro symbol.
I want to add 25 to all of the cells in column A. I do not 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.
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:
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 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
'Rename the sheet for today
NewLast = LastSheet + 1
ActiveWorkbook.Sheets(NewLast).Name = NewName
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
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
Ihave 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 years 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 ideas what I have 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.
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:
As long as you are not using conditional formatting to turn your cells red, this
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
Now, enter the function =Sumred(B1:B10) in your worksheet.
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
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.
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:
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.
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
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
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
How can I get Excel to accept an entry like "5+5" without the leading
equals sign as a formula? I have done this is the past but can not remember how.?
Go into Tools>Options. On the Transition tab, check the box for Transition formula
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
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 format a worksheet to have alternating green and white rows like a "greenbar"
Select the range you want to format. From the menu, select Format > Conditional
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.
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