Formulas per column

jocareher

Hello, Smartsheet team:

 

If I select a cell containing a formula, I know I can drag the little box in the right-hand corner downwards to apply the formula to more cells of the column. Unfortunately, I need to do this for 300,000 rows!

Is there a shortcut that will apply a formula to the entire column, or to a selected part of the column?

 

Thanks!

Jim Hook

I typically copy the formula, select the cells that it needs to pasted into by clicking on the cell in the first row to be updated then scrolling with the scroll bar down to the last cell, shift-click to select all those cells and then paste it. Much faster than having Smartsheet scroll down by itself. I don't think you can copy a formula into a complete  column in one step.

That is a massive sheet!

Mike Wilday

I definitely miss the ability to double-click on that little square and have it fill in blank fields down... like the functionality in Google Sheets and Excel. 

Chris McKay

Hi jocareher,

Here's how I'd do it:

1. Enter the formula in your first cell
2. Making sure this cell is still highlighted, scroll to the bottom of the sheet
   
   (using the scrollbars so not to take the focus from the first cell)
3. Hold the Shift key down (very important) and Highlight the last cell
4. Press Crtl + D

This will copy the formula all the way down the sheet from the first to the last cell. You'll need to make sure your formula doesn't have any $ sign absolute references (e.g.[Task]$1) that will affect the results. I'm also uncertain about how long Smartsheet will take to copy the formula down, as 300K rows is an enormous sheet.

Assuming you have more than one column, it sounds like you should be developing a solution on top of an SQL or Hadoop backend instead of using Smartsheet.

sinhuat.aw73551

The above works fine if the rows/data are already in the sheet.

What if I need a formula automatically applied to a column, for every new rows inserted?  

Andrée Starå

Hi Sinhuat,

As long as there are some rows with the correct formulas, it will work with a function called Formula Autofill.

Please see the attached link/screenshot for more information.

https://help.smartsheet.com/articles/1641473-auto-filling-formulas-and-formatting

Would that work?

Happy Holidays & Happy New Year!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

JeremiahHorstick

To copy something to the bottom of the sheet I like to copy the formula (CTRL+C)

Then Select the cell beneath it, press (CTRL+SHIFT+DOWN ARROW)  & Then paste (CTRL+V)

It works the other way with the up arrow.

Sometimes i have to hit the arrow a couple of times to extend the selection past empty cells. 

Autofilling Formulas is good for when new rows are added to the sheet. 

Also, last I heard, the max # of rows in a sheet is 5000.