Excel: Change multiple formulae at once?

excel formula
toolshed picture toolshed · Mar 26, 2012 · Viewed 52.9k times · Source

I've sorted a chunk of data into sub-totaled fields using the Subtotal tool under the Data tab. However, you are only able to choose one formula to apply.

How can I apply a separate formula to one of the columns based on SUBTOTAL(1,RANGE)? So far I have populated it with Subtotal (9,RANGE), but is there a quicker method to select all of these and change the "9" to a "1"?

Answer

assylias picture assylias · Mar 26, 2012
  1. Select the column.
  2. Press CTRL+H.
  3. Find What: SUBTOTAL(9
  4. Replace with: SUBTOTAL(1
  5. Click Replace All

Related questions

How to turn a string formula into a "real" formula

I have 0,4*A1 in a cell (as a string). How can convert this "string formula" into a real formula and calculate its value, in another cell?

Read More
Fill formula down till last row in column

I'm trying to draw down the formula that's in cell M3 to the end of the data set. I'm using column L as my base to determine the last cell with data. My formula is a concatenation of two cells …

Read More
Using OR & AND in COUNTIFS

I would like to include an "AND" condition for one of the conditions I have in my COUNTIFS clause. Something like this: =COUNTIFS(A1:A196;{"Yes"or "NO"};J1:J196;"Agree") So, it should return the number of rows where: (…

Read More