How to Make a Bar Graph in Excel without Numbers

Get FREE Advanced Excel Exercises with Solutions!

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.

Dataset-How to Make a Bar Graph in Excel without Numbers

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.

COUNTIF Function-How to Make a Bar Graph in Excel without Numbers

Step 2: Press ENTER, then drag the Fill Handle to apply the formula to other cells.

Fill Handle

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.

Bar Chart Insertion

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

Bar Chart

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.

COUNTIF and COUNTA Function-How to Make a Bar Graph in Excel without Numbers

Step 2: Follow Step 3 of method 1 to insert a bar chart. The Chart will look like the screenshot below.

Bar Chart

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.

Pivot Table-How to Make a Bar Graph in Excel without Numbers

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.

Pivot Table window

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.

Pivot Table Format window

🔺 In case, you tick the Refund field, the field placements will be as follows.

Refund-Pivot Table Format window


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.

Value Fields Settings

Step 2: In the Value Field Settings window, select the Summarize Value By section > choose Count > Click OK.

Summarize value field by Count


🔺 Repeat Step 3 of method 1. The bar graph representation of the refund reason looks similar to the following image.

Bar graph-Refund Reason

🔺 Alternately, for refund status, the bar graph looks like the picture below.

Bar Graph-Refund

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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Islam
Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo