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.
Download Excel Workbook
3 Easy Methods to Make a Bar Graph in Excel without Numbers
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 Bar Graph in Excel with 4 Variables (with Easy Steps)
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 Combine Two Bar Graphs in Excel (5 Ways)
Similar Readings
- How to Make a Bar Graph in Excel with 2 Variables (3 Easy Ways)
- How to Add Vertical Line to Excel Bar Chart (3 Quick Ways)
- Excel Bar Chart Side by Side with Secondary Axis
- How to Show Percentage in Bar Chart in Excel (3 Handy Methods)
- Reverse Legend Order of Stacked Bar Chart in Excel (With Quick Steps)
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 Plot Stacked Bar Chart from Excel Pivot Table (2 Examples)
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.
Related Articles
- How to Make a Double Bar Graph in Excel (with Easy Steps)
- How to Create Clustered Stacked Bar Chart in Excel
- Excel Stacked Bar Chart with Subcategories (2 Examples)
- How to Change Bar Chart Color Based on Category in Excel (3 Methods)
- Excel Chart Bar Width Too Thin (2 Quick Solutions)
- How to Make a Diverging Stacked Bar Chart in Excel (with Easy Steps)