fbpx

Learn how to create a horizontal bar chart in Excel

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.

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

      Learn how to create a horizontal bar chart in Excel

    2. Go to Formula tab
    3. Click on Name Manger
    4. Click on New Button
    5. Give it a name and formula. Refer the image below.
      excel-monthly-income
  1. Now you need to create a formula in Name Manger under Formula Tab. Here are the steps to do that:
  2. Preparation work done, here are the final steps to make percentage bar chart:
    1. Select a cell on a worksheet where you want the horizontal bar chart to be placed
    2. Type =TotalMonthlyExpenses in the selected cell
    3. Go to Conditional Formatting tab, click on Manage Rules.

    1. Create a New Rule
    2. 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
  1. 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.

    Learn how to create a horizontal bar chart in Excel

  2. To get the percentage on right side, write this formula =TotalMonthlyExpenses/TotalMonthlyIncome in the cell next to bar chart.

Leave a Reply