Horizontal bar chart may look appealing, but to make it may be troublesome. This article shows a step by step process to make a percentage horizontal bar chart in Excel.
- Consider there are two tables i.e. Income and Expense
- Select the data with headers and Press Ctrl + T. Do it for both Income and Expense.
-
- You need to give the table a Name. To do so follow these steps:
- Click on any cell in the table.
- Go to Table Tools in Ribbon then Click on the Design tab
- Type the name for Table for future reference to create the horizontal bar chart. Check the below image for reference
- Go to Formula tab
- Click on Name Manger
- Click on New Button
- Give it a name and formula. Refer the image below.
- You need to give the table a Name. To do so follow these steps:
- Now you need to create a formula in Name Manger under Formula Tab. Here are the steps to do that:
- Preparation work done, here are the final steps to make percentage bar chart:
- Select a cell on a worksheet where you want the horizontal bar chart to be placed
- Type =TotalMonthlyExpenses in the selected cell
- Go to Conditional Formatting tab, click on Manage Rules.
-
-
- Create a New Rule
- Fill all the details as mentioned below (Refer below image for more clarity.)
-
-
-
- i.Select the Rule Type as Format all cells based on their values
-
- ii.In Edit the Rule Description section:
- Select Data Bar in Format Style dropdown
- Tick box for show Bar only to hide values displayed in Bar
- Select Type as Number for Minimum and Maximum columns
- For Value, Select 0 for Minimum and write formula =TotalMonthlyIncome for Maximum
- For Bar Appearance, select a fill color and border as per your preference.
- Click Ok to complete the process.
- To get the percentage on right side, write this formula =TotalMonthlyExpenses/TotalMonthlyIncome in the cell next to bar chart.
