We've picked three common computing problems and present to you, in three successive blog posts, how to solve each of these problems in the Alteryx Designer can tackle. Today it's the turn of blog entry #1, in which we take a look at Excel.

One thing is clear: Excel is a great tool for data processing. Accordingly, Excel can be found everywhere. And where Excel is widely used, a lot is done by hand. Spreadsheets and copypaste go hand in hand. Modern businesses wield a slightly finer blade and automate tasks that are a) pretty boring and b) consist largely of CTRL+C and CTRL+V. This leaves workers' heads free for the really exciting tasks - copying data from A to B is not one of them.

Use case: Reading in several Excel files and processing them together

A concrete example: Let's say you have been assigned the task of calculating the average energy consumption across different production sites every month. The data is extracted by your colleagues in production and sent to you as an Excel file:

Unfortunately, each of the files contains five sheets:

As a skilled Excel warrior, this is of course no problem. After half a day's work, you proudly present this one key figure, which was actually the goal of your task. And that's exactly the point: the metric and the associated analysis become a minor matter.

In Alteryx Designer, the whole thing is a bit simpler and looks like this from start to finish:

At first glance, this may look more complicated than it is. In the following, we will explain the individual steps in more detail.

 Step 1

In the input tool, the file path is specified to read in the Excel files.

 Step 2

In the macro tool, the same step can be repeated as often as desired. Here it is configured so that Alteryx reads in an Excel file and imports the data sheet by sheet. Afterwards the macro continues with the next file and processes one sheet after the other. Instead of five files with five sheets each, we finally have only one table in Alteryx that contains all the data:

Step 3

In the Dynamic Rename tool, the reference table is joined:

This will automatically rename the column names. Even if the order of the columns should be different or other names should occur in the next Excel file.

Step 4

The RegEx tool cleans data in no time. In this example, it removes various interfering characters in the third column. the potential of this tool is huge: RegEx allows data cleansing of long strings, extraction of certain strings from complex XML structures and much more.

Step 5

The Select tool puts the columns in the desired order and renames them if desired. The tool can also be used to change the data format (e.g. from numeric data to string data):

Step 6

The Summarize tool also offers a wide range of possibilities. In this case it calculates the minimum, maximum and average of the energy consumption column:

Step 7

The output tool exports the data in the desired format to the desired location, for example in the Excel format xlsx. Done!

Bonus! Once created - done forever

Of course, all these steps could have been done in Excel as well - but in Alteryx the whole thing is much faster, more relaxed, with more functionalities, with documented traceability, with the possibility of inserting work steps as well as significantly less error-proneness. In addition: Alteryx offers the possibility of automation. With just a few clicks, you can determine when and how often the workflow should be calculated:

That means as long as the source data is in the desired folder, this work is done in the future - no more CTRL+C, no more CTRL+V, no more search & replace - and you can devote yourself to the important tasks.

And, could we arouse your interest in Alteryx? If yes, you can send us a free trial version and try the tool out for yourself. And if you have any questions, we'll be happy to show you what Alteryx can do.