How to automatically create large number of columns in Excel 2013

Solution 1:

At first you need to change reference style: go to file - options - formulas and check "R1C1 reference style"

Creating headers:

• Go to the small box in the left of formula bar and type R1C1:R1C500 then press Enter, this select the desired range
  
• type the formula ="column" & COLUMN() then press CTRL+Enter
• press CTRL+C then go to home - paste and select "values"

Filling random data:

• go to address bar and type R2C1:R101C500
• Enter formula =RANDBETWEEN(0,10) and press CTRL+Enter
• press CTRL+C then go to home - paste and select "values"

Finally you may want to change back reference style from R1C1.