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.

Inserting Necessary Columns for Recipe Database

  • 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.

Filtering by IF Function and Data Validation

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

how to create a recipe database in excel step 2

  • 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.

how to create a recipe database in excel step 2

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.

Filtering by Filter Command

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

how to create a recipe database in excel step 2

  • 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.

how to create a recipe database in excel step 2

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.

  • Select a cell from where your recipe array will start and write down the following formula in it.

=FILTER('recipe list'!C5:E8,'recipe list'!B5:B8=filter!B5,"")

Filtering by FILTER Function

The formula returns the Recipe Item and corresponding Ingredients and Time Requirements.

Select the Beef category from the Data Validation list and see how this goes.

how to create a recipe database in excel step 2

This operation will return the recipe item of the beef category using the formula.

Thus you can create a recipe database in Excel and use it efficiently when needed.

Download Practice Workbook

Related Articles

<< Go Back To Database in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Meraz Al Nahian
Meraz Al Nahian

Md. Meraz Al Nahian has worked with the ExcelDemy project for over 1.5 years. He wrote 140+ articles for ExcelDemy. He also solved a lot of user problems and worked on dashboards. He is interested in data analysis, advanced Excel, statistics, and dashboards. He also likes to explore various Excel and VBA applications. He completed his graduation in Electrical & Electronic Engineering from Bangladesh University of Engineering & Technology (BUET). He enjoys exploring Excel-related features to gain efficiency... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF