Microsoft recommends several methods for recovering Excel data. Vista-Windows 10 if System Protection has been turned on. Mac and Solaris etc.
Hello All I have a very large number of data points, approximately 36000 pairs that was genrated from a test. I would like to reduce the number of datasets so it is not so large of a file to manipulate by skipping a every other row (or maybe 2 or 3 rows). Does anyone now of a formula or routine to create a new data set in this way? Thanks. Subscribe for Weekly Excel Tips and TricksHelpful tutorials delivered to your email!Similar Topics
I have a file that became too big due to phantom bloat, unused range saved by Excel and all that kind of reasons. Thank to previous posts on that board tackling that issue, I was able to find how to proceed to reduce the file back to its normal size. But I did so in a beta file (test file). The real file has become so big (103MB!) that Excel cannot even open it anymore! The file contains archive info that we do not have anywhere else. Is there anyway then to open the file or to reduce its size without opening it (through magics...)? I just honestly don't know how to retrieve that info before deleting that file. Thank you very much for your help.
Is it possible to import a single text file into Excel, splitting the incoming data across multiple worksheets rather than a single worksheet? Each each row on the text file would be evaluated by the value in one of it's 'columns' and written to the appropriate worksheet. The file is '!' delimited and has 11 columns for each row. Currently, I import the file into one worksheet and cut/paste the rows manually into new worksheets/tabs. The files are very large, sometimes exceeding the 65,536 row limit, which I could avoid if the data was split out coming in. Any help anyone could provide would be WONDERFUL. Thanks!
Is there a way to reduce the unused rows in a worksheet without copying the populated rows and pasting in a different tab? Any help would be great. Thanks.
Hi all. I have set up a workbook that is sent out to lots of different users. They each keep and use their own copy. I have set it up so that everything looks OK and is visible on MY screen, but I'm conscious that some users may have different screen sizes, different toolbars set up, and so on, which might make some parts not immediately visible to them. I have set up an auto-execute macro which automatically sets the zoom factor to best fit, for several of the worksheets, and this works fine. Here's the code that does it. Code: By repeating this code for each worksheet, I can make each one be zoomed just right. However, the file contains 8 sheets that are all identically laid out, except the number of rows is different. What I want to do is go to the worksheet that has the largest number of rows (it's always the same worksheet, so I know which one it is), set the zoom factor for THAT worksheet (which I can do, and it always has the same number of rows), and then take THAT zoom factor, whatever it is - and it will vary depending on the user - and apply that to the other worksheets that have a similar layout. I could just go through each worksheet and zoom it automatically, but that would mean that some of the sheets looked very large, others very small, and I'd like them to have a consistent appearance. I could also specify a range on each worksheet that was similar to the appropriate range on the longest worksheet, and zoom that automatically, but that's not ideal either, because some of the row heights vary from sheet to sheet, and again I'll end up with different font sizes. Anyone know how to do this ?
Could you guide me please.... I need to insert 5 blank rows repeatedly between every existing data rows for approximately 300 rows. If I go about doing the repeat short-cut 'Control+Y', it just repeats inserting ONE row only between the consecutive data row. Is there some command, which helps me highlight all the rows & allows me to insert 5 blank rows between every consecutive existing data row? Thanks in advance for your kind advice.
I want to add data points to the scatter plot that are not part of the data ie. add points to find their concentration on a standard curve.
Ok, I'm going to try and explain this. I am going to have 5 data points in cells such as A2:A6 All I need is to know if there is an upward trend or downward trend of the numbers.... I can't have something that simply takes the first and last number and checks if it's higher or lower. I need to actually see if they are trending upwards. Also how would i go about making it so that if they are trending upwards it shows an up arrow, and down arrow for downwards... And finally, if the trend continues at the same rate, how long it will take the number to reach a goal. Each data point will be 1 week apart, with an ultimate goal needed to be achieved at the end. This will be a completely separate formula on the sheet.... Any help?
I have never really used VBA and so am completely stuck at this problem. I need to create a macro which auto-populates a master worksheet from the individual user sheets in a shared workbook. Sheet 1 is the master sheet 'Team Stats'. There will be an undetermined number of individual worksheets to accomodate new staff. Each worksheet will be identical, using columns A-I with row 1 having the headings: Date, Name, Reference, Value, Price, Age, Purchased?, Destination, Add. Products (the last 3 columns will have a drop-down list which will be used to enter data into the cell). There will be a varying number of rows in each of the individual sheets. If possible I would like the macro to run every time data is entered into one of the individual worksheets. If this is not then it would be fien to update every time the workbook is opened. If anyone can help it would really cut down the time I spend collating these stats every day!
We have a large (4,000+ rows) excel worksheet from a prior employee that I would like to be able to use, but it is password protected and no one has the password. Is there a way to copy or save it as a different file that will not copy the password protection? It would save me a couple days of work if it is possible. Thanks
I have a large worksheet. Over 20,000 rows. I want to split every 900 rows into new worksheets. I need each worksheet to be 900 rows or less when done. Any macro would be helpful.
I have a large spreadsheet in Excel 2007. I am converting it to a .csv file to import in to another program. I need to delete all commas from all data. When I try to replace all commas (with nothing or with another character), I get the error message 'The formula you typed contains an error.' I have tried various formats (text, general, etc.) and various file types (.xls,.xlsx, .csv) and still get the same error.
Hi peeps I want to combine data from several worksheets into one worksheet. For example, I have data in Sheet1 (Columns A,B,C), data in Sheet2 (Columns A,B,C), data in Sheet3 (Columns A,B,C) all with varying amounts of rows. (All the rows contain text data). I need to combine all of the data from the 3 sheets into a single sheet, Sheet4 (Columns A,B,C), eliminating the empty rows. I've been looking into this for a while, and can't find anything that really helps. Anyone got any pointers of what to look into? Any help will be beautiful. Cheers I'm trying to write a formula that will automatically take a group of football (soccer) scores, compare them with a set of predictions, and then allocate points according to how close the predictions are to the actual scores. E.g. Manchester United 3 - 0 Arsenal Prediction: 4 - 1 Points: 1 I've found a formula online that works for most scores; the correct score (e.g. 3 - 0), a correct win (e.g. 4 - 1), and in the case of a draw (e.g Man Utd 1 - 1 Arsenal, and the prediction 2 -2), correct draw (but not the correct score). The points are as follows: Correct draw (Prediction = result): 4 Correct win (Prediction = result): 3 Correct win, incorrect score (Winner correct, score not): 1 Correct draw, incorrect sco 1 The formula I found online for all but the correct draw is: =IF(A3&B3=C3&D3),3,IF(AND(A3=B3,C3=D3),1,IF(AND(A3>B3,C3>D3),1,IF(AND(A3<B3,C3<D3),1,0))))) Could someone help me work out how I can write a formula to add before the above that will check the contents of four cells (the home score, away score, home prediction, away prediction), and if the number in all 4 cells are the same, allocate 4 points? I've tried using averages, but some combinations would void this. I've also used the SUM function, but the same happens, as with MULTIPLYING. Something like =IF(A3:D3) are equal, 4,0 Below are a few examples of what I mean: (Result) (Prediction) (Points) A B C D E 3 - 3 3 - 3 4 2 - 1 3 - 1 1 1 - 0 1 - 0 3 3 - 3 2 - 2 1 Apologies in advance if I haven't explained this clearly enough. Thanks Will
This is my first post in these forums. I am trying to create a formula which will look at the data in columns A - I (50 rows) and copy this data to columns K - S but ignoring any blank cells. Thereby consolidating the data in the upper rows with no spaces. I am sure there is a relativley simple answer but it is driving me mad! Hope you 'Guru's' can help Thanks Added example spreadsheet to aid assistance.
Also, I cannot move the Pivot Table from the new Worksheet it is created in, to my existing Worksheet. I am running Excel 2011 for Mac. I have a Worksheet with a small amount of data, and when I click the Pivot Table button it automatically creates the Pivot Table in a new Worksheet. The Pivot Table data is fine, but I want it to display alongside the source data. I've tried using the Move Pivot Table option and choosing to Create Pivot Table in Existing Worksheet and choosing a source cell alongside my current data, but nothing appears. Also, I checked in Edit menu and found that the Pivot Table appears to have not even been created/moved because there is no option to undo it. Appreciate any help, let me know if you have any further questions. Hunter Okay, so I tried (for hours) to find a solution to this and have finally gotten frustrated to the point where i think asking here will be my best bet. Basically I want to export data from excel worksheets to various places on a word document I had. I have created a word template as well as bookmarks for that template, as that seems to be the recommendation for performing such a task with excel. My problem is ... Now what? I basically have 1 constant worksheet that contains all my affiliates info (Name, Address etc), which i'd like to use to populate some bookmarks in my word template, and then i have another worksheet for the previous month, indicating sales etc for that affiliate. I intend on adding a new worksheet every month that i would use to create new invoices. Furthermore, i'd really like to be able to update a new file (or perhaps a new page on the same file) for each company with the use of a button, similar to the one's you'd see in an access form. Can anyone help? Let me know if you need me to clarify. Thanks! Matt Hi, Im working with a large data spreadsheet that has Yes and No answers in different columns. There are blanks within the selection too. I need to calculate only the Yes. I have tried using the filters in the Pivot Table field list and they are not working. So now i'm trying to add a COUNTIF formula in the Calculated Fields section. The formula I'm using is =Countif('Time limit extension'='yes') and i get the answer 'too few.' What should I be using in the formula to get the calculated field to work? I have search the whole forum and I haven't found an answer that can help me. Please HELP!!! I'm working on an Excel file that it's size is about 90 M.B and that makes it very slow to deal with ... is there anyhow to make it faster ??? i use in it alot of formulas and conditional formats thank u I have workbook in which I want to save a specific worksheet to a new file with only the values saved - all data in this worksheet are references to cells on another worksheet, which is using VLOOKUP to pull data from a database. Found the following code and it gets me close, but it copies the cell references, not the values. It also allows me to specify the file name from a cell reference. I want the new workbook file to simply be saved, not opened, and a message box to display stating where the file was saved (will always be in the same location on the LAN). What modifications do I make to this to get this to work per above requirements? Sub CopyMe() Dim SaveMeAs As String SaveMeAs = Sheets('Sheet1').Range('B2').Text Sheets('Sheet3').Copy ActiveWorkbook.SaveAs Filename:='C:My Documents' & SaveMeAs End Sub
I have an excel worksheet that adds two other worksheets in a data triangle. I copied it to create a new data set and used find & replace to change the worksheet references to the new ones. The cells still contain the result of the old formula referring to the previous worksheets. The only way I can get the formula to return the correct result is to edit (F2) each cell and press enter. Calc now (F9) does nothing. I've seen this before, but this time, I need to calculate many thousands of cells and don't have time for this workaround. Any ideas? Thanks. Don S I feel like this should be really easy and I am just out of it today but I cannot figure this out. I want to lock a row and filter (with filter,not sort, tool) the other rows. I attached an example The row in RED I want to never be changed or added in the filter, but I want row 2 and 1 to filter together. So I want (in this case) the flavor to filter with the same number. SO i guess the filter process would be dependent upon the total number of votes. Any ideas? Thanks! I had to create the information in tables and went from there. Got it. Thanks!
I have a spreadsheet with simple (addition/subtraction) formulas. The file is quite large and the formulas are too. All of a sudden, the formulas stopped working except when I double click in the cell containing the actual formula. For example, if I enter '2' in each cell, A2 and B2, cell B3 should reflect '4' because there is a formula in cell B3 which totals cells A2 and B2. Only by double clicking on cell B3 will the program actually calculate. I'm totally perplexed and so is my IT contact. Anyone? Hi all, I hope you can help me with what I would like to do. I've searched and searched and not come up with anything yet!! I have one sheet which is my 'data' sheet with a number of columns, one of which is a date which may be repeated. I then want to use another sheet with two cells where two dates can be entered and a button which allows you to copy the rows of data from the data sheet which have dates between the two specified originally. The list in the data sheet may not be in date order although I could update a macro to do this for me if needed. I would also like to do it so that if 'ALL' is typed in the date fields, it pulls over ALL the data. (Or something similar) Any help you can give would be much appreciated. Cheers, Kevin After you filter out data, how to you re-number the row numbers so they are, again, consecutive?
I know this question has been asked a bajillion times, so I apologize for the redundancy. I am working with an Excel spreadsheet and saving it as a .csv file in order to upload to an application that parses out the .csv data as transactions. The system requires .csv files, so this is how I need to save my doc (with this extension). I have been successful at preventing Excel from coverting that long number into scientific format. I have saved as a TXT file, pasted the longer number and it displays correctly. That is all good. But I have to save as a .csv. So if I do that, close the Excel window, and then open again (as the .csv file), the numbers are back to being displayed in scientific format. I have tried creating an Excel doc from scratch and entering text in Text format, to see if this created a cleaner file. But again, the second I save as .csv, close the window and then open that file up again, that dang scientific format is back. Does anyone have any idea of how to work around this? Once I have successfully gotten the numbers to display as the long-chain number, how can I get them to 'stick' so that they don't revert back to scientific format when I reopen the file? Thanks so much for your help! |
You can use the Allow Users to Edit Ranges command button in the Changes group on the Review tab of the Excel 2013 Ribbon to enable the editing of particular ranges in the protected worksheet by certain users. When you use this feature, you give certain users permission to edit particular cell ranges, provided that they can correctly provide the password you assign to that range.
To give access to particular ranges in a protected worksheet, you follow these steps:
1Click the Allow Users to Edit Ranges command button on the Ribbon’s Review tab or press Alt+RU.
Note that the Allow Users to Edit Ranges command button is grayed out and unavailable if the worksheet is currently protected. In that case, you must remove protection by clicking the Unprotect Sheet command button on the Review tab before you retry Step 1.
Excel opens the Allow Users to Edit Ranges dialog box, where you can add the ranges you want to assign.
2Click the New button.
Doing this opens the New Range dialog box where you give the range a title, define its cell selection, and provide the range password.
3If you wish, type a name for the range in the Title text box; otherwise, Excel assigns a name such as Range1, Range2, and so on.
Next, you designate the cell range or nonadjacent cell selection to which access is restricted.
4Click the Refers to Cells text box and then type in the address of the cell range (without removing the = sign) or select the range or ranges in the worksheet.
Next, you need to enter a password that’s required to get access to the range. Like all other passwords in Excel, this one can be up to 255 characters long, mixing letters, numbers, and spaces. Pay attention to the use of upper- and lowercase letters because the range password is case-sensitive.
5Type in the password for accessing the range in the Range Password dialog box.
You need to use the Permissions button in the New Range dialog box to open the Permissions dialog box for the range you’re setting.
6Click the Permissions button in the Range Password dialog box.
Next, you need to add the users who are to have access to this range.
7Click the Add button in the Permissions dialog box.
Doing this opens the Select Users or Groups dialog box, where you designate the names of the users to have access to the range.
8Click the name of the user in the Enter the Object Names to Select list box at the bottom of the Select Users or Groups dialog box. To select multiple users from this list, hold down the Ctrl key as you click each username.
If this list box is empty, click the Advanced button to expand the Select Users or Groups dialog box and then click the Find Now button to locate all users for your location.
You can then click the name or Ctrl+click the names you want to add from this list, and then when you click OK, Excel returns you to the original form of the Select Users or Groups dialog box and adds these names to its Enter the Object Names to Select list box.
9Click OK in the Select Users or Groups dialog box.
Doing this returns you to the Permissions dialog box where the names you’ve selected are now listed in the Group or User Names list box. Now you need to set the permissions for each user. When you first add users, each one is permitted to edit the range without a password.
To restrict the editing to only those who have the range password, you need to click each name and then select the Deny check box.
10Click the name of the first user who must know the password and then select the Deny check box in the Permissions For list box.
You need to repeat this step for each person in the Group or User Names list box that you want to restrict in this manner.
11Repeat Step 10 for each user who must know the password and then click OK in the Permissions dialog box.
As soon as you click OK, Excel displays a warning alert dialog box, letting you know that you are setting a deny permission that takes precedence over any allowed entries, so that if the person is a member of two groups, one with an Allow entry and the other with a Deny entry, the deny entry permission rules (meaning that the person has to know the range password).
12Click the Yes button in the Security alert dialog box.
Doing this closes this dialog box and returns you to the New Range dialog box.
13Click OK in the New Range dialog box.
Doing this opens the Confirm Password dialog box where you must accurately reproduce the range password.
14Type the range password in the Reenter Password to Proceed text box and then click the OK button.
Doing this returns you to the Allow Users to Edit Ranges dialog box where the title and cell reference of the new range are displayed in the Ranges Unlocked by a Password When Sheet Is Protected list box.
If you need to define other ranges available to other users in the worksheet, you can do so by repeating Steps 2 through 14.
When you finish adding ranges to the Allow Users to Edit Ranges dialog box, you’re ready to protect the worksheet. If you want to retain a record of the ranges you’ve defined, go to Step 15. Otherwise, skip to Step 16.
15(Optional) Select the Paste Permissions Information Into a New Workbook check box if you want to create a new workbook that contains all the permissions information.
When you select this check box, Excel creates a new workbook whose first worksheet lists all the ranges you’ve assigned, along with the users who may gain access by providing the range password. You can then save this workbook for your records. Note that the range password is not listed on this worksheet — if you want to add it, be sure that you password-protect the workbook so that only you can open it.
Now, you’re ready to protect the worksheet. If you want to do this from within the Allow Users to Edit Ranges dialog box, you click the Protect Sheet button to open the Protect Sheet dialog box.
If you want to protect the worksheet later on, you click OK to close the Allow Users to Edit Ranges dialog box and then click the Protect Sheet command button on the Review tab of the Ribbon (or press Alt+RPS) when you’re ready to activate the worksheet protection.
16Click the Protect Sheet button to protect the worksheet; otherwise, click the OK button to close the Allow Users to Edit Ranges dialog box.
If you click the Protect Sheet button, Excel opens the Protect Sheet dialog box, where you can set a password to unprotect the sheet. This dialog box is also where you select the actions that you permit all users to perform in the protected worksheet.
After you turn on protection in the worksheet, only the users you’ve designated are able to edit the cell range or ranges you’ve defined. Of course, you need to supply the range password to all the users allowed to do editing in the range or ranges at the time you distribute the workbook to them.