This Kakeibo uses pivot tables that can aggregate and analyze large amounts of data.
It is recommended for those who want to analyze data thoroughly, as it allows analysis from various perspectives, such as by expense item, by account, or by store.
Recommended type
- People who want to manage their accounts.
- People who want to know the monthly consumption amount by store.
- People who want to manage their consumption by product or by specific item name.
Features of a Kakeibo
- Enter expense, account, and store names on the Setup sheet
- Record daily household accounts on the Input sheet
- Compare monthly household accounts on the Table sheet and the Graph sheet
- Pick up only the information you want to know by expense, account, store, etc.
Setup sheet
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-17-9.20.40-1024x266.png)
Input sheet
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-19-11.38.39-1024x679.jpg)
Table sheet
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-19-11.52.32-1024x993.jpg)
Graph sheet
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-21-11.53.56-1024x608.jpg)
Specific time period Show only items
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-21-9.33.28-1024x452.jpg)
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-21-11.33.47-1024x606.jpg)
How to create a Kakeibo
Setup Sheet
Naming the file
Open and name a new Excel file.
Select “Excel Book (.xlsx)” as the file type.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-16-10.58.57-1024x640.jpg)
What to do first
- Change the sheet name to “Settings” in the lower left corner.
- Remove the grid lines.
Select the entire sheet by clicking on the triangle in the upper left corner.
Uncheck the Gridlines.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-16-11.01.46-1024x664.png)
Setting Up Item
- Income
- Taxes
- Savings
- Fixed Expenses
- Variable expenses
- Account
- Store Name
Enter the name of the income and expense item, store name, and account on the second line.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-16-14.52.38-1024x210.png)
Setting up the framework
Select cell B2 and choose Insert, Table.
The range is B2, check “My table has headers”.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-16-14.56.22-1024x418.jpg)
It is automatically framed and colored.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-16-14.59.50.png)
Set other items in the same way.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-16-14.58.03-1024x316.png)
Set up the design
Select the table and click on “Table” at the top.
Select a design.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-16-15.10.14-1024x548.jpg)
Increase the width of a column.
Select a column, right-click, and choose “Column Width".
Enter a numerical value.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-16-15.11.15-1024x633.png)
Entering sample names
Enter a sample name.
A frame is automatically added each time you enter a name.
There is no limit to the number of frames.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-17-9.20.40-1024x266.png)
What to do if a frame is not added automatically
In Microsoft
Options > Text Editing > AutoCorrect Options > Input AutoFormat
Check Include new rows and columns in table
On a Mac
Preferences > Tables and Filters > Check “Automatically expand tables
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-17-9.25.23-1024x729.png)
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-17-9.25.47-1024x456.png)
Give a name
Name all expenses as “Income” for income expense names, “Tax” for tax expense names, and so on.
Named items are used in the expense setup on the input sheet.
Target Item
- Income
- Taxes
- Savings
- Fixed Expenses
- Variable expenses
- Account
- Store Name
Click on the formula and "Define Name".
For the name, enter “Income”.
To see next, select a range of income names.
Name must match the name on the second line.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-17-9.40.33-1024x406.jpg)
To see the named range, click on “Name Manager” in the formula.
The range is automatically extended when you add an expense item name.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-17-9.45.18-1024x633.jpg)
Input sheet
Refer to “What to do first” on the setup sheet to enter the sheet name and remove the grid lines.
Create Heading Row
Create a heading on the second line.
- Date: Enter the date
- Items: select from Income, Taxes, Savings, Fixed Expenses, and Variable Expenses
- Category: Display the expense name for each item
- Item name: Enter manually
- Amount: Enter manually
- Store name: display the name of the store you set up
- Account: Display the name of the account you set up
- Remarks: Manually input
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-17-10.21.04-1024x162.png)
Create Table
Select the heading and choose “Insert” “Table”.
Check the “My table has headers” checkbox.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-17-10.22.53-1024x390.jpg)
Table has been created.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-17-10.23.04-1024x173.png)
Date Setup
Set the date display format.
Right-click on cell B3 and select “Format Cells”.
Select Number, Date, Type as “Year/Month/Day” format.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-17-10.30.49-1024x993.png)
Set Item
Set tabs to allow selection from Income, Taxes, Savings, Fixed Expenses, and Variable Expenses.
Select cell C3 and click on “Input Regulations” for the data.
For Allow, select “List” from the tab.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-17-15.04.42-1024x584.jpg)
In "the original value"Source, enter “Income, Taxes, Savings, FixedExpenses, VariableExpenses”.
Tabs are now set up to allow selection of item names.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-17-15.07.12-1024x293.png)
The item name must match the name on the configuration sheet.
Setting up Expenses
Expense item names are listed using the INDIRECT function.
The INDIRECT function is a function that allows you to specify cell references as strings.
By using the INDIRECT function, the expense names are listed for the item name selected in the item.
Click on cell D3 and click on the "data validation".
Enter “list” for the "Allow "and “=INDIRECT(C3)” for Source.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-17-15.12.49-1024x565.jpg)
If the item name is a fixed cost, the fixed cost expense name will be displayed in the expense line item.
If the item is not named on the setup sheet, it will not be displayed.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-17-15.13.05-1024x372.png)
Adding currency units to amount columns
Right-click on the amount cell F3 and select “Format Cells”.
Click the Number, Currency, select your local currency unit.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-17-15.29.50-1024x995.png)
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-17-15.30.08-1024x243.png)
Set tabs for store name and account
Set tabs for the store name and account cells.
Select cell G3 and click on Data Validation.
For Allow, select list, for Source, enter "=StoreName".
In the Account cell, enter “=Account”.
Enter the name you named in the settings sheet.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-18-10.46.55-1024x485.jpg)
The information entered on the setup sheet will be reflected.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-18-10.47.55-1024x353.png)
Fix heading rows
Fixing the second line and moving only the subsequent lines makes daily input easier.
Select the third row, view, and click “Freeze Panes.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-18-11.00.16-1024x291.png)
When scrolling down, the heading line is fixed and only subsequent lines are movable.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-18-11.00.28-1024x253.png)
Select a design
Select the design of your choice from “Table.”
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-18-11.07.42-1024x547.jpg)
Enter sample values
Enter sample values to create the Income and Expenses sheet.
Enter sample values after creating the Fixed Expenses sheet.
Enter the date as month/day, e.g. “10/5”.
Enter expense items with a minus sign.
(To display income/expenses (income - expenses) on the income/expenses sheet)
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-19-11.38.39-1024x679.jpg)
Fixed Cost Sheet
To make inputting as easy as possible, a fixed cost sheet is provided.
By entering all items other than variable expenses in this field, you can copy and paste the ranges from the fixed cost sheet into the input sheet each month.
Copy input sheet
Right-click on the sheet name to copy the sheet.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-18-11.17.13.png)
Once you have made a copy, change the sheet name.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-18-11.17.43.png)
Enter Sample Values
Enter the items that will be required each month except for variable expenses.Leave the date blank.
Enter expense items with a minus sign
To show income/expenses (income minus expenses) in the income/expenses table.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-19-11.31.04-1024x380.png)
Drag the bottom right table edge down to expand the table.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-18-11.28.53-1024x341.png)
Paste to input sheet
Paste the range from the fixed cost sheet into the input sheet at the time of the month change.
Copy the range after the heading.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-19-11.31.27-1024x419.png)
Paste to the line following the last line of input on the input sheet.
Right click and paste only the values.
If you paste everything, the tabs for expenses will disappear.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-19-11.37.48-1024x648.jpg)
After pasting on the input sheet, enter the date and correct the amount if necessary.
Table sheet
Create tables by month and by expense category based on the information entered on the input sheet.
Create Pivot Table
Select the input range of the input sheet (including headings) and click on “Pivot Table” under Insert.
Check “New Worksheet” for the destination of the pivot table.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-19-11.42.51-1024x600.jpg)
A new sheet will appear with the pivot table fields.
Change the name of the sheet to something easy to understand, such as “Table" .
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-19-11.43.33-1024x601.jpg)
In the Pivot Table fields shown to the right, select the columns, rows, and values for the table.
- Column: Date
- Row: Item, Category
- Value: Amount
Select the field name and drag it into the "Columns", "Rows", and "Values".
Dates are grouped by Years, and Quarters.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-19-11.47.38-1024x602.jpg)
A table has been created.
We want to display the year and month, so we group the month entries.
Right-click on any month and select “Grouping”.
Select “Year” and “Month” and click “OK.
Click on the minus to the left of 2024, click on Qtr4 to see the month.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-19-11.48.15-1024x857.jpg)
Reorder items.
Click on the item you want to reorder and move it up or down.
(You can move it up or down when the hand symbol appears.)
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-19-11.52.14-1011x1024.jpg)
Creating a Timeline
Add a timeline to display only a specific month or year.
Insert about 10 rows above the table.
Click on “Insert Timeline” in Pivot Table Analyze.
Check "the dates"Date" and click “OK.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-19-16.05.34-1024x774.jpg)
The timeline is now displayed.
You can view only a specific month or year.
To undo, click the X in the upper right corner.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-19-16.06.31-640x1024.jpg)
Click on the month view to change it to “YEARES” or “QUARTERS".
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-19-16.06.42-1024x738.png)
Creating a Slicer
To display only specific items, display the slicer.
Click on “Insert Slicer” in the Pivot Table Analyze.
Check the items you want to display.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-21-9.29.37-1024x598.jpg)
The slicer for the checked item will be displayed.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-21-9.34.06-1024x824.jpg)
For example, if you want to view the consumption amount for “Credit1”, click on “Credit1” in the account.
Only the consumption amount for “Credit1” will be displayed.
To undo the change, click on the cross in the upper right corner of the slicer.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-21-9.33.28-1024x452.jpg)
Design Selection
Pivot Table Design
Select “Report Layout” under Design.
- Compact form: Display items and expenses in one column
- Outline form: Display items and expenses in two columns
- Table form
You can choose from three types of formats.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-21-9.49.40-1024x695.jpg)
Please select your favorite design from the Design.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-21-9.50.03-1024x722.jpg)
Timeline Design
Select your preferred design from the Timeline.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-21-9.50.34-1024x490.png)
Slicer Designs
Select your favorite design from the Slicer.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-21-9.51.09-1024x391.jpg)
Once the design has been changed, the table sheet is complete.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-21-10.06.41-1024x674.jpg)
Graph Sheet
Create a graph based on the table.
If you create a graph based on the table in the table sheet, a graph by expense category will be created.
We want to create a graph by month, so we create a new graph sheet.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-21-10.12.54-1024x610.jpg)
Copy Sheet
Copy a sheet from the table sheet.
See “Copying a sheet” in the Fixed Expenses sheet.
Alternatively, you can copy a sheet by holding down the Ctrl button and moving the table sheet to the right.
(Mac: “option” button)
Rename the copied sheet.
Create Graph
Click on a portion of the pivot table on the graph sheet.
Select “Pivot Chart” in PivotTable Analyze.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-21-10.18.17-1024x578.jpg)
Click on a portion of the pivot table and select “Switch Row/Column” in Design.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-21-10.23.02-1024x568.jpg)
Graphs are displayed by month.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-21-10.23.48-1024x529.jpg)
Change the bar graph to a stacked bar graph.
Select Column, Accumulating Bar from Change Chart Type.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-21-10.26.02-1024x529.jpg)
To display the graph above the table, insert a row above the table and position it so that the graph is above the table.
Select Design
Click on “Design” and select the design of the graph.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-21-10.58.59-1024x649.jpg)
Add Data Table
Select “Add Graph Element” under Design.
Click on “with legend markers” in the data table.
A data table appears below the graph.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-21-11.00.05-1024x599.jpg)
Select Graph Color
Select the graph color from “Change Color” in Design.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-21-11.23.47-1024x629.jpg)
To set the color by expense item, right-click on the bar chart and select “Format Data Series”.
Change the fill color.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-21-11.25.11-1024x603.jpg)
Select specific time period and items
Select a time period on the timeline.
You can use the slicer to display only specific items.
![](https://ari-mama.com/eng/wp-content/uploads/2024/10/スクリーンショット-2024-10-21-11.33.47-1024x606.jpg)
Download Samples
The file is downloaded in Zip format.
Please unzip it before use.
Two templates are included: a sample template and the original template.