Since I have been using Excel for years, I have found it to be very helpful in terms of cleaning and transforming data. In contrast, when I got a chance to learn Alteryx, my views changed entirely. I realize we can automate many repetitive processes without tedious VBA, most importantly without copying and pasting.
To demonstrate Alteryx’s capabilities, I will compare it with how Excel performs these functions, so readers of this blog can get a sense of how powerful, versatile, and user-friendly Alteryx is. Alteryx contains a vast library with over 200 different tools to take on a variety of datasets.
– VLOOKUP vs JOIN
For illustration, I will use the below dataset. One sheet contains clients’ information and another sheet contains account status information for each client. Let’s suppose I want to see the account status of each customer.
VLOOKUP and INDEX/MATCH functions would be used in excel to find the account status of the customer. If I want to see the records that do not match or missing. I would likely have to run another VLOOKUP on account status to figure out which records are missing leading to the repetition of the process.
In Alteryx JOIN is the alternate. Which enables easy recognition of match and nonmatch results without repeating the process.
Observing the circles in blue, we can see that 6 of our records matched, while 2 remained unmatched.
– UNION of Datasets
Consider I want to combine below two datasets into one:
The only way I know to achieve this in Excel is to copy data from the second sheet and paste it at the bottom of the first sheet. During this process, I need to be sure that I am copying and pasting data into the correct fields. While it might be practical to combine small datasets. But imagine combining two datasets with over a million rows and over fifty columns. Well, my solution to this is Alteryx (UNION tool).
We can see the output of the Union tool with 8 records (circled red).On the configuration side (circled blue) we can see fields of each sheet matched with each other.
– Data Cleansing
Take the example of dirty data with plenty of white spaces in a string, empty cells for numeric values, null rows, and strings with incorrect cases. Excel requires formulas to address these shortcomings. Which is sometimes an onerous job.
While Alteryx has a powerful tool (CLEANSING tool) to resolve all these shortcomings without writing any formulas.
– Finding Unique or Duplicate Records
We often use remove duplicates 
or conditional formatting to find unique or duplicate records in Excel. But it would be very hard if we want to see them separately. While in Alteryx it’s very easy to achieve with its UNIQUE tool.
It can be seen above dataset has 6 unique (circled blue) and 2 duplicate (circled red) records.
– Visual Work Flow
It is very difficult to keep track of the steps taken for data cleansing and transformation in Excel. If you want to change something in earlier steps, you might end up repeating the whole process. Alternately, Alteryx gives you full flexibility with its Visual Work Flow process, you can change or revert to any step, you can even annotate and label each step.
Alteryx has a clear edge over excel, so this blog will be a helpful overview to newbies who want to switch to Alteryx.