How to Create a Recipe Database in Excel: 3 Methods

Method 1 – Inserting Necessary Columns for Recipe Database

Select some cells to store the recipe information and type the heading. Choose the category and food item that you want to make in the future.

Insert the corresponding ingredients. We filled all the ingredients for a recipe in a single cell. We need to increase the row height. Put the cursor in the place shown in the following image and drag it below.

Start typing the ingredients. When you finish typing the first ingredient, then press ALT+ENTER. Take your cursor to the next paragraph.

Type all the other ingredients for that recipe and press the ENTER.

Thus, you can fill all the ingredients in one cell for each recipe. This basically creates your recipe database.

Method 2 – Filtering Recipes from the Recipe Database

1. Filtering by IF Function and Data Validation

Use the IFS (family function of IF) and INDIRECT functions to apply some filters to the recipe database. Go through the following section to get a better understanding

Open a new sheet and select a cell to store the Data Validation.
Go to Data >> Data Validation (from the Data Tools group).
In the Data Validation window, select List from the Allow section and type the Category names in the Source.
Click OK.

You will see the Data Validation list in B5. Click the drop-down icon to see this.

We are going to use the following formula to get the recipe overview when we select a category from the Data Validation

=IFS(B5="None","",B5='recipe list'!B5,INDIRECT("'recipe list'!C5:E5"),B5='recipe list'!B6,INDIRECT("'recipe list'!C6:E7"),B5='recipe list'!B8,INDIRECT("'recipe list'!C8:E8"))

The formula uses the IFS and INDIRECT functions. The formula looks a bit complex but in reality, it just contains some conditions and arrays. We are checking whether the category in B5 matches the category in the recipe list sheet (please download the file for a better understanding). It returns corresponding recipe items, ingredients, etc.

If you look at the image above, you can see that the cell containing the ingredients is not clear. Use the Format Painter to format this cell properly.
Go back to the Recipe Database sheet and select one or multiple cells (we selected D6:D7).
Select Home >> Clipboard >> Format Painter.

Go to the Filtering sheet, place the Format Painter icon on the cell you want to format, and click on it.

This operation will make the cell format of D5 of the Filtering sheet similar to the previously selected cell. Select another category from the Data Validation List and see how it goes. The Chicken Category has two items, so two items will show up.

The second item’s ingredients are not visible now. To view them, double-click the mouse to increase the row height. When you put the cursor on the corresponding row, the following icon appears.

Filter the recipe item to find your desired one easily from a large recipe database.

Method 2 – Filtering by Filter Command

Notice that the Category Chicken is merged through B6:B7. If we apply Filter to this data, some of the data will not appear.
Copy the cells B5:B8 and paste them somewhere in the sheet. We pasted them in G5:G8. G6:G7 will remain a merged cell.

Unmerge the cells (B6:B7). Fnd this option in the Alignment group of the Home Tab.

Simply use the Format Painter to copy the format of G6:G7 merged cells and paste it to B5. The process of using the Format Painter is described in the previous process.

The cells B6:B7 look merged, but they both contain the Category (Chicken).

Use the Filter. Just select the headers (B4:E4) and press CTRL+SHIFT+L. You can find this command from the Editing group of the Home Tab.
The Filter drop-down icons will appear.

Click on the drop-down icon and check the Category you want.
Click OK.

Filter your desired items.

Method 3 – Filtering by FILTER Function

Use the FILTER function to filter the recipe items. Use Format Painter to format the merged cells as we did in the second filtering method. Create the Data Validation following the procedures of the first filtering method. Follow the instructions below.