All these words are used in describing copy paste function of formulas or functions
Relative reference: This is the normal way we write a formula or function. In excel when we copy a formula by default it will change reference automatically. For ex we have data from A1 to A10 in cell A11 when we use a function sum (Sum(A1:A10)), it will return total of values given in A1 to A10. When we copy the function given in A11 and paste it in B11 the function will be (sum(B1:B10)) that will return total of values given in B1 to B10. This is called relative reference.
Absolute reference: In this scenario even if we copy a formula the reference will not change. We make this happen by using $ symbol in function syntax. Going y previous example instead of the above syntax if I use sum($A$1:$A$10), even if I paste the formula in B11 it will continue to give me total of values present in A1 to A10.
Mixed reference: In mixed reference we will only lock in either row or column that means we use $ symbol to fix either row or column and leave other one as relative reference. For ex sum(A$1:A$10) this formula when I copy and paste in B11 it will be changed to sum(B$1:B$10) but when I paste same formula in A33 it will not change.
In Microsoft Excel 2003 it is possible to color code the tabs of worksheets. Changing the color of worksheets tabs containing related information can make it easier to navigate through very . Or to use tab colors to differentiate between sheets containing unrelated information.
The default setting for Excel toolbars is to display them on a single row, thereby hiding many of the toolbar buttons. It is quite easy to change the default settings to show Excel toolbars on two rows, making for a easier time formatting your spreadsheet.
* Some of the Business functions (BAPI's / RFC unctions) offer data upload
* The general tool in SAP for data upload is the Batch input / call transactions
In the batch input file you specify screen by screen and field by field the input to SAP.
SAP has a recorder to create the sample file. Just run trans SHDB. Within this transaction, you run a case of the changes you want to do. On exit of the trans you will have a batch input file that you can download and from excel. Manipulate the file before you call the RFC function RFC_CALL_TRANSACTION.
Number formatting in Excel spreadsheets is used to change the look of the numbers in the cell. The most common formatting options are currency, comma, date, accounting and percent, although there are more options available.
The Excel SUM function is probably the most often used function in Excel spreadsheets. The AutoSUM button on the Standard toolbar makes it easy to quickly sum columns or rows of adjacent data without actually typing in the cell references.
One of the most useful functions in Excel is the IF function. What it does is test a value in a specific cell to see if that value meets a certain condition. If it does, one result will happen, if not, a different action can occur. This article covers how to write an IF function.
Microsoft Excel is an electronic spreadsheet program that people use for storing, organizing and manipulating data. The types of data that Excel can use includes numbers, text and formulas. Formulas are used to perform basic mathematical operations, calculating repayment plans for loans or mortgages, and to find the average, maximum, or minimum values in a specified range of data.
In olden days we have Lotus 123, it is also having spread sheets for using accounts purpose. Now excel having spread sheets not only accounts purpose but also for various types of formats with calculations.
To open a file as copy, just follow the steps
* Select your file through the file open box (CTRL+O)
* Now at the bottom you may notice an ARROW BOX next to the OPEN button (in fact, it is a drop down box)
* Click the arrow and select OPEN AS COPY.
* Now you could be having a copy of the original version of the file.
If you open a Excel file using the OPEN AS COPY option, then a new file is created and saved in the same directory of the original file. So if you have a file, say test.xls in D drive. Then if you open the file as copy, then Excel creates a file in the D drive with the name Copy(1) of test.xls.
You can access this file through the path D:/Copy(1) of test.xls. You can always rename and copy your file somewhere else but by default Excel saves the copy of the file in the same drive. Also, Excel duplicates the original file and whatever is in the original file, it is copied including formats, formulas and other functionalities.
1. Choose Tools > Options and select the General tab.
2. All files listed in the "At startup, open all files in:" box will be loaded when Excel starts.
3. Alter or remove this list.
Clear out c:windows emp directory on the machine that houses the files, then reboot.
Links come in several flavors; linked formulas, defined names (Insert > Name > Define menu), objects (buttons and stuff) assigned to remote macros.
1. Choose Tools > Options and select the General tab.
2. Remove the check mark from 'R1C1 reference style'
In Excel 2002, and later versions, you can colour the sheet tabs. Right-click on a sheet tab, and choose Tab Color.
Note: When the sheet is selected, the colour will show in a thin strip at the bottom of the sheet tab. The full tab is coloured for non-selected sheets.
To increment a formula reference by rows, when copying across columns you need a combination of the INDIRECT and ADDRESS Function. For example, place this formula in any row in Column "D".
Now copy it across any amount of Columns. You will note that at each Column the row number increases by 1. In affect it will look like:
=SUM($A$1:$A$2) at Column D
=SUM($A$1:$A$3) at Column E
=SUM($A$1:$A$4) at Column F
=SUM($A$1:$A$5) at Column G
The Syntax for the ADDRESS Function is
The last 3 arguments (abs_num,a1,sheet_text) are all optional. If your range resides on another sheet, it is the sheet_text that you need, eg
Note that the Sheet name is supplied to the first ADDRESS function only
An add-in with a programming error is causing problems.
1. Choose Tools > Add-ins
2. Note which are checked
3. Uncheck all (but one)
4. Restart XL
5. If no error, check the next one and repeat from step 4
Got the error? Uncheck the one you last checked. If no luck, see if there's an .xla file in the startup directory (see previous tip). Scanner software typically place itself there, sometimes with code errors like the one in question.
Not an add-in? It will be a hidden workbook then.
1. Try (in Excel) Windows, Unhide and write down which files are listed.
2. Unhide them.
3. Now go to the VBE (press Alt + F11).
4. On the left side there should be the project explorer.
5. Select the first project you unhid
6. From the menu choose Debug, compile
7. No Errors? Select the next project listed, repeat 6.
18. I have an Excel file that I use every day at work. Some time ago, mysteriously, the file began opening two copies of itself every time I double-click the icon. If I close one of the copies, both close. Any changes made to one copy show up in the other?
This sounds like you just have two windows open that are displaying the same workbook.
Select the window that you want to close, and press Ctrl + w
OR, you can close the extra window by clicking the lower of the two X buttons in the upper right corner of the screen (don't click the top X button, as that will close Excel). Then save the workbook.
19. When I click on an Excel file to open it, the file opens but an error message is displayed that says a file with that name is already open and that I can not open two files open with the same name?
If you are sure it's not really happening (you may have Book1 open and then attempt to open a Book1 from another folder), then try re-registering Excel.
1. On the Windows taskbar, click the Start button
2. Choose Run
3. Type "C:Program FilesMicrosoft OfficeOfficeExcel.Exe" /regserver (include the quotes) -- adapt to fit your path.
Other things to check:
1. In Excel, choose Tools>Options.
2. On the General tab, make sure "Ignore Other Applications" is not checked
This could really be anything. Considering that, it's remarkably often a corrupt toolbar file. So worth a try the following:
1. Close Excel (if it managed to open).
2. Reboot if necessary.
3. Locate all files (with Start > Search etc) with extension .xlb
4. Rename the extension(s) to something like .old
5. Start XL again.
Try opening Excel without any addins or hidden workbooks:
1. On the Windows taskbar, click the Start button
2. Choose Run
3. Type "C:Program FilesMicrosoft OfficeOfficeExcel.Exe" /Automation (include the quotes) -- adapt to fit your path.
If your error disappears, there is either an add-in or a (hidden) workbook that is loaded upon XL's start, which is causing problems.
* Try locating the XLSTART directory, move everything from there.
* In XL: Tools, Addins, note the ones checked and uncheck them one at the time, each time restarting XL
* In XL, Tools, options, general. Check if a path is entered after "Alternate startup file location". Clear it.
Another option is to open XL in Safe mode:
"C:Program FilesMicrosoft OfficeOfficeExcel.exe" /Safe
Also, you might try:
"C:Program FilesMicrosoft OfficeOfficeExcel.exe" /Regserver
Another possible problem is a corruption of your toolbar customisation file:
* Locate all files with extension .xlb
* Rename the extension(s) to something like .old
* Now try and start XL again.
Select the row below the section that you want to freeze. Then, on the Ribbon's View tab, click Freeze Panes, and click the Freeze Panes option.
Lock the title row in Excel 2003, and earlier versions:
Assuming title row is 1, select cell A2, then choose Window > Freeze Panes
Excel may be remembering some data that was in row 500, but has been deleted. To go to the cell which is currently the "Last Cell" in the worksheet, hold the Ctrl key, and press the End key. If the Last Cell (cell J500 in this example) is outside the range of cells that is actually being used, you can reset the used range, so the scroll bar works correctly.
Note: If any cells contain references to the deleted cells, those references will be replaced with a #REF! error. If you have cells that are formatted, but outside the range that contains data, the formatting will be lost.
To manually reset the used range:
1. Select the last cell that contains data in the worksheet
2. To delete any unused rows:
o Move down one row from the last cell with data.
o Hold the Ctrl and Shift keys, and press the Down Arrow key
o Right-click in the selected cells, and, from the shortcut menu, choose Delete
o Select Entire Row, click OK.
3. To delete any unused columns:
o Move right one column from the last cell with data.
o Hold the Ctrl and Shift keys, and press the Right Arrow key
o Right-click in the selected cells, and, from the shortcut menu, choose Delete
o Select Entire Column, click OK.
4. Save the file. Note: In older versions of Excel, you may have to Save, then close and re-open the file before the used range is reset.
If the high contrast setting is turned on you won't see the fill colour. There's information in the following MSKB article:
The fill color, the fill pattern, or the line color of a WordArt or AutoShape object in an Office document does not change
Fill patterns were designed for older printers with low resolution. Today's printers may print the patterns in such fine detail, and so small, that the pattern sometimes disappears. If you really, need to print patterns, you could roll your own, using white rectangles, with transparent border, and lines, etc.
Create a square that represents one repeat of the pattern. For example, if you want closely spaced vertical lines, draw a 1" white square with 6 vertical 1" black lines. Use the alignment and spacing commands to finish the pattern. Group the objects in each pattern, copy, and paste into the data series. Then format the series to have a black border. If necessary, set the Picture format (in Fill Effects) to Stack. It doesn't look too good on the screen, but prints beautifully
I need more than 256 columns and/or 65536 Rows.
In Excel 2003, and earlier versions, that's the limit. Excel 2007 has 1,048,576 rows and 16,384 columns.
How many worksheets I can put in a workbook?
It is not a limit per se, but of course there is a practical one which depends on the computer resources.
How many characters can be placed in a cell?
In Excel 97 and above, you can have up to 32K characters in a worksheet cell, but only the first 1000 or so are displayed (the exact number depends on your font and display characteristics). However, you can increase the number of characters displayed if you add line breaks in the cell (press Alt+Enter).
Before protecting the worksheet:
1. Select all cells that users ARE allowed to change.
2. On the keyboard, press Ctrl + 1 to open the Format Cells dialog box
3. On the Protection tab, remove the check mark from Locked.
4. Click OK, to close the dialog box
Protect the worksheet in Excel 2007
1. On the Excel Ribbon, click the Review tab
2. Click Protect Sheet.
Protect the worksheet in Excel 2003
1. Click the Tools menu, then click Protection
It is not a limit per se, but of course there is a practical one which depends on the computer resources.
Well, there are passwords and there are passwords.
* One is to open the file
* one other is to unprotect the worksheet
* still another is to unprotect the workbook
* yet another one is to unprotect the Excel VBA macro cod
There is no foolproof method for this, and most solutions limit themselves to require macros enabled on opening. This said, creating your own system is a fun challenge allowing lots of "evil creativity".
This needs a macro to run first:
.EnableAutoFilter = True
.Protect DrawingObjects:=True, _
contents:=True, Scenarios:=True, _
Goto Tools>Options/Edit and uncheck the Fixed decimals checkbox.
If you have Google Desktop Search installed, turn it off in Excel.
To turn that option off in Excel 2002 and Excel 2003:
* Choose Tools | AutoCorrect Options
* Select the AutoFormat as you type tab
* Remove the check mark from Internet and network paths with hyperlinks
* Click OK
To undo the hyperlink creation in all versions:
* Type the email address and press Enter
* Immediately, press Ctrl+Z. This is a shortcut for Undo, and will convert the hyperlink back to text.
Excel only maintains 15 significant digits for numbers and cannot be used for credit cards which require 16 digits. If you don't need to do math on them, input long numbers into a range that you've formatted as text (Format>Cells, Number tab).
To prevent both of the above issues (hyperlinks and 16-digit numbers) -- as well as entries that are automatically converted to dates (e.g. 3/4) -- type a single quote ( ' ) before the entry. Excel will treat the cell as pure text and change nothing.
36. I just began to design an intricate spreadsheet and after over an hour got an error message in Excel - which then closed automatically. Unfortunately, I did not save my file. Excel did not automatically recover the file. Is there anything I can do to get it back?
No. There should be an Autosave add-in in Tools > Add-Ins menu that you now might consider start using. (But then again, you may not want to overwrite an existing file with every little test you do in it, so be careful).
Yes it is. While in Excel, you must first click on Tools on the menu bar, select Options on the pull down window. In the View box, deselect gridlines. Then you can copy and paste the spreadsheet into Powerpoint.
38. In Excel, I am trying to open a Lotus 1-2-3 WK4 file. The file contains a bitmap. I am receiving the following error message: "This program has performed an illegal operation and will be shut down, Invalid Page Fault in Excel.exe at 0137:304997F4." Why am I receiving this message?
This is a common problem, and despite the message, you're still a law-abiding citizen. You can solve your dilemma by installing Excel SR-1, or by removing the bitmap, or by opening the file and then saving the .wk4 file in Excel 7.0 before opening the file in Excel.
Under the Tools, Option, General tab, uncheck the R1C1 box. This should straighten things out.
You have a corrupted User Profile on the server. To solve this, you must delete the profile. However, deleting the profile from the local drive will not correct the problem. You must delete the profile from the server.
41. I am working in MS Excel 5.0 in Windows 95. I have a spreadsheet where the gridlines are not printing. The sheet is 87 rows by 49 columns, "A1 to AW87." The gridlines are not printing from column "AP" to "AW." The other gridlines are printing correctly. What is the problem?
First, make sure Gridlines are turned on (File, Page Setup, Sheet tab, Gridlines checked). If the gridlines are still not printing, perform a Copy and Paste of the affected gridlines to New File.
This occurs if the 1904 date system option is not checked. This is found under the Tools/Options menu, and on the Calculation tab. It is also in the Workbook Options section.
When you select a cell and press Delete, Excel deletes the contents of the cell. But you may want to delete other elements as well like formatting, comments, or everything: 1. Select the cell(s) from which you want to delete something. 2. Choose Edit + Clear. 3. From the menu list, select the elements you want to delete.
This can be accomplished by highlighting the area to be printed. Then go to File, print area, set print area, this will print only what is selected.
45. I am using Excel, and want to create a line graph with the data on my spreadsheet, ranging from January to December. However, I don not want the empty cells to be represented as zeroes in the line graph. Is there a way to graph all months without displaying the zero values?
This can be accomplished by going to Tools, Options, menu, choose the Graph Tab, under "Plot empty cells as" option, Select, "not plotted (leave blank)".
46. In Excel, how can I prevent a row from printing in a range if certain cells in a particular column equal 0. I do not want to extract the records to another section of the sheet. Is it possible to hide those rows, then print, then unhide?
There's a menu-driven solution that's called AutoFilter. Just put the cursor anywhere in the range of cells you want to print, open the Data menu, select Filter, then choose AutoFilter. When you do, Excel will add dropdown-arrows to the cell at the top of each column. Click that dropdown arrow for the column that contains the zero entries. Select the Custom... option. That opens the Custom AutoFilter dialog box. In the Show Rows Where fields section, choose the greater-than sign for the operator and enter 0 (the number) for the value. Then, click OK. When you do, Excel will display only the rows that contain values greater than zero in the column you selected. To re-display those rows, open the Data menu, choose Filter, and choose AutoFilter again to deactivate it.
Select the Tools menu and then the Options menu. The Options menu will give you a property sheet with several tabs. Select the tab labeled "EDIT". Check the box for Fixed Decimal. This action allows you to increase or decrease the "PLACES" field. Note: The default for PLACES is 2. When you have selected the proper is setting click OK to save the property.
48. I have several objects that are in my Excel spreadsheet. They are pictures, charts, text boxes; simply a variety of objects to make my spreadsheet look attractive. It is rather difficult to align many of these objects. Is there an easier way than to move objects independently?
Objects can be snapped to alignment by using the Drawing toolbar through the following steps: 1. Hold down the Ctrl key and click each object you want to align. 2. On the Drawing toolbar, click the Draw button. 3. Choose Align or Distribute from the pop-up menu. 4. Select the alignment option you prefer; the diagrams next to each option illustrate the result. Excel aligns the objects. Because the objects are still selected, you can also move them together to another location without disturbing the alignment.
49. While entering a series of numbers I was manually entering a comma. Excel auto-formatted the field to accommodate the comma like 1,200. I erroneously entered 12,00. Obviously the comma was placed in the incorrect position, but Excel interpreted this as text. When I wrote my sum formula, the results were not as expected. How can this be avoided in the future?
Format the column ahead of time to specify the format to show commas, decimal places, etc. This way you don't have to enter the comma or decimal places while keying the numbers.
1. Right-click a chart that uses the defaults you prefer. 2. Choose Chart Type from the menu that appears. 3. Click the Set as Default Chart button. 4. Click OK. From now on, Excel will create charts based on the default you prefer, this will save you a couple of clicks per chart.
First you will need to click on the specific cell that you want the comment to refer to. Go to Insert on the menu bar and then select Comment. You can now type your message. After typing your message you can click outside of the comment box onto another cell. You can tell that there is a comment associated with that cell because a small red triangle will be in the upper right hand corner of the cell. When you want to see the comment, you can either put your mouse over the cell and it will appear or you can set Excel to always show comments. To do this you will need to go to Tools on the Menu bar and select Options and then click on the View tab. Then, choose the Comment & Indicator option in the Comments section and then click on OK.
52. Please tell me how I can split combined data into separate columns in an Excel spreadsheet. For example I have a spreadsheet that contains the city and state (i.e. Kennett Square, PA) in the same column and I would like to have them in two separate columns?
In Microsoft Excel you can convert delimited text into columns. To do this you must first insert a blank column to the right of the data you want to split. Next, select the text you want to convert and on the menu bar choose Data then Text to Columns. The Delimited wizard will then open. You then need to select Delimited option and click on Next. On the next sheet of the wizard, select the character that separates your data from the Delimiters panel (i.e. comma, space, tab or whatever may be separating the text in your spreadsheet, in this case it would be a comma between the city and state). Take notice of the changes shown in the Data Preview panel. Click Next and look through the Data Preview panel to make sure your data is converted to the format you like. Make any necessary changes in the Column Data Format panel. Finally, click Finish.
Press CTRL+F4 which will not only close the workbooks, but Excel as well. Excel will ask you if you would like to save your files.
To do this you will need to select the range you want to clear. Once you have it highlighted then drag the fill handle (the small black box in the lower-right corner of the range) up or to the left to clear what you want in the range.
This requires customizing your toolbar as follows: 1. Choose Tools + Customize to launch the Customize dialog box. 2. Click the Commands tab. 3. In the Categories list, select File. 4. In the Commands list, drag the Page Setup button (it has the words Page Setup and no picture) to the Standard toolbar. We recommend positioning it just to the left of your Print button. 5. Click Close to make the Customize dialog box disappear and save your changes.
Yes, you can use the NETWORKDAYS function. The following is the syntax for the function: NETWORKDAYS(START_DATE,END_DATE,HOLIDAYS) START_DATE is a date that represents the start date. END_DATE is a date that represents the end date. HOLIDAYS is an optional range of one or more dates to exclude from the working calendar.
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
Yes you can. You will need to open Excel and then open the .prn file. This will open up the Text Import Wizard. Follow through the wizard to determine the outcome of how you want you text to be imported into Excel.
59. 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.
1. Press and hold the shift key while clicking on the File menu
2. Choose Close All. Holding down the shift key changes the File Menu option from Close to Close All.
Yes!! Even though your system is new, it still needs to have annual maintenance performed. This will help keep the warranty in effect. Most all equipment manufactures state in the terms of their warranty that annual maintenance must be performed by a qualified technician. Keeping your system maintained will give you assurance that it is operating at peak efficiency and safety.
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
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".
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.
Run a macro that has this line:
ActiveCell.Value = ActiveSheet.Name
Run a macro that has this line:
ActiveWorkbook.Windows(1).Caption = ActiveWorkbook.FullName
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.
Rename the macro to be AUTO_OPEN.
Or - add the macro to the Workbook_Open procedure on the code pane for ThisWorkbook.
Turn off the screen updating during macro execution. Insert this line at the beginning of your macro:
Application.ScreenUpdating = False
Go into Tools>Options. On the Transition tab, check the box for Transition formula entry.
Turn off the screen updating during macro execution. Insert this line at the beginning of your macro:
Application.ScreenUpdating = False
Use a line of code like this:
OrigColor = Selection.Interior.ColorIndex
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
73. 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.
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.
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.
76. 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
Now, enter the function =Sumred(B1:B10) in your worksheet.
77. 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.
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
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-i
80. 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
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)
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.
Microsoft offers a new Tahoma font with the Euro symbol.
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.
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.
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.
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.
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:
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:
Excel 2000 (due out later this year) adds support for the Euro.
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")
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.
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:
Then, as you change data on Sheet 2 and the totals change, they will automatically update on Sheet 1.
93. 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.
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.
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.
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.
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.
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.
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:
=MID(CELL("filename"), FIND("]",CELL("filename"))+1, LEN(CELL("filename"))-FIND("]", CELL("filename")))
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.
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 charts.
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'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.
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 OK.
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.
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:
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.
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 wages:
Make sure the cell that contains the formula is formatted as a number, not a time.
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 23, 2000.
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 correctly.
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.
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 a formula.
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.
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.
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 Step 6.)
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 looks empty)
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.
Application.DisplayAlerts = False
'code to save, overwrite, delete, whatever goes here
Application.DisplayAlerts = True
If MsgBox("Are you sure ?", vbYesNo + vbQuestion) = vbNo _
Then Exit Sub
'Code goes here instead of
MsgBox "Actions here"
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.
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 workbook
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 Toolbox.
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...
Assign the toolbar button to this macro, which should be in a standard VBA module:
Sub ShowForm ()
Combine the two solutions above:
Private Sub Workbook_Open()
Go to Tools > VBAProject properties, lock the project for viewing, and enter a password.
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.)
'your code here
Be sure to protect your macro code to hide the sheet password.
You cannot. A workaround is to simulate keystrokes with the SendKeys method
If ActiveSheet.UsedRange.Count < 2 Then
MsgBox Columns("A:A").Find(What:="", LookAt:=xlWhole).Row
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.
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
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
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.
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 required.
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.
To combine chart types, follow these steps:
1. If the Chart toolbar isn't already displayed, right-click any Toolbar and select Chart.
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).
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
1. Must Always highlight the data before formatting
2. Click Format
3. Then go to Cells
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
1. Click Tools
2. Scroll down to Protection, then Protect Sheet
3. Enter a password, Click OK
4. Re-enter password,
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.