2

I would like to use a same formula for cells in a column. Instead of writing it for all cells, is there a way to set it in a cell and tell to apply this formula to all cells in a column (or any set of cells)?

If I use the fill handle, as suggested by @Michelfrancis Bustillos, I have then to correct all cells because I am using a static cell.

enter image description here

As you can see, I would like to set the H1 cell for all cells in the column E. But the fill handle set H2 for the cell E4, and so on for the next cells.

The spreadsheet: https://docs.google.com/spreadsheets/d/1fM1Rrwj_lrxiGoiLyKEQqsYfcPbw6sA4avP9oVBGuHE/edit?usp=sharing

3 Answers 3

4

I would like to set the H1 cell for all cells in the column E. But the fill handle set H2 for the cell E4, and so on for the next cells.

This means you need an absolute reference: H$1 instead of H1. The dollar sign means this number will not be changed as the formula is propagated to other cells.

0
3

You can use the fill handle (small square that appears at the bottom right corner of the cell when it is selected/moused over) to fill the formula down.

5
  • I am using it, but it is not very useful because I have some cells which are the same for all rows and this tool is not smart enough to understand that. Jan 8, 2016 at 15:16
  • So you want it so skip over certain cells, or you want it to not auto-increment? Jan 8, 2016 at 15:18
  • I have added some details in my question Jan 8, 2016 at 15:27
  • Not having the time to read this all myself, I'd suggest you take a look here: mrexcel.com/forum/excel-questions/… Jan 8, 2016 at 15:31
  • Thank you. I do not have really understood how to do that yet, but I will! Jan 8, 2016 at 15:40
1

Please try:

=ArrayFormula(H$1*(0,365*B3:B*C3:C+D3:D))

I have simplified your formula a little, anchored (with $) the H1 reference so that remains the row reference for the "H" column entry even when the formula is copied down, and, since you mention a formula for all cells in a column converted it into array form, so no need to copy or drag.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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