0
Is there a way to globally change the year in a multi-sheet spreadsheet that contains many dates?
Shannonkpp
If it is the same year that you want to change from then it can be done one year at a time. You can do individually by sheets, as outlined here, or globally using a Macro, outline later in this answer. So say you wanted to change all the dates with 2007 to 2009, then you would do the following. Open the Edit menu and pick Replace, or press and hold the Ctrl key and press H. In the Find What box type 2007 and in the Replace With box type 2009. Note that this would also change any cells with the value 2007 or the digits 2007 as part of another value like 20075 in them and also any text with 2007 in it. So it is something you need to use with care.
If you want to do it all at once across all worksheets then you need to use a Macro.
On the Tools menu in Microsoft Excel, point to Macro, and then click Visual Basic Editor. On the Insert menu, click Module. Type or copy the code into the code window of the module. If you want to run the macro from the module window, press F5. When you're finished writing your macro, click Close and Return to Microsoft Excel on the File menu. By pressing Alt-F8 you will get the Macro menu and you can run the macro from there. You will need to edit it for different years. The same precautions, in terms of other things that may also be change will apply here too. It would be advisable to make a copy of your workbook first, before trying this in case it is not exactly what you want. The code would be as follows:
Sub DateChanger()
'
' Macro to change dates on all sheets
'
Dim wsh As Worksheet
For Each wsh In ActiveWorkbook.Worksheets
wsh.Select
Cells.Replace What:="2007", Replacement:="2009", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Next wsh
End Sub
Wiki User
∙ 14y ago
Add your answer:
Earn +20 pts
What would you change on a spreadsheet if the space is not big enough?
you make the spreadsheet bigger
Anyone who can access the spreadsheet cant read or change the data it contains?
the cells are protected and hidden. thatswhy ther are not aviable to edit or view. ---chandu KIMS
When you change the entry in a spreadsheet it is called what?
It can be called editing.
How do you change default margins on Excel spreadsheet?
How to change default page margins in excel.
What is a constant on a spreadsheet model?
It usually is a baseline number that does not change.
What kinds of information should be locked on a spreadsheet?
You lock things that you don't want people to be able to change. You might have a critical formula or value in a spreadsheet that you don't want people to change. You might have wage rates in a spreadsheet calculating pay, that you don't want people to change to give themselves more wages. So anything like that, that is important to the spreadsheet, or that you do not want to be maliciously changed, you can lock.
What is difference between linking and embedding a spreadsheet in a word document?
If it is linked then when the data in the spreadsheet is changed, that change will show in the Word document. If it is embedded, then changes in the spreadsheet will not have any effect on the figures in the Word document.
When you change the way data in a spreadsheet looks what is it called?
Formatting data.
What does Excel use to automatically to modify values?
Formulas will change their results as other things change in a spreadsheet.
What is the meaning of the term 'automatic re-calculation' as it relates to spreadsheet theory?
The whole basis of a spreadsheet's usefulness is its ability to recalculate all its formulas every time a change is made to any cell on the spreadsheet. This keeps the spreadsheet up to date when new values are put in. On a paper-based spreadsheet, one figure being changed requires that the user manually recalculates everything in their spreadsheet. An electronic spreadsheet will do it automatically, which is what is meant by automatic recalculation. There is an option to turn this off and have the spreadsheet only recalculate when the user hits a specified key, usually the F9 key. This enables the user to change a figure and then look at the existing figures before they are changed, to more easily note what changes occur when they recalculate. On an electronic spreadsheet manual calculation does not mean that the user has to do all the calculations themselves, as they would on paper, but just that they can tell the spreadsheet when to recalculate all formulas, rather than it being done every time they make a change to anything.
What is an external Excel spreadsheet linked into a Word document?
It is still an Excel spreadsheet. The workbook does not change, just because it is linked to another document to form a compound document.
How do you change color in Excel spreadsheet when cell protected?
If this feature has been protected then you must "unprotect" the worksheet first. If you do not have the password to do this then I'm afraid you are using a spreadsheet in which the creator did not want you to change such things (and they probably had a good reason to do so).
