How to Create Petty Cash Book Format in Excel (With Easy Steps)

Microsoft Excel is a powerful software. We can perform numerous operations on our datasets using excel tools and features. There are many default Excel functions that we can use to create formulas. Many educational institutions and business companies use excel files to store valuable data. Almost all business organizations keep their monetary transaction records in excel worksheets. But creating the excel template with the required formats can be a complicated task for someone who isn’t used to excel. This article will show you the step-by-step procedures to create a Petty Cash Book Format in Excel.

What Is Petty Cash Book?

Petty means something which is of little value. Big organizations have cash books where they record their revenues and expenditures. The major transactions occur through cheques. However, there are sundry expenses that need to be paid via cash only. The little amounts are not ideal to go with cheques. We also need to track those dealings in cash books. But, recording them in the main cash book is a burden and requires time and labor. That’s when the petty cash book comes to the rescue. We can keep the frequent, quick, and small payments recorded in the petty cash book.

How to Create Petty Cash Book Format in Excel: With Easy Steps

In this article, we’ll create an analytical petty cash book format in an excel worksheet. For this purpose, we’ll prepare an excel template. We can create a simple petty cash book where we’ll only insert the received and payment amounts. And we can also build an analytical cash book to elaborate on the expenditures. Therefore, go through the steps below carefully to carry out the operation.

STEP 1: Design Petty Cash Book

  • First, we need to input the required headers for our cash book.
  • In the Received column, we’ll insert the initial amount the petty cashier will get to expend on small expenses.
  • Then, we have a Date and Group.
  • The Group is about the payment type, which are Postage, Cartage, and Miscellaneous. They are also column headers.
  • After that, another header is Total Payment on the specific date.
  • In this way, you can create other desired headers as per requirement.

petty cash book format in excel

Read More: How to Create Daily Cash Book Format in Excel

STEP 2: Input Received Amount & Date

  • Now, we’ll input the received amount and the date associated with it.
  • See the below picture to understand clearly.

Input Received Amount & Date

NOTE: Notice that the Received column has Accounting number format and Date has Date format in excel.

STEP 3: Fill up Group

Here, we have 3 types of expenses. In this regard, we’ll create a drop-down list from the Group section from which you can choose instead of typing. So, follow the below process to perform the task.

  • Firstly, select the range D5:D12.
  • Then, go to Data > Data Tools > Data Validation.

Fill up Group

  • As a result, the Data Validation dialog box will pop out.
  • There, choose Allow > List.
  • Next, select the range F4:H4 in Source.
  • Press OK.

data validation list to petty cash book

  • Thus, you’ll get a drop-down symbol beside every cell in the Group column.
  • Hence, you can click your desired type from there.
  • Look at the following figure to have a better understanding.

output of data validation in petty cash book

STEP 4: Insert Total Payment

  • In this step, we’ll have to input the total payment amount manually.
  • For instance, see the below dataset where we have placed our payments.

Insert Total Payment

STEP 5: Create Formula for Postage

  • First of all, click cell F5.
  • Afterward, type the formula:
  • Subsequently, press Enter and apply AutoFill.
  • The IF function tests whether the D5 cell value is the same as the F4 cell value.
  • In such a way, the total payment amount in E5 for the postage group will be placed in this column automatically.
  • And otherwise, it’ll be a blank cell.

Create Formula for Postage

STEP 6: Apply Formula to Get Cartage

  • Choose cell G5 at first.
  • Input the formula:
  • Next, hit Enter.
  • Accordingly, use AutoFill to complete the series.
  • Thus, it’ll return the payment amounts for cartage.

Apply Formula to Get Cartage

STEP 7: Generate Formula for Miscellaneous

  • In cell H5, insert the formula:
  • Then, press Enter and apply AutoFill to get other outcomes.
  • Hence, you’ll get expenses made for miscellaneous.

Generate Formula for Miscellaneous

STEP 8: Calculate Total

  • Now, select cell B13 and the range E13:H13 at the same time by pressing the Ctrl key.
  • Next, use the AutoSum feature in excel to get the total of Received, Total Payment, Postage, Cartage, and Miscellaneous.
  • See the following picture for the result.

Calculate Total

STEP 9: Find Present Balance

  • Accordingly, we’ll need to determine the balance.
  • For that purpose, select cell F15.
  • Type the formula:
  • Click Enter.
  • Thus, you’ll get the net balance.

Find Present Balance

STEP 10: Apply Conditional Formatting

Moreover, it’s practical to highlight the net balance whenever we’re running low. It’ll alert us and we can take necessary actions. In this step, we’ll fill the net balance in red color whenever it’s below $500. Therefore, learn the following process.

  • First, select cell F15.
  • Then, click Home > Conditional Formatting > Highlight Cells Rules > Less than.

Apply Conditional Formatting

  • In the pop-out dialog box, input 500 and choose Light Red Fill or any other options according to your preference.
  • Consequently, press OK.

apply conditional formatting rule to petty cash book

  • Thus, whenever the balance falls below $500, it’ll be filled in red as demonstrated below.
  • Finally, the petty cash book format is ready for demonstration in excel.

output of conditional formatting in petty cash book

Download Practice Workbook

Download the following workbook to practice by yourself.


Henceforth, you will be able to create a Petty Cash Book Format in Excel following the above-described steps. Keep using them and let us know if you have more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

Related Articles

<< Go Back to Excel Cash Book Templates | Accounting Templates | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!
Aung Shine
Aung Shine

Aung Shine completed his bachelor’s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF