In normal circumstances, Excel places a limit on the number of nested conditional formulas that you can use. The limit is 7. However, it is possible to circumvent the limitation over the number of nested conditional formulas by cascading them. This article will look at the different methods for nesting multiple IF statements in Excel.

## How to nest multiple IF Statements using text data?

Consider cell A2 as the source cell, in which you will either enter a text or numeric value. Take as an example data in text format ranging from "one" to "fifty-six". To facilitate the monitoring data that will be collected in the numeric format and inserted into the formula, they could be located in target cells, in this case referring to the address of each cell.

- Simply complete the
**value if false**of each nest by 0 (zero) and restart a new nesting, preceded by**+**:

=IF(A2="one",1,IF(A2="two",2,IF(A2="three",3,IF(A2="four",4,IF(A2="five",5,IF(A2="six",6,IF(A2="seven",7,0)))))))+IF(A2="eight",...

## How to nest multiple IF statements using numeric data?

This time, consider cell A8 as the source cell. The data will be in numeric format, ranging from 1 to 40, and the data in the cell (e.g. D8) in text format.

- Simply complete the
**value if false**for each nests by "" (empty), and restart a nesting preceded by an**&**:

=IF(A8=1,"one",IF(A8=2,"two",IF(A8=3,"three",IF(A8=4,"four",IF(A8=5,"five",IF(A8=6,"six",IF(A8=7,"seven","")))))))&IF(A8=8,...

## How to nest multiple IF statements using fields?

This method allows you to create fields containing the conditional formula. First, enable the cell that will display the result (e.g cell D5).

- Create the first field. In
**refer to**, enter the formula and finish with classical conditional**Value if false**= FALSE:

=IF(Sheet1!A2="one",1,IF(Sheet1!A2="two",2,IF(Sheet1!A2="three",3,IF(Sheet1!A2="four",4,IF(Sheet1!A2="five",5,IF(Sheet1!A2="six",6,IF(Sheet1!A2="seven",7,FALSE)))))))

- Continue by creating a new field named Form2, and enter the following formula to continue the conditional nesting:

=IF(Sheet1!A2="eight",8,IF(Sheet1!A2="nine",9,IF(Sheet1!A2="ten",10,IF(Sheet1!A2="eleven",11,IF(Sheet1!A2="twelve",12,IF(Sheet1!A2="thirteen",13,IF(Sheet1!A2="fourteen",14,FALSE)))))))

- In the active cell D5, enter a simple conditional formula referring to named fields:

=IF(Form1,Form1,IF(Form2,Form2,IF(Form3,Form3,IF(Form4,Form4,IF(Form5,Form5,IF(Form6,Form6,IF(Form7,Form7,IF(Form8,Form8,""))))))))

- The possibilities are very important, and we have 7 nested by field X 8 or 56 conditional IF. It is also possible to relaunch a new cascade nest based on the same criteria, which allows for a very large number of possibilities:

IF(Form8,Form8,""))))))))&IF(Form9,...

or

IF(Form8,Form8,0))))))))+IF(Form9,...

- This last method is interesting because it allows you to test multiple cells, including several sheets, or create a cascade of cells, or sheets, or in a defined order.

## How to nest multiple IF statements using masked cells?

This method will allow us to obtain an unlimited number of conditionals using masked cells in a column.

- Example, in a first cell (C13), the first of the 6 conditional, completed by the
**value if false**referring to the cell containing the second conditional formula C14:

=IF(Sheet1!A11="one",1,IF(Sheet1!A11="two",2,IF(Sheet1!A11="three",3,IF(Sheet1!A11="four",4,IF(Sheet1!A11="five",5,IF(Sheet1!A11="six",6,IF(Sheet1!A11="seven",7,C14)))))))

- In cell C14, put the following conditional formula with a
**value if false**pointing to the address of the cell containing the formula (C15):

=IF(Sheet1!A11="eight",8,IF(Sheet1!A11="nine",9,IF(Sheet1!A11="ten",10,IF(Sheet1!A11="eleven",11,IF(Sheet1!A11="twelve",12,IF(Sheet1!A11="thirteen",13,IF(Sheet1!A11="fourteen",14,C15)))))))

This can be used as in many cells as needed, without limitation.

## How to use single sheets to simplify a formula?

When all the cells are tested on the same sheet, it is possible to simplify the formula, replacing **Sheet1!A2** by cell address **A2** or absolute reference **$A$2**.

## Need more help with Excel? Check out our forum!

## Excel

- How many nested if statements in excel
- Maximum if statements in excel
- An if statement nested within another if statement will produce how many possible results?
- Nested If Statement not working [solved] > Forum - Excel
- Sbi statement > Guide
- If statement with date range > Guide
- How to remove false from if statement [solved] > Forum - Excel
- Combine if statement and data validation > Forum - Excel
- Change date format in Excel: to dd/mm/yyyy, mm/dd/yyyy
- Copy data from one excel sheet to another: automatically
- How to take screenshot in Excel: shortcut, sheet, cell
- Fix arrows keys in Excel: Scroll lock, alternative method
- How to apply a function to multiple sheets on Excel
- Convert numbers to words in Excel: without VBA, formula
- How to change author name in Excel: spreadsheet, VBA
- How to display multiple columns in a validation list
- How to use Excel color cell if formula
- Check if a value exists in an array VBA
- How to enable VBA in Excel: Mac, Office 365
- How to use an IF statement to add 1 to total
- How to perform a partial cell match in Excel?
- VBA select case like: operator, string, statement
- Search and find using VBA in Excel
- How to run macro when data entered in a cell
- What is the VBA code to select last sheet in workbook
- How to insert file path in excel: cell, sheet
- How to insert GIF in Excel: 365, sheet, VBA
- How to disable auto recover in Excel
- Open and convert an Excel file in Notepad
- VBA Excel color codes: index number, list
- How to count names in Excel: formula, using COUNTIF
- How to enter multiple lines in a single Excel cell
- How combine IF function, SEARCH and ISERROR in Excel
- Run macro on opening: worksheet, workbook
- Create new sheet based on cell value: Excel, macro
- Recalculate Excel workbook before saving
- How to manipulate data in Excel: VBA
- Repeat rows in Excel: based on cell value, VBA
- Insert a hyperlink in Excel: with text, to another tab
- Most useful Excel formulas: for data analysis
- How to automatically transfer data between sheets in Excel
- How to change a column to numeric in Excel?
- How to transfer data from one Excel sheet to another?
- Recover Excel file: previous version
- Select the default number of processors in Excel
- How to add a number of days to a date in Excel
- How to copy a Macro into a blank cell
- Mark sheet grade formula in Excel: template
- How to fill multiple Excel sheets from master sheet
- Insert picture in Excel: cell, shortcut, using formula
- Excel functions in French
- How to generate email notifications for Excel updates
- How to add sheet to workbook: VBA, Excel
- How to create a cascading combo box: Excel, VBA
- How to calculate VAT in Excel: formula
- Excel send value to another cell
- How to create calculator in Excel VBA
- Select empty cell in Excel: VBA
- Unlock password protected Excel file: Macro
- Excel export data: from one sheet to another
- How to connect VB 6.0 with MS Access
- Split a workbook into individual files in Excel
- How to create UserForm: in Excel, VBA
- Using VBA to find last non empty row: in column, in table
- Excel VBA add command button programatically
- How to remove leading apostrophe in Excel
- Compare two Excel sheets: and combine data
- How to change enter key function in Excel
- How to use Excel auto numbering formula
- Copy data from one Excel workbook to another
- How to change Excel date format
- How to insert a transparent image into an Excel document
- How to copy data to another workbook: using VBA
- How to count occurrences of characters and numbers in Excel
- Transfer Excel data from one sheet to another: VBA
- How to show or hide formula bar in Excel: VBA, shortcut
- Delete duplicates in Excel: column, formula
- How to insert blank rows using macros in Excel
- Conditional formatting with dates: in Excel
- Send email with attachment Excel: VBA, macros
- Auto generate serial number in Excel: VBA, formula
- Transfer a worksheet to another Excel workbook: without VBA
- Insert an image into an Excel comment box
- How to change row color in Excel VBA based on value
- Create a new workbook and copy data in Excel via a Macro
- Shortcuts to insert in Excel
- Run Macros in Excel: online, shortcuts, VBA
- How to clear formatting in Excel
- How to create a timer in Excel VBA
- How to create a drop-down list in Excel
- Excel IF, AND, OR, and NOT functions
- Update dates in Excel spreadsheet: each year, formula
- How to use conditional formatting in Excel
- Credit summation formula
- How to split text in columns in Excel
- How to copy data to multiple worksheets in Excel
- How to insert multiple rows in Excel with a macro?