Practically, the **Material Reconciliation Format** is one of the most used formats in** Excel** in manufacturing or construction processes. It is very handy to create a** Material Reconciliation Format** in** Excel **of your own and use it whenever you need. This article will demonstrate how to create a basic **Material Reconciliation Format** in **Excel **with very easy steps.

## Download Practice Workbook

You may download the following Excel workbook for better understanding and practice it by yourself.

## What is Material Reconciliation?

**Material Reconciliation **is mainly checking and analyzing the materials of a project at the end of the process. In any manufacturing or construction industry, the consumption of a specific amount of material is very significant. **Material Reconciliation** tells us whether the project is going on as per it was planned or not. Mainly, an expert compares the **Difference** between the **Assumed Consumption** and **Actual Consumption**. Moreover, other information about the materials is also included in a **Material Reconciliation Format**. Therefore, a visitor can easily analyze the process and take necessary steps to increase the efficiency of the project.

## Step-by-Step Procedures to Create Material Reconciliation Format in Excel

Suppose we have some information regarding the material estimated for the** Ecozen Bridge Project**. In this project, the company bought and kept the materials at the site. Daily usage of the materials for each work is estimated. In any construction, it is very important to use the materials in exact amounts which are calculated theoretically. If we want to check if the materials are used according to the assumed amount or not, we need to create a **Material Reconciliation Format **in **Excel**.

To create a **Material Reconciliation Format**, explore the following steps carefully.

### Step-01: Create a Table

In this project, there are** 5 **types of work. These are** RCC**,** PCC**,** Plaster**,** DPC**, and** Brick Work**. For these works, we need **Cement**, **Sand**, **Coarse Aggregate**, **Bricks, **and **Acco Proof**. Firstly, we will create a table according to the information we received.

- To begin, create a
**Table**with the necessary headings. - Afterward, insert the name of each
**Work**and the**Quantity**of the work as table header. - Besides, change the
**Fill Color**of the**Table Header**to make it look attractive.

**Read More: ****How to Reconcile Data in Excel (4 Easy Ways)**

### Step-02: Input Material Data

Succeeding, we will input the data in the table created previously.

- Firstly, input the
**Quantity**of each work in the table. Be careful about this because practically, there would be so many columns and if you input a single wrong data, the entire**Material Reconciliation Format**will be ruined. - Later, input the amount of each material.

**Read More: ****How to Do Reconciliation in Excel (12 Effective Techniques)**

### Step-03: Apply Necessary Formulas for Reconciliation

This is the most important step where we will use **the SUM function** and formulas to calculate necessary information from the table.

- First and foremost, insert some more rows in the table.
- These are
**Total Consumption (Assumed)**,**Quantity Purchased**,**Remaining Quantity (Assumed)**,**Current Quantity (at site)**, and**Difference**. - Next, insert the
**Quantity Purchased**and**Current Quantity (at site)**information carefully in the chart. - Now, the table is ready to apply the formulas.

- Insert the formula in
**Cell E11**.

`=SUM(E6:E10)`

- Next, press
**Enter**to get the**Total Consumption (Assumed)**of**Cement**.

**summation**of the cell range

**E6:E10**.

- Secondly, copy the formula through the entire row shown in the snapshot.

- Consequently, we got the
**Total Consumption (Assumed)**for each material with the help of**the AutoFill feature**of Excel.

- After this, calculate the
**Remaining Quantity ( Assumed)**. - In the beginning, write down the formula in cell
**E15**.

`=E14-E11`

- Then, press
**Enter**to get the value of**Remaining Quantity (Assumed)**for**Cement**.

**subtracts**the value of cell

**E11**from cell

**E14**. This means subtracting the

**Total consumption**from

**Quantity Purchased**.

- Moving forward, copy the formula to the entire row.
- Therefore, we will get the
**Remaining Quantity (Assumed)**for each**Material**.

- Correspondingly, we got the desired information for each material by using the
**AutoFill**.

- Finally, copy the formula to cell
**E19**to compute the**Difference**between**Current Quantity (at site)**and**Remaining Quantity (Assumed)**for cement.

`=E17-E15`

- Accordingly, press
**Enter**to get the result in cell**E19**.

**subtracts**the value of cell

**E15**from cell

**E17**.

- In conclusion, copy the formula to the rest of the cells of the row to calculate the
**Difference**for all the materials.

- We got the
**Difference**for**Sand**,**Coarse Aggregate**,**Bricks,**and**Acco Proof**by implementing**the AutoFill feature**.

**Read More: ****How to Reconcile Data in 2 Excel Sheets (4 Ways)**

**Similar Readings**

**How to Do Intercompany Reconciliation in Excel (2 Easy Methods)****Automation of Bank Reconciliation with Excel Macros****How to Reconcile Vendor Statements in Excel (2 Easy Methods)**

### Step-04: Format the Table

To make the **Table** easy to read, we will apply some** Formatting** to it.

- To start, select the row of
**RCC**. - Next, click on
**Fill Color**.

- At this point, select a
**Fill Color**according to your choice. - Here, we have chosen a shade of
**Green**.

- After selecting the
**Fill Color**, the selected row will become colorful like the image below.

- Following this, repeat the same action to change the
**Fill Color**of the rest of the rows. - Thereby, the
**Material Reconciliation Format**will look like the picture below.

- You can also change the
**Font Color**and**Fill Color**of the other headings. - In that case, you can follow how we did this in the given snapshot.

- Following this, you can give a
**Thick Border**to make the**Difference**part more prominent as this is the most essential part of the**Format**. The visitor normally checks this. - Earlier, select the
**19th Row**which is**Difference**. - Then, click on the
**BorderÂ****icon.**

- Then, select
**Thick Outside Borders**from the options.

- Eventually, you can see a
**Thick Border**outside the row.

- Presently, change the
**Font Color**of the**DifferenceÂ**row. - First, select the entire row.
- Then, click on the
**Font Color**. - Finally, select a color of your own choice.

- Finally, we have completed the
**Material Reconciliation Format**in Excel.

**Read More: ****How to Reconcile Two Sets of Data in Excel (9 Simple Ways)**

### Step-05: Prepare a Reconciliation Report

After analyzing the** Material Reconciliation**, you must prepare a **Reconciliation**** Report** for the higher authority. Here is an example of the report of the mentioned Project.

## Things to Remember

- Be careful about entering the
**Values**in the**Table**. - Donâ€™t apply too much
**Formatting**that it would be tedious to read. - Try to summarize everything in a sheet so that the reader can read the values at a glance.

## Practice Section

Here, we have provided a practice sheet for you to practice.

## Conclusion

In this article, we have tried to give you an idea about creating a **Material Reconciliation Format **in **Excel**. What we have given is an example. You may have to create the format with different data and materials. So, try to follow the steps and customize your **Material Reconciliation Format **according to your demand. If you face any problems regarding this article, please comment so that we can help.