Bar Chart - removing items with 0 values

joeino

New Member
Joined
Oct 28, 2020
Messages
15
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Hi again!

I am wondering if there is a way for me to remove the blank items in my chart? Currently using name range for the category (axis label) to remove categories with 0 values however it was replaced with empty or blank spaces.

Tried everything I know and running out of ideas. Help!

Book2.xlsx
BCDEFGHIJKLMNOPQRSTUVWXYZAA
20123456
32385704020100123456789101112
4Action TakenNo TicketSegment/ITIN IssueOthersDuplicate PNROther Segment IssueSVAN/CBBG/Extra SeatTicket RefundedFare Class IssueIncorrect Coupon StatusAgent ErrorName Issue#VALUE!No TicketSegment/ITIN IssueOthersDuplicate PNROther Segment IssueSVAN/CBBG/Extra SeatTicket RefundedFare Class IssueIncorrect Coupon StatusAgent ErrorName Issue
5Sent QQ Remarks2154304000100836Re-Associated Ticket00100000000
6PNR already actioned231000000000333Added RMRKS/NFAT00000020000
7Added RMRKS/NFAT00000020000211Corrected on ET00300000000
8HD Escalation0000000000002PNR already actioned231000000000
9MCC BO Escalation0000000000001Sent QQ Remarks2154304000100
10Re-Associated Ticket001000000001#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!
11Deleted Segment000000000000#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!
12Placed on Queue000000000000#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!
13Issued ticket000000000000#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!
14Others000000000000#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!
15Corrected on ET003000000003#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!
16Tagged as VARA000000000000#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!
17Emailed Crew Control000000000000#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!
Sheet1
Cell Formulas
RangeFormula
P4P4=OFFSET($P$5,0,0,MAX(1,COUNT($Q$5:$AA$17)),1)
N5:N17N5=SUM(C5:M5)
O5:O17O5=MATCH(SMALL($N$5:$N$17,COUNTIF($N$5:$N$17,0)+ROWS($N$5:N5)),$N$5:$N$17,0)
P5:P17P5=VLOOKUP(O5,$A$5:$B$17,2,0)
Q5:AA17Q5=VLOOKUP($P5,$B:$M,Q$3,0)
Named Ranges
NameRefers ToCells
test=OFFSET(Sheet1!$P$5,0,0,MAX(1,COUNT(Sheet1!$O$5:$O$17)),1)P4, Q5:AA5
 

Attachments

  • Untitled.png
    Untitled.png
    16.3 KB · Views: 1

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,059
Office Version
  1. 2016
Platform
  1. Windows
A simple solution is to make a pivot chart. With a filter on value>0 you hide the empty items.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,529
Messages
5,625,351
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top