fbpx
Search
Close this search box.

Kill NULLs – a Simple macro to save time when importing data from SQL Server

Share

Facebook
Twitter
LinkedIn

As part of a my ongoing consulting gig, I often run painfully long queries on SQL Server to fetch data. This data obviously ends up in Excel for further analysis. Now, some of these queries return NULL values in several columns (did I tell you that the queries have a gazillion left joins on them, oh yeah, they do). Although technically NULL is nothing, when you import this data to Excel, we get the text value NULL in the cells. And I don’t need these NULL values messing up all the calculations and pivots.

SNAG-0077

Of course, we can go ahead and use the isnull() SQL function to deal with them at the query level. But since the queries have 100s of columns and used by various teams for different purposes, changing them causes a lot of pain. So I did what any sensible Excel user would do. Just kill those NULLs mercilessly once they are in Excel.

How to get rid of all NULLs?

Simple. Find replace. Just press CTRL+H and enter NULL as find value, replace with nothing, check “Match entire cell contents” option and viola. NULLs are gone.

Of course, doing this NULL Kill find replace can quickly get tiring and dull. So I went ahead and wrote a one line macro that does this and stuck this macro on the quick access toolbar. Now, whenever there is some new query data, I just press this button, play swoooosh sound in my mind and smile.

Here is the macro, incase you deal with the same problem everyday.


Sub killNull()
    Dim rng As Range
    
    Set rng = ActiveCell.CurrentRegion
    
    rng.Replace "NULL", "", LookAt:=xlWhole
End Sub

Here is instruction on how to add this macro to your personal macros workbook and how to add it to QAT or Ribbon.

Check out more short & sweet macros to save time.

Dirty data distressing daily?

If you deal with dirty data, please share examples of your problems in the comments. I am always looking for new material / ideas to discuss on the blog. Alternatively, if you have a smart way to deal with dirty data, post it in the comments. I am always looking for things to learn.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

11 Responses to “Kill NULLs – a Simple macro to save time when importing data from SQL Server”

  1. Duncan says:

    I know this doesn't apply to your SQL story but for dirty data Flash Fill is a gift for 2016. Try it: it even learns from its own mistakrs.

  2. james says:

    it would be nice if it also created a mapped matrix of null value coordate locations within the dataset so as not to lose the information which could also be revelant to some forms of analysis

  3. Sarah says:

    A common dirty data issue I have is a Crystal format report. It looks pretty, with data grouped by person, location, etc. But it is nowhere near the flat file format I need for analytics.

    Luckily excel can help here to! Indirect, Offset, and Vlookups are helpful here.

  4. Thomas Wäsbom says:

    Two things i always have to do when importing data from our sql server to Excel:
    Adjusting the date format which comes as yyyy-mm-dd hh-mm-ss.xxx
    Have to remove the .xxx part in order to work with dates.
    Number two is that Excel treat our unit serial number as number, (ex 54025552 00256). Need to have it as text.

  5. Jason Owens says:

    Why not simply ISNULL on the server side?

  6. Amarnath says:

    Simple Yet Powerful Weapon to Kill all the Dirty Stuffs...

  7. minimalist says:

    I want to replace all "*" charcacters with "s" and all ".." with "dd" in my worksheet "PCR Plate" and include this code in an existing macro.

    I tried:

    Sheets("PCR Plate").Select

    Dim rng As Range
    Set rng = activeCell.CurrentRegion

    rng.replace "~*", "s", LookAt:=xlWhole
    rng.replace "..", "dd", LookAt:=xlWhole

    This does not replace anything. What is the proper syntax?

    Thank you!

    • Kuldeep Mishra says:

      Hi Mini,

      Use below macro..

      Sub test()
      Dim rng As Range
      Set rng = ActiveSheet.UsedRange
      With rng
      .Replace "~*", ""
      .Replace "..", "dd"
      End With

      End Sub
      In your case activesheet is PCR PLATE.

  8. Walt says:

    If you looking for a non VBA solution, connect power query to database then filter out or replace null values before loading the data into Excel.

  9. Parag D says:

    I have a mastersheet which is CSV file having 1000000 of records and I want to split it into multiple workbook (xlsx) so that I can share it with customers separately. Records must be 50000 per workbook and all workbooks must be saved at the same path where the original master sheet is present.

    It would be a great help if you can provide a solution for this.

    Thanks in advance!

Leave a Reply