In most cases, users deal with non-numeric data and want to make a Bar Graph in Excel without numbers. Counting unique entries using COUNTIF or COUNTA and Pivot Table allows users to insert a Bar Graph with non-numeric entries.
Let’s say we have certain products that are returned for various reasons. Along with the reasons, we have the product’s Refund Status set to Yes or No. Now, we want to make a Bar Graph using this data.
In this article, we demonstrate ways to count unique entries using the COUNTIF, COUNTA functions, and PivotTable, then use the counting to insert a Bar Chart in Excel.
How to Make a Bar Graph in Excel without Numbers: 3 Easy Methods
Use any of the below methods to make a Bar Chart in Excel without numbers.
Method 1: Applying COUNTIF Function to Make a Bar Graph in Excel without NumbersÂ
The COUNTIF function returns the occurrences within a range. The syntax of the function is COUNTIF (range, criteria). We can use the function to count all the unique reasons and then use the data to insert a bar chart.
Step 1: Type the following formula in cell G4.
=COUNTIF(C:C,F4)
In the formula, C:C is the range and F4 is the criteria.
Step 2: Press ENTER, then drag the Fill Handle to apply the formula to other cells.
Step 3: Afterward, highlight the range, go to the Insert tab > Click on Insert Column or Bar Chart (in the Charts section) > Click on any Bar Chart type.
🔺 Excel inserts a Bar Chart instantly. Furnish the Chart according to your needs, and in the end, it may have a similar depiction to the image below.
Read More: How to Make a Simple Bar Graph in Excel
Method 2: Combining Functions to Make a Bar Graph in Excel without Numbers
As an alternative to method 1, users can find the Percentage of Refund Status (i.e., Yes or No). A combination of COUNTIF and COUNTA functions results in percentages.
Step 1: Apply the below formula to the G4 cell. Use the Fill Handle to execute it for other cells.
=COUNTIF($D:$D,F4)/(COUNTA($D:$D)-1)
Here the COUNTIF function returns the Refund Status occurrences and COUNTA the non-numeric entries except the column header.
Step 2: Follow Step 3 of method 1 to insert a bar chart. The Chart will look like the screenshot below.
Read More:Â How to Create a Bar Chart in Excel with Multiple Bars
Method 3: Using PivotTable to Create a Bar Graph Using Non-numerical Data
What if users don’t want to use any function to create a bar graph with non-numerical data? The Excel Pivot Table automatically counts each field and displays them as directed.
Step 1: Select the range, then go to Insert > Click PivotTable (in the Tables section) > Click From Table/Range.
Step 2: Excel brings up the PivotTable from table or range dialog box. Mark New Worksheet as Choose where you want the PivotTable to be placed > Click OK.
Step 3: Excel brings up the PivotTable Fields side window. Tick Refund Reason or Refund under Choose fields to add to report > Place the fields as depicted in the picture below.
🔺 In case, you tick the Refund field, the field placements will be as follows.
â§ Make sure you select the Count operation in the Values area. To display the Count in Values results, go through the below steps.
Step 1: Click on the Arrow Sign beside the existing Values fields. A Menu appears > Click on Value Field Settings.
Step 2: In the Value Field Settings window, select the Summarize Value By section > choose Count > Click OK.
🔺 Repeat Step 3 of method 1. The bar graph representation of the refund reason looks similar to the following image.
🔺 Alternately, for refund status, the bar graph looks like the picture below.
Read More: How to Make a Grouped Bar Chart in Excel
Download Excel Workbook
Conclusion
This article demonstrates the usage of COUNTIF, COUNTA, and PivotTable to make a bar graph in Excel without numbers. We hope these above-mentioned methods help you achieve your desired result. Comment if you have further inquiries or have anything to add.