Imagine this: you’ve just pulled a fresh batch of data from Bloomberg into Excel. You’re excited. You’re thinking about trends, insights, and dazzling charts. And then—bam! You see cells filled with #N/A N/A. Ugh. It’s like ordering a sundae and getting an empty cup.

Don’t worry. We’re here to fix this.

We’ll show you how to filter out those pesky #N/A N/A values in a way that’s simple, fun, and Excel-magical.

🎯 What’s the Deal with #N/A N/A?

The #N/A means “not available.” Bloomberg leaves it in when data is missing. It’s not a bug. It’s just Excel’s way of saying, “Sorry, no data here.”

These errors can mess up your formulas, charts, and pivot tables. So let’s clean things up—fast.

🌟 Option 1: Use Filters the Smart Way

This one’s easy.

  1. Click on a cell in your dataset. Any cell will do.
  2. Go to the Data tab in the Ribbon.
  3. Click the Filter button. Little arrows will appear on each column header.
  4. Click an arrow on a column that might contain #N/A N/A.
  5. Uncheck the box next to #N/A N/A.
  6. Voilà! It’s gone.

Problem: Sometimes #N/A N/A doesn’t show up in filters because it’s treated as an error, not a value. That’s when magic trick #2 comes in.

🪄 Option 2: Use IFERROR to Hide It

If you’re pulling data using Bloomberg formulas like =BDP(), =BDH(), or =BDS(), errors might pop up when data points are missing.

To clean those up, wrap your formula in IFERROR and give it a gentle hug:

=IFERROR(BDP("IBM US Equity", "PX_LAST"), "")

This tells Excel: “If there’s an error, show nothing instead.”

You can also choose to display a zero or even the word “Missing”:

=IFERROR(BDP("IBM US Equity", "PX_LAST"), "Missing")

Now your spreadsheet looks way cleaner!

📚 Option 3: Use Conditional Formatting to Highlight or Hide

Sometimes you just want to see where the errors are. Use colors to train your eyes.

  1. Select the range of cells with Bloomberg data.
  2. Click Home → Conditional Formatting.
  3. Choose New Rule.
  4. Select Format only cells that contain.
  5. In the drop-down, choose Errors.
  6. Pick a fill color—maybe bright yellow or red.
  7. Click OK.

Now #N/A values light up like warning signs. You can filter or fix them as you wish.

🧹 Option 4: Remove Errors with Filter and Go To Special

Want to zap errors fast? This advanced trick will do the job.

  1. Select the range that might contain #N/A N/A.
  2. Press F5 (or Ctrl + G) to open the Go To window.
  3. Click Special.
  4. Select Formulas, then check only the Errors box.
  5. Click OK. Bam! All error cells are selected.
  6. You can press Delete or even add a formula to overwrite them.

Just be careful. This deletes all errors, not just #N/A N/A. Double-check your data!

🎯 Bonus: Use FILTER or SORT to Ignore Errors

If you’re on Microsoft 365 or Excel 2021, you have access to awesome new formulas like FILTER().

They ignore errors by design—if you ask nicely.

=FILTER(A2:A100, ISNUMBER(A2:A100))

This grabs only the numeric values and skips over anything weird—like #N/A N/A.

You can also try:

=SORT(FILTER(A2:A100, ISNUMBER(A2:A100)))

Clean and sorted. Just like your kitchen drawer (hopefully).

😎 Create a Cleaner Copy

Want to keep your original data untouched? Make a second clean version.

Copy columns with Bloomberg data into a new sheet.

Then use formulas like:

=IF(ISNA(A2), "", A2)

This one specifically targets #N/A errors and replaces them with an empty string.

Or try:

=IF(ISERROR(A2), "", A2)

This hides any error type, just in case.

⏳ Tip: Trap It at the Source

Don’t wait until your spreadsheet is a mess. Be proactive.

Before pulling in Bloomberg data, think about which fields might be unreliable. Skip them if you don’t need them.

Also remember Bloomberg functions like =BDP() can return #N/A N/A if

  • The security ticker is wrong
  • The field code is invalid
  • The market is closed

Double-check your formula inputs. It saves a lot of cleanup later.

✨ Quick Recap

  • Use IFERROR or IFNA to hide errors in formulas.
  • Apply filters to remove #N/A N/A values manually.
  • Use Conditional Formatting to highlight them.
  • Go To Special can select all error cells fast.
  • FILTER() and SORT() work wonders on error-free value sets.
  • Double-check Bloomberg formulas to avoid errors at the source.

🎉 Celebrate Your Clean Spreadsheet!

Say goodbye to confusing #N/A N/As and hello to beautiful, organized data.

You just leveled up your spreadsheet skills. Throw some confetti. You earned it!

Now go on—build that dashboard, impress your boss, and never fear Bloomberg errors again.

Happy filtering!