MS Excel Questions And Answers

61⟩ 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

Is this answer helpful? 0 Yes | 0 No

Answer This Question

177 views

62⟩ 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.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

198 views

63⟩ 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.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

159 views

64⟩ 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.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

191 views

65⟩ 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.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

175 views

66⟩ 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)

Is this answer helpful? 0 Yes | 0 No

Answer This Question

179 views

67⟩ 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

Is this answer helpful? 0 Yes | 0 No

Answer This Question

174 views

68⟩ 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.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

156 views

69⟩ 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

Is this answer helpful? 0 Yes | 0 No

Answer This Question

172 views

70⟩ 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 entry.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

173 views

71⟩ 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

Is this answer helpful? 0 Yes | 0 No

Answer This Question

180 views

72⟩ 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

Is this answer helpful? 0 Yes | 0 No

Answer This Question

186 views

73⟩ 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.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

186 views

74⟩ 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

Is this answer helpful? 0 Yes | 0 No

Answer This Question

159 views

75⟩ 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

Is this answer helpful? 0 Yes | 0 No

Answer This Question

160 views

76⟩ 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.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

197 views

77⟩ 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".

Is this answer helpful? 0 Yes | 0 No

Answer This Question

184 views

78⟩ 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

Is this answer helpful? 0 Yes | 0 No

Answer This Question

156 views

79⟩ Is there an easier way to change formula results to fixed values in Microsoft Excel? I usually copy the cells that I am working with and then use the paste special command to past the permanent values over the original formulas?

Yes there is. You will need to first select the cell or range of cells that you want to convert to fixed values. Then you will need to drag the blocked cell or range one row over, either to the right or left. The row you are moving it to should be empty. Then using the right mouse button, drag the selected cell or range to where you originally had it. Once you release the right mouse button a shortcut menu will display, which will give you several options to choose from, you will select “Copy Here as Values Only”. This will give you a permanent value.

Is this answer helpful? 0 Yes | 0 No

Answer This Question

189 views

80⟩ How can I vary the width in the columns of a spreadsheet?

Varying the width is not possible. Changing the width of one cell also changes the width of all cells in that same column. The perspective of varied cell widths in one column can be achieved by merging cells together. 1. select both cells click the Merge and Center button on the toolbar

Is this answer helpful? 0 Yes | 0 No

Answer This Question

150 views

MS Excel

Home Applications Programs MS Excel

120 MS Excel Questions And Answers

61⟩ 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)?

63⟩ I created a worksheet in Excel 95. In Excel 97, Excel ignores my page breaks?

64⟩ Well, then how do I fit something to one page wide?

65⟩ 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?

66⟩ 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?

67⟩ 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?

69⟩ Can I see a list of the ColorIndex and the corresponding color?

70⟩ 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.?

71⟩ How can I speed up my Macro execution?

72⟩ How can I make the screen stop flashing during macro execution?

73⟩ How can I format a worksheet to have alternating green and white rows like a "greenbar" report?

74⟩ How can I show the complete path and filename in the Excel title bar?

75⟩ How can I copy the name of the worksheet tab to a cell in the worksheet?

76⟩ Hey! Excel used to have letters to indicate the columns. Now, all of a sudden, there are numbers for the columns?

77⟩ I have a column of numbers in the format 99999-99999. What is a simple macro to remove the dash?

78⟩ Is there a way to determine the last active column in a spreadsheet?

79⟩ Is there an easier way to change formula results to fixed values in Microsoft Excel? I usually copy the cells that I am working with and then use the paste special command to past the permanent values over the original formulas?

80⟩ How can I vary the width in the columns of a spreadsheet?

Quick Links:

MS Excel

Home Applications Programs MS Excel

120 MS Excel Questions And Answers

61⟩ 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)?

63⟩ I created a worksheet in Excel 95. In Excel 97, Excel ignores my page breaks?

64⟩ Well, then how do I fit something to one page wide?

65⟩ 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?

66⟩ 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?

67⟩ 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?

69⟩ Can I see a list of the ColorIndex and the corresponding color?

70⟩ 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.?

71⟩ How can I speed up my Macro execution?

72⟩ How can I make the screen stop flashing during macro execution?

73⟩ How can I format a worksheet to have alternating green and white rows like a "greenbar" report?

74⟩ How can I show the complete path and filename in the Excel title bar?

75⟩ How can I copy the name of the worksheet tab to a cell in the worksheet?

76⟩ Hey! Excel used to have letters to indicate the columns. Now, all of a sudden, there are numbers for the columns?

77⟩ I have a column of numbers in the format 99999-99999. What is a simple macro to remove the dash?

78⟩ Is there a way to determine the last active column in a spreadsheet?

79⟩ Is there an easier way to change formula results to fixed values in Microsoft Excel? I usually copy the cells that I am working with and then use the paste special command to past the permanent values over the original formulas?

80⟩ How can I vary the width in the columns of a spreadsheet?

BE THE FIRST TO KNOW

Quick Links: