Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have to simulate a sheet with more or less 200 columns x 200 rows. When I add the data manually, Excel shows the message "not responding" and I have to wait about 20 minutes till it fills all the cells. As a matter fact, I'd like to know if there is a faster way to insert a big amount of data in a sheet.

The way I'm doing:

I'm writing the data in the first cell (A1), and then, with the mouse, I'm dragging it until the cell (GU1); it goes quite fast, but when I select from A1:GU1 until A100:GU100 (to fill the half of the "matrix" I want), it takes too long (20min or more).

To fill the second half of it, I tried to replicate the data of the first row (A101) until (A200), and then drag it all until the column GU, but it still takes too long.

I also checked this blog but I couldn't find useful information for this issue, so, I'd like to know if someone knows if there is a way to copy a single value to a big "matrix" of cells in excel in a faster way.

Thanks

PC's configuration

  • processor: Intel Core2Duo CPU E7500 @ 2.93Ghz,
  • Ram memory: 8Gb Excel,
  • Excel Version: 2010

System's consummation while replicating data to cells: enter image description here

share|improve this question
    
A 200 x 200 cell sheet shouldn't be a problem at all for Excel. What is your PC configuration? Which version of Excel are you using? –  Pincopallino Feb 20 '13 at 13:52
    
I just edited the question and added my PC configuration, @Pincopallino –  periback2 Feb 20 '13 at 14:00
    
Try setting the calculation to manual while you are entering the data. This will stop the recalculation of dependant formulas. Once you are done you can turn the calculation back to automatic –  kbsou Feb 20 '13 at 14:02
    
PS: there are some macros in the sheet –  periback2 Feb 20 '13 at 14:02
1  
I normally do 2 x 1 dimension copies. So drag from A1 to GU1, and when that's filled in, drag from A1:GU1 to A200:GU200. But is sounds like you do this in part 2, so I'm not sure if it will help. –  mcalex Feb 20 '13 at 14:03
show 3 more comments

1 Answer

up vote 1 down vote accepted

As I pointed out in my comment, a 200x200 cell selection should not be a problem at all for Excel, given your computer specs. Maybe you should repair Office installation.

By the way, here's a faster way to insert the same number in a range of cells (if that is what you want to do). You can type the range of cells that you want to be selected in the textbox at the left of the formula text box. For example if you want to select a square of cells from A1 to GU200, you may write

A1:GU200

You then click with the mouse in the formula textbox (notice that the cells are selected) and write the number (or the formula), and then you press CTRL + SHIFT + ENTER. The formula is applied to all the cells in the selection.

EDIT: The PS that you have macros in your sheet is important information, as the macros might slow down the calculations. What are those macros for?

share|improve this answer
    
idk what they're doing, in fact, because it's a template generated by the system i'm using. I tested the solution you proposed and it still takes too much time to fill the data. This way, I believe we can say it's because of the macros, right? –  periback2 Feb 20 '13 at 14:15
1  
Well, open a new spreadsheet (or disable the macros), and do the same operation. It should take less than a second. If it is so, then it is because of the macros. Probably they do a lot of work for each cell, and are not optimized for large amounts of data. –  Pincopallino Feb 20 '13 at 14:20
1  
I agree with you. I just tested in a new worksheet and it takes less than a second. Probably the macros are doing some validation or something else for each cells –  periback2 Feb 20 '13 at 14:32
    
There's a good chance it's a "SheetCalculate" or "SheetChange" event. To see this in the VBE click the ThisWorkbook object in the Project Explorer. –  Gary Feb 20 '13 at 21:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.