How to Drag Formula and Ignore Hidden Cells in Excel (2 Examples)

Get FREE Advanced Excel Exercises with Solutions!

In some cases, we hide some of our rows and columns in Excel. But if we drag the formula from there, it will drag along with the hidden cells. If you actually drag any formula and ignore hidden cells in Excel, this article is for you. This article will mainly focus on how to drag the formulas ignoring hidden cells with some valuable examples. I think you will gain some valuable knowledge about this topic.


How to Drag Formula and Ignore Hidden Cells in Excel: 2 Suitable Examples

To drag the formula and ignore hidden cells in Excel, we have found three effectively through which you can gain proper knowledge about this matter. All of these examples are user-friendly and easy to digest. To show all the examples we take a dataset that includes some person and money earned from selling three different products.

Drag Formula Ignore Hidden Cells in Excel


1. Drag Formula and Ignore Hidden Cells Using SUBTOTAL Function

To drag the formula ignoring hidden cells, first, we need to utilize the SUBTOTAL function.

Steps

  • At first, hide some of the rows before applying anything.
  • Press Ctrl and select row 7, row 9, and row 11.

  • Next, right-click on the select row.
  • A Context Menu will appear.
  • Select Hide from there.

  • It will hide all the selected rows. See the screenshot.

Drag Formula Ignore Hidden Cells in Excel

  • Now, we need to find out the total income from product 1 sale. Here, we use the SUBTOTAL function.
  • First, select cell D13.

  • Now, write down the following formula in the formula box.
=SUBTOTAL(109,E5:E12)

Here, 109 is the function_num which denotes the SUM function but will ignore hidden cells while applying.

Drag Formula Ignore Hidden Cells in Excel

  • Press Enter to apply the formula.

Drag Formula Ignore Hidden Cells in Excel

  • Drag the Fill Handle icon to the right to fill down the next value. It will calculate the sum of product 2 and product 3. In all the cases, the function ignores the hidden cells.

Drag Formula Ignore Hidden Cells in Excel

  • Now, copy the range of cell B4 to F13 by pressing Ctrl+C after selecting the range of the cell.
  • Paste it at cell It will show up all the hidden cells and also change the sum value.

Drag Formula Ignore Hidden Cells in Excel

  • But we want to drag the formula ignoring hidden cells.
  • To do this, first, select the range of cells B4 to F13.

  • Next, press ‘Alt+;’. See the screenshot.

  • Then, press Ctrl+C to copy the selected range of cells. It will highlight the cells in the following way. See the screenshot.

Drag Formula Ignore Hidden Cells in Excel

  • Next, select cell B15.
  • Then, press Ctrl+V to paste the selected range of cells.
  • Here, you will see the cells and formula ignoring the hidden cells.

Drag Formula Ignore Hidden Cells in Excel

Note:

You can’t paste the selected range of cells with some hidden cells on the right side of the original dataset. You can paste it either below the dataset or into another worksheet. The reason behind this is that you hide some cells in the dataset so if you paste it on the right side of the dataset, the paste value must use the same rows as the original dataset.

Read More: How to Drag Formula in Excel with Keyboard


2. Drag Formula and Ignore Hidden Cells Using AGGREGATE Function

We can drag the formula ignoring hidden cells using the AGGREGATE function.

Steps

  • At first, hide some of the rows before applying anything.
  • Press Ctrl and select row 7, row 9, and row 11.

  • Next, right-click on the select row.
  • A Context Menu will appear.
  • Select Hide from there.

  • It will hide all the selected rows. See the screenshot.

Drag Formula Ignore Hidden Cells in Excel

  • Now, we need to find out the total income from product 1 sale. Here, we use the SUBTOTAL function.
  • First, select cell D13.

  • Now, write down the following formula in the formula box.
=AGGREGATE(9,5,D5:D12)

Here, 9 is the function_num which denotes the SUM function. Next, we will get some more options. Where 5 denotes ignore hidden rows.

Drag Formula Ignore Hidden Cells in Excel

  • Press Enter to apply the formula.

Drag Formula Ignore Hidden Cells in Excel

Drag Formula Ignore Hidden Cells in Excel

  • Now, copy the range of cell B4 to F13 by pressing Ctrl+C after selecting the range of the cell.
  • Paste it at cell It will show up all the hidden cells and also change the sum value.

Drag Formula Ignore Hidden Cells in Excel

  • But we want to drag the formula ignoring hidden cells.
  • To do this, first, select the range of cells B4 to F13.

  • Next, press ‘Alt+;’. See the screenshot.

  • Then, press Ctrl+C to copy the selected range of cells. It will highlight the cells in the following way. See the screenshot.

Drag Formula Ignore Hidden Cells in Excel

  • Next, select cell B15.
  • Then, press Ctrl+V to paste the selected range of cells.
  • Here, you will see the cells and formula ignoring the hidden cells.

Drag Formula Ignore Hidden Cells in Excel

Read More: How to Enable Drag Formula in Excel


How to Drag Formula in Excel When Filtered

When you filter your dataset, it will create an appearance where you can hide cells. To show this process, we use the SUBTOTAL function.

Steps

  • First, select the range of cells B4 to F12.

  • Then, go to the Data tab in the ribbon.
  • From Sort & Filter group, select Filter.

Drag Formula Ignore Hidden Cells in Excel

  • It will filter your dataset.

  • Next, select cell D13.

  • Write down the following formula in the formula box
=SUBTOTAL(9,D5:D12)

Here, Here, 9 is the function_num which denotes the SUM function but will ignore unchecked filtered cells.

Drag Formula Ignore Hidden Cells in Excel

  • Press Enter to apply the formula.

  • Drag to the Fill handle icon to the right. It will calculate the sum of product 2 and product 3.

  • Next, click on the down arrow beside the Name header.
  • Number Filter dialog box will appear.
  • From there uncheck Elijah, Noah, and Pope.
  • Finally, click on OK.

  • It will filter the dataset and hide all three names.
  • More importantly, it will change the overall sum of the three products because it allows only visible rows.


Things to Remember

When you use the SUBTOTAL function, you may have to get two function_num 9 and 109. But to have an effective solution, you need to use 9 function_num when you filter your dataset and you need to utilize 109 function_num when you hide your cells manually.


Download Practice Workbook

Download this practice workbook.


Conclusion

To drag the formula to ignore hidden cells, we have shown some suitable examples. All of these examples provide effective results and really easy to digest. If you have any questions, feel free to ask in the comment box.


Related Articles


<< Go Back to Fill Handle in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Durjoy Paul
Durjoy Paul

Durjoy Kumar, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, is a dedicated contributor to the ExcelDemy project. His substantial contributions include crafting numerous articles and demonstrating expertise in Excel and VBA. Durjoy adeptly automates Excel challenges using VBA macros, offering valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, SPSS, C, C++, C#, JavaScript, Python Web Scraping, Data Entry... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo