Unfortunately, Excel will not save this setting after you close the window. Right-click the Sheet Name tab on which the scroll area should be limited, select View Code, and then enter the following:. Any macro that tries to select a range outside this scroll area including selections of entire rows and columns will no longer be able to do so. This is true particularly for recorded macros, as they often use selections. If your macros do select a range outside the scrollable area, you can easily modify any existing macros so that they are not limited to a specific scroll area while operating.
Our recorded macro selects cell Z and formats it to boldface. It then selects the worksheet named Daily Budget, selects cell T on that sheet, and un-bolds it. We added ActiveSheet. When we select another worksheet DailyBudget ,we again allow the code to select any cell on this worksheet and set the scroll area for this worksheet back to the desired range.
A third method, the most flexible, automatically limits the scroll area to the used range on the worksheet within which you place the code. To use this method, right-click the Sheet Name tab on which you want the scroll area limited, select View Code, and enter the following code:. The preceding macro will run automatically each time you activate the worksheet in which you placed it.
However, you might encounter a problem with this macro when you need to actually enter data outside the existing used range. To avoid this problem, simply use a standard macro that will reset your scroll area back to the full sheet. If you want to, you can make your macro easier to run by assigning it to a shortcut key. Select ResetScrollArea the name of your macro , click Options, and assign a shortcut key. Each time you need to add data outside the established bounds of your worksheet, run the ResetScrollArea macro to readjust the borders. After you run the macro, make any changes you were unable to make while the scroll area was limited.
Activation of the worksheet will cause the code to run and limit the scroll area to the desired range. You may want to let users change cells that contain data without providing them access to change formulas. You can keep cells containing formulas under lock and key without having to protect your entire sheet or workbook. When we create a spreadsheet, most of us need to use formulas of some sort.
Excel Hacks, 2nd Edition by Raina Hawley, David Hawley
The easiest and most common way of barring people from playing with your formulas is to protect your worksheet. Sometimes you do not want to go this far. Three solutions are: locking the formula cells, using data-validation on the formula cells, and auto-toggling worksheet protection, although none of these solutions is bulletproof.
By default, all cells on a worksheet are locked; however, this has no effect unless worksheet protection has been applied. Here is a very easy way to apply worksheet protection so that only formula cells are locked and protected.
Split a sheet into panes
Click OK. Select Formulas from the Go To Special dialog and, if needed, limit the formulas to the subtypes underneath. Now you need to protect your sheet. Apply a password if required and click OK. The preceding method certainly saves a lot of time and eliminates possible errors locating formulas so that you can protect them. Unfortunately, it can also prevent users from using certain features, such as sorting, formatting changes, aligning text, and many others you might not be concerned with, even when in an unlocked cell.
Data validation is far from bulletproof when it comes to preventing users from entering nonvalidated data into cells. Users can still paste into a validated cell any data they want and, in doing so, remove the validation from that cell unless the copied cell also contains data validation, in which case this validation would override the original validation. Now select Formulas from the Go To Special dialog and, if needed, limit the formulas to the subtypes underneath. This method will prevent a user from accidentally overtyping into any formula cells, although, as stressed in the earlier warning, it is not a fully secure method and should be used only for accidental overtyping, etc.
To start, ensure that only the cells you want protected are locked and that all other cells are unlocked. Right-click the Sheet Name tab, select View Code from the pop-up menu, and enter the following code:. If a password is used, change the word Secret to your password. Now, each time you select a cell that is locked, your worksheet will automatically protect itself. The moment you select any cell that is not locked, your worksheet will unprotect itself. The keyword used in the code, Target , will refer only to the cell that is active at the time of selection.
For this reason, it is important to note that if a user selects a range of cells with the active cell being an unlocked cell , it is possible for him to delete the entire selection because the target cell is unlocked and, therefore, the worksheet will automatically unprotect itself. The improved functionality in Conditional Formatting in Excel makes this hack suitable for prior versions only.
Viewing Two Worksheets At Once (Microsoft Excel)
People frequently have to identify duplicated data within a list or table, and doing this manually can be very time-consuming and error-prone. Select the top-left cell, A1, and drag it over and down to H In the field to its right, enter the following code:. All those cells containing duplicate data should be lit up like a Christmas tree in the color you chose, making it much easier to eyeball duplicate data and delete, move, or alter it as appropriate.
By this we mean that the conditional formatting formula in cell A1 will read as follows:. To do this, select cell A1 the cell in the top-left corner and drag it down to H Again, it is important that A1 is the active cell in your selection. Click in the white box to the right of Formula Is. Click the Format button, select a color you want to apply to identify data that appears more than three times, and click OK; or for pre versions, go to the Patterns page tab, select a color, and click OK. Click New Rule, repeat the steps a third time pre, from the Condition 3 box, select Formula Is , and add the following formula:.
Again, select a different color from those previously chosen. You will have different cell colors depending on the number of times your data appears within your table of data. In Excel , your Conditions are limited only by your system memory, whereas in pre-Excel versions, the limit is 3 Conditions. Although most toolbars you build apply to just about any work you do, sometimes the functionality of a custom toolbar applies to only one workbook in particular. With this hack, you can tie custom toolbars to their respective workbooks. The Quick Access Toolbar options in Excel make this hack suitable for prior versions only.
What if your custom toolbar contains recorded macros meant only for a specific workbook? You can do this by inserting some very simple code into the private module of the workbook. Change the text MyCustomToolbar to the name of your own custom toolbar. Reactivate the appropriate workbook, and poof! You even can take this down a level, making the custom toolbar available only to a specific worksheet within the workbook. Right-click the Sheet Name tab of the sheet on which you want the toolbar to be accessible and select View Code.
Enter this code:. The firing of the code changes the Enable property of your custom toolbar to False so that it cannot be seen or displayed. The second procedure is fired each time you activate the worksheet and sets the Enable property of your custom toolbar to True so that it can be made visible. The line of code that reads Application. CommandBars "MyCustomToolbar". Switch worksheets and the toolbar is gone; switch back and it reappears like magic.
In Excel, a formula reference can be either relative or absolute, but sometimes you want to move cells that use relative references without making the references absolute.
Once you do this, no matter where you copy your formula, the formula will reference the same cells. Sometimes, however, you may have already set up a lot of formulas that contain not absolute references, but relative references. You would usually do this so that when you copy the original cell formula down or across, the row and column references change accordingly. If you already set up your formulas using only relative references, or perhaps a mix of relative and absolute references, you can reproduce the same formulas in another range on the same worksheet, another sheet in the same workbook, or perhaps even another sheet in a different workbook.
Of course, these could be any symbols you are sure are not being used in any of the formulas. Click Replace All. The equals sign in all the formulas on your worksheet will be replaced with the ampersand sign. Your formulas now should be referencing the same cell references as your originals. Ah, phantom links. External links are links that reference another workbook. Unexpected external linking can occur for various reasons, many of them stemming from moving or copying charts, chart sheets, or worksheets into another work-book.
Here are a few ways to deal with the spooky phantom link problem. First, you need to see whether you have any real external links nonphantom that you forgot about. If you are not sure whether you have real external links, start looking in the most obvious place: your formulas. In later versions of Excel, Find… and Replace… provide the option of searching within the sheet or workbook.
Once you find the formula links, simply change the formula accordingly or delete it altogether. Whether you change the formula or delete it depends on the situation, and only you can decide which route to take. The Delete Links Wizard is designed to find and delete links such as defined name links, hidden name links, chart links, Microsoft query links, and object links. However, in our experience, it does not find phantom links. To do this, we like to start from within the Excel workbook containing the phantom links. Then, from the Paste Name dialog, click Paste Link.
This will create a list of all the names in your workbook, with their referenced ranges in the corresponding column. F3 will also bring up the Paste Name dialog and works in all versions. Another potential source of links is in your charts. Links also can lurk in objects, such as text boxes, autoshapes, etc. Objects can try to reference an external workbook.
This will select all objects on the worksheet.
- mac auf windows pc installieren anleitung!
- giveawayoftheday mac blu ray player.
- Create a New Worksheet.
- View multiple panes, sheets, or workbooks;
- View multiple sheets in one workbook.
You should do this on a copy of your workbook. Then, with all objects selected, you can delete, save, close, and reopen your copy to see whether this has eliminated the problem. Finally, the last not-so-obvious place to check for real links is in the hidden sheets that you might have cleverly created and forgotten about. If the Unhide option on the right-click Sheet submenu is grayed out, that means you have no hidden sheets.
Sometimes you can simply select the unwanted link, click Change Source, and then refer the link back to itself. Often, though, you will be told that one of your formulas contains an error, and you will not be able to do this. Create a real link between the two by opening both workbooks. Now click a cell in the well-behaved workbook and press Enter so that you have a true external link to the other workbook. Save your workbook again and delete the cell in which you created the true external link. Finally, save your file. This often eliminates the offending phantom link, as Excel now realizes you have deleted the external link to the workbook.
If this does not solve the problem, however, try these next steps, but make sure you save a copy of your workbook first. The following process involves deleting data permanently. Therefore, before you begin, create a backup copy of your workbook. Neglecting to do so could create new problems for you. With the problem workbook open, delete one sheet, save, and then close and re-open the workbook. If you are not prompted to update your missing links, the sheet you deleted contained the phantom link. You will need to add a new sheet before you delete the last sheet, as any workbook must have at least one sheet.
Open the copy of your workbook the one that still has data in it and make another copy. Are you absolutely sure you saved a copy? Save, close, and reopen the problem worksheet. If you are not prompted to update those links, you found the problem and your reward is to redo that block of cells. If you are prompted to update the links, continue deleting cells until you are no longer prompted.
Then redo the badly behaved cells. We hope these techniques will save you some of the frustration that arises when those dreaded phantom links appear in your workbooks. Ever notice that your workbook is increasing in size at an alarming rate for no apparent reason? There are several causes of workbook bloat, and some slimming solutions. The introduction of workbook size being limited only by the amount of memory your system in Excel will allow should eliminate workbook bloat; however, you may find some of the following tips handy if you have a particularly large workbook.
Workbook bloat in Excel is much the same thing. Workbook bloat is a term for a workbook that has had so much done to it that it has swollen to such a size that it no longer functions correctly. We checked out the size of a typical workbook containing a fairly large amount of data. With data only, the workbook file size was 1. Then we added a pivot table referencing four entire columns for its data source and noted that the file size increased dramatically to 2.
Add some formatting and your typical workbook size has blown out to almost double by performing a few actions. One of the more common causes of file bloat, particularly in earlier versions of Excel, is the application of formats to entire columns or rows rather than to just the data range in use.
Another mistake is referencing entire columns as the data source for charts and pivot tables rather than just the cells with actual data in them. To fix these problems, you will need to eliminate all the superfluous formatting and restrict your data source to only the useful range of cells.
Having manually located the cell you know to be your last cell containing legitimate data, highlight the row immediately following it. Now apply the same logic to unwanted formatting lurking in your columns. Locate the cell in the last column containing data and click the column header of the column immediately to the right. If you have macros, now you need to address the modules that the macro code resides in. This is a fairly quick, painless, and straightforward process that entails exporting all modules this functionality is not available on Mac OS X and UserForms to your hard drive and then deleting the existing modules and UserForms, pressing Save, and importing the modules you exported.
To do this, go into the Visual Basic Editor and, from within the Project Explorer, right-click each module and select Remove Module1 or whatever the name of the module happens to be. When you are asked whether you want to export your module before removing it, say Yes, taking note of the path. Do this for each module in turn, as well as for any UserForms you might have. Once you have done all this, save the workbook. Following this process will create a text file of each module and that, in turn, removes all extra baggage that the modules might be holding.
The Web contains some free utilities that will automate this task to some degree, but we have heard cases of these utilities making a mess of code or even increasing file sizes. If you do use one of them, always save a backup copy of your file first, as the developers will take no responsibility for any loss of data. If, after performing the previous steps, you still believe your file size is unrealistically large, another possible suspect is referencing unused cells in PivotTables and PivotCharts.
This is true particularly of PivotTables, as people frequently reference all rows in order to avoid manually updating ranges as new data is added. If this is your modus operandi, use dynamic named ranges [ Create Ranges That Expand and Contract ] for your data sources instead. If you still believe your workbook is too large, it is possible that your workbook or component sheets are corrupt. Unfortunately, determining a point of corruption requires a manual process of elimination. If this menu option is grayed out, you have no hidden worksheets to worry about.
With all your sheets visible, start from the sheet on the far left and move one-by-one to the right. To replace a corrupt sheet in your workbook, create a new worksheet, manually select the data in the corrupt sheet, and cut do not copy and paste it into the new sheet. Delete the corrupt sheet from your workbook, save, and repeat. By cutting rather than copying, Excel automatically will follow the data to the new sheet, keeping references intact. Workbook corruption can mean the loss of vital data, costing you more than just money. This hack explores some methods that might recover your data.
Workbooks sometimes become corrupt for no apparent reason. This can cause all sorts of problems, especially if the workbook is vital and for whatever reason you have no backup. Lesson 1: Always back up your data somewhere. Realistically, though, this does not always happen, and corruption can, of course, occur right before your regularly scheduled backup. To add to your frustration, even though you know your workbook is corrupt, you sometimes might still be able to open it and even perform certain actions in it. If you can open the offending workbook, before doing anything else, be sure to save a copy of it; otherwise, you might regret it.
If you have a copy, you can always seek professional help! Now, try opening the workbook in a later version of Excel and simply saving it again. Obviously this is not possible if you already are using the latest version of Excel. Finally, try opening your file and saving it in SYLK. Note that when you save a workbook in this format, only the active worksheet is saved.
So, you will have to do the same for each worksheet.
Reopen the file and save it in a desired format such as. PivotTables and charts they can be saved, but are lost when the file is opened in this format again in Excel. Also, shared workbooks in versions of Excel before Excel will no longer be shared. If your workbook is corrupt to the point that you cannot even open it, open your spreadsheet in Microsoft Word or via the Spreadsheet viewer, which can be downloaded from the Microsoft web site, then copy your data from the open file note that much of your formatting, formulas, etc, will be lost.
Next, open a new workbook and create an external link to the corrupt workbook—e. Copy this link down as many rows and across as many columns as needed. Do the same for each worksheet in the workbook. If you cannot remember any of the names of the worksheets, create any old sheet name using the correct filename path, and Excel will display the sheet names for you when you press Enter. One final thing you can do is visit the OpenOffice. Except for different names for different tools and commands, OpenOffice. In fact, about 96 percent of the formulas used in Excel can be created and applied by using the spreadsheet in OpenOffice.
To download the free version of OpenOffice. Then install the program. In many cases, your Excel data can be recovered. Sadly, if none of these methods works, you probably will have to pay to try to have your workbook recovered with special software. After purchase and installation, run the ExcelFix program. Click Select File, select a corrupt file, and then click Diagnose to recover the file.
You should now see the recovered file in the workbook viewer. Click Save Workbook to save the workbook into a new readable file that you can open from Excel. Also available is a demo version that does not enable you to save the file, but all versions of the program enable you to start again and recover as many files as you want. Stay ahead with the world's most comprehensive technology and business learning platform. With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.
Start Free Trial No credit card required. Reducing Workbook and Worksheet Frustration. Hacks 1— Structural Tips. Unnecessarily spreading data over many different workbooks Unnecessarily spreading data over numerous worksheets Unnecessarily spreading data over different tables Having blank columns and rows in tables of data Leaving blank cells for repeated data.
Formatting Tips. Formula Tips. Create a Personal View of Your Workbooks. Enter Data into Multiple Worksheets Simultaneously. Grouping Worksheets Manually. Warning When your worksheets are grouped together, you can look up to the title bar and see the word Group in square brackets. Grouping Worksheets Automatically.
Tip In most cases, you will be taken directly to the private module when you right-click on a workbook or worksheet and select View Code. Prevent Users from Performing Certain Actions. Warning Users can bypass all these protections by disabling macros entirely. Preventing Save As… in a Workbook. Warning Before trying this at home, be sure to save your workbook first.
Quick access menu in Excel to the private module for the workbook object. Preventing Users from Printing a Workbook. Preventing Users from Inserting More Worksheets. Prevent Seemingly Unnecessary Prompts. Prompting to Save Nonexistent Changes. Warning If your macro does not complete for any reason—a runtime error, for instance—Excel might never get to the line of code that turns alerts back on. Warning Once you have selected 2 -xlSheetVeryHidden from the Properties window, it might appear as though your selection had no effect. Properties window of a worksheet having its visible property set to 2 - xlSheetVeryHidden.
Customize the Templates Dialog and Default Workbook. Warning This hack assumes you have a single installation of Excel running on your computer. Creating Your Own Template Tab. Using a Custom Default Workbook. Once each worksheet you want to search is highlighted, perform a Find, and all highlighted worksheets will be searched. For example, let's say your worksheet names are the defaults, "Sheet1," "Sheet2," and "Sheet3". You have information in each worksheet, and you want to search for "computer" in Sheet1 and Sheet3.
To do this, you would follow these steps. Additional information See our Excel , Find , and search definitions for further information and related links. Microsoft Excel help and support.